Tim Haynes, OpenLink Software, Tue Jun 3 09:09:38 2003
An example of the API in action
ODBC Application Considerations
ODBC is a system for providing an abstraction on top of database access, transferring SQL and meta-data queries to the database and conveying the results back.
From the end-user perspective, one application can be used quickly, easily and uniformly against many different backend sources of data.
From the programmer's perspective, ODBC is a C-based API that saves time and effort writing applications. Instead of writing using native access methods for Oracle, Sybase, PostgreSQL etc, you write once against ODBC and point the data-source wherever you desire at run-time.
From the system administrator's perspective, it's just another small package to maintain.
Starting at the top, an ODBC application needs to invoke the Driver Manager component - normally this involves being linked, either dynamically or statically, against libodbc.so or libiodbc.so.
Because of the generic nature of ODBC, an ODBC application should not be written with any particular RDBMS product in mind for the backend.
The Driver Manager handles all the calls the application makes - such as "what data-sources do you have?", "connect to ThatDataSource", "execute the query `select * from ThatTable;'", and so on. For this reason, the application should be linked against libiodbc.so or libodbc.so, in order to provide all the SQLsomething() functions.
The most useful ODBC calls the application will make involve using a specific driver to connect to a specifc backend database. The Driver Manager's job is to load the appropriate driver and pass the relevant ODBC calls straight to it.
The ODBC driver itself makes a connection to the backend database; this is normally possible through either a Unix socket or using TCP/IP, optionally over a remote network to a different server. In the case of OpenLink Multi-tier drivers, this driver can involve multiple components - a generic client, a request broker on the server and a database agent to handle the connection, adding extra levels of security and better performance.
It is the driver's responsibility to pass queries on to the driver in a syntactically valid form (for the level of SQL compliance the driver claims to implement) and maintain integrity when conveying the results back. It also adds value to the connection, however, in that much meta-data about the driver and the database can be used dynamically. Also, transaction support is standardized and can be coupled with reusable (prepared) queries to increase performance significantly.
For example, suppose you wished to execute the SQL statement
insert into mytable values (99, 'Tim', '01-03-2003');
repeatedly, changing the values as you go. While it is certainly possible to generate varying statement strings in your application and then execute them, the database must compile and run the statement in entirety every time, taking locking considerations on the row or even whole tables into account. This is highly inefficient.
The alternative is to prepare a query of the form:
insert into mytable values (?, ?, ?);
then for each row intended, bind parameters to each of the place-holders, and repeat just the execution phase. If several rows are batched-up at a time and a transaction put around them, the net time taken can be reduced by an order of magnitude.
It is the ODBC driver's job to handle the binding of parameters here, and to control the transaction/isolation levels, etc.
Now you also have extra flexibility as an application writer; the above simple statement had an obvious ambiguity in the date field. Typically, databases vary in both the format of dates supported (and preferred for a particular database instance), and in the size and precision of timestamps. ODBC works around all this by providing the application author a metadata call to establish what the native name for a given type of field is - DATE or TIMESTAMP or DATETIME, whatever - and three unified escape-syntaxes, for date, time, and timestamp. The driver will then transform the escaped syntax into one the database is configured to understand with no ambiguity.
Unlike Windows, Unix has historically never come with a system-wide ODBC installation, therefore there is greater scope for setting everything up the way you wish.
Also on Unix, there are two main-stream implementations of ODBC: UnixODBC and iODBC. The former comes with a QT-based implementation of a GUI, while iODBC provides a GTK+-based administrator.
The iODBC package is available in several formats - source tarball, source RPM, and a choice of binary components. Installing the binary packages is a simple exercise - for example,
sudo rpm -Uhv libiodbc-3.0.6-2.i386-glibc21.rpm
will install the iODBC runtime library.
There follows an alternative, walking through an unpacking, configure and build of the complete sources from tarball:
zsh, purple 6:10PM C/ % tar xvpfz libiodbc-3.0.6.tar.gz libiodbc-3.0.6/ libiodbc-3.0.6/admin/ libiodbc-3.0.6/admin/Makefile.am libiodbc-3.0.6/admin/Makefile.in [snip] zsh, purple 6:10PM C/ % cd libiodbc-3.0.6 zsh, purple 6:10PM libiodbc-3.0.6/ % ls AUTHORS LICENSE NEWS bin/ etc/ mac/ COPYING LICENSE.BSD README configure* include/ samples/
Configuration, starting with a check of interesting options specific to iODBC:
zsh, purple 6:10PM libiodbc-3.0.6/ % ./configure --help Defaults for the options are specified in brackets. Configuration: -h, --help display this help and exit --help=short display options specific to this package --help=recursive display the short help of all the included packages [snip] --enable-fast-install=PKGS optimize for fast installation default=yes --disable-libtool-lock avoid locking (might break parallel builds) --enable-gui build GUI applications (default), --disable-gui build GUI applications --disable-gtktest Do not try to compile and run a test GTK program --enable-odbc3 build ODBC 3.x compatible driver manager (default) --disable-odbc3 build ODBC 2.x compatible driver manager [snip] zsh, purple 6:10PM libiodbc-3.0.6/ % ./configure --prefix=/usr/local/stow/iodbc --with-gtk --enable-gui && nice make checking for a BSD-compatible install... /bin/install -c checking whether build environment is sane... yes checking for gawk... gawk
Installation, starting with becoming root by sudo, then stow-ing the package correctly into /usr/local:
zsh, purple 6:15PM libiodbc-3.0.6/ % sudo -s Password: zsh, purple 6:17PM libiodbc-3.0.6/ # make install Making install in admin make: Entering directory `/home/tim/C/libiodbc-3.0.6/admin' [snip] make: Nothing to be done for `install-exec-am'. make: Nothing to be done for `install-data-am'. make: Leaving directory `/home/tim/C/libiodbc-3.0.6' make: Leaving directory `/home/tim/C/libiodbc-3.0.6' zsh, purple 6:18PM libiodbc-3.0.6/ # cd /usr/local/stow/ zsh, purple 6:18PM stow/ # chmod -R og=rX iodbc/ zsh, purple 6:18PM stow/ # stow iodbc/ zsh, purple 6:18PM stow/ # ^D zsh, purple 6:18PM libiodbc-3.0.6/ % ls /usr/local/stow/iodbc/bin/ iodbc-config* iodbcadm-gtk* odbctest*
The graphical (GTK+-based) configuration screen makes setting up your data-sources (DSNs) quite intuitive, especially if you're used to the setup screens on Windows:
An example of adding a new datasource follows:
The iODBC library searches for its DSN through a set few files:
The format of this file is very simple; there are 3 sections, one for ODBC itself (setting up tracing), one for a list of DSNs, and one for the definitions of those DSNs, thus:
[ODBC] Debug = 1 Trace = 0 DebugFile = /home/tim/temp/odbc-debugfile.log TraceFile = /home/tim/temp/odbc-tracefile.log TraceAutoStop = 1
[ODBC Data Sources] Virtuoso30 = OpenLink Virtuoso 3.0
[Virtuoso30] Description = Virtuoso 3.0 Driver = /opt/opl/virtuoso-o12/lib/virtodbc.so Address = localhost:1111 UserName = dba User = dba
Each DSN configured has an entry in the `ODBC Data Sources' section, and a complete definition in a paragraph section of its own.
There is also an ODBCINSTINI file; this contains descriptions of the ODBC drivers available.
[ODBC Drivers] OpenLink Generic = installed [OpenLink Generic] Driver = /opt/openlink/odbcsdk/lib/oplodbc.so
To test that a DSN connects correctly, you can use the supplied `odbctest' utility.
zsh, purple 3:58PM bin/ % ls iodbc-config* iodbcadm-gtk* odbctest* zsh, purple 3:58PM bin/ % echo $ODBCINI /home/tim/.odbc.ini zsh, purple 3:58PM bin/ % ./odbctest iODBC Demonstration program This program shows an interactive SQL processor Enter ODBC connect string (? shows list): Progress9.x(solaris) | OpenLink Generic ODBC Driver Progress9.x(solaris) | OpenLink Generic ODBC Driver pgsqlPurple | PostgreSQL native driver pgsqlPurpleOpl | PostgreSQL using OpenLink driver pgsqlPurpleVirtDemo | Virtuoso database driver SQLServer | OpenLink Generic ODBC Driver Enter ODBC connect string (? shows list): DSN=pgsqlPurpleOpl Driver: 04.50.0801 OpenLink Generic ODBC Driver (oplodbc.so) SQL>select count(*) from timtest; count ----------- 100 result set 1 returned 1 rows.
Any of the DSN attributes can be overridden in the connect-string, which takes the form
The attributes themselves depend on the database driver behind the DSN; normally they control the username (where the attribute could be called`userid' or `uid') and password (if specified) used to connect to the database, some form of server hostname specification (`host=' or `server='), and a means to identify a database instance on that server (`database='). A driver may also have custom attributes, such as FetchBufferSize, Port, etc.
The following environments are where ODBC is commonly to be found:
PHP scripts, typically on a webserver: PHP itself contains no intrinsic native database-abstraction layer, meaning code has to be written against Oracle, MySQL, PostgreSQL, .. etc. ODBC to the rescue - PHP does support this as a backend.
Perl - typically found as a CGI script or using mod_perl, on a webserver: Perl does have the DBI for an abstraction layer, which makes writing scripts against a database uniform, and the syntax gels well with the language. ODBC is one possible backend here.
Ruby - another, object-orientated, scripting language with its own implementation of the DBI standard.
Star Office - this has the ability to interact with an ODBC data-source for its own database functionality; notably its address-book components are just another database.A short sample Perl script using the DBI and DBD::ODBC modules to retrieve data from a table can be found here.
Over the years, the ODBC API itself has changed from version 1.0, through 2.5 up to 3.0 and latterly 3.5. The main changes from 2.5 to 3.5 have been the introduction of SQLSetStmtAttr() and related functions, instead of the older SQLSetStmtOption() and relatives; also, in 3.0 and above, support for Connection Pooling was added, cursor support was enhanced, etc.
Fortunately, it is the job of the ODBC Driver Manager component to ensure that function-calls made by the application in a given version of ODBC are mapped to the same version that the driver understands. It is also possible for the application to enquire on a more fine-grained basis, to determine which features are available in a driver, on a given connection and on a statement-handle, primarily through the SQLGetInfo() function call.
The OpenLink `odbctest.c' application is a simple example C application written using the ODBC API. As an overview, the flow of control for connection, executing a simple statement and retrieving data is as follows:
This diagram shows the following salient features:
There is scope for further enhancement as follows:
Any real-time application has to be sensitive to changes in the data at the backend - the validity of any given query's resultset could potentially be quite short-lived.
Consequentially, ODBC provides various models of cursors, some implemented in the Driver Manager, others left for individual drivers to support.
The models of cursors available are:
ODBC provides 4 levels of isolation for transactions. The following definitions are used:
The defined isolation levels are:
ODBC provides unified syntaxes for several SQL expressions that typically vary between vendors' implementations: notably,
This document is empty and basically useless. It is generated by a web service that can make some statements in HTML+Microdata format. This time the service made zero such statements, sorry.