Linux/UNIX ODBC

This document contains all the information you need to get started accessing ODBC data sources on Linux and UNIX platforms. The document provides background information about ODBC and its implementation on Linux and UNIX, describes the unixODBC ODBC Driver Manager in detail and lists some commonly used Linux/UNIX applications and interfaces that support ODBC.

Contents

• Introduction
• What is ODBC?
  • ODBC versions
  • Components of ODBC
• What is the state of Linux ODBC?
• ODBC Driver Managers
  • What does the ODBC Driver Manager do?
• ODBC Drivers
• The unixODBC ODBC Driver Manager
  • What is unixODBC?
  • Obtaining, Configuring and Building unixODBC
    • Obtaining unixODBC
    • Configuring and building unixODBC
  • How do you install an ODBC driver?
  • Where are ODBC drivers defined?
  • How do you create an ODBC data source
  • What are System And User data sources
  • Where are ODBC data sources defined?
  • What does a data source look like?
  • Testing DSN connections
  • isql beyond testing connections
  • Tracing ODBC calls
  • What does the cursor library do?
  • Setting ODBC driver environment variables automatically
  • UNICODE in unixODBC
• ODBC support in applications and interfaces
  • ODBC abstraction libraries
    • libodbc++
  • C
  • Perl
  • PHP
  • Python
  • Rexx/SQL
  • QT
  • OpenOffice.org
  • StarOffice
  • hsODBC
  • FreeRADIUS
  • IBM UniVerse/UniData
  • Lotus Notes/Domino
  • Micro Focus COBOL
  • mnoGoSearch
  • OpenLDAP
  • Snort
  • Delphi and Kylix
• Appendix A: Resources

Introduction

This document was written to help people understand ODBC on Linux.

What is ODBC?

Open Database Connectivity (ODBC) is a standard software API specification for using database management systems (DBMS). ODBC is independent of programming language, database system and operating system.

ODBC was created by the SQL Access Group and first released in September, 1992. ODBC is based on the Call Level Interface (CLI) specifications from SQL, X/Open (now part of The Open Group), and the ISO/IEC.

The ODBC API is a library of ODBC functions that let ODBC-enabled applications connect to any database for which an ODBC driver is available, execute SQL statements, and retrieve results.

The goal of ODBC is to make it possible to access any data from any application, regardless of which database management system (DBMS) is handling the data. ODBC achieves this by inserting a middle layer called a database driver between an application and the DBMS. This layer translates the application's data queries into commands that the DBMS understands.

ODBC Versions

There are (to date) 4 significant versions of ODBC:

Components of ODBC

A basic implementation of ODBC on Linux is comprised of:

• An ODBC compliant application i.e. an application which uses the ODBC API to talk to a DBMS.
• The ODBC Driver Manager. The ODBC Driver Manager (see ODBC Driver Managers) is the link between an ODBC application and an ODBC driver. Applications requiring ODBC access link with the driver manager and make ODBC API calls which cause the driver manager to load the appropriate ODBC Driver. The ODBC Driver manager also provides other functions (see What does the ODBC Driver Manager do?).
• A repository containing a list of installed ODBC drivers and defined ODBC data sources. The ODBC driver manager normally looks after these definitions and consults them when applications connect to a data source.
• An ODBC driver. The ODBC driver translates ODBC API calls into something the backend DBMS understands.

However, ODBC also includes:

• A cursor library (see What does the cursor library do?)
• Utilities and APIs to install, remove and query installed drivers.
• APIs for data sources to be configured/create/removed from an application (e.g. ConfigDSN etc).
• Utility APIs an ODBC driver can use to handle the reading and writing of data source definitions (e.g. SQLGetPrivateProfileString).
• A GUI and non-GUI ODBC Administrator.
• All the header files required to build ODBC applications.

What is the state of Linux ODBC?

ODBC on Linux is in a healthy state today with many applications and interfaces having ODBC support and a wealth of available ODBC drivers.

The general goal of ODBC for Linux was to:

1. Replicate the ODBC functionality available on Windows so that application authors could write ODBC applications that worked on Windows and Linux/UNIX. This required the writing of an ODBC Driver Manager.

   For the most part this has been achieved in unixODBC which provides a full ODBC 3.5 compatible driver manager including the full ODBC API, all the driver utility functions, installer, deinstaller and configuration library for ODBC drivers, a GUI administrator, an odbctest utility, the full development headers, a non-GUI administration utility (odbcinst) and a command line ODBC application to test data sources and issue SQL to the underlying ODBC driver.

