AddThis Social Bookmark Button

Listen Print Discuss
Mac OS X > Excerpts >

Open Database Connectivity in Jaguar
Pages: 1, 2

Location, Location, and Type

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:

  • IM003 [iODBC][Driver Manager]Specified driver could not be loaded followed by
  • 0000 [iODBC][Driver Manager]NSLinkModule0 failed for dlopen()

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.

Other Options

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:

  • HY000: [MySQL][ODBC 3.5.1] No DSN entered

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.

Final Thoughts

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.