![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
Open Database Connectivity (ODBC) is an interface to provide universal data access to relational and non-relational database servers. It is a software API based on the X/Open Call Level Interface (CLI) used to access data from a database server. It allows you to write database applications using a standard API that is common to all ODBC Drivers so that you don't need to write your application in a specific way for a specific database. This helps reduce the development time and makes the solution more portable.
Typical components of an ODBC application are:
- ODBC client: An application that uses the ODBC API to interact with a database server.
- ODBC Driver: A library or set of libraries that contains the implementation of the ODBC API for a particular database server.
- ODBC Driver Manager: A library or set of libraries that can be used by an ODBC client to interact with an ODBC Driver.
The Informix ODBC Driver is included as part of the IBM Informix CSDK package. It follows the Microsoft® Open Database Connectivity (ODBC) version 3.5 standards. The Informix ODBC Driver supports several IBM Informix database servers such as IBM Informix Ultimate Edition and IBM Informix Choice Edition. See the Resources section for a link to the IBM Informix ODBC Driver release notes that show the database server compatibility.
On Microsoft Windows®, the ODBC Driver is automatically registered during the CSDK installation. The name of the Informix ODBC Driver is IBM Informix ODBC Driver.
On UNIX® and Linux® platforms, the ODBC Driver is normally referenced by the name of the library. The following list describes the different versions of the Informix ODBC Driver libraries included in the Informix CSDK package:
- libifcli.a or libcli.a: Static version for a single non-threaded application
- libifcli.so or iclis09b.so: Shared version for a single non-threaded application
- libthcli.a: Static version for a multi-threaded application
- libthcli.so or iclit09b.so: Shared version for a multi-threaded application
- libifdrm.so or idmrs09a.so: Shared library for the driver manager Replacement (DMR)
The file extension varies from platform to platform.
As Figure 1 shows, ODBC applications can use these libraries in the following three configurations:
- Link to the driver directly (A): The Informix ODBC Driver library is directly used by the ODBC application.
- Link an ODBC Driver Manager (B): The ODBC Client application links to an ODBC Driver Manager, and the ODBC Driver Manager performs all the operations required to load and use the ODBC Driver acting as an intermediate layer between the application and the ODBC Driver.
- Link to the Driver Manager Replacement and the driver (C) : In addition to the Informix ODBC Driver, the Informix CSDK package includes an Driver Manager Replacement (DRM) library (not available on Windows). This library provides most of the features of an ODBC Driver Manager and can be used by an ODBC Client if there is no ODBC Driver Manager installed on the machine.
Figure 1. ODBC client applications connectivity to Informix server
The ODBC Driver Manager is a software layer that manages the communications between the user application and ODBC Driver. The ODBC Driver Manager performs jobs such as loading and unloading the appropriate ODBC Driver required by the application, as well as verification of the API parameters passed to the driver. When an application uses a driver manager, the API calls are handled by the ODBC Driver Manager, which performs basic error checking and then processes these calls, or passes them on to the underlying ODBC Driver if needed.
ODBC Driver Manager software is available in the market from third-party vendors for most UNIX and Linux platforms. On some operating systems such as Microsoft Windows or MAC OS X, the ODBC Driver Manager is a part of the operating system.
The typical configurations files used by the ODBC Driver Manager are:
- odbcinst.ini: Contains information specific for the Driver Manager such as which drivers are available to the Driver Manager, and configuration parameters shared by all DSN.
- odbc.ini: Contains information of ODBC Data Source Name or DSN. A DSN defines a connection with the database. The odbc.ini provides all the information required for the connection to the database and configuration parameters specific for the ODBC Driver.
The odbcinst.ini file contains a list of installed ODBC drivers on the UNIX system, and specific attributes for each driver, such as the location of the shared library. This file is usually part of the ODBC Driver Manager, and its default location is the system /etc directory.
Included as part of the Informix CSDK package is the sample odbcinst.ini file shown in Listing 1 that is included in the installation directory under the /etc subdirectory (pointed by the INFORMIXDIR environment variable).
Listing 1. Sample odbcinst.ini file
[ODBC Drivers] IBM INFORMIX ODBC DRIVER=Installed [IBM INFORMIX ODBC DRIVER] Driver=/extra/informix/lib/cli/iclit09b.so Setup=/extra/informix/lib/cli/iclit09b.so APILevel=1 ConnectFunctions=YYY DriverODBCVer=03.51 FileUsage=0 SQLLevel=1 smProcessPerConnect=Y |
The odbc.ini file contains data source configuration information and is used by the ODBC Driver and the ODBC Driver Manager. The default location of the odbc.ini is the user home directory (/odbc.ini) but this location can be changed using the ODBCINI environment variable.
A sample odbc.ini file for the Informix ODBC Driver is provided with the CSDK package, as showing in Listing 2. It is located in the CSDK installation directory (usually pointed by the INFORMIXDIR environment variable) under the etc subdirectory.
Listing 2. Sample odbc.ini file
[ODBC Data Sources] Infdrv1=IBM INFORMIX ODBC DRIVER Infdrv2=IBM INFORMIX ODBC DRIVER ; ; Define ODBC Database Driver's Below - Driver Configuration Section ; [Infdrv1] Driver=/extra/informix/lib/cli/iclit09b.so Description=IBM INFORMIX ODBC DRIVER Database=stores_demo LogonID=odbc pwd=odbc Servername=ids_server1 [Infdrv2] Driver=/extra/informix/lib/cli/iclis09b.so Description=IBM INFORMIX ODBC DRIVER Database=stores_demo LogonID=odbc pwd=odbc Servername=ids_server2 CursorBehavior=0 CLIENT_LOCALE=en_us.8859-1 DB_LOCALE=en_us.8859-1 TRANSLATIONDLL=/extra/informix/lib/esql/igo4a304.so ; ; UNICODE connection Section ; [ODBC] ;uncomment the below line for UNICODE connection ;UNICODE=UCS-4 ; ; Trace file Section ; Trace=0 TraceFile=/tmp/odbctrace.out InstallDir=/extra/informix TRACEDLL=idmrs09a.so |
See the Resources section for a link to the IBM Informix ODBC Driver Guide where you can get a complete description of all the odbc.ini configuration parameters.
Using the ODBC Driver with a ODBC Driver Manager
Some of the parameters specified in the odbc.ini are critical when using the Informix ODBC Driver with a third-party ODBC Driver Manager. The most common problem when setting up the ODBC Driver is the Unicode parameter.
The Unicode parameter in the odbc.ini file is used to specify the Unicode flavor required by the driver. It varies between platforms and even between ODBC Driver managers. The IBM Informix ODBC Driver supports UCS-2, UCS-4 and UTF-8 encodings, as shown in Table 1.
Table 1. Default Unicode values for ODBC Driver Manager
| DRIVER MANAGER | Unicode |
|---|---|
| UnixODBC | UCS-2 |
| DataDirect | UTF-8 |
| iODBC | UCS-4 |
The following section contains examples of a working ODBC.INI file for the most common platforms.
Linux distributions do not normally include an ODBC Driver Manager.
UnixODBC, an open source ODBC Driver Manager, is the most common driver manager used on Linux platforms. You can find information on how to build and configure the UnixODBC in the Resources section.
The default Unicode encoding used by the UnixODBC Driver Manager is UCS-2. This means that the Unicode parameter in the odbc.ini file should be UCS-2, otherwise applications using the Unicode ODBC API will fail to work properly.
The output in Listing 3 shows the contents of the odbc.ini file used with UnixODBC Driver Manager.
Listing 3. The odbc.ini file used with UnixODBC Driver Manager
[stores7] Driver=/usr3/370uc1/lib/cli/iclit09b.so Description=IBM INFORMIX ODBC DRIVER Database=stores7 LogonID=informix pwd=password Servername=irk1150 ; ; UNICODE connection Section ; [ODBC] ;uncomment the below line for UNICODE connection UNICODE=UCS-2 ; ; Trace file Section ; Trace=0 TraceFile=/tmp/odbctrace.out InstallDir=/usr3/370uc1 TRACEDLL=idmrs09a.so |
There are two ODBC client tools included as part of the UnixODBC Driver Manager. These tools can be used to test if the configuration of the ODBC Driver is valid.
ISQL is a basic ANSI ODBC client, It uses the ANSI ODBC API so it’s not affected by the Unicode parameter. IUSQL is similar to ISQL but it uses the Unicode ODBC API.
Before attempting to use the ODBC Driver, make sure the environment is set up properly. The Informix ODBC Driver uses the Informix native libraries to communicate with the database server. Environment variables such as INFORMIXDIR and INFORMIXSQLHOSTS are used by these libraries to locate the configuration and resources files needed for connection. In the following sample, the Informix CSDK package was installed in "/usr3/csdk370" so the environment variable INFORMIXDIR points to that directory:
informix@irk:/usr3/370uc1$ cat $INFORMIXDIR /usr3/csdk370 |
The name of the IDS server you are using (as defined in the odbc.ini) is irk1150, which means that you should have the communication parameters for that server in the Informix sqlhosts file.
informix@irk:/usr3/370uc1$ cat INFORMIXSQLHOSTS | grep irk1150 irk1150 onsoctcp irk.bedfont.uk.ibm.com 9089 |
The output shown in Listing 4 shows how to use the IUSQL tool using the odbc.ini.
Listing 4. Using IUSQL
informix@irk:/usr3/370uc1$ iusql -v stores7
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> SELECT dbinfo('version', 'major') version FROM systables WHERE tabid=1
+--------+
| version|
+--------+
| 11 |
+--------+
SQLRowCount returns -1
1 rows fetched
SQL>
|
Having an incorrect Unicode value in the odbc.ini will results in an error as shown in Listing 5.
Listing 5. Example using incorrect Unicode value
informix@irk:/usr3/370uc1$ grep UNICODE $ODBCINI ; UNICODE connection Section ;uncomment the below line for UNICODE connection UNICODE=UCS-4 informix@irk:/usr3/370uc1$ iusql -v stores7 [unixODBC][ [unixODBC][ [ISQL]ERROR: Could not SQLDriverConnect informix@irk:/usr3/370uc1$ |
The error is caused due a mismatch between the Unicode encoding used by the application (UCS-2) and the encoding use by the driver manager (UCS-4).
Another ODBC Driver manager that can be used on a Linux OS is Progress DataDirect. On Linux platforms, the DataDirect ODBC Driver Manager uses UTF-8 as Unicode encoding. Included in the install package, there are a couple of ODBC client applications that you can use to test if the configuration of the ODBC Driver is valid.
The output in Listing 6 shows the demoodbc
binary connecting to an Informix database server through the IBM Informix
ODBC Driver.
Listing 6. Using demoodbc to connect to Informix
$ grep UNICODE $ODBCINI ; UNICODE connection Section ;uncomment the below line for UNICODE connection UNICODE=UTF-8 $ $ pwd /usr3/datadirect/samples/demo $ ./demoodbc -uid informix -pwd password stores7 ./demoodbc DataDirect Technologies, Inc. ODBC Sample Application. ./demoodbc: will connect to data source 'stores7' as user 'informix/password'. First Name Last Name Hire Date Salary ---------- --------- --------- ------ Tyler Bennett 2077-06-01 32000 John Rappl 2087-07-15 47000 George Woltman 2082-08-07 53500 Adam Smith 2088-01-15 18000 ... ... SQLFetch returns: SQL_NO_DATA_FOUND $ |
The Unicode parameter is the only change in the odbc.ini file between UnixODBC and DataDirect. As before, if the value of this parameter is not right, then the application will fail to connect without any meaningful error message, as shown in Listing 7.
Listing 7. Example using incorrect Unicode
$ grep UNICODE $ODBCINI ; UNICODE connection Section ;uncomment the below line for UNICODE connection UNICODE=UCS-2 $ ./demoodbc -uid informix -pwd password stores7 ./demoodbc DataDirect Technologies, Inc. ODBC Sample Application. ./demoodbc: will connect to data source 'stores7' as user 'informix/password'. SQLConnect: Failed... SQLSTATE = 365 NATIVE ERROR = 0 MSG = 523 364 SQLConnect: Retrying Connect. SQLConnect: Failed... SQLConnect: Retrying Connect. SQLConnect: Failed... SQLConnect: Retrying Connect. No connection could be established. $ |
The UnixODBC on an AIX machine uses UCS-2 for Unicode, as shown in Listing 8.
Listing 8. Unicode on AIX
$ uname -a AIX portland 3 5 005929BA4C00 $ grep UNICODE $ODBCINI ; UNICODE connection Section ;uncomment the below line for UNICODE connection UNICODE=UCS-2 $ echo $INFORMIXDIR /usr2/support/products/SDK-3.70.UC1 $ iusql -v stores7 informix password +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> select tabid from systables where tabname='systables' +------------+ | tabid | +------------+ | 1 | +------------+ SQLRowCount returns -1 1 rows fetched SQL> quit $ |
Any other value will result in a failure during the connection.
When using the DataDirect ODBC Driver Manager on a AIX platform, UTF-8 is the Unicode value required in the odbc.ini. The output in Listing 9 shows the odbc.ini contents used on a AIX machine.
Listing 9. odbc.ini contents on AIX
$ echo $INFORMIXDIR /usr2/support/products/SDK-3.70.UC1 $ cat $ODBCINI [stores7] Driver=/usr2/support/products/SDK-3.70.UC1/lib/cli/iclit09b.so Description=IBM INFORMIX ODBC DRIVER Database=stores7 LogonID=informix pwd=password Servername=gholmes1150 ; ; UNICODE connection Section ; [ODBC] ;uncomment the below line for UNICODE connection UNICODE=UTF-8 ; ; Trace file Section ; Trace=0 TraceFile=/tmp/odbctrace.out InstallDir=/usr2/support/products/SDK-3.70.UC1 TRACEDLL=idmrs09a.so $ |
Just as on AIX, by default, the UnixODBC Driver Manager needs the Unicode parameter in the odbc.ini file to be set to UCS-2, as shown in Listing 10.
Listing 10. odbc.ini on Solaris
$ uname -a SunOS gholmes 5.9 Generic_122300-17 sun4u sparc SUNW,Ultra-5_10 $ which iusql /usr/local/bin/iusql $ ldd /usr/local/bin/iusql libodbc.so.1 => /usr/local/lib/libodbc.so.1 libiconv.so.2 => /usr/local/lib/libiconv.so.2 libodbcinst.so.1 => /usr/local/lib/libodbcinst.so.1 libltdl.so.7 => /usr/local/lib/libltdl.so.7 libreadline.so.4 => /usr/local/lib/libreadline.so.4 libcurses.so.1 => /usr/lib/libcurses.so.1 libdl.so.1 => /usr/lib/libdl.so.1 libthread.so.1 => /usr/lib/libthread.so.1 libc.so.1 => /usr/lib/libc.so.1 libgcc_s.so.1 => /usr/local/lib/libgcc_s.so.1 /usr/platform/SUNW,Ultra-5_10/lib/libc_psr.so.1 $ grep UNICODE $ODBCINI ; UNICODE connection Section ;uncomment the below line for UNICODE connection UNICODE=UCS-2 $ iusql -v stores7 +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> quit $ |
DataDirect ODBC Driver Manager on the other hand, uses UTF-8 for Unicode, as shown in Listing 11.
Listing 11. DataDirect Driver Manager on Solaris
$ pwd /usr3/datadirect/samples/demo $ ldd demoodbc libodbc.so => /usr3/datadirect/lib/libodbc.so libCrun.so.1 => /usr/lib/libCrun.so.1 libm.so.1 => /opt/SUNWspro/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 libsocket.so.1 => /usr/lib/libsocket.so.1 libdl.so.1 => /usr/lib/libdl.so.1 libIXicu20.so => /usr3/datadirect/lib/libIXicu20.so libodbcinst.so => /usr3/datadirect/lib/libodbcinst.so libnsl.so.1 => /usr/lib/libnsl.so.1 libposix4.so.1 => /usr/lib/libposix4.so.1 libmp.so.2 => /usr/lib/libmp.so.2 libaio.so.1 => /usr/lib/libaio.so.1 libmd5.so.1 => /usr/lib/libmd5.so.1 /usr/platform/SUNW,Ultra-5_10/lib/libc_psr.so.1 /usr/platform/SUNW,Ultra-5_10/lib/libmd5_psr.so.1 $ grep UNICODE $ODBCINI ; UNICODE connection Section ;uncomment the below line for UNICODE connection UNICODE=UTF-8 $ ./demoodbc -uid informix -pwd password stores7 ./demoodbc DataDirect Technologies, Inc. ODBC Sample Application. ./demoodbc: will connect to data source 'stores7' as user 'informix/password'. First Name Last Name Hire Date Salary Dept ---------- --------- --------- ------ ---- Tyler Bennett 2077-06-01 32000 D101 John Rappl 2087-07-15 47000 D050 George Woltman 2082-08-07 53500 D101 … … |
The OpenSource iODBC Driver Manager uses 4 bytes for the Unicode code
points (UCS-4). The sample in Listing 12 shows the output of the
iodbctest iODBC sample tool connecting to an
Informix database through the Informix ODBC Driver.
Listing 12. The iodbctest iODBC sample tool
./iodbctest iODBC Demonstration program This program shows an interactive SQL processor Driver Manager: 03.51.0001.0908 Enter ODBC connect string (? shows list): dsn=stores7;uid=informix;pwd=ximrofni Driver: 3.50.0000 3.50.U SQL>select tabid from systables where tabid=1 tabid ----------- 1 result set 1 returned 1 rows. SQL> SQL>quit Have a nice day.# # grep UNICODE $ODBCINI ;uncomment the below line for UNICODE connection UNICODE=UCS-4 # |
Listing 13 shows UnixODBC on a HP/UX machine, with UCS-2 for Unicode.
Listing 13. UnixODBC on a HP/UX
# uname -a HP-UX nanook B.11.11 U 9000/800 2812716602 unlimited-user license # grep UNICODE $ODBCINI ; UNICODE connection Section ;uncomment the below line for UNICODE connection UNICODE=UCS-2 # iusql -v stores7 informix password +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> quit # |
Unicode=UTF-8 is needed for the DataDirect ODBC Driver Manager, as shown in Listing 14.
Listing 14. Unicode=UTF-8
# grep UNICODE $ODBCINI ; UNICODE connection Section ;uncomment the below line for UNICODE connection UNICODE=UTF-8 # pwd /usr3/products/DataDirect/samples/demo # ./demoodbc -uid informix -pwd password stores7 ./demoodbc DataDirect Technologies, Inc. ODBC Sample Application. ./demoodbc: will connect to data source 'stores7' as user 'informix/password'. First Name Last Name Hire Date Salary Dept ---------- --------- --------- ------ ---- Tyler Bennett 2077-06-01 32000 D101 John Rappl 2087-07-15 47000 D050 George Woltman 2082-08-07 53500 D101 … … |
The output in Listing 15 shows a simple ODBC client code which connects to
a given DSN, and selects the first three rows from the
systables catalog table.
Listing 15. ODBC client code
informix@irk:~$ cat test.c
/* ----- test.c ------ */
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <signal.h>
#include "sql.h"
#include "sqlext.h"
#define BUFFER_LEN 10
#define ERRMSG_LEN 200
SQLINTEGER CheckError ( SQLRETURN rc,
SQLSMALLINT handleType,
SQLHANDLE handle,
SQLCHAR* errmsg)
{
SQLRETURN retcode = SQL_SUCCESS;
SQLSMALLINT errNum = 1;
SQLCHAR sqlState[6];
SQLINTEGER nativeError;
SQLCHAR errMsg[ERRMSG_LEN];
SQLSMALLINT textLengthPtr;
if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO))
{
while (retcode != SQL_NO_DATA)
{
retcode = SQLGetDiagRec (handleType, handle, errNum, sqlState,
&nativeError, errMsg, ERRMSG_LEN,
&textLengthPtr);
if (retcode == SQL_INVALID_HANDLE)
{
fprintf (stderr, "SQL_INVALID_HANDLE!\n");
return 1;
}
if (( retcode == SQL_SUCCESS) ||
( retcode == SQL_SUCCESS_WITH_INFO))
fprintf (stderr, "ERROR: %d: %s : %s \n",
(int)nativeError, sqlState, errMsg);
errNum++;
}
fprintf (stderr, "%s\n", errmsg);
return 1;
}
else
return 0;
}
int main (long argc, char* argv[])
{
SQLHDBC hdbc;
SQLHENV henv;
SQLHSTMT hstmt;
SQLCHAR dsn[100];
SQLRETURN rc = 0;
SQLINTEGER in;
SQLCHAR sname[15+1];
SQLLEN lcode = 0;
SQLLEN lsname = 0;
if (argc != 2)
{
fprintf (stdout, "Please specify a DSN!\n");
return(1);
}
else
sprintf((char *) dsn, "%s", (char *)argv[1]);
fprintf (stdout, "Connecting to DSN : %s\n", dsn);
rc = SQLAllocHandle (SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
if (rc != SQL_SUCCESS)
{
fprintf (stdout, "SQLAllocHandle failed\n");
return ;
}
rc = SQLSetEnvAttr (henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, 0);
if (CheckError (rc, SQL_HANDLE_ENV, henv, (SQLCHAR *) "SQLSetEnvAttr failed\n"))
return ;
rc = SQLAllocHandle (SQL_HANDLE_DBC, henv, &hdbc);
if (CheckError (rc, SQL_HANDLE_ENV, henv, (SQLCHAR *) "SQLAllocHandle failed\n"))
return ;
rc = SQLConnect (hdbc, dsn, SQL_NTS, (SQLCHAR *) "", SQL_NTS,
(SQLCHAR *) "", SQL_NTS);
if (CheckError (rc, SQL_HANDLE_DBC, hdbc, (SQLCHAR *) "SQLConnect failed\n"))
return ;
fprintf (stdout, "Connected to %s\n",dsn);
rc = SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hstmt );
if (CheckError (rc, SQL_HANDLE_DBC, hdbc, (SQLCHAR *) "SQLAllocHandle failed\n"))
return ;
rc = SQLPrepare (hstmt, (SQLCHAR *) "SELECT FIRST 3 tabname FROM systables;",
SQL_NTS);
if (CheckError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "SQLPrepare failed\n"))
return ;
rc = SQLExecute (hstmt);
if (CheckError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "SQLExecute failed\n"))
return ;
rc = SQLBindCol (hstmt, 1, SQL_C_CHAR, &sname, sizeof(sname), &lsname);
if (CheckError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "SQLBind failed\n"))
return ;
while ((rc = SQLFetch(hstmt))!=SQL_NO_DATA_FOUND)
fprintf (stdout, "TABNAME = %s\n", sname);
SQLFreeStmt (hstmt, SQL_CLOSE);
SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
SQLDisconnect (hdbc);
SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
SQLFreeHandle (SQL_HANDLE_ENV, henv);
}
/* ----- test.c ------ */
|
Using the –lodbc flag instructs the linker to add the ODBC library to the executable, as shown in Listing 16.
Listing 16. The -lodbc flag
informix@irk:~$ cc test.c -lodbc -o test informix@irk:~$ ldd test linux-gate.so.1 => (0xb8072000) libodbc.so.1 => /usr/local/lib/libodbc.so.1 (0xb7ff1000) libc.so.6 => /lib/tls/i686/cmov/libc.so.6 (0xb7e8e000) libltdl.so.7 => /usr/lib/libltdl.so.7 (0xb7e85000) libdl.so.2 => /lib/tls/i686/cmov/libdl.so.2 (0xb7e81000) libpthread.so.0 => /lib/tls/i686/cmov/libpthread.so.0 (0xb7e68000) /lib/ld-linux.so.2 (0xb8073000) informix@irk:~$ ./test stores7 Connecting to DSN : stores7 Connected to stores7 TABNAME = systables TABNAME = syscolumns TABNAME = sysindices informix@irk:~$ |
You can verify common connect level problems by checking the following issues when you try to set up a third-party driver manager.
- The INFORMIXDIR environment variable might be set incorrectly.
- The INFORMIXSQLHOSTS variable has the proper values for your Informix server.
- Check and make sure the shared library path is setup correctly, for example: LD_LIBRARY_PATH, SHLIB_PATH).
- Make sure you use the same 32 vs 64 binaries. You can use a tool like file/truss/strace to check which libraries files are been loaded, which could point to errors in the environment.
- You can also debug the problems by using UnixODBC Driver Tracing , ODBC Driver Tracing , or SQLIDEBUG .
An example error is
[ISQL]ERROR: Could not SQLDriverConnect.
UnixODBC Driver Manager tracing
UnixODBC Driver tracing provides you information about the names of the ODBC APIs that are getting called from the application, as well as their parameters. If a serious error occurs, (i.e., which could be a problem in unixODBC), you can find it in the source code file by looking at the line number from where it is generated. You can enable tracing by setting Trace parameter to Yes in ODBCINST.INI file as shown in Listing 17.
Listing 17. Trace parameter set to Yes
[ODBC] ; Trace file Section ; Trace=Yes TraceFile=/tmp/odbctrace.out |
ODBC Driver tracing provides you with information about the names of the ODBC APIs that are getting called from the application, as well as their parameters. When developing ODBC applications, common mistakes are often made while giving parameter values for API’s. You can generate a trace file of all ODBC calls to the ODBC Driver using the provided ODBC Trace facility. On a Windows system, you can enable ODBC Trace using the ODBC Data Source Administrator (odbcad32.exe). On UNIX machines you can enable tracing by setting Trace parameter to 1 in the ODBC.INI file, as shown in Listing 18.
Listing 18. Enable trace on UNIX machines
; Trace file Section ; Trace=1 TraceFile=/tmp/odbctrace.out InstallDir=/usr2/support/products/SDK-3.70.UC1 TRACEDLL=idmrs09a.so |
The output in Listing 19 shows some entries in a ODBC trace file.
Listing 19. Entries in ODBC trace file
ppid=0010303e,pid=00000001 EXIT SQLPrepare SQLHSTMT 0x20154AA8 SQLCHAR * select tabname from systables where tabid=1; SQLINTEGER 45 with return code 0 (SQL_SUCCESS) ppid=0010303e,pid=00000001 EXIT SQLPrepareW SQLHSTMT 0x20154AA8 SQLWCHAR select tabname from systables where tabid=1; SQLINTEGER -3 with return code 0 (SQL_SUCCESS) ppid=0010303e,pid=00000001 ENTER SQLExecute SQLHSTMT 0x20154AA8 ppid=0010303e,pid=00000001 EXIT SQLExecute SQLHSTMT 0x20154AA8 with return code 0 (SQL_SUCCESS) ppid=0010303e,pid=00000001 ENTER SQLNumResultCols SQLHSTMT 0x20154AA8 SQLSMALLINT * 804399056 ppid=0010303e,pid=00000001 EXIT SQLNumResultCols SQLHSTMT 0x20154AA8 SQLSMALLINT * 804399056 with return code 0 (SQL_SUCCESS) |
ODBC uses SQLI protocol to communicate with the IDS server. The SQLIDEBUG trace contains all messages communicated between the client application and the IDS server. You can enable SQLIDEBUG trace at the Informix Client side by defining the environment variable as follows: SQLIDEBUG=2:/tmp/sqlitrace.
The Informix communication libraries will trace all the SQLI messages into a series of files, with one file per connection, and with the prefix path provided in the SQLIDEBUG variable.
The SQLIDEBUG contains SQLI packages communicated between the server and the client in a binary format. To obtain a readable output, you must use the sqliprint tool (called sqliprt.exe on Windows platforms). This tool is also included as part of the Informix CSDK package.
The output in Listing 20 shows how to set SQLIDEBUG, as well as how to decode the SQLI trace file generated.
Listing 20. Set SQLIDEBUG
informix@irk:/usr3/370uc1$ export SQLIDEBUG=2:/tmp/sqlitrace informix@irk:/usr3/370uc1$ iusql test informix password +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> select tabid from systables where tabname='systables'; +------------+ | tabid | +------------+ | 1 | +------------+ SQLRowCount returns -1 1 rows fetched SQL> quit informix@irk:/usr3/370uc1$ ls -la /tmp/sqlitrace* -rwxr-xr-x 1 informix informix 766 2011-06-02 09:50 /tmp/sqlitrace_28920_12100_8627490 informix@irk:/usr3/370uc1$ informix@irk:/usr3/370uc1$ sqliprint -o trace.txt /tmp/sqlitrace_28920_12100_8627490 informix@irk:/usr3/370uc1$ |
The output in Listing 21 shows a section of the decoded trace.txt file.
Listing 21. Decoded trace.txt file
C->S (68) Time: 2011-06-02 09:50:06.84851 SQ_PREPARE # values: 0 CMD.....: "select tabid from systables where tabname='systables'; " [55] SQ_NDESCRIBE SQ_WANTDONE SQ_EOT S->C (62) Time: 2011-06-02 09:50:06.85346 SQ_DESCRIBE Stmt Type...........: 2 Server Stmt Id......: 0 Estimated Cost......: 0 Size of output tuple: 4 # output fields.....: 1 Size of string table: 6 0) Field 'tabid' Index into string table: 0 Starting offset in tuple: 0 Type....................: SERIAL; NOT NULLABLE Length : 4 (0x4) SQ_DONE |
You can find additional information regarding ODBC and SQLIDEBUG traces in the Resources section.
This article has served as a quick guide to using Informix ODBC Driver with third-party driver managers such as UnixODBC and Data Direct. In addition, it provided troubleshooting techniques for common connection problems and tracing options.
Learn
- See the IBM Informix ODBC Driver Guide where you can get a complete
description of all the odbc.ini configuration parameters that show the
database server compatibility.
- Find the database server compatibility
for the IBM Informix ODBC Driver.
- Visit the developerWorks resource page for Informix to read articles and
tutorials and connect to other resources to expand your Informix skills.
- Stay current with developerWorks technical events
and webcasts focused on a variety of IBM products and IT industry
topics.
- Learn more about Information Management at
the developerWorks
Information Management zone. Find technical documentation, how-to
articles, education, downloads, product information, and more.
- Follow developerWorks on
Twitter.
- Watch developerWorks on-demand demos
ranging from product installation and setup demos for beginners, to
advanced functionality for experienced developers.
Get products and technologies
- Download the IBM Informix Client Software Development Kit (SDK).
- Download a trial
version of Informix.
- Download the unixODBC Driver Manager.
- Find additional
information regarding ODBC and SQLIDEBUG in the IBM Informix Developer's Handbook.
- Build your next
development project with IBM trial
software, available for download directly from developerWorks, or
spend a few hours in the SOA Sandbox learning how to
implement Service Oriented Architecture efficiently.
Discuss
- Participate in the discussion forum.
- Check out the developerWorks
blogs and get involved in the developerWorks
community.
Muralidhar V K Vanapalli works in the IBM Data Server Client Drivers team with IBM Software Labs, India. He has been with IBM Software Labs for over three years, with overall experiences of more than five years. He worked with various client drivers in Informix and DB2. He got working expertise in developing RTP/RTCP protocol, Symbian C++ applications, and Device Drivers in Linux/Windows.
Javier Sagrera is a software engineer on the Common Client Technologies (CCT) group. He joined the Informix team in 2000 and has over 10 years experience in application development for Informix database servers and Informix clients. Currently based in the IBM UK Bedfont Lab in London, he has extensive knowledge on all the Microsoft® technologies and is considered as a subject matter expert worldwide on all the Informix development tools.
Table of contents
Next steps from IBM
Tags
Use the slider bar to see more or fewer tags.
For articles in technology zones (such as Java technology, Linux, Open source, XML), Popular tags shows the top tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), Popular tags shows the top tags for just that product zone.
For articles in technology zones (such as Java technology, Linux, Open source, XML), My tags shows your tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), My tags shows your tags for just that product zone.
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
