unixODBC ODBC Driver Manager
This directory contains the unixODBC Driver Manager as distributed
with StarSQL for UNIX.
The version of unixODBC in this directory is 2.2.12.
For configuration and usage information, refer to the StarSQL for UNIX
Users Guide.
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.
Special considerations for Solaris:
The following iconv-related patches should be installed when using
unixODBC on Solaris 8:
Solaris 8:
Patch-ID# 113261-01
Keywords: UTF-8 ICONV
Synopsis: SunOS 5.8: UTF-8 locale ICONV patch
Date: Oct/11/2002
ODBC Applications:
The following applications from unixODBC are included in
$STARSQL/odbc/bin:
* isql
Syntax:
isql DSN [UID [PWD]] [options]
Options
-b Batch. No prompting etc
-dx Delimit cols with x
-w Wrap results in an HTML table
-v Verbose.
--version Display version number of isql
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.
The following GUI applications from unixODBC are not included in
the StarSQL distribution, but can be obtained in one of the following
ways:
* install the package supplied with your UNIX distribution for unixODBC GUI applications:
on Red Hat & Fedora Linux, install the unixODBC-kde RPM package
on SUSE Linux, install the unixODBC-gui-qt RPM package
on Ubuntu Linux, install the unixODBC-bin package
on FreeBSD: cd /usr/ports/databases/unixODBC; make install WITH_GUI
* download the source code from http://www.unixodbc.org
* contact StarQuest support
These applications require the the Qt class library (http://www.trolltech.com),
which is included in Linux and FreeBSD distributions, but must be obtained separately for
AIX, Solaris and HP-UX.
* 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 problems like a
"Can't SQLColumns" error. This is a known problem; a possible workaround
is to use the DefaultQualifier key in your ODBC DSN.
To use the SQL editor to submit SQL commands (either entered in the
application window or input from a file):
* 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.
* 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.
* Select the SQL pane
* Enter SQL statement
* Click on the Running Person icon
The results will appear in the Results icon
There is a File Menu for Open & Save:
While viewing the SQL pane, you can save the SQL statement entered or open
a text file containing a SQL statement.
While viewing the Results pane, you can save the results to a text file.
* 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" on SUSE Linux.
Third Party Applications:
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"
available on the support area of http://www.starquest.com
php and php-odbc:
Refer to the StarQuest tech note
"Using StarSQL with PHP and php-odbc"
available on the support area of http://www.starquest.com
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.
OpenOffice 1.x & StarOffice 6.0:
A document describing the use of unixODBC with OpenOffice.org 1.x
(the open source version of StarOffice 6.0)
"OpenOffice.org 1.0, unixODBC, and MySQL" is available at
http://www.unixodbc.org/doc.
* Sun StarOffice 5.1
To create a database document that other types of documents
(e.g. text document) can use:
Select File/New.. Database
and fill in the dialogs for ODBC, etc.
Using the database document in other modules:
Text Document:
File/Mail Merge
Edit/Exchange Database
drag cell or row from database window to word processing window
Spreadsheet:
Choose View/Explorer
than 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 critera, sort, etc.
You can use File/Autopilot to create
a new query
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.
usage:
- type applix -db to start the database component of Applixware.
- Select "Choose Server..." from the Query menu.
- Choose the "ShelfSQL" tab (do not use the ODBC tab)
- Set Gateway to "Shelf ODBC"
- Select "Specify Connection Info..."
- In the ODBC Connection dialog, select Browse.
This will display the available datasources in odbc.ini
(both machine and user DSN's).
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
$STARSQL/odbc/lib.ar containing the unixODBC driver manager in this format.
To use StarSQL and unixODBC with the JDBC-ODBC Bridge on AIX, include
$STARSQL/odbc/lib.ar in your LIBPATH in place of $STARSQL/odbc/lib:
e.g.
LIBPATH=$STARSQL/lib:$STARSQL/odbc/lib.ar
export LIBPATH
Do not include both $STARSQL/odbc/lib and $STARSQL/odbc/lib.ar in your
LIBPATH, as only the first one will be used. If you need access to
both flavors 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
0509-027 Member libodbc.o is not found or file is not an archive.
0509-022 Cannot load library libodbc.a[libodbc.o].
TROUBLESHOOTING:
Q: I'm getting the following error:
SQLState=08004, [StarSQL][StarSQL CLI Driver]Database server (unknown) V4R4M0
(QSQ04040) not supported by this version.
A: Make sure that you have copied swodbc.ini from $STARSQL/etc to your home
directory, and that you have have read/write 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. Such problems can usually be handled by a
simple edit to $HOME/.swodbc.ini; check the StarSQL tech notes at
www.starquest.com/support or contact Support.
Q: I'm getting the following error:
[unixODBC][Driver Manager]Data source name not found, and no default driver
specified, SQLSTATE=IM002
I know that /usr/local/etc/odbc.ini file is OK, and SQLDataSources() returns a
list of DSN's.
A: If you are receiving a "DSN not found" error, make sure your Driver= line in
your $HOME/.odbc.ini contains the entire path to libSWODBC.so.
* 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:
sushi{david}14: cd $STARSQL/bin
sushi{david}15: 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 trace
To turn on ODBC tracing, set the following in /usr/local/etc/odbcinst.ini:
[ODBC]
Trace = 1
Trace File = /tmp/sql.log
This will create a trace file /tmp/sql.log.
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 creating an [ODBC] block
containing Trace and Trace File parameters in the user's $HOME/.odbc.ini file.
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. CPTime=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.