ODBC: The Unix Story

Tim Haynes, OpenLink Software, Tue Jun 3 09:09:38 2003


Table of Contents:


What is ODBC?

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.

[top]

A Walk-through of ODBC Components

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.

ODBC architectural overview graphic

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.

[top]

Installing ODBC

Driver Manager implementations

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[1]: Entering directory `/home/tim/C/libiodbc-3.0.6/admin'
[snip]
make[2]: Nothing to be done for `install-exec-am'.
make[2]: Nothing to be done for `install-data-am'.
make[2]: Leaving directory `/home/tim/C/libiodbc-3.0.6'
make[1]: 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*

  
[top]

Configuring DSNs

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:

GTK+ config screen

An example of adding a new datasource follows:

[top]

The real files

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
  
[top]

Making a test connection

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

DSN=dsn_name[;attr=value]*
  

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.

[top]

Known working ODBC applications

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.
[top]

An example of the API in action

Handling multiple versions

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.

Visualising ODBC call sequences

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:

odbctest.c: flow of control through ODBC API functions

This diagram shows the following salient features:

There is scope for further enhancement as follows:

[top]

ODBC Application Considerations

Scrollable Cursors

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:

Transaction Isolation levels

ODBC provides 4 levels of isolation for transactions. The following definitions are used:

The defined isolation levels are:

Escape Syntaxes

ODBC provides unified syntaxes for several SQL expressions that typically vary between vendors' implementations: notably,