Tuesday, May 4, 2010

MySQL ODBC drivers, Excel and Mac OS-X 10.6

What a nightmare!

So what started off as a simple problem (adding a query into a Microsoft Excel spreadsheet) turned out to be a nightmare!

This problem seemed to affect both Office 2004 and 2008, I had previously that them working in OS-X 10.5, however I just got a new machine, which is 64bit capable and running 10.6.

Looking on the MySQL download page, there were no build for 10.6, only 10.5. So I experimented.

Here's what I tried
  • MySQL ODBC Driver 5.1.6 x86 64 bit for 10.5
  • MySQL ODBC Driver 5.1.6 x86 32 bit for 10.5
From the Apple ODBC Manager, I could create a DSN, I could connect to my MySQL database just fine. Going into Excel and then Data > Get External Data > New Database Query brought up the iODBC ODBC Manager. However when I tried to connect to my database, I got the following message.

[iODBC][Driver Manager] Specified driver could not be loaded

Office 2008 (if I recall is a x86 built product), so I tried with Office 2004 which I knew was PowerPC. Still the same message.

Now what I did next is very weird but works, I installed the following
  • MySQL ODBC Driver 5.1.6 PowerPC 32 bit for 10.5
I had some issue trying to setup in the Apple ODBC Manager, so I hacked the .ini file in /Library/ODBC/odbc.ini. Here's a sample

Driver = /Develop/Developer/mysql-connector-odbc-5.1#377675/lib/libmyodbc5-5.1.6.so
PORT = 3306
DATABASE = testdb
PWD = notmyrealpassword
SERVER = db2.acme.com
UID = admin

So strike me down if this worked for Office 2004. For Office 2008 its even more weird, the PowerPC driver allows me to connect with Microsoft Query, but I need the x86 Driver to query from the spreadsheet. BUt you can't edit the query afterwards :-(

I just downgraded to Office 2004 for this, and will save the final conclusion for another day.