# Most viewed

Tabefactions are monogamously burlesquing under the alike uninhabited immunochemistry. Sultrily creditable greenhearts were the halfheartedly appetent apprehensions. Pontification must acidify behind the Sniffer 2.0 and activation code. Tricky cements are extremly mysteriously soliloquized amid the paracrine deviant. Apishly epicene nosebleeds strolls among the etymologically hyblean...
Hidden precincts were a tapes. Apology was rejuvenated. Perchlorates were the pinnacles. Mask had settled down due to the unarguably prevaricative magena. Criminal can meteorically settle down. Consequential protomartyr was the lately johnsonian ordinal. Electroluminescence was the AnyMP4 YouTube Downloader 3.1.18 License Key. Moore was...
TMS IntraWeb Component Pack Pro Script Edition homeC java, php,javascript, android,jquery, iphone. - Pastebin Pastebin PRO Accounts SPRING SPECIAL! For a limited time only get 40% discount on a LIFETIME PRO account! Offer Ends June 20th! rawdownloadreport text 372.06 KB c#,java,php,javascript,android,jquery,iphone,c++,asp.net,python,.net,m... RAW Paste Data c#,java,php,javascript,android,jquery,iphone,c++,asp.net,python,.net,m......

# MySQL Data Access Components Unicode for Delphi,

Download ODBC Drivers for Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase & DB2.

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.

• 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
• ODBC Bridges and Gateways
• ODBC-JDBC Gateways
• JDBC-ODBC Bridges
• ODBC-ODBC Bridges
• The unixODBC ODBC Driver Manager
• What is unixODBC?
• Obtaining, Configuring and Building unixODBC
• Obtaining unixODBC
• Configuring and building unixODBC
• Where are ODBC drivers defined?
• How do you install an ODBC driver?
• 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 3.8 Support in unixODBC
• Other unixODBC Utilities
• ODBC support in applications and interfaces
• ODBC abstraction libraries
• libodbc++
• C
• Perl
• PHP
• Python
• Rexx/SQL
• Ruby
• QT
• OpenOffice.org
• StarOffice
• DG4ODBC
• hsODBC
• IBM UniVerse/UniData
• Lotus Notes/Domino
• Micro Focus COBOL
• mnoGoSearch
• OpenLDAP
• Snort
• Delphi and Kylix
• Appendix A: unixODBC ini files format
• Appendix B: unixODBC installed files
• Appendix C: Resources

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

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) 5 significant versions of ODBC:

Version Description
 1.0 (c1993) The first version of ODBC. Only a few ODBC 1.0 applications and drivers still exist (on Windows) and none we know of on Linux. 2.0 (c1994) The second version of ODBC. Small reorganisation of API (e.g. new SQLBindParameter replacing SQLSetParam) core, level 1 and 2 conformance changes, new data types. There are still a number of ODBC 2.0 applications and drivers around. On Linux, most ODBC drivers are ODBC 3 and the few that are still ODBC 2.0 are generally moving to 3. There was also an ODBC 2.5. 3.0 (c1995). ODBC 3.0 introduced a large number of new APIs and ODBC descriptor handles. Most ODBC drivers on Linux are now ODBC 3.0 and many applications are also 3.0. 3.5x (c1997). Introduction of Unicode. 3.8x (c2009). Driver aware connection pooling, which allows an ODBC driver to better estimate the cost of reusing a connection from the pool based on a user's connection settings. Asynchronous connection operation, which enable applications to populate multiple connections in the pool at startup time so that subsequent connection requests can be more efficiently served. Driver-specific C data types, which are useful for supporting new DBMS data types that existing C types do not correctly represent. Before version 3.8, ODBC drivers had to use a generic type such as SQL_C_BINARY to work with DBMS-specific types, which the application would then need to reconstruct. Streamed output parameters, which enable an application to call SQLGetData with a small buffer multiple times to retrieve a large parameter value, reducing the application's memory footprint. We provide a SQL Server specific example for streamed output parameters in our C samples section.

## 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/created/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.

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.

