DataDirect Connect ODBC for INFORMIX

Copyright 1998 INTERSOLV Inc. All rights reserved. INTERSOLV and DataDirect are registered trademarks of INTERSOLV, Inc. Connect ODBC is a trademark of INTERSOLV, Inc. Other company or product names mentioned herein may be trademarks or registered trademarks of their respective companies.


About Connect ODBC for INFORMIX

DataDirect Connect ODBC for INFORMIX (the “INFORMIX driver”) supports multiple connections to the INFORMIX 5x, 6x, and 7x databases in the Windows 9x and Windows NT environments.

DataDirect Connect ODBC for INFORMIX 9 (the “INFORMIX 9 driver”) supports multiple connections to the INFORMIX 7x, and 9x databases in the Windows 9x and Windows NT environments.

The INFORMIX driver file name is IVINFnn.DLL, where nn is the revision level. The INFORMIX 9 driver file name is IVINF9nn.DLL, where nn is the revision level.


INFORMIX 7.2 System Requirements
System Requirements

INFORMIX

To access remote INFORMIX 5.x, 6.x, or 7.x databases through the INFORMIX driver, you need INFORMIX-Connect 7.2 for Windows 9x and Windows NT from INFORMIX.

Note: DataDirect Connect ODBC for INFORMIX for Windows 9x and Windows NT does not work with versions of INFORMIX-Connect earlier than 7.2.

Use the SETNET32.EXE utility supplied with INFORMIX-Connect 7.2 to define servers and the location of the INFORMIX directory. Use ILOGIN.EXE to test your connection to the INFORMIX Server.

The path to the ISQLT07C.DLL must be in your PATH environment variable. If it is not, the following message appears:

The setup routines for the INTERSOLV 3.00 32-BIT INFORMIX ODBC driver could not be loaded due to system error code 126.

When you click OK, the following message appears:

Could not load the setup or translator library.

INFORMIX 9

To access remote INFORMIX 7.x or 9 databases through the INFORMIX 9 driver, you need INFORMIX-Connect 9.1.3 or greater for Windows 9x and Windows NT from INFORMIX.

Use the SETNET32.EXE utility supplied with INFORMIX-Connect 9.1.3 to define servers and the location of the INFORMIX directory. Use ILOGIN.EXE to test your connection to the INFORMIX server.

The path to the ISQLT09A.DLL must be in your PATH environment variable. If it is not and you attempt to configure a data source, a message similar to the following appears:

The setup routines for the INTERSOLV 3.00 32-BIT INFORMIX ODBC driver could not be loaded due to system error code 126.

When you click OK, the following message appears:

Could not load the setup or translator library.


Configuring INFORMIX 7.2 Data Sources
Configuring Data Sources

To configure an INFORMIX data source, do the following:

1	Start the ODBC Administrator to display a list of data sources.

2	If you are configuring an existing data source, select the data source name and click Configure to display the ODBC INFORMIX Driver Setup dialog box.

If you are configuring a new data source, click Add to display a list of installed drivers. Select INFORMIX and click Finish to display the ODBC INFORMIX Driver Setup dialog box.

3	Specify a data source name and, optionally, a description and database name. Click Apply.

4	Click the Connection tab to configure additional connection information. Settings for Default User Name, Host Name, Service Name, and Server Name will be read automatically from the system information. Click Apply.

5	Click the Advanced tab  to configure optional data source settings. Click Apply.

6	Click Translate to display the Select Translator dialog box, which lists the translators specified in the ODBC Translators section of the system information. INTERSOLV provides a translator named INTERSOLV OEM ANSI that translates your data from the IBM PC character set to the ANSI character set.

Select a translator, then click OK to close this dialog box and perform the translation.

6	Click OK or Cancel. If you click OK, the values you have specified become the defaults when you connect to the data source. You can change these defaults by using this procedure to reconfigure your data source. You can override these defaults by connecting to the data source using a connection string with alternate values.


Connecting to INFORMIX 7.2 Using a Logon Dialog Box
Connecting to a Data Source Using a Logon Dialog Box

Some ODBC applications display a Logon dialog box when you are connecting to a data source. In these cases, the data source name has already been specified.

In the Logon dialog box, do the following:

1	Type the name of the database you want to access or select the name from the Database Name drop-down list. This list displays names returned from the INFORMIX server. Or, if you set the Get DB List from Informix option to 0 on the Advanced tab on the ODBC INFORMIX Driver Setup dialog box, this list displays the names you specified.

2	Type the name of the server (host name) on which INFORMIX resides.

3	If required, type your user name as specified on the INFORMIX server.

4	If required, type your password.

5	Optionally, click Options to display the INFORMIX Server Options dialog box, where you can change the Service Name, Server Name, and Protocol Type that you specified in the ODBC INFORMIX Driver Setup dialog box. Click OK to save your changes.

