Robert is the former maintainer of the Linux Frequently Asked Questions with Answers Usenet FAQ. He can be reached at [email protected]
The Open DataBase Computing (ODBC) Standard provides an efficient, platform-independent API for writing client-server applications. Although most people associate ODBC with Microsoft Windows servers, implementations also exist for UNIX systems.
ODBC's API is designed for writing SQL clients and servers that run on a single host, but UNIX's networking capabilities allow ODBC client programs to manage and query networked data sources that might not be compatible or accessible.
Even if you don't want to spend another minute writing a TCP/IP interface for a DBMS client, you can use existing UNIX network utilities to access remote data sources. You can also install dedicated network bridges, or build proxy servers and clients with existing toolkits.
A number of ODBC library implementations exist for UNIX systems. The two best known are the unixODBC (http:// www.unixodbc.org/) and iODBC (http:// www.iodbc.org/) SDKs. Both are compatible with the X/Open ODBC Standard, and are free software licensed using the GNU General Public License.
In this article, I focus on unixODBC because of its support for MySQL and PostgreSQL. iODBC also supports Java clients and servers and, in fact, it requires a Java Developer's Kit to be installed on the system. Both of the SDKs support proprietary ODBC-ODBC bridges. iODBC's developers also have an available ODBC-JDBC bridge, and iODBC works with the Perl Database Interface API.
In general, unixODBC has better support for open-source DBMS servers, as well as nonDBMS data sources such as text files and Usenet news servers. Its text-mode utility programs seem to be designed for use with PHP-enabled web servers.
iODBC supports commercial DBMSs such as IBM's DB2, Ingres, Oracle, and Sybase. Its installation programs present a more polished user interface.
Being open-source development projects, both of the SDKs support software driver development by third-party programmers. Table 1 lists the software drivers available for each SDK.
The version of unixODBC described here is 2.2.0, and the iODBC version is 3.0.5. You can download the source code, as well as binaries, documentation, and DBMS drivers, from the aforementioned URLs.
The unixODBC DataManager (Figure 1) provides a UI similar to its Microsoft Windows counterpart. It can process simple SQL queries like the one shown here, where the data source is a PostgreSQL DBMS server. ODBCConfig (Figure 2) is unixODBC's GUI configuration utility. You can use ODBCConfig to set properties for system and file data sources, as well as configure drivers that are provided with unixODBC or written by third-party developers.
The unixODBC DataManager and ODBCConfig clients use the Qt libraries for their GUIs. iODBC's driver manager uses GTK for its GUI and provides a data manager that uses a web browser for its user interface. However, you don't need GUI clients to use either unixODBC or iODBC.
unixODBC has two text-mode utility programs, odbcinst and isql, that configure data sources and drivers, and execute queries from a shell prompt. The command-line interface also lets the utilities install and query software drivers and data sources without user intervention, which makes them useful on remote as well as local systems. Isql has the option of formatting its output as an HTML table.
Building the libraries and text-mode utilities requires only that the system has a working C compiler. Versions of GNU GCC from 2.95.2 onward can compile the libraries and client programs without difficulty.
Although configuration options for different servers depend on the nature of the data source and driver, the ODBC driver and data-source templates adhere to the ODBC Standard. They resemble typical Windows .ini file entries, like the System DSN entry in Listing One.
Using odbcinst, for example, you can list the data sources that are installed on the system:
# odbcinst -q -s
Current config mode tells us to use...( ODBC.INI )
Listing Two is the text-mode MySQL Gutenberg query using odbcinst and isql.
Odbcinst can also install drivers from file templates, like the myodbc.tmpl file (Listing Three). In this case, the command # odbcinst -i -d -f myodbc.tmpl installs the driver.
Installing DBMS-Specific Drivers
The current release of unixODBC provides software drivers in various stages of completion for PostgreSQL, MiniSQL, text files, and Usenet news servers.
There are two versions of the PostgreSQL driver libraries, presumably to support different versions of the DBMS server protocol. The more recent version of the libraries, libodbcpsql.so.2, worked with PostgreSQL Version 7.2.1 built on a Linux system with glibc Version 2.1.3 and GCC 2.95.2.
Driver installation on iODBC is much easier than the ODBC installation process. Drivers are distributed as platform-specific binaries. The developers have compiled binaries that support numerous hardware platforms and operating systems, and work with different versions of the DBMSs they support, often with the option of using either threaded or nonthreaded libraries. All of the driver packages use a shell script for installation.
The MyODBC Driver for MySQL
Installing MyODBC, the MySQL ODBC software driver, follows the typically convoluted process of configuring and building client-server middleware. It is first necessary to build MySQL with threadsafe client libraries, and then install the ODBC libraries; unixODBC in this case, although MyODBC also builds against the iODBC libraries.
During installation, MyODBC must be able to locate the MySQL and ODBC libraries and include files. The default configurations for each package install these files in subdirectories of /usr/local for unixODBC and /usr/local/mysql for MySQL.
Telling the linker where to find the additional run-time libraries depends on the operating system. On Linux systems, it is necessary to add the library directory, /usr/local/mysql/lib/mysql, to the start of the /etc/ld.so.conf file, and then run the ldconfig utility. On a Solaris 8 system, including the library directory in the environment did the job:
mysql:$LD_LIBRARY_PATH ; \
> export LD_LIBRARY_PATH
On one Linux system, it was necessary to install the utility program, "file," which is normally part of UNIX system software distributions. Once these steps are accomplished, the following command configures MyODBC correctly:
# ./configure \
> with-mysql-includes=/usr/local/mysql/include/mysql \
In any event, you should pay close attention to the warning messages that the configuration and build processes generate. MyODBC may build and install the drivers even with inaccurate information about the system's existing libraries.
When adding the driver to the ODBC configuration, unixODBC still provides the MySQL configuration properties, although MyODBC provides the driver module proper. The following libraries worked to configure and connect to MySQL DBMS data sources:
When linked against the iODBC libraries, MyODBC reported that it could not locate the libodbcinst configuration library. In normal use serving client requests, the driver worked fine with an existing data-source template. Neither the DBMS server nor the iODBC driver seemed to notice the lack.
Because the UNIX ODBC libraries communicate with DBMS servers using UNIX sockets, they normally serve only clients on local machines. You need to add an additional layer of software to connect over a TCP/IP network.
At the high end of the connectivity spectrum, EasySoft's ODBC-ODBC Bridge (OOB) and OpenLink's Virtuoso are commercial products that provide client and server proxies for a number of different platforms. unixODBC is the primary ODBC interface here, so I installed the EasySoft OOB.
The OOB server normally runs as a process of the inetd superserver and listens for client connections on port 8888.
When configuring OOB, you need to ensure that the data source works correctly for use with local clients. If the server doesn't respond to a remote client connection, you can start the server manually and view the connection status on a terminal:
# ./esoobserver standalone 8888
Starting HTTP Server
Connection from 192.168.0.8 (unknown)
Connection from 192.168.0.8 (unknown)
Once everything is working, you can configure a remote data source with properties such as those shown in Listing Four. The LOGONUSER and TARGETUSER properties allow you to use different user IDs when logging in to the remote host and DBMS server.
With OOB installed, a client-server system using networked host machines might look something like the block diagram in Figure 3.
Finding Local and Remote Data Sources
On UNIX systems, odbcinst can provide a secure method to query the data sources on remote systems when run from a remote TCP/IP client. Rsh is shown here.
# rsh -l kiesling desktop /usr/local/bin/odbcinst -q -d
If you don't have odbcinst, the utility program dsn.c (available electronically; see "Resource Center," page 5) is a simple ODBC client. It follows the basic steps for connecting to an ODBC data source. The program first allocates ODBC environment and connection handles with separate calls to SQLAllocHandle. It then configures the handles and calls the function SQLDataSources to list each of the data sources on the system.
Perl's DBI libraries provide another method to achieve network connectivity. During installation, the DBD::ODBC module gets its configuration from the environment. These are the environment variables and their values, which are necessary to configure DBD::ODBC for use with the MyODBC driver.
# export DBI_DSN="dbi:ODBC:Gutenberg"
# export DBI_USER="kiesling"
# export DBI_PASS="passwd"
# export ODBCHOME="/usr/local"
# perl Makefile.PL
After building the library, run "make test" to find out if there are any incompatibilities between the libraries or if there are services that are not supported by the driver, DBD::ODBC, or the DBMS server. For example, the tests reported that autocommit is not implemented in the MyODBC driver.
Query.pl (available electronically) is the Perl DBI implementation of the Gutenberg database query. This simple script doesn't do any output formatting on its own. Instead, it simply shows the steps involved in initializing connection and statement handles, and checks the results of queries for row data results or error messages.
Sqlinfo.pl (available electronically) prints the names and values of all SQLGetInfo parameters for a data source, login, and password given as command-line arguments. Sqlinfo.pl generates copious information about all of a data source's attributes, but you can filter the output to find the information you need:
# ./sqlinfo.pl Gutenberg kiesling password \
> | grep 'SERVER'
SQL_SERVER_NAME : Localhost via UNIX socket
Installing DBI also installs software drivers and scripts for proxy daemons and clients. It's easy to write clients that connect to networked data sources. Provided that the Perl library bindings exist for a given data source, it's easy to imagine numerous, thought-provoking applications that take advantage of ODBC connectivity.
The ODBC API provides a platform-independent API for accessing and managing data sources. The networking capabilities of UNIX systems provide additional features to access the data sources of a number of different servers and host systems from a single network client.
[Project Gutenberg Catalog] Description = PostgreSQL 7.2.1 Tables Driver = PostgreSQL 7.2.1 Trace = No TraceFile = Database = gutenberg Servername = localhost UserName = Password = Port = 5432 Protocol = 6.4 ReadOnly = No RowVersioning = No ShowSystemTables = No ShowOidColumn = No FakeOidIndex = No ConnSettings =
# odbcinst -q -s Current config mode tells us to use...( ODBC.INI ) Gutenberg demo # isql Gutenberg +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> select collection,author,title from catalog where author like "%Thoreau%" +-----------+-----------------------------------+----------------------------+ | collection| author | title | +-----------+-----------------------------------+----------------------------+ | etext93 | Henry David Thoreau | Duty of Civil Disobedience | | etext95 | Henry David Thoreau | Walden | | etext97 | Henry David Thoreau [Thoreau #3]| Walking | +-----------+-----------------------------------+----------------------------+ 3 rows affected SQL>
[MyODBC 3.51.02] Description = MySQL ODBC Driver Driver = /usr/local/lib/libmyodbc3-3.51.02.so Setup = /usr/local/lib/libodbcmyS.so.1.0.0
[Gutenberg_cube1] Description = OOB Driver = OOB Server = cube1.mainmatter.com Transport = TCP/IP Port = 8888 TargetDSN = Gutenberg LogonUser = kiesling LogonAuth = <password> TargetUser = kiesling TargetAuth = <password> BlockFetchSize = 0 Unquote_Catalog_Fns = 0 MetaData_ID_Identifiers = 0 MetaDataBlockFetch = 1 DisguiseWide = 0