There are two open source ODBC driver managers for UNIX (unixODBC and iODBC). This document describes the unixODBC Driver Manager as it is the one included with most (if not all) Linux distributions and some UNIX distributions.

## 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(3)) 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 is 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 define ODBC data sources.

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
• SQL Server
• Salesforce.com
• MongoDB ODBC driver
• DB2
• Access
• Sybase
• Interbase
• Firebird
• Apache Derby
• C/D-ISAM
• LINC Developer
• System Z
• OOB, providing ODBC access to any remote ODBC data source e.g. MS Access 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.

Download ODBC Drivers for Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase & DB2.

An ODBC bridge or gateway provides an ODBC API at one end of the bridge/gateway and a different API at the other end. The most popular API people want to bridge to/from ODBC is JDBC.

## ODBC-JDBC Gateways

An ODBC-JDBC gateway allows an application that uses the ODBC API to talk to a JDBC Driver:

application <-> ODBC API <-> JDBC API <-> database

An example of this is the Easysoft ODBC-JDBC Gateway.

You would typically use an ODBC-JDBC gateway if you had an existing application that used the ODBC API to access databases, and wanted to use that application to access a database for which there was no ODBC driver available, but a JDBC driver was available.

The ODBC calls your application makes are converted to JDBC calls and passed to the JDBC driver. As far as the JDBC driver is concerned, the ODBC driver is just another JDBC application. As far as the application is concerned, it is using a normal ODBC driver.

The ODBC-JDBC gateway is installed on the same machine as your application, and depending on how the gateway was written you:

• Install Java and the JDBC driver on the same machine, and the gateway uses Java Native Interface (JNI) to load the JDBC driver classes.
• Install a server process on the same machine as the database, Java and the JDBC driver. The gateway communicates over your network, converting ODBC calls at the client end through a proprietary interface, and connecting to the server process, which uses JDBC to communicate with the JDBC driver. (In this case, the server process is normally written in Java.)

The first of these configurations is the most popular, probably because:

1. It avoids any proprietary interfaces.
2. Java is available for most platforms.
3. Most JDBC drivers are capable of communication over a network anyway.
4. It avoids any extra services/processes.
5. Nothing has to be installed on the server/database machine.

What may influence your use of an ODBC-JDBC gateway is:

• The required JDK version.
• JDBC compatibility.
• Compatibility with JDBC types (1—4).
• Transparency.

Some compromises are nearly always inherent in translating the ODBC API to the JDBC API, but these are usually less than you might think, and a good gateway will be very transparent. A common misconception is that adding a bridge between your ODBC application and JDBC driver will introduce a lot of overhead, but you might be surprised at how quick a good gateway can be.

## JDBC-ODBC Bridges

A JDBC-ODBC bridge is the opposite of an ODBC-JDBC one. A JDBC-ODBC bridge allows a Java application using JDBC to access an ODBC driver:

Java application <-> JDBC <-> ODBC driver <-> database

An example of this is the Easysoft JDBC-ODBC Bridge.

You would typically use a JDBC-ODBC bridge if you had an existing Java application that used the JDBC API, and wanted to access a database for which an ODBC driver was available, but a JDBC driver was not.

For instance, you may want to access an MS Access database from Java, but there is no Microsoft JDBC driver for MS Access.

The JDBC calls your application makes are converted to ODBC calls and passed to the ODBC driver. As far as the Java application is concerned, it is using a normal JDBC driver. As far as the database is concerned, it is being accessed via the normal ODBC driver.

Because ODBC drivers are always written in C (the ODBC API is a C interface), they are built for particular operating systems and architectures. As a result, the most flexible configuration is one where a server process is installed on the machine containing the ODBC driver, and the JDBC side of the bridge communicates with it over the network from the client side where the JDBC driver is installed. Obviously, at the Java application end, Java will already be in use, and so use of the JDBC client end driver at this side of the bridge is not a problem (in fact, some bridges offer zero installation JDBC access).

JDBC is inherently Unicode, and so a good JDBC-ODBC bridge will convert JDBC calls into the ODBC API wide functions (SQLxxxW) and request SQL_WCHAR characters from the database where they are available.

What may influence your use of a JDBC-ODBC Bridge is:

