Connecting PowerBuilder to Informix Using Native Drivers

This document is a supplement to the connection information found in the Connecting to your Database manual provided. It goes into more detail regarding Informix client software and provides more debugging information.

Connecting PowerBuilder to Informix

Overview

This document is a supplement to the connection information found in the Connecting to your Database manual provided. It goes into more detail regarding Informix client software and provides more debugging information.

Informix Client Software

The following connectivity products are shipped by Informix; at least one of the following must be installed on your client in order to connect to an Informix database.

Informix-Connect

Informix-Connect (I-Connect) is a runtime connectivity product that includes the runtime libraries of the Informix APIs which comprise the Informix Client SDK product. These libraries are required by applications running on client machines in order to access Informix servers.

How PowerBuilder Talks to Informix

Versions and Naming Convention

The driver used by PowerBuilder is determined by the first three characters of your DBMS specification for the transaction object - which is reflected in the middle three letters in the actual dll name, as in PBxxx90.DLL.

Native Drivers and Informix Client Software DLLs

These native drivers call functionality in the Informix client software dlls as shown below.

	PowerBuilder 7	PowerBuilder 8	PowerBuilder 9
PB Native Driver	pbin770.dll pbin970.dll	pbin780.dll pbin980.dll	pbin990.dll
Informix Client Software Required	Informix ESQL v7.2 (for pbin770.dll) Informix ESQL v9.1 (for pbin970.dll)	Informix Connect ESQL v7.2 (for pbin780.dll) Informix Connect ESQL 9.1 (for pbin980.dll)	Informix Connect 9.x
Informix Client SDK 2.81	Not Supported	pbin980.dll	pbin990.dll
Support Database versions	Informix -OnLine versions 7.x and 9.x Informix -SE versions 7.x and 9.x	Informix Dynamic Server Informix Online and Informix-SE versions 7.x and 9.x	Informix Dynamic Server Informix Online and Informix-SE version 9.x
I-Connect 7 runtime dll	ISQLT07C.dll	ISQLT07C.dll	ISQLT07C.dll
I-Connect 9 runtime dll	ISQLT09A.dll	ISQLT09A.dll	ISQLT09A.dll
Informix Client SDK 2.81 runtime dll	N/A	ISQLT09A.dll	ISQLT09A.dll

Setnet for Informix Environment Variables and Login Information

Informix supplies the configuration tools Setnet (I-Net) and Setnet32 (I-Connect) to configure the connection information to the server.

I-Connect
Setnet32 settings such as hostname, servicename, user information etc. are written to the registry into the following locations:
- Environment variables
HKEY_CURRENT_USER\Software\Informix\Environment
- Login Information per defined Informix server
HKEY_LOCAL_MACHINE\SOFTWARE\Informix\SqlHosts\<informixserver>
- User Information
HKEY_CURRENT_USER\Software\Informix\netrc\<hostname>

Be sure the Windows environment variable INFORMIXDIR points to the installation directory of I-Net or I-Connect. Informix software products are designed to be installed in a single directory identified by this environment variable.

Informix and the Services File

The client machine needs an entry in the services file for each server the client needs to access.

The services file resides in the <Windows> boot directory or on NT 4.0 in the directory <WinDir>\System32\drivers\etc

The entry consists of <servicename> <portnumber>/<protocoll>
An Informix Online server installation defaults to:
turbo 1526/tcp

Informix SE (Standard Engine) uses sqlexec

Testing Outside of PowerBuilder

Before attempting to connect from PowerBuilder you should verify that you can connect from I-Login. I-Connect version 9 also supplies DBPing to test the connection.

I-Login

To test a connection with I-Login you can fill in values for Hostname, Servicename (if you need to), otherwise it will get the information
from the registry.

DBPing

In I-Connect 9.x you can also use DBPing to verify the connection outside of PowerBuilder. On the Connection tab, like with I-Login, you can specify the User and Login information or test the connection to the default settings held in the registry.

Connecting to Multiple Informix Servers

In order to connect to mulitple Informix servers from PowerBuilder at the same time another transaction object other than SQLCA needs to be created, like the following:

transaction INFORMIX2
INFORMIX2 = CREATE transaction

// set all required connection parameters of the transaction object
...
INFORMIX2.servername = "<hostname>@<informixserver>"
...
CONNECT using INFORMIX2;

Make sure that the Informix servers listen to different service names.

Server parameter in Transaction Object

To distinguish the servers the servername parameter of the transaction object has to be set as follows:

<transactionobject>.servername = "<hostname>@<informixserver>"

Please refer to the manual Connecting to Your Database, chapter Defining the Informix database interface for more information.

Debugging

PowerBuilder Trace

All PowerBuilder native driver connections can be traced by checking the Generate Trace checkbox in the Connection tab of the profile or by putting the word 'trace' in front of the driver identification of the DBMS property of the transaction object, e.g.: SQLCA.DBMS = "trace IN9". For more information on tracing, please refer to the manual Connecting to Your Database, chapter Troubleshooting your Connection.

Informix Trace

Informix provides a trace option that in some cases can supply useful information. For I-Connect, the tracing on the client can be activated by setting the Windows environment variable SQLIDEBUG like this:
2:<drive>:\<path>\<filename>
example:
2:c:\informix\inetout

