unixODBC ODBC Driver Manager

January 2018

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.5. 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.

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 "DSN=server;UID=test;PWD=test"
-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.

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".

OpenOffice 2.x & 3.x

StarQuest is preparing a tech note on using StarSQL and unixODBC with OpenOffice 2.x & 3.x. Contact StarQuest support for the latest information.

There are known issues with ODBC support in the 64-bit version of OpenOffice. We recommend either using the 32-bit version of OpenOffice and the 32-bit version of StarSQL, or use the JDBC support of either the 32-bit or 64-bit version of OpenOffice with StarSQL for Java.

OpenOffice 1.x & StarOffice 6.0

See the document "OpenOffice.org 1.0, unixODBC, and MySQL", describing the use of unixODBC with OpenOffice.org 1.x (the open source version of StarOffice 6.0), available at http://www.unixodbc.org/doc.

Sun StarOffice 5.1

To create a database document that other types of documents (e.g. text or spreadsheet documents) can use, Select File/New.. Database and fill in the dialogs for ODBC, etc.

To use the database document in other modules:

Text Document:
File/Mail Merge

Edit/Exchange Database and drag cell or row from database window to word processing window

Spreadsheet:
Choose View/Explorer, then use Explorer to drag to spreadsheet window

Choose View/Beamer - this displays the database in a window at the top you can drag a cell or row to a spreadsheet.

Presentation:
Choose View/Beamer. This displays the database in a window at the top you can drag a cell to a slide. Using View/Beamer, you can add criteria, sort, etc. You can use File/Autopilot to create a new query or a new table (based on existing database tables).

Applixware 5.0

- Applixware 5.0 ships with unixODBC and works with StarSQL. Previous versions of Applixware shipped with other ODBC driver managers and may not work with StarSQL.

  1. Type applix -db to start the database component of Applixware.
  2. Select "Choose Server..." from the Query menu.
  3. Choose the "ShelfSQL" tab (do not use the ODBC tab)
  4. Set Gateway to "Shelf ODBC"
  5. Select "Specify Connection Info..."
  6. In the ODBC Connection dialog, select Browse. This will display the available datasources in odbc.ini (both machine and user DSNs).

If you get an error "setuid to nnn: operation not permitted", this is because another user has previously used Shelf ODBC and the server process /opt/applix/axdata/axnet is already running under that user's ID. Request that the other user end his server process.

JDBC-ODBC Bridge on AIX:

The JDBC-ODBC Bridge supplied with the IBM JDK for AIX expects to find an ODBC driver manager in a certain format (an archive "libodbc.a" containing a member named "libodbc.o"). On AIX, StarSQL will install an alternative directory $STARDIR/odbc/lib.ar containing the unixODBC driver manager in this format.

To use StarSQL and unixODBC with the JDBC-ODBC Bridge on AIX, include $STARDIR/odbc/lib.ar in your LIBPATH in place of $STARDIR/odbc/lib:

e.g.
LIBPATH=$STARDIR/odbc/lib.ar
export LIBPATH

Do not include both $STARDIR/odbc/lib and $STARDIR/odbc/lib.ar in your LIBPATH, as only the first one will be used. If you need access to both versions of the driver manager - i.e. you will be using the JDBC-ODBC Bridge as well as other applications that use the unixODBC driver manager in its normal format, you will either need to change your LIBPATH environment frequently, or wrap your applications in a shell script that sets the environment variable before invoking the application.

There may be other AIX applications that expect the driver manager in this format; if you see a message like the following, then try using the lib.ar version:
exec(): 0509-036 Cannot load program <program name> because of the following:
0509-027 Member libodbc.o is not found or file is not an archive.
0509-022 Cannot load library libodbc.a[libodbc.o].

 

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 StarQuest

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

Company Information Address
Phone
Fax
Contact Information First and Last Name of individual contact
Email Address
Host Type Hardware and Operating System (i.e., AS/400 V5R4)
Network Protocol/Gateways Protocol and/or Gateways used (i.e., TCP/IP, HIS 2000)
Client Type Client Type and Operating System (i.e., Windows 2008 SP2)
StarQuest Product,
Version, and Source
Which StarQuest product and what version is installed (i.e., StarSQL for Windows v6.11.07.20)
Where you obtained the software (i.e., direct from StarQuest, name of specific reseller)
Problem Information Provide as much detail as possible, including information about any application that is using the StarQuest product when the problem occurs and the exact error message that appears.

© 2018 StarQuest Ventures, Inc. All rights reserved.

StarQuest Ventures logoStarQuest Ventures, Inc.
PO Box 1076
Point Reyes Station, CA 94956
Telephone: +1 415.669.9619
FAX: +1 415.669.9639
Sales information: https://www.starquest.com/contact
URL: https://www.starquest.com
Support: https://support.starquest.com
Info Center: https://www.starquest.com/docs