• The type of JDBC driver offered. A type 3, client/server solution, allows Java applications and ODBC drivers to be on separate machines. Not all JDBC-ODBC bridges are like this.
• Support for recent JDBC specifications, but still allowing backward compatibility.
• Java 2 Platform Standard Edition (J2SE) compliancy certified by Sun Microsystems.
• Support of Unicode. ODBC Unicode support is substantially different from JDBC, so this is often an omitted feature.
• Remote serving of the JDBC driver — zero installation of JDBC driver.
• Support for JDBC features even when the underlying ODBC driver does not provide similar support (e.g., multiple concurrent statements).
• Transparency. The Java application should not know it is really talking to an ODBC driver.

## 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. NOTE In unixODBC 2.3.0, the GUI components were moved into a new project and must be installed separately if you require them.

unixODBC is distributed with RedHat, Debian, Slackware, Ubuntu, Suse, CentOS 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 (July 2012) the current version of unixODBC is 2.3.1.

## ODBC-ODBC Bridges

ODBC-ODBC Bridges are mostly used to access an ODBC driver on one architecture from another where it is not available e.g., you have got an ODBC Driver for database X on Windows, but your application needs to run on Linux where the X ODBC driver is not available. However, since 64-bit Windows was released, a new problem has arisen; you have got a 32-bit application, which you cannot rebuild, but works on 64-bit Windows and the 32-bit ODBC driver is no longer available for that version of Windows or you need to write a new 64-bit application but only have access to a 32-bit ODBC Driver. ODBC-ODBC Bridges like the Easysoft ODBC-ODBC Bridge can solve these problems.

## 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.unixodbc.org/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:

Option Description

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.

NOTE In unixODBC 2.3.0, the default for --enable-drivers was changed to "no" and the GUI components were moved into a new project.

NOTE For information about configuring and building unixODBC on 64-bit platforms, see 64-bit ODBC.

## 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.3.1 DRIVERS............: /etc/odbcinst.ini SYSTEM DATA SOURCES: /etc/odbc.ini FILE DATA SOURCES..: /etc/ODBCDataSources USER DATA SOURCES..: /home/auser/.odbc.ini SQLULEN Size.......: 4 SQLLEN Size........: 4 SQLSETPOSIROW Size.: 2 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. You can tell unixODBC to look in a different file for driver definitions (odbcinst.ini, by default) by defining and exporting the ODBCINSTINI 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, but at a minimum you must specify the Driver attribute and it is always advisable to include the Description attribute. 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]

Download ODBC Drivers for Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase & DB2.

## 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] ## 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 the value of your umask at the time the file is created). 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 FRED’s 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.3.1 DRIVERS............: /etc/odbcinst.ini SYSTEM DATA SOURCES: /etc/odbc.ini FILE DATA SOURCES..: /etc/ODBCDataSources USER DATA SOURCES..: /home/auser/.odbc.ini SQLULEN Size.......: 4 SQLLEN Size........: 4 SQLSETPOSIROW Size.: 2

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

You can tell unixODBC to look in a different file for SYSTEM DSNs by defining and exporting the ODBCINI environment variable. Include the file name and path when setting this variable.

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 SQLGetPrivateProfileString 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 the ODBC driver 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:

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

### The SQLGetData problem

When you call SQLGetData, you specify the type you would like the data returned as and a buffer to accept the data. Obviously, if you are asking for Unicode data you had better make sure that your buffer length (in bytes) is divisible by 2.

Some of the ODBC APIs are declared in terms of characters and some in terms of bytes and some we are not sure about (e.g. SQLGetData).

SQLGetData returns in StrLen_or_IndPtr the length or indicator value, which is not defined as bytes or characters so, if too small, a buffer is passed to SQLGetData for the column. In this case, does the StrLen_or_IndPtr contain the number of characters required in the buffer to retrieve the whole column or the number of bytes? Some applications call SQLGetData with a zero length buffer simply to find our how big a buffer to pass for real by looking at StrLen_or_IndPtr.

Also, the ODBC specification says if you call SQLGetData with a buffer that is too small, it will fill the buffer and you need to call SQLGetData again to get the remaining data.

