Stelo Technical Documents

Using StarSQL for UNIX with the DataDirect ODBC Driver Manager

Last Update: 20 December 2022
Product: StarSQL
Version: All
Article ID: SQV00SU004

Abstract

An ODBC Driver Manager implements an API layer between an ODBC application and the ODBC drivers that provide access to specific databases. This ensures database independence for the application. In addition, the Driver Manager provides functions such as mapping between different ODBC API versions and allows Unicode applications to work with ANSI drivers.

On Windows, the Microsoft ODBC Driver Manager is included as a standard part of the Windows operating system.

On Linux and UNIX there are several options:

• unixODBC (http://www.unixodbc.org), an open source Driver Manager which ships with many Linux distributions; it is also distributed by Microsoft for use with the SQL Server ODBC driver for Linux.
• iODBC (http://www.iodbc.org), another open source Driver Manager.
• DataDirect/Progress (http://www.datadirect.com), a commercial product that is included with some commercial ODBC applications.
  

StarSQL is built with unixODBC, and in most cases the use of unixODBC is recommended. However, in some cases a user may wish to use another Driver Manager if it is included and supported by the ODBC application that is being used.

Solution

This technical document contains instructions on how to use StarSQL with the DataDirect Driver Manager. In the examples provided, we assume that the DataDirect Driver Manager is installed in /opt/odbc; substitute the actual location for the software as installed your system.

Bitwidth

You must match the bitwidth of the software components - on a 64-bit operating system, the ODBC application, the Driver Manager, and the ODBC driver (e.g. StarSQL) must all be either 64-bit or 32-bit.

Environment Variables

Library Search Path

The library search path environment variable should be set so that the Driver Manager can be located at the time of execution.

Solaris & Linux:
LD_LIBRARY_PATH=/opt/odbc/lib; export LD_LIBRARY_PATH

AIX:
LIBPATH=/opt/odbc/lib; export LIBPATH

HP-UX:
SHLIB_PATH=/opt/odbc/lib; export SHLIB_PATH

Do not include the StarSQL-supplied unixODBC Driver Manager (e.g. /opt/starsql/odbc/lib) in the library search path if you are using the DataDirect Driver Manager.

If you are using an ODBC application that was linked with unixODBC, such as the StarSQL-supplied utilities such as starping, simpleconn, & isql, you may need to create a symbolic link of the DataDirect driver manager named libodbc.so.1 or libodbc.so.2:

# cd /opt/odbc/lib
# ln -s libodbc.so libodbc.so.1
# ln -s libodbc.so libodbc.so.2

To verify that the correct Driver Manager is being used, use the ldd command to list the dynamic dependencies of the ODBC application.

DD_INSTALLDIR

This variable provides the driver with the location of the product installation directory so that it can locate support files, including the message catalog files. Failure to set this value will cause errors to be displayed as numeric values rather than text.

DD_INSTALLDIR=/opt/odbc; export DD_INSTALLDIR

Alternatively, you can specify InstallDir in the [ODBC] section of your odbc.ini file (see below).

ODBCINI

Set this to the location of your odbc.ini file (typically $HOME/.odbc.ini or /opt/odbc/odbc.ini).

ODBCINI=$HOME/.odbc.ini; export ODBCINI

This file should contain your StarSQL data source; in addition, it should contain an ODBC block that looks something like this:

[ODBC]
IANAAppCodePage = 4
InstallDir = /opt/odbc
Trace = 0
TraceFile = /tmp/odbctrace.out
TraceDll = /opt/odbc/lib/ddtrc27.so

If you fail to set ODBCINI, you may see this error message:
SQLState = 08004, [StarSQL][StarSQL CLI Driver]Some connection parameters are missing for Data Source.

ODBCINST

Set this to the location of your odbcinst.ini file. Set this to either the odbcinst.ini file created during installation of StarSQL (typically /usr/local/etc/odbcinst.ini) or the DataDirect-supplied file /opt/odbc/odbcinst.ini. If you choose the latter, you must add the StarSQL driver information to this file; use a text editor to copy the [StarSQL] section from /usr/local/etc/odbcinst.ini to /opt/odbc/odbc.ini.

If you fail to set ODBCINST, you may see this error message:
SQLState = IM005, [DataDirect][ODBC lib] Driver's SQLAllocHandle on SQL_HANDLE_DBC failed

Driver Keyword in data source

The StarSQL data source should use a full path for the Driver keyword
e.g.
Driver=/opt/starsql/lib/libSWODBC.so

Using Driver=StarSQL will result in the following error:

SQLState = IM003, [DataDirect][ODBC lib] Specified driver could not be loaded

Unicode Configuration

Add the following keyword to the ODBC data source to inform the DataDirect Driver Manager that StarSQL v6.x is a Unicode (UTF-16) driver:

DriverUnicodeType=1

If this keyword is missing or incorrect, the following error will appear:

[ODBC][DataDirect][ODBC lib]Unicode converter buffer overflow.

Do not use the DriverUnicodeType keyword with StarSQL 5.x, which is an ANSI ODBC driver.

Test Tools

To verify connectivity, you can use simpleconn (supplied with StarSQL), isql, odbctest, or DataManager (supplied with unixODBC), or the samples example and demoodbc provided by DataDirect.

For example, example (ODBCHOME/samples/example) can be used to connect to a data source and execute SQL. To run the program, type example and follow the prompts to enter your data source name, user name, and password. If successful, a SQL> prompt appears and you can type in SQL Statements

If you are using Informatica as your ODBC application, ssgodbc provides a similar connectivity test.

AIX Considerations

On AIX, create a symbolic link for the StarSQL driver:

# cd /usr/lpp/starsql/lib
# ln -s libSWODBC.a libSWODBC.so

Otherwise you will see the following error:

SQLState = IM003, [DataDirect][ODBC lib] Specified driver could not be loaded

On AIX, the following error may occur if you use the DataDirect Driver Manager with an ODBC application that was linked with unixODBC, such as the sample applications that are included with StarSQL. To use these applications with the DataDirect Driver Manager, they should be rebuilt and linked with the DataDirect Driver Manager.

exec(): 0509-036 Cannot load program simpleconn because of the following errors:
0509-150 Dependent module /opt/odbc/lib/libodbc.a(libodbc.so.1) could not be loaded.
0509-152 Member libodbc.so.1 is not found in archive