Stelo Technical Documents

Using StarSQL for UNIX with unixODBC

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

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.

This technical document contains information and hints for using StarSQL with unixODBC.

For more configuration and usage information, see

• StarSQL for UNIX User's Guide
• Quick Start Guide to Using the StarSQL ODBC Driver for UNIX/Linux.
• Common StarSQL Error Messages
• Troubleshooting Tips for StarSQL for UNIX
• How to Report a StarSQL ODBC Driver for UNIX Problem.

Documentation and source for unixODBC are available at http://www.unixodbc.org.

Solution

• History
• unixODBC Applications (isql, iusql, odbcinst, odbc_config)
  
• Using StarSQL with Third Party Applications
  
• Pooling
  
• Developing ODBC Applications for StarSQL
  
• Troubleshooting
  
• ODBC & DRDA tracing
  
• Environment Variables
  
• Known Issues

History

Prior to StarSQL 6.50, a version of unixODBC built and tested by Stelo was included in the StarSQL installer to support operating systems that did not include unixODBC (e.g. AIX & Solaris) or certain Linux distributions that included an older version of unixODBC that was not adequate for supporting StarSQL. The StarSQL installer also included unixODBC GUI applications (ODBCConfig, odbctest, and DataManager) that depend on older versions of Linux packages that are difficult to install on current versions of Linux, so support for the GUI applications is now deprecated.

StarSQL 6.50 and later includes the following changes:

• The StarSQL installer does not include unixODBC; instead, the StarSQL RPM has a dependency on unixODBC (2.3.7 or later) supplied as an RPM from the Linux distro or from the Microsoft yum repository.
• The typical unixODBC RPM places odbcinst.ini & odbc.ini in /etc rather than /usr/local/etc. It also places the unixODBC libraries in /usr/lib64, so it is no longer necessary to use LD_LIBRARY_PATH. Similarly, the unixODBC binaries such as isql are in /usr/bin, so it is not necessary to modify PATH, and include files are in /usr/include, so it is not necessary to use the -I and -L directives when building ODBC applications.
• The unixODBC GUI applications (odbctest, DataManager, DataManagerII, and ODBCConfig) are not included.
• Configuration of ODBC data sources is done by using a text editor to modify ~/.odbc.ini or /etc/odbc.ini, rather than using the GUI application ODBCConfig.
• Two driver entries are created in /etc/odbcinst.ini:
  StarSQL64
  StarSQL (64-bit)
  We recommend using "StarSQL (64-bit)" for compatibility with StarSQL on Windows.
• Installers for 32-bit StarSQL and tar-based installers for non-RPM Linux systems are no longer provided.
• The StarSQL installers previously used a shell script setup to invoke rpm or tar commands to extract the contents and perform post-install tasks. setup and post-install are no longer used, and rpm is invoked directly.

unixODBC Applications

The following applications are part of unixODBC and typically installed to /usr/bin:

isql

isql is a utility which can be used to submit SQL to a data source and to format/output results. It can be used in batch or interactive mode, and is an ANSI ODBC application.

Syntax:
isql [options] DSN [UID [PWD]]

Options:

-b Batch mode - no prompting etc
-dx Delimit columns with x
-x0xXX Delimit columns with XX, where x is in hex, ie 0x09 is tab
-w Wrap results in an HTML table
-c Display column names on first row. (only used when -d)
-mn Limit column display width to n
-v verbose.
-lx Set locale to x
-q Wrap char fields in quotes
-3 Use ODBC v3 calls
-n Use new line processing
-e Use SQLExecDirect not Prepare
-k Treat the DSN parameter as a connection string and use SQLDriverConnect
e.g. isql -k "DRIVER=StarSQL (64-bit);Server=MYRDB;HostName=myhost;port=446;UID=myuid;PWD=mypwd;PkgColID=STARSQL"
-Ln length of column display (default 300)
--version Display version

Using the command "isql -v DSN UID PWD" will run the application in an interactive mode in which you can enter SQL statements. We recommend using the -v option in order to see error messages returned by the driver.

In addition to supplying ad-hoc query commands such as "SELECT * FROM MYTABLE", the help command can be used to make catalog calls; type help help to see a list of available commands:

help help - output this help
help - call SQLTables and output the result-set
help table_name - call SQLColumns for table_name and output the result-set
help catalog schema table type - call SQLTables with these arguments
where any argument may be specified as "" (for the empty string) or null to pass a null pointer argument.

e.g.
help % "" "" "" - output list of catalogs
help "" % "" "" - output list of schemas
help null null b% null - output all tables beginning with b
help null null null VIEW - output list of views

isql also supports redirection and piping for batch processing:

Examples
cat My.sql | isql WebDB MyID MyPWD -w

Each line in My.sql must contain exactly 1 SQL command except for the last line, which must be blank.

iusql

iusql is the Unicode version of isql; see isql for usage.

Note that iusql doesn't recognize the -k parameter for connection strings; instead start the connection string with a semicolon.
$ iusql -v ";DRIVER=StarSQL (64-bit);Server=MYRDB;HostName=myhost;port=50000;UID=myuid;PWD=mypwd;PkgColID=STARSQL"

odbcinst

