Installing ODBC

In Part 1 we looked at what ODBC does and how it works. We said that ODBC is divided logically into three parts: database drivers which are required for each database that is accessed, the Driver Manager which dynamically loads and unloads database drivers when connecting to a data source, and the Administrator which provides a user interface and routines for defining data sources from/for the database drivers. This month's article discusses where to get the Driver Manager and Administrator and how to install them. Installing each of the database drivers is held off until next month in Part 3 because the installation of each database driver is so different and specific to the database. We will also quickly cover installing the ODBC components on WinOS2.

Before we talk about installing ODBC we must talk about versions of ODBC. ODBC has evolved from the original version 1 to the latest version 3 for OS/2 (there is a version 3.5 available for Windows but it has never been implemented in OS/2.) Each later version added more features and functions to the ODBC specification. As a user accessing data in an application via ODBC you won't notice the difference between the versions because the application will mask the differences. However you must be aware of the versions in order to insure that the ODBC pieces work together. The versions affect the database drivers, the Driver Manager and the Administrator; the Administrator of any version will work with ODBC components of any other version, but the Driver Manager must be the same or a higher version than the highest version of any database driver you have. In other words, if you have an ODBC database driver that is version 2.5 and another database driver that is version 3 then you must have a Driver Manager that is at version 3 - but you can use any version Administrator you want. If all the database drivers you have are at version 2.5 then you only need a version 2.5 Driver Manager. The vast majority of ODBC database drivers are version 2.5; in fact the only database drivers that are at version 3 that I know of are bundled with Lotus SmartSuite.

Obtaining ODBC

In an ideal world the ODBC files - the Driver Manager and Administrator - would be included as part of OS/2. But since they aren't, you are going to have to go and get them. And since the companies that made the ODBC components for OS/2 don't want to sell them anymore, we have to be creative about where we get them.

The Driver Manager and Administrator are not available by themselves, but they are available bundled in various ways:

