ODBC Connectivity tips

Posted by on in Programming

In my recent CodeRage X talk I presented many examples of ODBC connectivity. However, there was a some more I wanted to say, but did not have time to do so. So, below is that content.

  • ODBC administrator has a 64 bit and 32 bit version on 64 bit versions of Windows. If you are using ODBC administrator from Control Panel, you are using the 64 bit version. To run the 32 bit version, you need to run c:\windows\syswow64\odbcad32.exe. The settings you make in the 64 bit or 32 bit version of ODBC administrator are the same, but if you want to test exactly the same as how your ODBC client application connects, you might want to use the right one.
  • When the InterBase ODBC driver installs, it checks to see if the InterBase client is already installed where ODBC Administrator is installed. There are separate checks for the 32 bit and 64 bit client. If an existing client is found, then a new client is not installed. This can be especially problematic if you have a client previous to InterBase XE3 installed and you need to connect to an InterBase XE3 or later server. This is because strong passwords were introduced with InterBase XE3 and versions of the InterBase client prior to InterBase XE3 cannot connect using strong passwords. The solution to this problem in most cases is to run the InterBase installer and install just the client. However, if you need to continue to use an older version of InterBase client, then you'll need to implement weak passwords on the server. That is discussed here.
  • The InterBase ODBC driver returns only 1 error, which is "Open database 'your database name' failed". So, if the path to your database is wrong, or the server name is wrong, or the user name is wrong, or the password is wrong, the server is not running, or an old client is found (see above), you'll get this same error. The only way to test which is wrong is to test using a different tool and the same connection properties.  The best way to test is to copy a tool of the same bitness to the directory where ODBC Administrator is being run from and run it from there. For example, if you are making a 32 bit connection on a 64 bit OS, copy either the 32 bit version of isql.exe or IBConsole.exe to c:\windows\syswow64. If you are making a 64 bit connection, copy the 64 bit version of isql.exe to c:\windows\system32. You cannot test a 64 bit client connection with IBConsole because IBConsole is a 32 bit application.
  • Here is a series of steps you can follow to test ODBC connectivity (other than ODBC Administrator)
    • Run File Explorer. Right click in a directory you have full rights to.
    • Select New | Text Document.
      • Rename the file you just created to have a .UDL extension. For example: New Text Document.udl.
      • Double click on the file. The will bring up the Data Link Properties dialog box.
      • Select the Provider tab and select the Microsoft OLE DB Provider for ODBC Drivers
      • Click Next and you should now have a similar interface to what your ODBC application has for connectivity.

These steps will do pretty much what any 64 bit application will so. Testing what a 32 application does on a 64 bit OS is a bit more complicated. I'm going to rely on an expert from Microsoft for this one. For details see: http://blogs.msdn.com/b/chaitanya_medikonduri/archive/2008/04/09/how-to-run-32-bit-udl-file-on-a-64-bit-operating-system.aspx


Gold User, Rank: 35, Points: 77
Support guy since 3/18/85. It was dBASE II/III back then.