Fun getting DBI/DBD::ODBC working on a 64bit Windows machine

Firstly, I should say I don't use Windows that much these days. The Windows machines where I test DBI and DBD::ODBC I set up ages ago with a Perl built with MS Developer Studio and I tend to keep them for that purpose. However, I bought a whole load of nice shiny new parts to build myself a new PC a month ago and installed Ubuntu and Windows as I thought I could get a more up to date Perl on Windows at home.

Now the last time I seriously installed Perl on Windows, ActiveState was the only player around for Windows unless you built Perl yourself. I did the latter as I could never get DBI and DBD::ODBC to build and work with ActiveState back then (I know it is a lot easily now with MS Developer Studio 6 or a recent Express or MinGW). Strawberry Perl seems to be the new contendor for Perl/Windows (thank you Curtis Jewell). I did try Strawberry Perl about 6 months or so ago but it was back when it had to install on your C drive and I didn't have one (my first drive was h:).

So I downloaded Strawberry Perl, installed it and then forgot about it as I had way too many other things to sort out with a new PC (and Windows 7 has moved so much around I got frustrated with it and mostly booted into Ubuntu/Linux).

One month on and I needed a Windows Perl setup to test out some scripts someone on irc was struggling with. I needed MS SQL Server so I installed MS SQL Server Express (far less painful than last time) and then the real pain started.

Where is the ODBC Administrator? It used to be control panel, Adminstration, ODBC Data Sources but with this new Windows 7 the control panel seems to have half the stuff it used to have and I could not find it. So I tried start menu and tapped in ODBC in the search but nothing. I went back to control panel and tapped in ODBC in the search there but nothing. Then I searched my system disk for odbc and found odbcad32.exe but surely that wasn't it as it has "32" in the name so it is bound to be the 32bit version of the ODBC administrator. Finally I thought about searching in control panel for "data sources" and it yielded the "Setup data sources (ODBC)" link so I clicked on that. hmm, but the about tab lists a whole load of files in the c:\windows\system32 directory - surely these are all 32bit binaries. I ignored that for now and set up a data source for MS SQL Server and tested it and it worked - excellent. Now back to Perl.

A simple one liner in Perl:

C:\Users\martin>perl -le "use DBI; my $h = DBI->connect('dbi:ODBC:asus2');print join(',', $h->tables);"

threw up:

C:\Users\martin>perl -MDBI -le "my $h = DBI->connect('dbi:ODBC:asus2');"
DBI connect('asus2','',...) failed: [Microsoft][ODBC Driver Manager] The specifed DSN contains an architecture mismatch between the Driver and Application (SQ-IM014) at -e line 1

Argh!

perl -V for this Strawberry Perl shows:

archname: MSWin32-x86-multi-thread

so it is a 32bit build - yes? I guess so. But the ODBC Administrator I used is called odbcad32.exe and resides in a system32 directory so surely that is 32bit too? No! So here lies an obvious problem of naming your executables based on architecture, when the architecture changes you have to change the name but MS decided it did not want to or could not for some reason! On top of that the path to odbcad32.exe is %windir%\system32\odbcad32.exe further suggesting it is 32bit. It turns out odbcad32.exe is actually 64bit and my Strawberry Perl is 32bit and you cannot mix the two (see Managing Data Sources). Of course I knew you could not mix 32bit applications and 64bit DLLs but it was hardly obvious from their names and locations.

So I uninstall Strawberry Perl and go and get the 64bit version of Strawberry Perl and install that. My simple one liner still does not work (something I did not note about DBI.dll being of the wrong architecture). OK, I'll reinstall DBI so I "cpan DBI" and it fails in the same way with a DLL incompatible error for DBI.dll. It turns out I'd installed DBI into Strawberry Perl and when I uninstalled the 32bit version it left an i:\strawberry dir around with a 32bit build of DBI. So I uninstall Strawberry Perl again and delete i:\strawberry and reinstall the 64bit Strawberry Perl. Now my one liner works and connects to the data source and returns a list of tables \o/

Now, I'm certainly not blaming Curtis or Strawberry Perl for any of this (obviously as it has nothing to do with them). You could argue some of it was my ignorance or my assumptions that an executable named something32.exe would be 32bit binary or that when I uninstalled something I should check it has removed files created after the install or that I should know how to find the ODBC Administrator when it is not in the control panel or start menu but really, this is rubbish. Unix has had 64bit for ages (OSF/Digital UNIX anyone on Alphas) and the mix of 32bit and 64bit shared objects was solved quite nicely in that you have lib32 and lib64 directories (or archives on AIX containing the 32bit and 64bit objects where the right one is picked at dynamic link time) and an easy "file" command that identifies the type of a binary or shared object. So what is with storing 64bit binaries in a directory called system32!

Hoping this long tale might help someone else who starts down this path.

As a side note, the 32bit ODBC administrator in Windows 64 is in %windows%\sysWOW64\odbcad32.exe! though heaven help you as the about tab here also shows the ODBC components being in c:\windows\system32 and on top of that even though I am the administrator of this machine when I click on "System DSNs" I get a warning saying "you are logged on with non-Administrative privileges. System DSNs could not be created or modified". However, after the warning I can create a SYSTEM DSN.

Comments

Win7 64 perl32 ODBC DBI

This worked for me: 1. Using the 32 bit ODBC tool at C:\Windows\SysWOW64\odbcad32.exe Create an ODBC connection called test. 2. Using 32 bit strawberry perl.
my $DSN = 'test';
my $dbh = DBI->connect("dbi:ODBC:$DSN") or die "$DBI::errstr\n";
my $sth = $dbh->prepare('select top 10 * from TB_MYTABLE')
    or die "Couldn't prepare statement: " . $dbh->errstr;

$sth->execute();

while( @data = $sth->fetchrow_array())
{
    foreach(@data) {
        print "[$_]";
    }
    print "\n\n";
}

$sth->finish;
$dbh->disconnect;

and yet more confusion

In the previous comment the data sources were system data sources and I just discovered an interesting (but confusing) thing if you change to User data sources. If you go into the 64 bit ODBC administrator and create a user DSN called test64u you can see it in the the 32 bit administrator and from a 32 bit application which calls SQLDataSources/data_sources. You can also connect to test64u from a 32 bit application which suggests some part of Windows or the ODBC Driver Manager knows the DSN exists put picks the appropriate driver.

I guess this is not that strange as if you used DSN-less connection strings the SQL Server Driver is called "SQL Server" in both cases. Still a bit confusing since System DSNs behave differently.

and further confusion

And now I discover another inconsistency. If I create DSN 1 called test32 in the 32 bit ODBC administrator and DSN 2 called test64 in the 64 bit ODBC administrator as you'd expect I can only see each one in its own administrator. When I call SQLDataSources (data_sources in Perl DBI) from a 32 bit Perl I only see test32 and from a 64 bit Perl I only get test64. However, if I attempt to connect to the test64 DSN from a 32 bit Perl I get:

"The specified DSN contains an architecture mismatch between the Driver and Application"

instead of data source not found!