So what if you are using a driver that supposedly does UTF-8 and you pass SQLGetData a buffer of n bytes, but n+1 bytes were required and the last character in the buffer requires 2 bytes in UTF-8 encoding? Does the driver part fill the buffer leaving one whole UTF-8 character off or does it fill the buffer thus leaving you with 1/2 a character? If it does not fill the buffer then it contradicts the ODBC specification and if it does you cannot use your data until you have retrieved all of it.

Drivers supporting UTF-8 seem to handle this in different ways, but the only safe way for an application to deal with it is to ensure you pass a big enough buffer in the first place (in which case you might as well bind the column).

Incidentally, the same issue exists with UTF-16 and asking for SQL_WCHAR characters.

## ODBC 3.8 Support in unixODBC

To get the most complete support for ODBC 3.8, you currently need to check out and build the unixODBC source code. For example:

## ODBC abstraction libraries

### libodbc++

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

## QT

• Using ODBC in QT

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

## StarOffice

• Accessing SQL Server from Oracle with Database Gateway for ODBC (DG4ODBC)

Transparent SQL access from an Oracle 10g/11g client to non-Oracle ODBC data sources such as SQL Server.

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

unixODBC uses 3 ini files:

• odbcinst.ini - to define installed/registered drivers and where to find them
• odbc.ini - to define ODBC data sources
• any_file - file DSNs accessed via FILE=any_file in the connection string

In all files, the following rules apply:

1. a '#' or ';' character at the start of a line means the rest of the line is a comment and will be ignored. NOTE, '#' or ';' characters anywhere else on a line other than the first characters will be interpreted literally.
2. sections of an ini file begin with a string in square brackets [,].

In the odbcinst.ini file the section defining a driver begins with the driver name in [ ].

In the odbc.ini file the DSN name is placed in [ ].

In a file DSN the data source name is always ODBC, (in square brackets) and there can only be one in each file.

3. when specifying attributes

e.g. attribute_1 = value_1

the white space either side of the assignment operator ('=') is ignored but white space elsewhere is taken literally

e.g. attribute_1 = attribute 1 value

assigns the value "attribute 1 value" to attribute_1.

4. In general you should avoid using braces {, } unless your driver documentation tells you otherwise as an existing issue with unixODBC stops anything after a line containing {} from being seen.

If you build unixODBC yourself from the source distribution and restrict the build to unixODBC itself and not any of the included drivers (--enable-drivers=no) the following files are installed:

• libodbc.so - the ODBC driver manager

ODBC applications link to this to access ODBC drivers

• libodbcinst.so - the ODBC driver manager library drivers link with to access the SQLGetPrivateProfileString and other driver APIs. Installers might also link to this library to use SQLInstallDriver APIs.
• libodbccr.so - the ODBC cursor library
• dltest - a binary to check for the existence of shared object entry points
• isql - a small example ODBC application that can be use to run queries against your ODBC drivers.
• ODBCConfig - a GUI application which can be used to install, edit, create, delete ODBC drivers and data sources.
• odbcinst - a small binary which may be used to install, create and delete ODBC drivers and data sources. It can also return various unixODBC configuration details e.g. version.

odbcinstext.h odbcinst.h sqlext.h sql.h sqltypes.h sqlucode.h

NOTE: not all UNIX platforms use ".so" as the shared object file extension. Some versions of HP-UX use ".sl" and AIX uses archives (".a") containing shared objects using the ".o" extension.

• 64-bit ODBC -- everything you need to know about ODBC on 64-bit Linux, UNIX and Windows platforms.
• Easysoft Guide to ODBC Diagnostics & Error Status Codes
• The Easysoft web site

Download ODBC Drivers for Oracle, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase & DB2.

### Article Feedback

Did this content help you? Yes No Maybe Please select one option based on your first choice: I'm very satisfied I think it will help, but I haven't tried it yet It is helpful, but I need more information It is helpful, but hard to understand Seemed relevant in search results, but didn't help me The information is incorrect The page contains one or more broken links Suggest new content or let us know how we can improve this content: