Getting Started With ODBC (on a non-MS platform)

Installation

One or both of unixODBC and iODBC are available for most Unix-like platforms.

On modern Linux platforms, they can be installed via yum, apt-get or similar. On BSD systems, pkg_add or pkgin may be used to install one or the other. Modern versions of Solaris come with unixODBC pre-installed. I have also been able to build and install many versions of each from source on many different platforms.

ODBC provides a common API for accessing different databases, but to access a database, you also need an ODBC driver for that database. ODBC drivers are generally available as shared object libraries from the database manufacturer. Most databases have ODBC drivers available for them, but not all drivers are available for Unix or Linux.

ODBC drivers are generally installed in the libdir of the ODBC package- generally /usr/lib or /usr/local/lib, but they can really be installed anywhere.

Accessing a Database

To access a database via ODBC, you have to add entries to two separate files.

odbcinst.ini

The file odbcinst.ini contains entries describing the database drivers. Specifically, they map a driver name (such as Oracle) to a user-friendly description of the driver and the driver files themselves.

For example, lets say you're using a database called MythDB and received an ODBC driver for Linux from the manufacturer. The following lines would need to be appended to odbcinst.ini to make the system aware of the driver.

[MythDB]
Description = ODBC for MythDB
Driver  = /usr/lib/libmythdbodbc.so
Setup   = /usr/lib/libodbcmythdbS.so
Driver64 = /usr/lib64/libmythdbodbc.so
Setup64  = /usr/lib64/libodbcmythdbS.so
FileUsage = 1

The [MythDB] line defines the name that will be used in other files when referring to this driver.

The Driver and Driver64 arguments are set to the full pathname of the 32-bit driver and 64-bit drivers respectively. These arguments are optional and each line may be excluded if you were not provided the corresponding driver. For example, if you were only provided a 32-bit driver, you should leave out the entire Driver64 line.

The Setup and Setup64 arguments are set to the full pathname of the 32-bit and 64-bit "setup" libraries. These libraries are used by the Driver Manager UI to describe parameters and are not essential. They may or may not have been provided by the manufacturer. The arguments are optional and each line may be excluded if you were not provided the corresponding file.

The FileUsage parameter is ambiguous. I've never been able to find a good description of it. It appears to always exist and always be set to 1.

odbc.ini

The file odbc.ini defines specific database connections, often called Data Source Names or DSN's for short.

For example, lets say that you wanted to connect to a database on the machine dbhost on port 6600 using username testuser and password testpassword and then use database testdb once logged in. Your odbc.ini entry might look like:

[TestDB]
Description = Connection to TestDB
Driver  = MythDB
Host  = dbhost
Port  = 6600
UserName = testuser
Password = testpassword
Database = testdb

The [TestDB] line defines the name of this DSN to be TestDB. Programs which use ODBC may use the name TestDB to refer to this set of connection parameters.

The Description parameter defines a user-friendly name for this DSN.

The Driver parameter refers back to a driver definied the odbcinst.ini file. Applications which use this DSN will load that driver.

The remaining parameters are specific to the driver, are given only as examples and might be different for other drivers. For example, another driver may not have the Database parameter at all, may refer to the UserName as User instead or may have additional parameters.

That said, the UserName and Password parameters are fairly well standardized and many tools allow them to be overridden at connect-time. As such, they are often left blank in the odbc.ini file. The line is included, but no value is placed after the equals sign.

isql

The command line program isql can be used to test a DSN.

Note that Firebird also comes with a command line program, also called isql and it is important to make sure you are using the ODBC program rather than the Firebird program. Running the isql command with no arguments should give you some indication of which one it is. The isql program that comes with unixODBC has output like the following:

**********************************************
* unixODBC - isql                            *
**********************************************
* Syntax                                     *
*                                            *
*      isql DSN [UID [PWD]] [options]        *
*                                            *
* Options                                    *
*                                            *
* -b         batch.(no prompting etc)        *
* -dx        delimit columns with x          *
* -x0xXX     delimit columns with XX, where  *
*            x is in hex, ie 0x09 is tab     *
* -w         wrap results in an HTML table   *
* -c         column names on first row.      *
*            (only used when -d)             *
* -mn        limit column display width to n *
* -v         verbose.                        *
* -lx        set locale to x                 *
* -q         wrap char fields in dquotes     *
* -3         Use ODBC 3 calls                *
* -n         Use new line processing         *
* --version  version                         *
*                                            *
* Commands                                   *
*                                            *
* help - list tables                         *
* help table - list columns in table         *
* help help - list all help options          *
*                                            *
* Examples                                   *
*                                            *
*      isql WebDB MyID MyPWD -w < My.sql     *
*                                            *
*      Each line in My.sql must contain      *
*      exactly 1 SQL command except for the  *
*      last line which must be blank (unless *
*      -n option specified).                 *
*                                            *
* Please visit;                              *
*                                            *
*      http://www.unixodbc.org               *
*      pharvey@codebydesign.com              *
*      nick@easysoft.com                     *
**********************************************

To connect to the database defined by the TestDB DSN, run the following command:

isql TestDB

or optionally...

isql TestDB testuser testpassword

to override the user and password.

You should now be presented with a session resembling the command line interfaces to other databases. You can run queries, view result sets, etc.

ODBC Quirks

Many databases support named bind variables. For example, in Oracle, you can have a query like:

select * from mytable where col1=:value1 and col2=:value2

In this query, value1 and value2 are the names of the bind variables.

Oracle also supports bind-by-position. For example, you can also have a query like:

select * from mytable where col1=:1 and col2=:2

In this query, 1 and 2 are the "names" of the bind variables.

ODBC does not appear to support binding by name, even if the underlying database does, or if ODBC does support it, I can't figure out how. As such, if you use ODBC to connect to a database, then your client programs must bind by position (as in the second example), not by name (as in the first example), even if the underlying database supports binding by name.

No comments:

Post a Comment