Use Informix ODBC Driver with an ODBC Driver Manager

Because there are many different types of ODBC Driver managers available in today's market, many users find it complicated to configure the IBM® Informix® ODBC Driver to work with third-party driver managers such as UnixODBC, Progress DataDirect, or iODBC. This article describes the components used by an ODBC application and provides a quick reference of the configuration parameters needed to set up IBM Informix ODBC with a third-party ODBC Driver Manager.

Share:

Muralidhar Vanapalli (muralidhar.vvk@in.ibm.com), Staff Software Engineer, IBM

Photo of author Muralidhar V K VanapalliMuralidhar 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 (javier.sagrera@uk.ibm.com), Software Engineer, IBM

Photo of author Javier SagreraJavier 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.



04 August 2011

Also available in Chinese

Overview

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.

Informix 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
illustrates the 3 configurations for ODBC apps

ODBC Driver Manager

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

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

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 MANAGERUnicode
UnixODBCUCS-2
DataDirectUTF-8
iODBCUCS-4

The following section contains examples of a working ODBC.INI file for the most common platforms.

Linux

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

AIX

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
	$

Solaris

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
#

HP/UX

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           
	…
	…

ODBC sample code

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:~$

Troubleshooting

Common connection problems

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

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)

SQLIDEBUG trace

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.


Conclusion

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.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=750281
ArticleTitle=Use Informix ODBC Driver with an ODBC Driver Manager
publish-date=08042011