2. Make available ODBC drivers on Linux. There are now a large number of commercial and Open Source drivers available for Linux/UNIX.

ODBC Driver Managers

There are two open source ODBC driver managers for UNIX (unixODBC and iODBC). This document describes the unixODBC Driver Manager as this is the one the author has greatest experience with.

What does the ODBC driver manager do?

The ODBC driver manager is the interface between an ODBC application and the ODBC driver. The driver manager principally provides the ODBC API so ODBC applications may link with a single shared object and be able to talk to a range of ODBC drivers. e.g. an application on Linux links with libodbc.so (the main driver manager shared object) without having to know at link time which ODBC driver it is going to be using. At run time the application provides a connection string which defines the ODBC data source it wants to connect to and this in turn defines the ODBC driver which will handle this data source. The driver manager loads the requested ODBC driver (with dlopen) and passes all ODBC API calls on to the driver. In this way, an ODBC application can be built and distributed without knowing which ODBC driver it will be using.

However, this is a rather simplistic description of what the driver manager does. The ODBC driver manager also:

• Controls a repository of installed ODBC drivers (on Linux this if the file odbcinst.ini).
• Controls a repository of defined ODBC data sources (on Linux these are the files odbc.ini and .odbc.ini).
• Provides the ODBC driver APIs (SQLGetPrivateProfileString and SQLWritePrivateProfileString) to read and write ODBC data source attributes.
• Handles ConfigDSN which the driver exports to configure data sources.
• Provides APIs to install and uninstall drivers (SQLInstallDriver).
• Maps ODBC versions e.g. so an ODBC 2.0 application can work with an ODBC 3.0 driver and vice versa.
• Maps ODBC states between different versions of ODBC.
• Provides a cursor library for drivers which only support forward-only cursors.
• Provides SQLDataSources and SQLDrivers so an application can find out what ODBC drivers are installed and what ODBC data sources are defined.
• Provides an ODBC administrator which driver writers can use to install ODBC drivers and users can use to defined ODBC data sources.

ODBC Drivers

An ODBC driver exports the ODBC API such that an ODBC application can communicate with a DBMS. Sometimes the ODBC driver is single tier where the driver accesses the files directly and sometimes the the driver is multi-tier where it communicates with the DBMS via another layer.

There are a large number of commercial and open source ODBC drivers available for Linux/UNIX. Easysoft have available a number of commercial ODBC drivers available for Linux including:

• Oracle
• Sybase
• Interbase
• Firebird
• C/D-ISAM
• LINC Developer
• System Z
• Tetra/CS3
• OJG, providing ODBC access to JDBC data sources
• OOB, providing ODBC access to any remote ODBC data source e.g. MS SQL Server from Linux
• dbExpress providing access to ODBC data sources from Borland's Kylix, Delphi and C++ Builder

In addition, you can find Open Source ODBC drivers for MySQL and Postgres.

The unixODBC ODBC Driver Manager

What is unixODBC?

unixODBC is a project created to provide ODBC on non-Windows platforms. It includes:

• An ODBC driver manager which adheres to the ODBC specification and replicates all the functionality you may be used to in the MS Windows ODBC Driver Manager (see What does the ODBC Driver Manager do? and Components of ODBC).
• A collection of open source ODBC drivers.
• A number of ODBC applications that illustrate ODBC usage and provide useful functionality e.g. the GUI DataManager, odbctest and isql.

unixODBC is distributed with RedHat, Debian, Slackware and most of the other Linux distributions and is available as source code (see Obtaining unixODBC).

unixODBC is a mature Open Source product having made its first beta release in in January 1999, version 1.0.0 in May 1999 and there have been many release since. At the time of writing (October 2005) the current version of unixODBC is 2.2.12.

Obtaining, Configuring and Building unixODBC

Obtaining unixODBC

unixODBC's web site is at www.unixodbc.org. unixODBC also has a sourceforge project at sourceforge.net/projects/unixodbc. You can download RPMs and the source from either site and you can find the latest development release at ftp.easysoft.com/pub/unixODBC.

Note that all Easysoft ODBC drivers for Linux/UNIX come with unixODBC prebuilt.

Configuring and building unixODBC

The unixODBC source distribution is a gzipped tar file. Uncompress it and then untar the resultant file e.g.

