ODBC - Revision history

Thomas Linder Puls: large fields

2020-01-30T11:00:59Z

large fields

← Older revision		Revision as of 13:00, 30 January 2020
Line 31:		Line 31:

	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.		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:

			<source lang="sql">SELECT <small>, <small>, <small>, <large>, <large> FROM ... </source>

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

			<source lang="sql">SELECT <small>, <small>, <large>, <large>, <small> FROM ... </source>



	== References ==		== References ==

Elizabeth Safro: a grammar mistake fix

2009-06-18T13:56:17Z

a grammar mistake fix

← Older revision		Revision as of 15:56, 18 June 2009
Line 1:		Line 1:
	Visual Prolog (Commercial Edition) ~~have~~ an ODBC package, which gives access to databases that have an ODBC driver. The distribution contains two ODBC examples (In IDE: '''Help->Install Examples...''').		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.		Here is a little background information about ODBC.

Thomas Linder Puls: Categorized (and wikipedia link)

2008-10-29T20:53:35Z

Categorized (and wikipedia link)

← Older revision		Revision as of 22:53, 29 October 2008
Line 36:		Line 36:
	* [http://msdn2.microsoft.com/en-us/library/ms710252.aspx Microsoft Open Database Connectivity (ODBC)]		* [http://msdn2.microsoft.com/en-us/library/ms710252.aspx Microsoft Open Database Connectivity (ODBC)]
	* [http://msdn2.microsoft.com/en-us/data/aa937730.aspx MDAC Downloads]		* [http://msdn2.microsoft.com/en-us/data/aa937730.aspx MDAC Downloads]
			* [[wikipedia:Open Database Connectivity]]

	~~[[Category:PFC]]~~
	[[Category:ODBC]]		[[Category:ODBC]]

Thomas Linder Puls: Categorized (and formatting bug)

2008-10-29T20:49:00Z

Categorized (and formatting bug)

← Older revision		Revision as of 22:49, 29 October 2008
Line 16:		Line 16:

	In ODBC a parameter is "?" like in:		In ODBC a parameter is "?" like in:
	<source ~~language~~="sql">SELECT Name FROM Person WHERE Age > ?</source>		<source lang="sql">SELECT Name FROM Person WHERE Age > ?</source>

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

	[[Category:PFC]]		[[Category:PFC]]
			[[Category:ODBC]]

Thomas Linder Puls: categorized

2007-09-23T15:03:28Z

categorized

← Older revision		Revision as of 17:03, 23 September 2007
Line 36:		Line 36:
	* [http://msdn2.microsoft.com/en-us/library/ms710252.aspx Microsoft Open Database Connectivity (ODBC)]		* [http://msdn2.microsoft.com/en-us/library/ms710252.aspx Microsoft Open Database Connectivity (ODBC)]
	* [http://msdn2.microsoft.com/en-us/data/aa937730.aspx MDAC Downloads]		* [http://msdn2.microsoft.com/en-us/data/aa937730.aspx MDAC Downloads]

			[[Category:PFC]]

Thomas Linder Puls: Copied from "Tips, How To's, Code Samples, etc"

2007-09-23T14:49:39Z

Copied from "Tips, How To's, Code Samples, etc"

New page

Visual Prolog (Commercial Edition) have 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 [http://msdn2.microsoft.com/en-us/library/ms710989.aspx Interoperability of SQL Statements].

In ODBC a parameter is "?" like in:
<source language="sql">SELECT Name FROM Person WHERE Age > ?</source>

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. [http://www.datadirect.com/index.ssp DataDirect]).

You can get Microsoft's ODBC drivers in the [http://msdn2.microsoft.com/en-us/data/aa937730.aspx 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.

== References ==

* [http://msdn2.microsoft.com/en-us/library/ms710252.aspx Microsoft Open Database Connectivity (ODBC)]
* [http://msdn2.microsoft.com/en-us/data/aa937730.aspx MDAC Downloads]