MS SQL Connection Information

Fun times with ODBC

Contents

Quick Problem

On 64 bit Windows you need to use 32 or 64 bit ODBC, to match the version of Iguana that is installed.
Note: This is not an issue if you are running 32 bit Windows.

Quick Solution

Always create a 32 and 64 bit version for each ODBC source, this way Iguana will “choose” the correct one.
Note: This allows you to run a test machine with 32 and 64 bit Iguana installed.

  1. Use the default (64 bit) ODBC Admin Tool to create your 64 bit data source
  2. Close the default (64 bit) ODBC Admin Tool, do not omit this step
  3. Use the 32 bit ODBC administrator to create a 32 bit data source using the same name
    • Run from here: %SystemRoot%\SysWow64\odbcad32.exe. (default install: C:\windows\SysWow64\odbcad32.exe)

Warning: Differentiating between which tool is running can be confusing, because:

  • The 32 and 64 bit ODBC Admin Tools look identical
  • The 32 bit and 64 bit executable are both called odbcad32.exe, which can be confusing…
    • 64 bit executable: %SystemRoot%\System32\odbcad32.exe. (default install: C:\windows\System32\odbcad32.exe)
    • 32 bit executable: %SystemRoot%\SysWow64\odbcad32.exe. (default install: C:\windows\SysWow64\odbcad32.exe)
  • All the ODBC DLLS are in the “System32” directory and called odbcXX32.dll (the same DLLs are used for 32 and 64 bit ODBC)
  • If you forget step 2. (closing the 64 bit tool before running the 32 bit tool), the 64 tool remains active and can fool you into thinking it is the 32 bit tool
  • Both of the ODBC Admin Tools use the same DLLs, therefore:
    • Comparing the DLLs the About tab does not help, as they are identical
    • Comparing the DLLs the Drivers tab does not help much, as the same driver DLLs are used (though different ones may be loaded)
  • Also see this Microsoft support page: http://support.microsoft.com/kb/942976

Until we completely get rid of Chameleon there are some aggravations which come up for people using the 64 bit version of Iguana on windows to talk to Sybase and SQL Server. There are some easy workarounds to the problems you face though.

Here’s some of the pitfalls.

For Chameleon

Chameleon is a 32-bit application. Yep, that’s a pain. It’s written in Delphi, which doesn’t support 64-bit yet and probably never will. This is one of the many reasons it would be nice to not have the Translator depend on Chameleon. That’s our plan.

In the meantime, if you are running on a 64-bit version of Windows, which is increasingly becoming the norm, you’re going to love that Microsoft have a 32-bit ODBC manager and a 64-bit ODBC manager.

To get to the 32-bit ODBC Administrator tool, you’ll need to look in: %SystemRoot%\SysWow64\odbcad32.exe. Depending on your setup, that may be C:\windows\SysWow64\odbcad32.exe.

(We have a 64 bit Chameleon run-time library, but it’s just for people running Chameleon-based applications.)

For Iguana

If you installed the 64-bit version of Iguana, you’ll need to use the standard ODBC database manager. Otherwise, if you installed the 32-bit version of Iguana, you’ll need to use the 32-bit ODBC manager.

Other Caveats

  • Remember to install your datasource as a System DSN
  • If your are using a version of iguana that is older than 5.6.16 then don’t use the Native SQL Server driver. Native driver (SNAC) support was added in iguana 5.6.16. But don’t worry, the standard one (blue highlight), is very fast anyway
  • Make sure that the user ID that Iguana runs under can see the ODBC sources you set up
  • Be careful to to pass the full path to obdbcad32.exe. We had one user who just executed odbcad32.exe and it turned out to be a link to the 64 bit ODBC manager which caused no end of confusion

One good thing is that you can often set up SQL Server connections so that you don’t need to put usernames and passwords in. That can be handy.

If you do run into issues with ODBC during your evaluation of Iguana, let us know. We’re willing to help out. We know that it’s not the easiest problem to solve.

We’ve had this information around for a while – it’s just becoming the norm now rather than the exception:ODBC Connection Issues Using 64-Bit Windows

Sometimes the questions comes up whether we have plans to support other database connection standards like OleDB. This question usually comes from Microsoft SQL users.

We probably are not likely to do that. To understand why you need to look at what we would be seeking to achieve by clearly understand the problems with ODBC. Firstly these are not problems:

  1. ODBC is not slow. We support native direct to the wire interfaces to Oracle and MySQL interfaces using their native library APIs. We find these interfaces are not any faster than using ODBC with these databases.
  2. ODBC doesn’t have any size limitations – any limitations come about from the underlying database drivers. Under the hood we use parameter binding for the data so it means we can handle very large messages with no problem.

The problems that do exist with ODBC:

  1. It’s a bit of hassle setting up ODBC connections – for sites which have 100’s of interfaces this can be bit of chore although with tricks like exporting your registry this problem can be overcome.
  2. It has a bad brand.
  3. There is the annoyance like we see above with 32 bit vs. 64 bit systems – although that will go away once Iguana can edit and import table schema directly within it’s GUI without requiring Chameleon – at that point 64 bit users can use the 64 bit version of Iguana and not experience the issues above.

Our most immediate priority on the database side is therefore to eliminate Chameleon’s role in the whole process. It would greatly improve the usability of the Translator to not require Chameleon.

Continue: ODBC Connection Issues Using 64-Bit Windows