SQLIDEBUG produces a binary output that can be made readable with the Informix program sqliprt.exe.
From a DOS prompt it can be called, as follows:

sqliprt -o <output file> <input file>
sqliprt -o in7.log inetout

Informix supplies the sqliprt.exe with Informix Client SDK 7.30 and higher. Sqliprt.exe is not included with Informix Client SDK 2.81. The log file can be read with a previous Informix Client version of sqliprt.exe.

DBParms

The following DBParms apply to the Informix native interface. These options can be set in the PowerBuilder profile, or programmatically in PowerScript DBParm values are set as follows:
<transactionobject>.<DBParm>=<value>
example:
SQLCA.DisableBind=1

For more information on these parameters, please refer to the manual Connecting to Your Database:

Async

Allows you to perform asynchronous operations on your database in PowerBuilder

ConnectString

Specifies the parameters required to connect to an ODBC data source.

CommitOnDisconnect

Specifies whether PowerBuilder should commit (the default) or roll back all previously uncommitted database updates before disconnecting from a data source.

DateTimeAllowed

The setting DateTimeAllowed=1 allows DateTime columns to be used in the WHERE clause of a SQL statement. DateTimeAllowed is a connection parameter and must be specified before the connection from PowerBuilder.

DBGetTime

Specifies the number of seconds you want PowerBuilder to wait for a response from the DBMS when you retrieve rows in a DataWindow object, query, or report. Used in conjunction with the Async dbparm.

DecimalSeparator

When your Regional Settings are set to a country using a comma as DecimalSeparator and you require the Informix environment variable DBMONEY to be set to a comma (e.g. 'DM,') you must set DecimalSeparator=',' in PowerBuilder. This ensures that under these circumtsances money and decimal column values are retrieved properly.

DelimitIdentifier

Specifies whether you want PowerBuilder to enclose the names of tables, columns, indexes, and constraints in double quotes when it generates SQL statements. This affects the behavior of any PowerBuilder painter that generates SQL syntax.

INFORMIX database servers can create a log of database transactions in either ASCII or non-ASCII format. If the database is creating a non-ASCII log, the setting of the DelimitIdentifier is optional. If the database is creating an ASCII log, you must set DelimitIdentifier = 'Yes' in order that the SQL syntax generated by PowerBuilder behaves as expected.

DisableBind

Specifies whether to bind input parameters to a compiled SQL statement.

INET_DBPath

Specifies the Informix DBPATH setting.

INET_Protocol

Specifies the network protocol that the Informix client software uses.

INET_Service

Specifies the name of the service that a remote Informix database server uses to listen to for requests.

OJSyntax

Specifies how PowerBuilder formats the SQL syntax for outer joins for the database backend you are accessing.

Scroll

Specifies whether to use a scroll cursor for fetch next row, previous row, etc.

ThreadSafe

Specifies whether your connection should take advantage of the database server threadsafe client libraries.

Common Error Messages

Please always make sure, that you can connect to Informix through I-Login or DBPing (please see above) before attempting to connect from PowerBuilder.

Error Messages using IN7 and IN9

DBMS INx INFORMIX-x is not supported in your current installation :

Make sure that the PowerBuilder DLL PBINxx0.DLL is installed and can be located through the environment path.

Could not load pbIN7X0.dll :

Make sure that the PowerBuilder DLL PBINxx0.DLL is installed and can be located through the environment path.

Unable to Locate DLL. The dynamic link library <informix-dll> could not be found in the specified path :

Make sure, that the <informixdirectory>\bin is in the environment path and that the client software is properly installed.

Informix SQL Error -930 : Cannot connect to database server (<hostname>).

Check whether the entry for hostname in Setnet32 (registry) is correct.

Informix SQL Error -931 : Cannot locate turbo service/tcp service in /etc/services. ISAM error 11004: Unknown error message 11004.

This error indicates that the entry in your services file in the <Windows> boot directory or on NT 4.0 in <WinDir>\System32\drivers\etc is missing or incorrect.
The error can also occurr when there is an incorrect entry for servicename in PowerBuilder as in
SQLCA.DBParm = "INET_SERVICE='<servicename>'"
(or under Service Name in the PowerBuilder profile on the Network tab)

Informix SQL Error -908 : Attempt to connect to database server (<informixserver>) failed.

ISAM error 60: Connection timed out
This is a basic connection error. Check whether the database server is up and running and the network connection is working correctly and make sure that you can connect using I-Login or DBPing.

ISAM error 61: Connection refused
This error can occur if the port number is in the services entry is incorrect.
Check the services file entry in the <Windows> boot directory or on NT 4.0 in <WinDir>\System32\drivers\etc

Informix SQL error -25556 : Invalid sqlhosts file format.

This error can occurr, when
- the Protocolname (see Setnet32 on the Server Information tab) is incorrect
- there is an incorrect entry for protocolname in PowerBuilder as in
SQLCA.DBParm = "INET_PROTOCOL='<protocolname>'"
(or under Protocol Type in the PowerBuilder profile on the Network tab)

Informix SQL error -406 : Unable to load locale categories.

Check whether the environment variable CLIENT_LOCALE is set to a valid value and whether the INFORMIXDIR is set correctly.