6	Click OK to complete the logon and to update these values in the system information.


Connecting to INFORMIX 7.2 Using a Connection String
Connecting to a Data Source Using a Connection String

If your application requires a connection string to connect to a data source, you must specify the data source name that tells the driver which section of the system information to use for the default connection information. Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in the system information. These values are not written to system information.

You can specify either long or short names in the connection string. The connection string has the form:

DSN=data_source_name[;attribute=value[;attribute=value]...]

An example of a connection string for INFORMIX is

DSN=INFORMIX TABLES;DB=PAYROLL

The paragraphs that follow give the long and short names for each attribute, as well as a description. The defaults listed are initial defaults that apply when no value is specified in either the connection string or in the data source definition in the system information. If you specified a value for the attribute when configuring the data source, that value is your default.

ApplicationUsingThreads (AUT): ApplicationUsingThreads={0 | 1}. Ensures that the driver works with multi-threaded applications. The default is 1, which makes the driver thread-safe. When using the driver with single-threaded applications, you may set this option to 0 to avoid additional processing required for ODBC thread safety standards.

CancelDetectInterval (CDI): Allows you to cancel long-running queries in threaded applications. Select a value to determine how often the driver checks whether a request has been canceled using SQLCancel. For example, if CDI=5, then for every pending request, the driver checks every five seconds to see whether the user has canceled execution of the query using SQLCancel. The default is 0, which means that requests will not be canceled until a request has completed execution.

CursorBehavior (CB): CursorBehavior={0 | 1}. Determines whether cursors will be preserved or closed at the end of each transaction. Valid values are 1 (preserve) and 0 (close, the initial default). Set this attribute to 1 if you want cursors to be held at the current position when the transaction ends. The value CursorBehavior=1 may impact the performance of your database operations.

Database (DB): Name of the database to which you want to connect.

DataSourceName (DSN): Identifies an INFORMIX data source configuration in the system information. Examples include "Accounting" or "INFORMIX-Serv1."

EnableInsertCursors (EIC): EnableInsertCursors={0 | 1}. Determines whether the driver can use Insert cursors during parameterized inserts. Using Insert cursors improves performance during multiple Insert operations using the same statement. This option enables insert data to be buffered in memory before being written to disk. When EnableInsertCursors=0, the driver does not use Insert cursors. The default is 1.

EnableScrollableCursors (ESC): EnableScrollableCursors={0 | 1}. Determines whether the driver provides scrollable cursors. The initial default value is 0 (no use of scrollable cursors). The INFORMIX driver can use scollable cursors only if there are no long columns (SQL_LONGVARCHAR or SQL_LONGVARBINARY) in a Select list. If you set this option to use scrollable cursors (EnableScrollableCursors=1), you must not include long columns in the Select list.

GetDBListFromInformix (GDBLFI): GetDBListFrom Informix={0 | 1}. Determines whether the driver requests the database list to be returned from the INFORMIX server or from the database list that the user entered at driver setup.

When set to 1, the initial default, the driver requests the database list from the INFORMIX server. When set to 0, it uses the list that was entered by the user at driver setup.

HostName (HOST): Name of the machine on which the INFORMIX server resides.

LogonID (UID): Your user name as specified on the INFORMIX server.

Password (PWD): A password.

Protocol (PRO): Protocol={olsocspx | olsoctcp | onsocspx | onsoctcp | seipcpip | sesocspx | sesoctcp}. Protocol used to communicate with the server. You can specify one or more values; separate the names with commas.

ServerName(SRVR): The name of the server running the INFORMIX database.

Service (SERV): Name of the service as it appears on the host machine. This service is assigned by the system administrator.

UseDefaultLogin (UDL): UseDefaultLogin={0 | 1}. Specify 1 to read the Logon ID and Password directly from the INFORMIX registry. The default is 0; that is, logon information is read from the system information, the connection string, or the Logon to INFORMIX dialog box.


INFORMIX 7.2 Data Types
Data Types

The INFORMIX data types map to the standard ODBC data types as follows:

