Obtaining, Configuring and Building unixODBC

http://www.easysoft.com/developer/interfaces/odbc/linux.html#getting_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.gztar -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:

OptionDescription--prefixThis defines where you want to install unixODBC. If you do not specify this it will default to /usr/local. If you do not want unixODBC all under a single directory you can use other configure options like --bindir, --sbindir etc for finer control.--sysconfdirThis defines where you want unixODBC configuration files to be stored. This defaults to <prefix>/etc. The configuration files affected are odbcinst.ini (where ODBC drivers are defined), the system odbc.ini (where system data sources are defined) and ODBCDataSources (where system file DSNs are stored).--enable-guiThe default is "yes" if QT is found. If you want to build the GUI ODBC Administrator, odbctest and DataManager set this to "yes" (e.g. --enable-gui=yes). You will need QT libraries and header files to build the GUI components (see later). You should probably also set --with-x.

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.

--enable-threadsThe default is "yes" if thread-support is found on your machine. All modern Linuxes will have pthreads support in glibc so it is probably best to leave this to default.--enable-readlineThe default is "yes" if libreadline and its headers are found on your machine. This principally only affects unixODBC isql program. If readline support is found then you can edit text entered at the SQL prompt in isql.--enable-driversThe default is "yes". When enabled this will build all the ODBC drivers included with unixODBC. This includes MySQL, Postgres, MiniSQL and a text file driver.--enable-iconvThis defaults to "yes" if libiconv and its header files are found on your machine. If you build with iconv and access then unixODBC can do Unicode translations.

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 -junixODBC 2.3.1DRIVERS............: /etc/odbcinst.iniSYSTEM DATA SOURCES: /etc/odbc.iniFILE DATA SOURCES..: /etc/ODBCDataSourcesUSER DATA SOURCES..: /home/auser/.odbc.iniSQLULEN Size.......: 4SQLLEN Size........: 4SQLSETPOSIROW 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 = OOBDescription = description_of_data_sourceServerPort = myoobserver:8888TargetDSN = mytargetdsnLogonUser = server_usernameLogonAuth = 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 = OOBDescription = description_of_data_sourceServerPort = myoobserver:8888TargetDSN = mytargetdsnLogonUser = server_usernameLogonAuth = 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 theEasysoft 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 -junixODBC 2.3.1DRIVERS............: /etc/odbcinst.iniSYSTEM DATA SOURCES: /etc/odbc.iniFILE DATA SOURCES..: /etc/ODBCDataSourcesUSER DATA SOURCES..: /home/auser/.odbc.iniSQLULEN Size.......: 4SQLLEN Size........: 4SQLSETPOSIROW 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.iniattribute1 = valueattribute2 = 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.