| SUMMARY: | 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. This document does not cover the connection through ODBC and 16-bit deployment for PowerBuilder 6. |
| Document ID: | 47934 | Last Revised: | 07/14/99 |
| Topic: | Connectivity | Document Type: | TechNote |
| Product: | PowerBuilder | Version: | 7.0 |
| Platform: | PC | Operating System: | Windows 95, Windows NT |
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. This document does not cover the connection through ODBC and 16-bit deployment for PowerBuilder 6.
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 7.x and 9.x
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.
The IN7 driver in PowerBuilder 6 and 7 can be used with either I-Connect 7.x or I-Connect 9.x. The IN9 driver of PowerBuilder 7 requires the installation of I-Connect version 9.x.
NOTE: The I-Connect version must be 7.20 or higher respectively 9.14 or higher.
Informix-Net 5.x
Informix-Net (I-Net) 32-bit is needed with the PowerBuilder IN5 Informix driver of PowerBuilder 6.x.
NOTE: The I-Net version should be 5.01.TE1 or higher.
How PowerBuilder Talks to Informix
Versions and Naming Conventions
PowerBuilder 6 supports Informix- OnLine and SE versions 5.x, 6.x, 7.x with a native driver interface. PowerBuilder 7 supplies native drivers for Informix- Online and SE versions 7.x and 9.x.
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 PBxxx70.DLL.
PowerBuilder 6 ships with an I-Net 5 and I-Connect 7 driver: PBIN560.DLL and PBIN760.DLL.
PowerBuilder 7 contains an I-Connect 7 and I-Connect 9 driver: PBIN770.DLL and PBIN970.DLL.
Native Drivers and Informix Client Software DLLs
These native drivers call functionality in the Informix client software dlls as shown below. The IN7 driver can also be used with I-Connect version 9, because this contains an ISQLT07C.DLL to supply compatibility:
| IN5 | IN7 | IN9 | |
| PB native driver | PBIN560.DLL | PBIN760.DLL oPBIN770.DLL | PBIN970.DLL |
| | | | | | | |
| I-Net 5 runtime | ISQLI501.DLL | | | | |
| | | | | ||
| I-Connect 7 runtime dll | ISQLT07C.DLL | | | |
| : | | | ||
| With I-Connect 9 installed | 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 7.x and 9.x
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.
I-Net 5
Setnet uses the Informix.ini in the <Windows> directory to maintain the Informix environment variables and login information.
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-Net 5, I-Connect 7, I-Connect 9). 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 etc. - if those fields are left blank I-Login will default to the values in the Informix.ini for I-Net 5.With I-Connect I-Login will default to the values held in the in the registry (see above).
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 IN7". 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 7.x and 9.x 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, for example, as follows:
sqliprt -o in7.log inetout
Informix supplies the sqliprt.exe with Informix Client SDK 7.30 and higher. For earlier versions of the Informix client software, please refer to Informix for information on how to obtain this program.
To activate tracing for I-Net 5.x the following line needs to be added to the environment section of the Informix.ini file:
[ENVIRONMENT]
...
SQLIDEBUG=2:<drive>:\<path>\<filename>
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:
ConnectString
Specifies the parameters required to connect to an ODBC data source.
Scroll
Specifies whether to use a scroll cursor for fetch next row, previous row, etc..
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.
DisableBind
Specifies whether to bind input parameters to a compiled SQL statement.
Recent enhancements to the PowerBuilder 6.5 Informix drivers IN7 and IN9
The following enhancements have been made after PowerBuilder 6.5 maintenance has been released.
These new DBParms are included in the latest downloadeable release (higher than 6.5) from the SYBASE support website http://support.sybase.com/
NOTE: These enhancements are NOT currently part of the PowerBuilder 7.0 release.
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.
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 to use delimiters for names and identifiers (like owner, table, column). When this DBParm is set to DelimitIdentifier='Yes' for an Informix database that uses ANSI logging then the PowerBuilder DataWindow generates delimited DataManipulationLanguage (SELECT, INSERT, UPDATE, DELETE) according to ANSI SQL-92. DelimitIdentifier is a connection parameter and must be specified before the connection from PowerBuilder.
NOTE: In general it is preferable to use undelimited ANSI-SQL.
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 pbIN760.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.
Error Messages using IN5
Could not load pbIN560.dll:
Make sure that the PowerBuilder DLL PBIN560.DLL is installed and can be located through the environment path.
Informix SQL error -932: Error on network connection.
This error number occurs for a number of reasons. To get an indication on how to possibly resolve this error check the system call information that follows.
<- connect(WSAETIMEOUT) system call failed.
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.
- getserverbyname(WSAECONNREFUSED) system call failed.
This error can occur because 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
- getserverbyname(WSANO_DATA) system call failed.
Check the services file entry in the <Windows> boot directory or on NT 4.0 in <WinDir>\System32\drivers\etc
This 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)
- gethostbyname(WSAHOST_NOT_FOUND) system call failed.
Check hostname specified in Setnet (informix.ini) or PowerBuilder DBParm SQLCA.ServerName="<hostname>"