INFORMIX	ODBC
Byte1	SQL_LONGVARBINARY
Char	SQL_CHAR
Date	SQL_TYPE_DATE
Datetime year to fraction(5)	SQL_TYPE _TIMESTAMP
Datetime year to fraction(f)2	SQL_TYPE_TIMESTAMP
Datetime year to second	SQL_TYPE_TIMESTAMP
Datetime year to day	SQL_TYPE_DATE
Datetime hour to second	SQL_TYPE_TIME
Datetime hour to fraction(f)2	SQL_TYPE_TIME
Decimal	SQL_DECIMAL
Float	SQL_DOUBLE
Integer	SQL_INTEGER
Interval year(p) to year	SQL_INTERVAL_YEAR
Interval year(p) to month	SQL_INTERVAL_YEAR_ TO_MONTH
Interval month(p) to month	SQL_INTERVAL_MONTH
Interval day(p) to day	SQL_INTERVAL_DAY
Interval day(p) to hour	SQL_INTERVAL_DAY_TO_HOUR
Interval day(p) to minute	SQL_INTERVAL_DAY_TO_MINUTE
Interval day(p) to second	SQL_INTERVAL_DAY_TO_SECOND
Interval day(p) to fraction(f)2	SQL_INTERVAL_DAY_TO_SECOND
Interval hour(p) to hour	SQL_INTERVAL_HOUR
Interval hour(p) to minute	SQL_INTERVAL_HOUR_ TO_MINUTE
Interval hour(p) to second	SQL_INTERVAL_HOUR_ TO_SECOND
Interval hour(p) to fraction(f)2	SQL_INTERVAL_HOUR_ TO_SECOND
Interval minute(p) to minute	SQL_INTERVAL_MINUTE
Interval minute(p) to second	SQL_INTERVAL_MINUTE_TO_SECOND
Interval minute(p) to fraction(f)2	SQL_INTERVAL_MINUTE_TO_SECOND
Interval second(p) to second	SQL_INTERVAL_SECOND
Interval second(p) to fraction(f)2	SQL_INTERVAL_SECOND
Interval fraction to fraction(f)2	SQL_VARCHAR
Money	SQL_DECIMAL
Serial	SQL_INTEGER
Smallfloat	SQL_REAL
Smallint	SQL_SMALLINT
Text1	SQL_LONGVARCHAR
Varchar*	SQL_VARCHAR
•	1 Not supported for Standard Engine Databases

•	2 Fraction(f) types are mapped to fraction(5) in the driver. Precision is type dependent and the scale as 5.

•	Note: The Interval data type is not supported. Existing columns of this type are mapped to the ODBC SQL_CHAR data type.

INFORMIX 9

In addition to the other INFORMIX data types, the INFORMIX 9 data types map to the standard ODBC data types as follows:

INFORMIX 9	ODBC
Blob	SQL_LONGVARBINARY
Boolean	SQL_BIT
Clob	SQL_LONGVARCHAR
Int8	SQL_BIGINT
Lvarchar	SQL_VARCHAR
Serial8	SQL_BIGINT
The INFORMIX 9 driver does not support any complex data types (for example, set, multiset, list, and named/unnamed abstract types). When the driver encounters a complex type it will return an Unknown Data Type error (SQL State HY000).


Isolation and Lock Levels Supported by INFORMIX 7.2
Isolation and Lock Levels Supported

If connected to an Online Server, INFORMIX supports isolation levels 0 (read uncommitted), 1 (read committed), and 3 (serializable). The default is 1. The Standard Engine supports isolation level 0 (read uncommitted) only.

INFORMIX also supports an alternative isolation level 1, called cursor stability. Your ODBC application can use this isolation level by calling SQLSetConnectAttr (1040,1).

Additionally, if transaction logging has not been enabled for your database, transactions are not supported by the driver (the driver is always in auto-commit mode).

INFORMIX supports page-level and row-level locking.


INFORMIX 7.2 ODBC Conformance Level
ODBC Conformance Level

The API functions supported are listed in Supported ODBC Functions, found in the General Help on DataDirect ODBC Drivers. In addition, the following X/Open functions are supported:

SQLProcedures

SQLColumnPrivileges

SQLTablePrivileges

SQLPrimaryKeys

SQLForeignKeys

SQLProcedureColumns

The driver also supports scrollable cursors with SQLExtendedFetch or SQLFetchScroll if the connection attribute EnableScrollableCursors is set to 1. The driver supports the core SQL grammar.


Number of Connections and Statements Supported by INFORMIX 5
Number of Connections and Statements Supported

The INFORMIX driver supports multiple connections to the INFORMIX database system and multiple statements per connection.


INFORMIX 7.2 ODBC Setup dialog box
ODBC INFORMIX Driver Setup Dialog Box

Use the ODBC INFORMIX Driver Setup dialog box to create new INFORMIX data sources or configure existing data sources.

Data Source Name: A string that identifies this INFORMIX data source configuration in the system information. Examples include "Accounting" or "INFORMIX-Serv1."

Description: An optional long description of a data source name. For example, "My Accounting Database" or "INFORMIX files on Server number 1."

Database Name: The name of the database to which you want to connect by default.

Connection Tab

Displays the Connection tab, where you can configure additional connection information, such as protocol type.

Advanced Tab

Displays the Advanced tab, where you can configure optional data source settings, such as cursor behavior and cancel detect interval.

