mpettis has asked for the wisdom of the Perl Monks concerning the following question:

UPDATE:

I should add for completeness that I do have a 64-bit version of strawberry perl on this system, and I can't recally, but I *might* have been using that to test my code inadvertently, not the 32-bit perl like originally posted. I didn't realize that it was ambiguous until I remembered that we installed strawberry on this server, and it became the default app to run files with .pl extenstions, which is how I was testing it. So sorry for that confusion -- it may have not been so confusing to begin with.

To be clear, both 32-bit and 64-bit now work fine, but I can't vouch for which way I was testing it for the results of my original post, but it was likely that I was running the 64-bit perl when I thought I was running the 32-bit exe. Apologies to all, and many thanks to mvaline for help.

Hello,

This is a problem which I've solved, but I don't know how or why, and I am hoping for some illumination from someone for my own benefit and for posterity.

I have a 64-bit Windows 7 system onto which I must put 32-bit Perl (work constraint). I am using DBI to connect to a Microsoft SQL server. The issue I had was that after I registered the DSN through the app launched at %WINDIR%\SysWow64\odbcad32.exe, I tested my connection, and everything was fine, but I got the perl error which contained the string 'SQL-IM014' ... basically, an architecture mismatch error between 32-bit perl and a 64-bit driver.

Well, I hunted around and found that that directory is not the only place that a 'odbcad32.exe' executable lives... So I launched the one at %windir%\system32\odbcad32.exe, which didn't show my previously registered DSN as registered. So I registered that there, and now it works.

My question is: what is the difference between these two executables (as they are named the same)? For database with which I use R, I had to register my DSNs in the former .exe, as that is where my drivers showed up when I installed them (they do not show up in the .exe of the latter). But the ones I use for perl had to be registered in the latter.

I suspect others might have or will soon run into this problem, and I'd like any insight as to what's going on (I know that this is tangentially perl, but I'm thinking it's close enough to log as a question here).

Thanks, Matt

Replies are listed 'Best First'.
Re: DBI Module, 32-bit Perl on 64-bit Windows 7: ODBC Connection confusion
by mvaline (Friar) on Feb 08, 2011 at 04:34 UTC

    WoW64 is an emulation layer that allows 32-bit Windows applications to run on 64-bit versions of Windows. The %WINDIR%\WoW64 directory contains 32-bit versions of the binaries located in %WINDIR%\System32 that interact with the emulation layer. Confusingly, the System32 directory on 64-bit Windows contains 64-bit binaries.

    Most of the information about ODBC DSNs and drivers is stored in the Registry. The WoW64 Registry Redirector component provides separate logical views of the Windows registry. In many cases, 32-bit and 64-bit applications read and write to what amounts to separate registries. The two copies of ODBCAD32.EXE write DSNs to the 64-bit and 32-bit logical registries. A subset of registry keys are kept in sync starting with the Windows 7 through something called registry reflection, and a few programs (mostly from Microsoft) are capable of looking in both places for various things. However, as you discovered, 32-bit ODBC drivers and DSNs need to be managed with %WINDIR%\SysWow64\ODBCAD32.EXE and 64-bit with %WINDIR%\System32\ODBCAD32.EXE.

    Update:

    I'm running the 64-bit version of ActiveState Perl 5.12.1 Build 1201. When I iterate through DBI->available_drivers(), I only see 64-bit DSNs for SQL Server. I'm surprised by your comment that you were only able to see your DSN after registering it with the 32-bit version of ODBCAD32.EXE from the SysWow64 directory. Has anyone else had the same experience? Could there by a 64-bit version of DBI installed under 32-bit Perl?

[reply]
[d/l]
[select]
      UPDATE 2:

      I should add for completeness that I do have a 64-bit version of strawberry perl on this system, and I can't recally, but I *might* have been using that to test my code inadvertently, not the 32-bit perl like originally posted. I didn't realize that it was ambiguous until I remembered the dev strawberry install, so sorry for that confusion.

      To be clear, both 32-bit and 64-bit now work fine, but I can't vouch for which way I was testing it for the results of my original post. Apologies.

      UPDATED:

      After reading a bit more carefully, and reading the links, I think I understand the post, but am still confused by the behavior of the system. If I'm understanding correctly, my perl install is 32-bit, so I'd think I'd have to use 32-bit drivers, which would have been registered in %WINDIR%\SysWow64\odbcad32.exe . But the opposite was true -- it didn't work until I registered them in the 64-bit app (%windir%\system32\odbcad32.exe).

      Likewise, my R install is 64-bit, but I used the 32-bit drivers (registered in %WINDIR%\SysWow64\odbcad32.exe).

      Either I'm fundamentally NOT getting this (likely), or this is seriously weird.

      ------------------------------------------

      Wow, thank you very much! That helped a ton! A good explanation of a very confusing situation...

      Matt
[reply]

        According to KB942976, system DSNs are stored in HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI. Registry redirection is enabled for this key, meaning that system DSNs for 64-bit and 32-bit applications are separated and only visible in the corresponding version of ODBCAD32. User DSNs are stored in HKEY_CURRENT_USER\Software\ODBC\ODBC.INI for which registry redirection is not enabled. Consequently, both versions of ODBCAD32 see all user DSNs.

        If you had created a 32-bit system DSN and Perl couldn't use the DSN, it must be the case that the DBI module was pulling the registry key from the 64-bit registry and using the 64-bit SQL Server ODBC Driver. Can you provide the actual error you received?

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom