Haskell, HDBC, ODBC, MySQL and Mac OS X

Question

I'm trying to use Haskell (version 6.10.3) and HDBC to connect to a MySQL Database. I've chosen to do it with Haskell ODBC. I've used cabal to install HDBC (2.1.1) and HDBC-ODBC (2.1.0.0). I've downloaded and installed the MySQL ODBC driver (5.1.5). I used macports to install unixODBC (2.2.14_1). All of this on top of Mac OS X (10.5.8).

I've mostly been using the instructions on this page http://en.wikibooks.org/wiki/Haskell/Database. At around this point:

"# Add the mysql driver to odbcinst.ini file (under $ODBC_HOME/etc/) and your data source in $HOME/.odbc.ini."

It looks like the macports version of unixODBC installs everything under /opt/local/. I've put an odbcinst.ini into /opt/local/etc/ and I've created a .odbc.ini in my home directory which looks something like this (note that I've experimented with UID vs. USERNAME and PWD vs PASSWORD):

[ODBC Data Sources]
myodbc = MySQL ODBC 5.1 Driver

[ODBC]
Trace         = 0
TraceAutoStop = 0
TraceFile     =
TraceLibrary  =

[myodbc]
Driver      = /usr/local/lib/libmyodbc5.so
DATABASE    = [hidden]
DESCRIPTION = [hidden]
SERVER      = localhost
PORT        = 3306
UID         = [hidden]
PWD         = [hidden]
PASSWORD    = [hidden]
USER        = [hidden]

And I've written and compiled this Haskell Program:

import Database.HDBC.ODBC
import Database.HDBC
import System

main = do
  args <- getArgs
  c  <-  connectODBC (args!!0)
  tables <-  getTables c
  mapM_ putStrLn $ tables

When I try a DSN of "DSN=myodbc" it errors out with:

Database: SqlError 
  {seState = "[\"HY000\"]", 
    seNativeError = -1, 
    seErrorMsg = "connectODBC/sqlDriverConnect: 
      [\"1045: [unixODBC][MySQL][ODBC 5.1 Driver]Access 
        denied for user 'jamie'@'localhost' (using password: YES)\"]"}

However, when I try a DSN of "DSN=myodbc;UID=[hidden];PWD=[hidden]", it lists all the tables in the database.

It looks vaguely similar to this http://software.complete.org/software/issues/show/106 except that if I try runghc instead of compiling it errors out with "can't load .so/.DLL for: odbc", so all of my tests have been with a compiled version. — Jamie McCrindle, Aug 30 '09 at 22:44

score 1 · Answer 1 · answered Aug 31 '09 at 09:08

1

This may be a unixODBC problem rather than a Haskell / HDBC / HDBC-ODBC problem. Running "isql myodbc" results in a "Bus Error". Running "isql -v myodbc" doesn't give any more information. Running isql [uid] [pwd] connects just fine.

answered Aug 31 '09 at 09:08

Jamie McCrindle

9,114
6
43
48

score 0 · Answer 2 · answered Nov 12 '12 at 18:21

iODBC, maintained and supported by my employer, has shipped as part of Mac OS X since Jaguar (10.2.x).

You'll be better off updating iODBC with all the latest patches (Apple tends to be a bit behind on these), than shifting to UnixODBC.

It's generally best to keep all your ODBC configuration in the default file locations for Mac OS X --

/Library/ODBC/odbc.ini
/Library/ODBC/odbcinst.ini
/Users/*/Library/ODBC/odbc.ini
/Users/*/Library/ODBC/odbcinst.ini

You can create symlinks from anywhere else you may want to have these files, e.g. --

ln -s ~/Library/ODBC/odbc.ini ~/.odbc.ini

Last, you may benefit from testing with a commercial ODBC driver for MySQL, such as one of my employer's offerings (two week free trial provided as part of download).

Haskell, HDBC, ODBC, MySQL and Mac OS X

2 Answers2