• In Lotus SmartSuite, which is available on its own and is also packaged with eCommStation. This is the best package to get since it is has the latest version (ODBC version 3) of the Driver Manager and Administrator. Unfortunately the Administrator has a bug and you need to get an Administrator from one of the following distributions.
• In the PostgreSql ODBC driver package (http://venuto.monrif.net/psqlodbcos2.zip) and the PostgreSQL database distribution (http://venuto.monrif.net/postgres7.0.2-server-bin_os2.zip). If you just want the ODBC files, download the ODBC driver package since it is smaller; the database distribution includes the ODBC stuff but it is a much larger file. Both packages include the ODBC version 2.5 Administrator and Driver Manager. The package is free to all end users. (PostgreSql itself is a nice database and will be covered in Part 3 next month.)
• In the DB2/2 OS/2 client application enabler (CAE) package. The version 5.2 CAE package (ftp://ftp.software.ibm.com/ps/products/db2/fixes/english-us/db2os2v5/client/FP15_WR21216/) includes an ODBC version 2.5 driver for DB2 and the Driver Manager and Administrator. You can also use the version 6 or version 7 CAE since they have the same ODBC files.
• In the Data Access Builder fix pack for VisualAge C++ for OS/2 version 3 (http://hobbes.nmsu.edu/cgi-bin/h-search?key=DAX11.ZIP) which includes various ODBC drivers and the ODBC version 2.5 Driver Manager and Administrator.

ODBC components, both drivers and the Driver Manager and Administrator, were also available in other bundles such as the short-lived IBM Visual Basic package and the Visual Query product, neither of which is readily available today.

The Driver Manager and Administrator were made and distributed by two different vendors: Intersolv (now Merant) and Visigenic (now Borland.) Components from those two vendors are interchangeable and interoperable. There is also a Driver Manager from the Unix iODBC project available for OS/2 which is not interoperable with the Visigenic/Intersolv components and doesn't work with most of the available ODBC applications for OS/2 - at least I have not been able to get them to work together.

Installing ODBC on OS/2

Installing ODBC involves:

• copying the ODBC executable and DLL files to some directory on the hard drive,
• creating the file ODBCINST.INI and ODBC.INI in some directory on the hard drive,
• adding the directory where the ODBC executable and DLL files were copied to the LIBPATH statement in the CONFIG.SYS file,
• adding a statement to the CONFIG.SYS file setting the environment variable ODBC_PATH to the directory where the ODBCINST.INI file was copied.

(Installing ODBC database drivers involves copying the driver file or files to your hard drive and adding statements to the ODBCINST.INI file for that driver, which is covered in next month's article: Part 3.)

I offer the following suggestions on the installation:

1. Place the ODBC executables and DLLs in separate directory dedicated to only ODBC files. This allows the ODBC directory to be moved in the LIBPATH statement in your CONFIG.SYS in order to fix conflicts between ODBC versions and between ODBC and other applications. If the ODBC DLLs are intermixed with other DLLs (for instance in C:\OS2) then it is impossible to move the directory in the LIBPATH statement to resolve DLL conflicts.
2. The ODBC ini files, ODBC.INI and ODBCINST.INI should be located in your OS2 directory on your boot drive. This is because some ODBC database driver installation routines assume the files are located in this directory.
3. Finally you need an environmental variable ODBC_PATH set to where the ODBC INI files are located, i.e. set to the OS2 directory on your boot drive. This is because some ODBC driver installation routines (the routines that install individual ODBC database drivers) look for that variable in order to determine where the INI files are located. The SET ODBC_PATH=C:\OS2 (assuming that C is your boot drive) statement should be in your CONFIG.SYS file.

All three bundles in the list above have problems either with their installation routines or with bugs in the installed files, therefore each of the packages has its own section below.

SPECIAL NOTE: If you have ODIN installed on your system you MUST rename the ODBC*.* files in the ODIN\SYSTEM32 directory to some other names. Otherwise they will conflict with the OS/2 ODBC files. Currently I do not know of any way for ODIN ODBC to co-exist with OS/2 ODBC.

Bundled in Lotus SmartSuite

The ODBC stuff is not installed by default. You have to use a "custom" install and select the ODBC items in order to get them to install. Lotus SmartSuite is located on the second CD of eCommStation in the SS directory. The install program is Install.Cmd.

Select CUSTOM install.

Each of the SmartSuite applications has a customize option that you use to specify what components you want to install.

In the application specific screen, the tabs at the top will get you to the ODBC parts. Be sure to select all the ODBC options. Do this for each of the applications - although not all the applications have ODBC parts available in the customize settings. The screen shot below show the customize install screen for WordPro. The screens for the other applications are similar. You may have to scroll the tabs at the top to get to the ones with ODBC.

Once you have completed the Lotus install, the ODBC stuff, and the various database driver files and the Driver Manager/Administrator files are located in x:\LOTUSW4\COMPNENT directory and the ODBC Administrator icon is located in the Lotus Accessories folder. There are three potential problems with this install:

1. The ODBC files are mingled with other Lotus files. If isn't so bad since they are at least in one directory, and you can just move the x:\LOTUSW4\COMPNENT in your LIBPATH statement if necessary. If you want to move the files to their own directory be sure to do it before you create any Data Sources, but you don't have to move the files.
2. The ODBC Administrator supplied in this distribution has a bug in it; the screen that shows the available ODBC Drivers does not have scroll bars so that if you have a lot of database drivers you may not be able to select them from the list. This can be fixed by installing and using the ODBC version 2.5 Administrator. The version 3 Administrator is in a notebook format with tabs along the top.

   

   The version 2.5 Administrator is shown below - it has buttons down the right side.

   

3. If you intend to use earlier versions of Intersolv drivers (Intersolv version 2.11 - ODBC version level 2.5) you must change some files and add some additional files. While the ODBC version 3 Administrator will work fine with ODBC version 2.5 database drivers Intersolv drivers have some specific requirements related to their unusual licensing routines and other DLLs.

The solutions for each of these potential problems are:

To move the ODBC files from the lotusw4\compnents directory you should:

1. Delete the ODBCINST.INI file, and the ODBC.INI file if it exists, from your C:\OS2 directory. Remember that if the ODBC.INI file exists, you have at least one Data Source defined and you will have to re-define it after the move.
2. Run the ODBCINST program and specify the x:\LOTUSW4\COMPNENT directory as the source directory. Check the Install Administrator and Driver Manager check box and select all the ODBC drivers listed and press the Install button. (The ODBCINST program is discussed below.)

You can solve the ODBC Administrator bug by installing the v 2.5 Administrator and then use the v 2.5 administrator by itself, or use both.

1. Copy the files: ODBCADM.EXE, ODBCINT.DLL, ODBCINST.DLL, ODBCCP.DLL and OS2UTIL.DLL to a directory separate from the Driver Manager files (odbc.dll, etc.).
2. Create an program icon for the Administrator pointing to the file ODBCADM.EXE
3. Copy ODBCCP.DLL from v 2.5 to driver manager directory.
4. Edit your CONFIG.SYS file and make sure the LIBPATH statement has the entry .; (dot semicolon) before any ODBC directories. The .; entry should really be at the very start of the LIBPATH statement anyway.
5. (Optional) If you want to run both administrators (but not at the same time) create another directory - different from the one where you placed the v 2.5 Administrator and different from the Driver Manager - and put the files ODBCADM.EXE, ODBCCP.DLL, ODBCINT.DLL from the v 3 distribution in that directory.

Since the file ODBCCP.DLL is common to the administrator and the driver manager, if you are running the v 3 driver manager and the v 2.5 administrator you should not open the administrator when any ODBC applications are running.

While the ODBC version 3 Driver Manager will run v 2.5 and lower drivers, Intersolv database drivers use some additional files for license control and other stuff. Driver Manager will not run earlier version Intersolv database drivers without some massaging. To run Intersolv v 2.11 and v 3.01 use the following steps:.

1. Copy the Intersolv v 2.11 distribution files to the ODBC directory, i.e. where you store the Driver Manager files.
2. Copy the file ODBCCP.DLL to some other location temporarily or rename the file so that it won't be overwritten in the next step.
3. Copy the Intersolv v 3.01 files to the ODBC directory.
4. Copy back ODBCCP.DLL to the ODBC directory, or rename the file back if you renamed it in step 2.

Some final advice: If you also have DB2 installed, move the SQLLIB path statements in the LIBPATH part of your config.sys after the LOTUSW4/COMPNENT statement (or the directory where you move the ODBC files.) To avoid conflict with PMVIEW when running Matrox 2.23 drivers move the LOTUSW4/COMPNENT statement after the OS2 and OS2/DLL statements in the LIBPATH statement in CONFIG.SYS. Otherwise PMVIEW will freeze up.

Bundled with PostgreSQL

The PostgreSql ODBC driver includes the ODBC v 2.5 Driver Manager and Administrator. Unfortunately, the installation program included with that driver incorrectly installs the ODBC components. When the program is finished you cannot add PostgreSql as an ODBC Data Source using the Administrator. The easiest way to fix this problem is to use the ODBC installer program. ODBCINST (http://hobbes.nmsu.edu/cgi-bin/h-search?key=ODBCINST.zip) (You could also copy the files manually and manually create the ODBCINST.INI file if you prefer.)

To use ODBCINST, start the program ODBCINST.EXE. Enter either the file psqlodbcos2.zip as the source file, or unzip psqlodbcos2.zip to a temporary directory and enter that directory as the source.

Enter the destination for the ODBC INI files. The default should not be changed unless you have a very good reason to do so.

If you want to install the PostgreSql ODBC driver, select it from the list. In the figure below, two drivers, Watcom SQL and SQL Anywhere, are in the driver list. The drivers that appear in the list will depend on what drivers are the source directory/file and what databases you have installed on your machine.

Bundled in DB2

DB2/2 comes with ODBC v 2.5, and a nice, working, ODBC installer. To install the ODBC Driver Manager and Administrator click on the ODBC Installer icon.

This brings up the following dialog box. The Install Directory is where Driver Manager and Administrator files are located (actually they are located in BIN and DLL subdirectories.) The Driver Directory is where the DB2 ODBC database driver files are placed. The ODBC INI files are always put (or modified) in the directory pointed to by the ODBC_PATH environment variable, or if that doesn't exist, in the C:\OS2 directory. If the ODBC_PATH variable points to a non-existent directory then the ODBC INI files are not created and no error message is given.

Bundled with Visual Age DAX

The best way to install the ODBC components is to use the ODBC Installer application ODBCINST. The steps are similar to installing from the PostgreSql bundle.

1. Enter the DAX11.ZIP file (fully qualified file name) in to the Source page.
2. Enter the destination directories - where you want the files to go.
3. Select the ODBC driver you want to install, if any.
4. Press the Install button.

WinOS/2

WinOS2 applications, since they are Windows 3.1 applications, use their own Windows 3.1 specific ODBC. You have to install the Windows ODBC files and any Windows ODBC database drivers you need in order to use ODBC from WinOS2. There is no "bridge" between WinOS2 ODBC and OS/2 ODBC that I am aware of. The company OpenLink makes a bridge that allows ODBC applications in one operating system to use ODBC drivers in another operating system - which would work perfect for WinOS2 to use OS/2 ODBC drivers, but they have not released that bridge for OS/2.

Windows ODBC is easy to come by. Almost all Windows applications that access data include the ODBC setup and some drivers. This include MS Access, Excel, Delphi, ReportSmith, etc.

Conclusion

ODBC is not integrated into OS/2 as it is in Windows, which means we must work harder to install it. However the install is not that painful and the rewards are great. Once installed it works well and provides all the same benefits to us on OS/2 as it does on Windows. Next month in Part 3 we discuss the ODBC drivers and ODBC enabled databases that exist for OS/2. While I could not get my hands on all the drivers and/or databases that existed for OS/2, I did get ahold of a lot of them. We discuss where to get them and how to install them. And we do a mini-comparison of OS/2 hosted databases that are ODBC enabled.

This article is courtesy of www.os2ezine.com. You can view it online at http://www.os2ezine.com/20010616/page_9.html.