OK

Cancel

Apply


Connection Tab, ODBC INFORMIX 7 Driver Setup Dialog box
Connection Tab, ODBC INFORMIX Driver Setup Dialog Box

Use the Connection tab on the ODBC INFORMIX Driver Setup dialog box to specify optional connection settings when you create new INFORMIX data sources or configure existing data sources.

Database List: The list of databases that will be displayed in the Logon dialog box if Get DB List From Informix on the Advanced tab is not checked.

Default User Name: The name of the user as specified on the INFORMIX server.

Use Default Login: Select this check box to read the Logon ID and Password entries directly from the INFORMIX registry. The check box is cleared by default; that is, logon information is read from the system information, the connection string, or the Logon to INFORMIX dialog box.

Host Name: The name of the machine on which the INFORMIX server resides.

Service Name: The name of the service as it appears on the host machine. This service is assigned by the system administrator. The name you specify is displayed in the INFORMIX Server Options dialog box.

Server Name: The name of the INFORMIX server as it appears in the sqlhosts file.

Protocol Type: The protocol used to communicate with the server. Specify one or more values; separate the names with commas. Values can be olsocspx, olsoctcp, onsocspx, onsoctcp, seipcpip, sesocspx, sesoctcp.

OK

Cancel

Apply


Advanced Options for Configuring Data Sources
Advanced Tab, ODBC INFORMIX Driver Setup Dialog Box

Use the Advanced tab on the ODBC INFORMIX Driver Setup dialog box to specify optional settings when you create new INFORMIX data sources or configure existing data sources.

Cursor Behavior: Select Preserve if you want cursors to be held at the current position when the transaction ends. Otherwise, leave this set to Close. Selecting Preserve may impact the performance of your database operations.

Cancel Detect Interval: Lets you cancel long-running querires in threaded applications. Select a value to determine how often the driver checks whether a request has been canceled using SQLCancel. For example, if CDI=5, then for every pending request, the driver checks every five seconds to see whether the user has canceled execution of the query using SQLCancel. The default is 0, which means that requests will not be canceled until the request has completed execution.

Enable Scrollable Cursors: Determines whether the driver provides scrollable cursors. The check box is cleared by default (no use of scrollable cursors). The INFORMIX driver can use scollable cursors only if there are no long columns (SQL_LONGVARCHAR or SQL_LONGVARBINARY) in a Select list. If you select this check box, you must not include long columns in the Select list.

Enable Insert Cursors: Determines whether the driver can use Insert cursors during parameterized inserts. Using Insert cursors improves performance during multiple Insert operations using the same statement. This option enables insert data to be buffered in memory before being written to disk. When this check box is cleared (the default), the driver does not use Insert cursors.

Get DB List from Informix: Determines whether the driver requests the database list to be returned from the INFORMIX server or from the database list that the user entered at driver setup.

When the check box is selected, the driver requests the database list from the INFORMIX server. When the check box is cleared, the driver uses the list entered by the user at driver setup.

Application Using Threads: Ensures that the driver works with multi-threaded applications. You can clear this check box when using the driver with single-threaded applications. Clearing this check box avoids the additional processing required for ODBC thread safety standards.

Translate Button

Displays the Select Translator dialog box, where you can translate your data from one character set to another. Choose the INTERSOLV OEM ANSI translator to translate your data from the IBM PC character set to the ANSI character set.

OK

Cancel

Apply


Logon to INFORMIX 5
Logon to INFORMIX Dialog Box

Database Name: Type the name of the database you want to access or select the name from the Database Name drop-down list. The names on the list are determined by the status of the Get DB List From Informix checkbox on the Advanced tab. If the box is checked, the names displayed are from the user-entered list. If it is not checked, the names displayed are returned from the INFORMIX server.

Host Name: Enter the name of the machine on which the INFORMIX server resides.

User Name: Enter the name of the user as specified on the INFORMIX server.

Password: Enter the user's password.

Options Button

Displays the INFORMIX Server Options  dialog box, where you can specify the Service Name, Server Name, and Protocol type.


INFORMIX 7.2 Server Options
INFORMIX Server Options Dialog Box

Service Name: Name of the service as it appears on the host machine. This service is assigned by the system administrator. If you entered a service name in the Setup dialog box, that name is displayed; you can change it.

Server Name: Name of the INFORMIX server as it appears in the sqlhosts file.

Protocol Type: This list displays the protocols specified in the Setup dialog box. Select a communications protocol.


Apply Button

Writes the settings you have specified to the system information. These settings remain in effect until you change them in this dialog box. Clicking Cancel does not affect settings that have been applied.


OK Button

Writes the settings you have specified to the system information and closes the dialog box.


Cancel Button

Closes the dialog box without saving settings that have not been applied.