gunzip unixODBC-2.2.12.tar.gz tar -xvf unixODBC-2.2.12.tar 

Change into the resultant directory and run:

./configure --help 

which will list all the options configure accepts. The principle ones you need to pay attention to are:

If you enable the GUI components then configure will try and find QT, its libraries and header files. If you have installed QT in a single place you can provide a hint to configure by setting the environment QTDIR (or --with-qt-dir) to point to the top of the tree where QT is installed. If QT libraries and header files are installed in separate trees and not the default places like /usr/lib and /usr/include you can use --with-qt-includes=DIR and --with-qt-libraries=DIR.

How do you install an ODBC driver?

There are three methods of installing an ODBC driver under unixODBC:

1. You write a program which links with libodbcinst.so and calls SQLInstallDriver.
2. You create an ODBC driver template file and run odbcinst. e.g.

    odbcinst -f template_file -d -i 

   In this case your template file must contain the Driver and Description attributes at a minimum and optionally the Setup attribute e.g.

    [DRIVER_NAME] Description = description of the ODBC driver Driver = path_to_odbc_driver_shared_object Setup = path_to_driver_setup_shared_object 

3. You directly edit your odbcinst.ini file and add the driver definition.

In the odbcinst.ini each driver definition begins with the driver name in square brackets. The driver name is followed by Driver and Setup attributes where Driver is the path to the ODBC driver shared object (exporting the ODBC API) and Setup is the path to the ODBC driver setup library (exporting the ConfigDriver and ConfigDSN APIs used to install/remove the driver and create/edit/delete data sources). Few ODBC drivers for UNIX have a setup dialogue.

You can list all installed ODBC drivers with:

$ /usr/local/easysoft/unixODBC/bin/odbcinst -s -q [sqlserver] [ODBCNINETWO] [aix] [bugs] [ib7] [ODBC_JDBC_SAMPLE] [postgres] [EASYSOFT_JOINENGINE1] [SYBASEA] 

Where are ODBC drivers defined?

In unixODBC ODBC drivers are defined in the odbcinst.ini file. The location of this file is a configure-time variable defined with --sysconfdir but is always the file odbcinst.ini in the --sysconfdir defined path. If unixODBC is already installed you can use unixODBC's odbcinst program to locate the odbcinst.ini file used to defined drivers:

$ odbcinst -j unixODBC 2.2.7 DRIVERS............: /etc/odbcinst.ini SYSTEM DATA SOURCES: /etc/odbc.ini USER DATA SOURCES..: /home/auser/.odbc.ini 

In this example drivers are defined in /etc/odbcinst.ini.

You can tell unixODBC to look in a different path (to that which it was configured) for the odbcinst.ini file and SYSTEM DSN file (odbc.ini) by defining and exporting the ODBCSYSINI environment variable.

If you are using the GUI ODBC Administrator (ODBCConfig) you can view data sources in User and System DSN tabs:

How do you create an ODBC data source

There are three main ways of defining an ODBC data source:

1. If your driver has a setup library (see your odbcinst.ini file) then you may be able to define a SYSTEM or USER data source using the unixODBC ODBC administrator. Start the ODBC administrator using ODBCConfig, select USER or SYSTEM, add, select the ODBC driver and click OK. You should be presented with a dialogue specific to the ODBC driver - fill in the fields and click on OK. e.g. with the Easysoft ODBC-ODBC Bridge driver you get a tabbed dialogue like:

   

