Open Database Connectivity in Jaguarby Andrew Anderson
One of the new, albeit virtually undocumented features included in Jaguar is ODBC, or Open Database Connectivity. ODBC allows programs to connect to databases from different vendors using the same set of connectivity protocols. This allows for simplified database programming as well as database access from programs that normally would not allow such access. For instance, with ODBC you can use Excel to get data from MySQL, or you can use FileMaker to get data from Oracle.
This article will give a brief overview of ODBC followed by a recount of my experiences trying to get ODBC working with MySQL. A later installment will include details of how to get ODBC working with PostgreSQL.
A Brief Overview of ODBC
ODBC basically requires four things to work:
- A datasource.
- An ODBC driver for the datasource.
- An ODBC driver manager.
- An ODBC compliant-program to access the data (such as FileMaker, Excel, RealBASIC).
The mechanics of an ODBC request are roughly:
- The ODBC-compliant program looks up the correct driver to use in the driver manager.
- The program uses the driver to connect to the datasource.
You are probably asking yourself, how does the ODBC-compliant program know what driver to look up and what datasource to use? There is an initialization file named odbc.ini that handles all sorts of setup information, including information on the drivers, and on the individual datasource aliases, known as DSNs (for datasource names). Setting up the odbc.ini file correctly is the key to making ODBC work properly. You could edit the file by hand, but fortunately, Apple and iODBC both provide Cocoa-based ODBC administrators, which make editing odbc.ini files a breeze.
Wait a Second ... What Is iODBC?
The ODBC driver manager that comes with Jaguar is actually OpenLink Software's iODBC product (click here for more information). iODBC is an open source driver manager and SDK for running ODBC in *nix environments including Jaguar. OpenLink also provides high-quality, commercial ODBC drivers for various database and operating system platforms. iODBC provides an ODBC SDK for OS X that includes an updated ODBC driver manager as well as its own Cocoa-based ODBC administrator. I would recommend using iODBC's SDK as the driver manager; it is better behaved and the ODBC administrator has more features than Apple's.
Please note that I used iODBC's SDK for all examples in this article. To download the SDK and to get other useful information on ODBC in general, check out iODBC's web site for more information.
MyODBC with the Standard MySQL Install
The first step towards getting ODBC to work with MySQL was to download MySQL's ODBC driver, named MyODBC. Since I had the standard installation of MySQL (the "pkg" installation from mysql.com), I went to mysql.com and downloaded the MyODBC driver. Then I installed the drivers into /usr/local/, as recommended by the manual.
Once the driver was installed, the next step was to set up the driver and DSN in the ODBC administrator. It's a straightforward process to set up a driver in the ODBC administrator. You run the ODBC administrator, choose the "ODBC Drivers" tab, and choose "Add a Driver". From there you give the driver a name and find the file for the driver. There was no need to set up key-value pairs at this stage as I had no options that were universal across all instances of this driver.
The MyODBC distribution includes a number of lib files so be sure to choose the correct file. The lib file that we are looking for is
libmyodbc3.so, which is a symlink to
libmyodb3-VERSION.so, where VERSION is whatever version of MyODBC that you have.
Now that I had the driver installed, the next step was to set up the DNS. To do this, I first chose the "User DSN" tab in the ODBC administrator. To add a new DSN, you choose the "Add" button and select a driver you would like to use. In this case I selected the MyODBC driver that I had just installed.
Next, a setup page for the DSN comes up. I chose the name "test" for my DSN and then went on to add key-value pairs for this datasource. These key-value pairs give all the information required for the ODBC driver to connect to the database. The minimum set for MyODBC connecting to a locally running database is: database, user, and password. (I realize that in an installation environment including a password is a security issue, but for the setup and testing it is significantly easier to include the password here.) Once the key-value pairs are set up, we can test out our connection.
Pages: 1, 2