From wiki.visual-prolog.com

Revision as of 13:00, 30 January 2020 by Thomas Linder Puls (talk | contribs) (large fields)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

Visual Prolog (Commercial Edition) has an ODBC package, which gives access to databases that have an ODBC driver. The distribution contains two ODBC examples (In IDE: Help->Install Examples...).

Here is a little background information about ODBC.

ODBC is an abstraction layer. (There are many abstraction layers in Windows, and the general principle is the same in all cases; It's a pattern).

The purpose of the layer is to provide uniform access to different databases.

Applications run on top of the layer. They access the layer through an Application Programming Interface (API).

Below the layer database vendors (and others) can hook-in ODBC drivers, through the Device Driver Interface (DDI).

The API/DDI pattern is an abstraction pattern that Microsoft uses in numerous contexts.

The SQL syntax you should use towards ODBC is defined by ODBC, so in principle there should be no differences in syntax: It is the responsibility of the device drivers to use this dialect of ODBC. The truth is a bit more complex, and you can find a more accurate description in Interoperability of SQL Statements.

In ODBC a parameter is "?" like in:

SELECT Name FROM Person WHERE Age > ?

Native Oracle SQL uses a different syntax for parameters, but Oracle ODBC drivers use the "?".

Microsoft has defined ODBC and developed MS SQL hand-in-hand, so not surprisingly, MS SQL follows the ODBC syntax very closely. MS Access is slightly less accurate, but in most work you will not feel any differences.

In any case the accuracy depends on the driver that you have. Most database vendors deliver ODBC drivers for their databases, but there are also third party companies that deliver ODBC drivers (e.g. DataDirect).

You can get Microsoft's ODBC drivers in the MDAC package.

Besides the obvious drivers Microsoft it also contains drivers for comma-separated text files and Excel.

The intention of this is not to use text files as a database, but to provide a simple way to read, write and otherwise manipulate comma separated text files.

The Excel driver has a similar purpose. But you must realize that the access is SQL oriented, not Excel oriented. So you can deal with date which is organised in a very special way in Excel, and thus not exploit the full features of Excel. And formulas and formatting is completely unknown in the ODBC access.

Large fields

In ODBC there are two alternative ways for fetch data from the server, either you allocate buffers into which the data will be placed before the data is fetched, or you request the data after a row has been fetched. The first approach requires much fewer client/server turnarounds and is thus more efficient. However if a field is potentially very large then it is a bad idea to allocate a buffer large enough. The Visual Prolog ODBC package uses a mixture of the two approaches, small fields are fetched into preallocated buffers but large fields are fetched "late" when the actual size of the specific instance of the field is known.

It is a know problem that some database servers/ODBC drivers have a restriction on mixing preallocated buffers with late buffers. The restriction is that the large fields must be the last fields fetched in the query.

So this is OK:

SELECT <small>, <small>, <small>, <large>, <large> FROM ...

But in this query a small field comes after a large one, and that can give an error:

SELECT <small>, <small>, <large>, <large>, <small> FROM ...