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.
ODBC basically requires four things to work:
The mechanics of an ODBC request are roughly:
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.
|
Related Reading Mac OS X in a Nutshell |
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.
|
There are two ways to test an ODBC connection: the first is to use the odbctest command line application (comes with Jaguar and is in /usr/bin), the second is to use the test feature of iODBC's administrator. I would recommend testing with both applications before making a definitive decision as to whether or not your driver or setup works. I have had experiences with various drivers where one works and the other does not but an ODBC connection can be established with the driver.
I started by testing from iODBC's administrator. I choose my DSN from the User DSN tab, crossed my fingers and clicked test. And it didn't work. I got back two messages:
The situation was bad, but neither message gave much insight into the actual problem, so it was time to test it in odbctest:
[troll:local/mysql/bin] aaa% odbctest
iODBC Demonstration program
This program shows an interactive SQL processor
Enter ODBC connect string (? shows list): ?
DSN | Description
---------------------------------------------------------------
test | myodbc
Enter ODBC connect string (? shows list): dsn=test
dyld errors during link edit for file
/Users/mysqldev/venu/local/mysql/lib/mysql/libmysqlclient.10.dylib
dyld: odbctest can't open library:
/Users/mysqldev/venu/local/mysql/lib/mysql/libmysqlclient.10.dylib
(No such file or directory, errno = 2)
Abort
[troll:local/mysql/bin] aaa%Not surprisingly it still did not work, but the errors were more helpful. Looks like it can't find libmysqlclient.10.dylib and the reason appears to be that the directory it is looking for does not exist. MySQL does not get installed in OS X the same way as *nix and it appears that it was looking for the file in the standard *nix location. Well why not try a hack and symlink libmysqlclient.10.dylib into the directory structure it asks for and see what happens?
Off I go searching for libmysqlclient.10.dylib. First, I check the MySQL lib directory ... not there. Then I search the whole MySQL directory structure ... not there either. Finally, I give up and search the entire file system. Wait a second, libmysqlclient.10.dylib does not exist on my system. Looks like the closest thing is libmysqlclient.a.
A static library, that is not good. We need a dynamic library. (.dylib (as well as .so files, and .bundle files in Mac OS X) are dynamic libraries, which means when a program is compiled you only link to the file, you do not include the file in your executable. On the other hand .a files are static libraries, when a program is compiled you link to the file and the library is included in your executable. The problem here is that libmysqlclient.a is a static library and can not be dynamically linked.)
It looks like if I want to connect MySQL with MyODBC, I have to either recompile the MyODBC shared library and have it statically link to libmysqlclient.a, or recompile libmysqlclient as a shared library.
I was not thrilled at the prospect of recompiling, as that would mean either mucking with the configure scripts or the makefiles, neither of which is fun. I was determined to get this to work though, so I back to the mysql.com site to download the MySQL debug package for OS/X (which includes the MySQL source code) and the MyODBC source code.
While downloading, I did some Googling to see if there were any places to find source code, documentation, FAQs, or anything else that would help in the endeavor. While I didn't find any helpful documentation, I did stumble across the Server Logistics OS X software site, which had a pkg distribution of MySQL that looked different from the one from mysql.com and its version claimed to include ODBC support. Figuring there was nothing to lose I decided to download the file and install it to see what it was all about.
After downloading, I backed up my MySQL data and removed the version of MySQL that I had installed. The Server Logistics package is a disk image (dmg) with several pkgs as well as documentation on how to set up, install and uninstall the software. I installed MySQL and the MyODBC packages and went to check out the lib's that were installed. In this case libmysqlclient was a dynamic lib. So far so good, so I restored my data and tried to setup ODBC again.
The first step was to set up the ODBC driver in iODBC's administrator. This was the same as the first time except the driver file was libmyodbc3.bundle and was located in /Library/MyODBC/lib. Next I setup the DSN, which was exactly the same as the previous setup.
Then came the test. First I tried the "Test" button on the "User DSN" and that gave an interesting response:
Hmm... I thought that was a weird error, so I wanted to see what odbctest had to say:
[troll:~] aaa% /usr/bin/odbctest
iODBC Demonstration program
This program shows an interactive SQL processor
Enter ODBC connect string (? shows list): ?
DSN | Description
---------------------------------------------------------------
mysqltest | MyODBC
Enter ODBC connect string (? shows list): dsn=mysqltest
Driver: 03.51.06
SQL> show tables;
Tables_in_test
--------------
testaaa
result set 1 returned 1 rows.Wow, looks like it worked! Now it was time to test it in an ODBC-compliant application, Excel V.x. (Note: When Excel V.x does ODBC queries it actually uses an application named Microsoft Query. Microsoft Query does not come in the standard distribution for Office V.x, so you may need to download it from Microsoft) Once inside Excel I choose "Data", "Get External Data", "New Database Query". This opens a screen that looks just like the iODBC manager screen. I choose the DSN I wanted to test, clicked "OK" and was now in the query form of Microsoft Query. As if by magic the table list for my database showed up on the right hand side of the screen. Crossing my fingers I ran a query and... voila!! I have an ODBC connection to my MySQL database.
After beating my head for a while, I did get MySQL to work with ODBC. It is unfortunate that the default distribution that mysql.com provides (and is likely the one that most people have installed) does not work out of the box with its ODBC drivers. This is the way it often goes with open source software, though. I would definitely recommend Server Logistics distribution however; it provides easy setup and has some goodies that are not provided in the standard distribution.
Andrew Anderson is a software developer and consultant in Chicago who's been using and programming on the Mac as long as he can remember.
Return to the Mac DevCenter.
Copyright © 2009 O'Reilly Media, Inc.