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

[test]
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.

1 comment:

Eric Pugh said...

Thanks for your great post. I followed the first half, ran into same issues, and decided this sucks... So going to export data form Mysql and manually import to build query. Going to eventually do charting using HighCharts anyway.... ODBC is always a pain.