2. Edit the SYSTEM or USER DSN ini file ( odbc.ini or .odbc.ini) and add a data source using the syntax:

    [ODBC_datasource_name} Driver = driver_name Description = description_of_data_source attribute1 = value . . attributen = value 

   where, ODBC_datasource_name is the name you want to assign to this data source, Driver is assigned the name of the driver (see odbcinst.ini file for installed drivers and "attributen = value" is the name of an attribute and its value that the ODBC driver needs. e.g. for the Easysoft ODBC-ODBC Bridge you might define

   [my_datasource] Driver = OOB Description = description_of_data_source ServerPort = myoobserver:8888 TargetDSN = mytargetdsn LogonUser = server_username LogonAuth = password_for_LogonUser 

   You need to check with the ODBC Driver you are using to see what attributes you need to define.
3. Create a FileDSN. ODBCConfig does not yet handle file DSNs properly but you can still use them if they are manually created or produced using the SAVEFILE connection attribute to SQLDriverConnect. A file DSN definition is basically the same as above (in the user and system ini files) except it is a file containing a single data source and the data source is always named ODBC. e.g.

   [ODBC] Driver = OOB Description = description_of_data_source ServerPort = myoobserver:8888 TargetDSN = mytargetdsn LogonUser = server_username LogonAuth = password_for_LogonUser 

   Note that File DSNs may be stored anywhere as they are referenced by including in the connection string FileDSN=/path_to_file_dsn.

You can list user and system data sources with:

$ /usr/local/easysoft/unixODBC/bin/odbcinst -q -s [sqlserver] [ODBCNINETWO] [aix] [bugs] [ib7] [ODBC_JDBC_SAMPLE] [postgres] [EASYSOFT_JOINENGINE1] [SYBASEA] 

What are System And User data sources

SYSTEM data sources are those accessible by anyone on the machine which defines the data source. Typically, these are defined in some system defined location that everyone has read access to (e.g. /etc/odbc.ini). USER data sources are defined in a users home directory in the file (.odbc.ini) and are only readable by that user (dependent on umask).

Whether you can access USER DSNs depends on the ODBC driver you are using and whether it is built with unixODBC support.

How your driver locates SYSTEM and USER DSNs depends on whether it was built to use SQLGetPrivateProfileString in unixODBC or not. Drivers which know about the unixODBC driver manager use the ODBC API SQLGetPrivateProfileString() to obtain DSN attributes. If a driver does this it does not matter where SYSTEM or USER DSNs are defined, as unixODBC knows where to look for them and what the format of the odbc.ini (or .odbc.ini) file is. If your driver does not have built in support which uses the SQLGetPrivateProfileString then:

1. It will not know where your ODBC data sources are defined.
2. It may not be capable of parsing the odbc.ini file format.

ODBC Drivers supporting the unixODBC Driver Manager link against libodbcinst.so and include odbcinstext.h. If you are an ODBC driver writer we strongly recommend you install unixODBC and build your driver with:

-I /path/include \ -L/path/lib -l odbcinst 

and include odbcinst.h.

Some Server applications that use ODBC do not support user credentials or change to the specified user so they run in the context that the server application was started in. In this case they cannot access USER DSNs since they are not running as the user in which the user DSN is defined. A common error with Apache is to define a user DSN in the .odbc.ini file in user FREDs account then run Apache under the nobody account. Bridges like the Easysoft ODBC-ODBC Bridge require a logonuser and logonauth which require the server application to change to the specified user and hence they have access to that user's DSNs. If you are using an application which runs as a specific user and you want to use USER DSNs then you need to define the USER DSN in that user's account or use a SYSTEM DSN.

Where are ODBC data sources defined?

ODBC data sources are defined in two different files depending on whether they are a USER DSN or a SYSTEM DSN (see What are System And User data sources). USER DSNs are defined in the .odbc.ini file in the current user's HOME directory. SYSTEM DSNs are defined is some single path defined at compile time for unixODBC with --sysconfdir. You can locate this directory after unixODBC has been built with:

$ odbcinst -j unixODBC 2.2.7 DRIVERS............: /etc/odbcinst.ini SYSTEM DATA SOURCES: /etc/odbc.ini USER DATA SOURCES..: /home/martin/.odbc.ini 

In this case USER DSNs are defined in /home/martin/.odbc.ini because the user running the odbcinst command was martin and his home account is /home/martin.

If you need to define the directory where USER data sources are to be found (different to the default) you can define the environment variable ODBCINI to point to the required path then export it. e.g. Suppose your Apache server was running as user nobody and you did not want to put the USER DSNs in /home/nobody you could define the ODBCINI environment variable as /path and then export it and USER DSNs would be looked up in /path/.odbc.ini.

If you are using the GUI ODBC Administrator (ODBCConfig) you can view data sources in User and System DSN tabs:

What does a data source look like?

Generally speaking a DSN is comprised of a name and a list of attribute/value pairs. Usually these attributes are passed to the ODBC API SQLDriverConnect as a semicolon delimited string such as:

DSN=mydsn;attribute1=value;attribute2=value;attributen=value; 

What a specific ODBC driver needs is dependent on that ODBC driver. Each ODBC driver should support a number of ODBC connection attributes which are passed to the ODBC API SQLDriverConnect. Any attributes which are not defined in the ODBC connection string may be looked up in any DSN defined in the ODBC connection string. e.g. Suppose your ODBC application calls SQLDriverConnect with the connection string "DSN=fred;" but it needs the name of a server where the database is located. Since the connection string does not contain the attribute this driver needs to locate the server (e.g. Server=xxxxx) the ODBC driver can look up the DSN "fred" and see if this defines a "Server" attribute.

Any driver supporting unixODBC will use SQLPrivateProfileString to lookup any attributes it needs using the DSN name as a key. Generally your ODBC application either passes all the attribute=value pairs in the connection string or it lets you choose a DSN from a list then calls SQLDriverConnect("DSN=mydsn;") and then looks up the additional attributes in the DSN definition.

Each ODBC driver should define the attributes which it needs to connect to a particular database. e.g. For the Easysoft ODBC-0DBC Bridge each DSN must define at a minimum, TargetDSN, LogonUser, LogonAuth and ServerPort where ServerPort is the name of the server where the ODBC-ODBC Bridge Server is running and the port it is listening on, TargetDSN is the name of the SYSTEM DSN on the server machine you want to connect to and LogonUser/LogonAuth are a valid username/password to logon to the server machine.

For unixODBC, SYSTEM DSNs are defined in an odbc.ini in the system defined path and USER DSNs are defined the the current user's home directory (in a file called .odbc.ini). The format of this file is:

[DSN_NAME] Driver = driver_name_defined_in_odbcinst.ini attribute1 = value attribute2 = value . . attributen = value 

Testing DSN connections

Once you have installed your ODBC driver and defined an ODBC data source you can test connection to it via unixODBC's isql utility. The format of isql's command line for testing connection is:

isql -v DSN_NAME db_username db_password 

You should use the -v option because this causes isql to output any ODBC diagnostics if the connection fails. The db_username and db_password are optional but you must supply them if your ODBC driver requires a database username and password to login to the DBMS.

If isql successfully connects to your DSN it should display a banner and a "SQL>" prompt:

bash-2.05$ isql -v my_dsn username password +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> 

If it fails to connect (and you specified -v) then any ODBC diagnostic from the ODBC driver explaining why it could not connect should be displayed.

$isql -v mysql_db username password [unixODBC][MySQL][ODBC 3.51 Driver] Access denied for user 'username'@'xxx.easysoft.local' (using password: YES) [ISQL]ERROR: Could not SQLConnect 

What this ODBC diagnostic says depends on the ODBC Driver and you should look up it in the documentation for your ODBC Driver.

Some errors may be reported by the unixODBC driver manager itself (if for instance it could not connect to the ODBC driver). An example is

$isql -v dsn_does_not_exist username password [unixODBC][Driver Manager] Data source name not found, and no default driver specified [ISQL]ERROR: Could not SQLConnect 

In this case unixODBC could not locate the DSN "dsn_does_not_exist" and hence could not load the ODBC driver. Common reasons for this error are:

• The DSN "dsn_does_not_exist" does not exist in your USER or SYSTEM ini files.
• The DSN "dsn_does_not_exist" does exist in a defined ini file but you have omitted the "Driver=xxx" attribute telling the unixODBC driver manager which ODBC driver to load.
• The "Driver=/path_to_driver" in the odbcinst.ini file points to an invalid path, to a path to an executable where part of the path is not readable/searchable or to a file that is not loadable (executable).
• The Driver=xxx entry points to a shared object which does not export the necessary ODBC API functions (you can test this with dltest included with unixODBC.
• The ODBC driver defined by DRIVER=xxx in the odbcinst.ini file depends on other shared objects which are not on your dynamic linker search path. Run ldd on the driver shared object named by Driver= in the odbcinst.ini file and see what dependent shared objects cannot be found. If some cannot be found than you need to defined your LD_LIBRARY_PATH environment variable to define the paths to any dependent shared objects or add these paths to /etc/ld.so.conf and rerun ldconfig.

isql beyond testing connections

NOTE: Unless you are running isql in batch mode we strongly suggest you run isql with the -v (verbose) argument because that will show ODBC diagnostics on failed commands and other useful information. Any examples in this section assume isql was run with the -v argument unless stipulated otherwise.

In Testing DSN connections we saw how isql can be used to test connection to your data sources. isql can do quite a lot more. Once connected to you data source you are provided with an SQL prompt at which you can:

• Enter SQL which is sent to the ODBC driver you are connected to
• Obtain the result-set from an SQLTables call to return a list of tables in your database. Just enter "help" at the prompt.
• Obtain the result-set from an SQLColumns call to return a list of column definitions in a table. Just enter "help TABLENAME".

Anything entered at the SQL prompt in isql which is not a recognised isql command (see above) is passed to the ODBC driver via the ODBC API's SQLPrepare then SQLExecute. If the SQLExecute fails (or returns SQL_SUCCESS_WITH_INFO), isql will use SQLError to obtain ODBC diagnostics. e.g.:

SQL> select * from table_does_not_exist [S0002][unixODBC][Microsoft][ODBC SQL Server Driver][SQL Server] Invalid object name 'table_does_not_exist'. [37000][unixODBC][Microsoft][ODBC SQL Server Driver][SQL Server] Statement(s) could not be prepared. [ISQL]ERROR: Could not SQLExecute SQL> 

If the SQLExecute for your SQL succeeds then isql will use SQLNumResultCols to ascertain if the SQL returned a result-set (e.g. you executed a select). If a result-set is found, it will be fetched and displayed using the any settings from the command line settings -d or -x (how to delimit columns), -w (output in HTML table), -c (column names on first row if -d/-x used) and -m (limit column display width).

After any SQL succeeds, isql will call SQLRowCount to see how many rows were affected. You should note that many ODBC drivers return -1 if the SQL was a result-set generating statement, otherwise this should be the number of rows inserted, deleted or updated.

As each command or SQL statement enterered at the prompt and terminated with a newline will be passed to the ODBC driver you can run isql with stdin redirected to a file containing SQL. e.g. Suppose you created the file myfile.sql containing:

create table test (a integer) insert into test values (1) insert into test values (2) 

then you can use:

isql -v mydsn dbuser dbauth < myfile.sql 

to execute multiple SQL commands in one go. Obviously, you can also redirect stdout.

Tracing ODBC calls

The unixODBC driver manager can write a trace of call ODBC calls made to a file. This can be a very useful debugging aid but it should be remembered that tracing will slow your application down. You enable tracing using one of the following methods:

• Locate your odbcinst.ini file (see Where are ODBC drivers defined?) and add a section to this file like:

  [ODBC] TraceFile = /tmp/sql.log Trace = Yes 

  You can use any file for the TraceFile argument and it does not need to pre-exist. The permissions on the odbcinst.ini may be such that you need to be the root user.

• You can enable tracing and define the trace file using the ODBC Administrator (ODBCConfig).

  

Be careful when running ODBC applications as different users and tracing because most users will set their umask such that other users cannot write to newly created files. If user A enables tracing and connects to the driver manager the trace file will be created and then when user B uses the driver manager it is likely nothing is traced because user B does not have write permission to the trace file.

Trace files generally contain a log of every entry and exit to each ODBC API. e.g.

[ODBC][9155][SQLAllocHandle.c][345] Entry: Handle Type = 2 Input Handle = 0x80899d0 [ODBC][9155][SQLAllocHandle.c][463] Exit:[SQL_SUCCESS] Output Handle = 0x8089f60 

The general form is:

[ODBC][Process ID][C source containing the ODBC API][source line number] Entry: argument 1 = value argument 2 = value argument n = value [ODBC][Process ID][C source containing the ODBC API][source line number] Exit: [ODBC status] output argument 1 = value output argument 2 = value output argument n = value 

With this tracing you can see:

• Each ODBC API called and in what order.
• The arguments provided to each ODBC API.
• Any values returned by an ODBC API
• The exist status of each ODBC API
• If a serious error occurs which could be a problem in unixODBC you can see the line number in the unixODBC source file where the error was generated.

What does the cursor library do?

The cursor library is included in unixODBC for applications which require cursors (more than forward-only cursors) but the driver does not support any cursor other than forward-only. Whether the cursor library is used depends on:

• How the application calls SQLSetConnectAttr for the attribute SQL_ODBC_CURSORS. The default (if SQLSetConnectAttr is not called to set the SQL_ODBC_CURSORS attribute) is SQL_CUR_USE_DRIVER which means to use cursors in the ODBC driver (if you need cursors and the driver does not support the one you require the application will fail). Other values for SQL_ODBC_CURSORS are SQL_CUR_USE_IF_NEEDED (which means the cursor library will be used if you attempt to use a cursor the driver does not support), SQL_CUR_USED_ODBC (which means to use the cursor library in unixODBC) and SQL_CUR_USE_DRIVER (see default).
• Whether you bind result-set values. The cursor library does not work for fetching results via SQLGetData. It only works if you issue a query, bind the columns to variables with SQLBindCol then call SQLFetch.

The cursor library is a shared object called libodbccr.so which will exist in the lib subdirectory of wherever you set --prefix to when you build/configure unixODBC. When the cursor library is in use the normal ODBC entry points to the ODBC driver are replaced with entry points in the cursor library which then go on to call the same entry points in the ODBC driver but they apply extra processing to imitate the required cursor.

Setting ODBC driver environment variables automatically

• DMEnvAttr/SQL_ATTR_UNIXODBC_ENVATTR

  This is a setting for the data source in the odbc.ini file. This is used to set ODBC environment attributes. The form is:

   DMEnvAttr = ATTRIBUTE_NAME=value 

  and if VALUE might contain spaces:

   DMEnvAttr = ATTRIBUTE_NAME={value} 

  where ATTRIBUTE_NAME is the name of an ODBC environment attribute (e.g. SQL_ATTR_CONNECTION_POOLING).

  unixODBC defines a new environment attribute for itself called SQL_ATTR_UNIXODBC_ENVATTR. If your driver needs some environment variables defined to run (e.g. ORACLE_HOME, DB2INSTANCE) you can set them via SQL_ATTR_UNIXODBC_ENVATTR like this:

   DMEnvAttr = SQL_ATTR_UNIXODBC_ENVATTR={envvar=value;envar=value} 

  e.g.

   DMEnvAttr = SQL_ATTR_UNIXODBC_ENVATTR= {ORACLE_HOME=/opt/OraHome} 

  which sets the ORACLE_HOME environment variable to /opt/OraHome before loading the Oracle ODBC driver.

• DMConnAttr and DMStmtAttr

  These unixODBC specific data source attributes work like DMEnvAttr (above). The format is:

   DMConnAttr = CONNECTION_ATTRIBUTE=value DMStmtAttr = STATEMENT_ATTRIBUTE=value 

  where:
  • CONNECTION_ATTRIBUTE is the name of an ODBC connection attribute (e.g. SQL_ATTR_CONNECTION_TIMEOUT).
  • STATEMENT_ATTRIBUTE is the name of an ODBC statement attribute (e.g. SQL_ATTR_NOSCAN).
  • "value" is the value you want to set the attribute to. e.g. SQL_ATTR_CONNECTION_TIMEOUT=30 or SQL_ATTR_NOSCAN=SQL_NOSCAN_OFF.

  e.g.

   DMConnAttr = SQL_ATTR_AUTOCOMMIT=SQL_AUTOCOMMIT_OFF 

  N.B.

  If you prefix the attribute name with a '*' then this fixes the value of that attribute i.e. in any attempt by the application to set that attribute the value specified by the application will be ignored and unixODBC will replace the value with that specified in the DMxxxAttr.

UNICODE in unixODBC

ODBC support in applications and interfaces

ODBC abstraction libraries

libodbc++

• You can find the libodbc++ library at http://libodbcxx.sourceforge.net/

C

• ODBC from C Tutorial Part 1

  Example-based introduction to using ODBC from C.

• ODBC from C Tutorial Part 2 - Fetching Results

  Binding result set columns, retrieving data from unbound columns and returning multiple rows of data.

Perl

• Enabling ODBC support in Perl with Perl DBI and DBD::ODBC

  Building Perl DBI, adding the DBD::ODBC module and using an Easysoft ODBC driver in your Perl scripts.

• Perl DBI/DBD::ODBC Tutorial Part 1 - Drivers, Data Sources and Connection

  Connecting to ODBC data sources from Perl.

• Perl DBI/DBD::ODBC Tutorial Part 2 - Introduction to retrieving data from your database

  Executing SQL queries from Perl and retrieving the results.

• Perl DBI/DBD::ODBC Tutorial Part 3 - Connecting Perl on UNIX or Linux to Microsoft SQL Server

  Accessing SQL Server from Perl. Tutorial and examples.

• Perl DBI - Put Your Data On The Web

  Using Perl CGI scripts to publish your data on the web.

PHP

• Enabling ODBC support in PHP under Apache

  Connecting to ODBC data sources from Apache/PHP.

• Accessing Microsoft SQL Server from PHP under Apache on UNIX or Linux

  Accessing SQL Server from Apache/PHP.

• Accessing ODBC Databases with PHP and PEAR DB

  Connecting to ODBC data sources through PEAR DB.

Python

• Accessing ODBC Databases from Python by using the mxODBC Interface

  Connecting to ODBC data sources through the Python mxODBC module.

Rexx/SQL

• Accessing ODBC Databases from Rexx by using the Rexx/SQL Interface

  Connecting to ODBC data sources through the Rexx/SQL interface.

QT

• Using ODBC in QT

  Accessing ODBC data sources from your QT programs. Includes QODBC code sample.

OpenOffice.org

• Accessing ODBC Databases from OpenOffice.org

  Connecting to ODBC data sources from OOo applications such as Base, Calc and Writer.

StarOffice

• Accessing ODBC Databases from StarOffice 8

  Connecting to ODBC data sources from StarOffice applications such as Base, Calc and Writer.

hsODBC

• hsODBC/Oracle Heterogenous Services

  Transparent SQL access from an Oracle client to non-Oracle ODBC data sources.

FreeRADIUS

• Storing FreeRADIUS Authorization Information in ODBC Databases

  Using an ODBC data source as a FreeRADIUS server backend.

IBM UniVerse/UniData

• Accessing ODBC Databases from IBM UniVerse/UniData

  Accessing ODBC data sources from the IBM U2 data servers, UniVerse and UniData.

Lotus Notes/Domino

• Accessing ODBC Databases from Lotus Notes/Domino

  Using Domino Enterprise Connection Services (DECS) to enable Lotus Notes/Domino applications to access ODBC data sources.

Micro Focus COBOL

• Accessing ODBC Databases from Micro Focus COBOL by using the Server Express OpenESQL Interface

  Using the Server Express OpenESQL Interface to access ODBC data sources.

mnoGoSearch

• Using ODBC Databases to Store mnoGoSearch Data

  Store mnoGoSearch indexer information in any database for which an ODBC driver is available.

OpenLDAP

• Using OpenLDAP with Microsoft SQL Server and Oracle Backend Databases

  Exposing data in SQL Server and Oracle databases to LDAP client applications.

Snort

• Accessing ODBC Data Sources from Snort

  Logging intrusions and alerts to remote ODBC data sources.

Delphi and Kylix

• Easysoft dbExpress-ODBC Gateway

  The dbExpress-ODBC Gateway lets applications developed with Borland Kylix, Delphi or C++ Builder access any database for which an ODBC driver is available.

Appendix A: Resources

• Easysoft Guide to ODBC Diagnostics & Error Status Codes
• The Easysoft web site

This document is a start point for all unixODBC manuals. If you feel that there is a need for improvement then please email me, Nick Gorham, and I will see what I can do to improve things.

User Manual
A neophyte's guide (contributed by Charles Morrison)
A guide to using unixODBC without the GUI (contributed by Nick Gorham)
How to build PHP3 with unixODBC(contributed by Nick Gorham)
unixODBC and the year 2000
Using DB2 and unixODBC
Using ColdFusion, interbase and unixODBC (contributed by Rotaru Calin Augustin)
How to use connection pooling with unixODBC
How to use unixODBC with FreeTDS (contributed by Peter Harvey)
How to use unixODBC with FreeTDS with specifics for Red Hat/Fedora (contributed by Bob Abbott)
How to use unixODBC with wine
Data Access With Linux by Adam Williams(PDF)
Using the Informix driver(Contributed by Adam Williams)
OpenOffice.org 1.0, unixODBC, and MySQL (updated 19th April for RedHat 9)(By John McCreesh)
How to use C/C++ interpreter Ch with unixODBC
Informix access with unixODBC (German)
Perl DBD::ODBC Tutorial Part 1 - Drivers, Data Sources and Connection (from Easysoft Web Site)
Perl DBD::ODBC Tutorial Part 2 - Introduction to retrieving data from your database (from Easysoft Web Site)
Perl DBD::ODBC Tutorial Part 3 - Connecting Perl on UNIX to Microsoft SQL Server (from Easysoft Web Site)
ODBC Diagnostics & Error Status Codes (from Easysoft Web Site)
Programming Manual tutorial
ODBC from C Tutorial Part 1 (from Easysoft Web Site)
Perl Tutorial Part 4 - Put Your Data On The Web (from Easysoft Web Site)
ODBC on Unix/Linux (from Easysoft Web Site)