odbcinst is a command line interface to key functionality in the libodbcinst library and can be used to configure data sources from the command line or a shell script, as well as other system-related tasks. Type odbcinst to display its help message.

odbc_config

odbc_config displays details about the installation and configuration of the unixODBC package. Type odbc_config to display its help message.

Using StarSQL with Third Party Applications

Oracle Database Gateway for ODBC (dg4odbc)

Refer to the Stelo tech note "Using StarSQL with the Oracle Database Gateway for ODBC".

perl plus DBI (database interface module) and DBD::ODBC module (ODBC Driver for DBI)

Refer to the Stelo tech note "Using StarSQL with the Perl DBI Module".

php and php-odbc

Refer to the Stelo tech note "Using StarSQL with PHP and php-odbc".

Pooling

unixODBC supports connection pooling - see http://www.unixodbc.org/doc/conn_pool.html for details.

To enable connection pooling, edit /usr/local/etc/odbcinst.ini:

* In the [ODBC] block, add Pooling = Yes
* In the [StarSQL] block, add a CPTimeout value, set to a non zero numeric value (the number of seconds a pooled connection will remain open if it is not being used)
e.g. CPTimeout=120

Pooling is only effective when used within a process. Note that there are some security risks that are described at http://www.unixodbc.org/doc/conn_pool.html

Developing ODBC Applications for StarSQL

To build your own ODBC application:

Install the unixODBC-devel RPM package, which contains header files needed for
building ODBC applications

# rpm -i unixODBC-devel

Since the header files are installed to /usr/include, and the libraries reside in /usr/lib64, you do not need to specify the include or lib directives.

Use -lobdbc to link your application with libodbc.so:

$ cc myprog.c -lodbc -lc -o myprog

See $STARDIR/samples/simpconn/build for examples of building a simple ODBC application that uses StarSQL to connect to a host.

Troubleshooting and other technical information

To verify connectivity, you can use simpleconn (supplied with StarSQL) or isql/iusql (supplied with unixODBC).

Resolving shared library references using ldd

If you suspect that there is a problem locating shared libraries, or you have multiple instances of a library and want to know which instance will be used (for instance, in the case of multiple ODBC driver managers), use the ldd command to list dynamic dependencies of executable files and shared libraries.

Example:

$ cd $STARDIR/bin
$ ldd simpleconn
linux-vdso.so.1 (0x00007ffd4ebf9000)
libodbc.so.2 => /lib64/libodbc.so.2 (0x00007f0a958d6000)
libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f0a95541000)
libm.so.6 => /lib64/libm.so.6 (0x00007f0a951bf000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f0a94fa6000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f0a94d86000)
libc.so.6 => /lib64/libc.so.6 (0x00007f0a949c1000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007f0a947bd000)
/lib64/ld-linux-x86-64.so.2 (0x00007f0a95b46000)

ODBC & DRDA tracing

To turn on ODBC tracing, set the following in /etc/odbcinst.ini:

[ODBC]
Trace = 1
TraceFile = /tmp/sql.log

This will create a trace file /tmp/sql.log. Note that you will need write access to /etc/odbcinst.ini.

Be sure to turn off the trace feature when it is no longer needed, as it will affect performance; using odbcinst.ini turns on tracing for all users.

An individual user can turn on tracing by copying /etc/odbcinst.ini to another directory, adding the above parameters to the copy of odbcinst.ini, and defining and exporting the ODBCSYSINI environment variable, which tells unixODBC to look in a different path for the odbcinst.ini file and system DSN file (odbc.ini):

$ mkdir /tmp/odbc
$ cp /etc/odbcinst.ini /tmp/odbc
$ vi /tmp/odbc/odbcinst.ini
$ ODBCSYSINI=/tmp/odbc; export ODBCSYSINI

To turn on DRDA tracing

• Verify that you have .swodbc.ini file in the user's home directory and that it is writable. For example:
  $ cp $STARSQL/etc/swodbc.ini .swodbc.ini
  $ ls -l .swodbc.ini $ chmod 644 .swodbc.ini

• Execute this command to enable tracing:

  $ trcstart -on /tmp/mytrace.sqd

• After collecting the trace, turn off tracing:

$ trcstart -off

 

Environment Variables

ODBCINI - full path to the the user DSN file; default $HOME/.odbc.ini

ODBCSYSINI - name of the directory where unixODBC can find odbcinst.ini file (driver definitions and tracing parameters) and odbc.ini (system DSN file); default /etc

ODBCINSTINI - name of file containing driver definitions and tracing parameters; default /etc/odbcinst.ini - DO NOT USE (see below)

Known Issues

Q: I am getting the following error:

SQLState=08004, [StarSQL][StarSQL CLI Driver]Database server (unknown) V4R4M0 (QSQ04040) not supported by this version.

A: Make sure that swodbc.ini exists in $STARDIR/etc and that you have read access to it. If the problem persists, it is possible that you are connecting to a new version of DB2 that StarSQL is not prepared to handle. Contact Stelo Support.

 

Using the ODBCINSTINI environment variable (which tells unixODBC to use a file other than /etc/odbcinst.ini or $ODBCSYSINI/odbcinst.ini) results in the following error:

SQLState = IM005, [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_DBC failed