unixODBC ODBC Driver Manager

August 2022

Introduction

This directory contains the unixODBC Driver Manager as distributed with StarSQL for UNIX.

The version of unixODBC Driver Manager in this directory is 2.3.7. The unixODBC GUI applications (ODBCConfig, DataManager, DataManagerII, and odbctest) are from unixODBC 2.1.14 and use QT3.

For configuration and usage information, refer to the StarSQL for UNIX User's Guide and the Quick Start Guide to Using the StarSQL ODBC Driver for UNIX/Linux. The chapter "Customizing the unixODBC Driver Manager Configuration" in the StarSQL for UNIX User's Guide contains important information for users who may already have a version of unixODBC supplied with their Linux or UNIX operating system or supplied by another vendor.

unixODBC is an open source project available under the GNU Library General Public License (LGPL). For more information about unixODBC, including documentation, or to obtain the source, see http://www.unixodbc.org. The unixODBC GUI applicationsC use Qt, which is a cross-platform application and UI framework available under both open source licenses (LGPL and GPL), as well as a commercial license - see http://qt.io/. Qt is included in most Linux distributions.

Note that StarSQL/Linux 2.44.0615 and later is also distributed in a "thin" RPM package that does not contain unixODBC; instead it relies on unixODBC RPM being installed from the distro or from the Microsoft yum repository. This package is 64-bit only and installs to /opt/StarQuest/starsql64 rather than /usr/share/starsql64. It does not contain any of the GUI applications.

ODBC Applications

The following unixODBC applications are included in $STARDIR/odbc/bin:

Command-line Tools


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.

 

GUI Applications

ODBCConfig

ODBCConfig is a graphical program for the configuration of ODBC data sources. The interface is similar to ODBC Administrator on Windows. See the StarSQL for UNIX User's Guide for details.

DataManager & DataManagerII

These GUI programs can be used to browse and explore ODBC data sources.

DataManager can:
- drill down a data source
- edit and submit SQL (when an active Data Source is selected)

While drilling down a data source, you may encounter the error message "Can't SQLColumns". This is a known problem; a possible workaround is to set the DefaultQualifer setting
to the schema of the table in your ODBC DSN.

To use the SQL editor to submit SQL commands (either entered in the application window or input from a file):

  1. Open a DSN . The color of the icon next to the DSN name will change from red to green to indicate the DSN is opened.
  2. Select that DSN. You will see a window on the right with 2 panels (labeled SQL and Results) and a button with an icon of a running person.
  3. Select the SQL pane.
  4. Enter SQL statement.
  5. Click on the Running Person icon. The results will appear in the Results icon.

There is a File Menu for Open & Save:

odbctest

This application is similar to the odbctest program supplied by Microsoft with the ODBC SDK. It provides a GUI interface to invoking ODBC calls.

Note that odbctest is called "qtodbctest" when installed on SUSE Linux as part of the SUSE-supplied unixODBC-gui-qt RPM package.

 

Using StarSQL with Third Party Applications:

Oracle Database Gateway for ODBC (dg4odbc)

Refer to the StarQuest 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 StarQuest tech note "Using StarSQL with the Perl DBI Module".

php and php-odbc

Refer to the StarQuest 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

TROUBLESHOOTING:

For additional hints, see the technical note titled "Common StarSQL for UNIX Error Messages" at http://www.starquest.com/Supportdocs/techStarSQL/browsesql.shtml.

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 StarQuest Support.

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. ldd is available with most operating systems. For older (4.3.3) versions of AIX, dump -H provides a similar function, and an AIX version of ldd is available from http://www.han.de/~jum/aix/ldd.c.

Example:

$ cd $STARDIR/bin
$ ldd simpleconn.odbc
libodbc.so.1 => /opt/starsql/odbc/lib/libodbc.so.1
libC.so.5 => /usr/lib/libC.so.5
libm.so.1 => /usr/lib/libm.so.1
libw.so.1 => /usr/lib/libw.so.1
libthread.so.1 => /usr/lib/libthread.so.1
libc.so.1 => /usr/lib/libc.so.1
libdl.so.1 => /usr/lib/libdl.so.1
/usr/platform/SUNW,Sun-Blade-1500/lib/libc_psr.so.1

ODBC tracing

To turn on ODBC tracing, use the Tracing panel of ODBCConfig, or set the following in /usr/local/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 /usr/local/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 /usr/local/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 /usr/local/etc/odbcinst.ini /tmp/odbc
$ vi /tmp/odbc/odbcinst.ini
$ ODBCSYSINI=/tmp/odbc; export ODBCSYSINI

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 /usr/local/etc

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

Known Issues

Using the ODBCINSTINI environment variable (which tells unixODBC to use a file other than /usr/local/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

 

Contacting Stelo

If you need to contact technical support, please provide the following information to help the support engineers address your issue. You can contact Stelo via phone, email, or facsimile as indicated at the bottom of this page.

Company Information Address
Phone
Contact Information First and Last Name of individual contact
Email Address
Host Type Hardware and Operating System (i.e., IBM i 7.3)
Network Protocol/Gateways Protocol and/or Gateways used (i.e., TCP/IP, SSL/TLS)
Client Type Client Type and Operating System (i.e., Windows Server 2019, Oracle Linux 8.6)
Stelo Product,
Version, and Source
Which Stelo product and what version is installed (i.e., StarSQL for Windows v6.41.0719)
Where you obtained the software (i.e., direct from Stelo, name of specific reseller)
Problem Information Provide as much detail as possible, including information about any application that is using the Stelo product when the problem occurs and the exact error message that appears.

© 2023 Stelo. All rights reserved.

Stelo logoStelo, a StarQuest company
548 Market St, #22938
San Francisco, CA 94104-5401
Telephone: +1 415.669.9619
Sales information: https://www.stelodata.com/contact-stelo
URL: https://www.stelodata.com
Support: https://support.stelodata.com
Info Center: https://docs.stelodata.com