Title | Contents | Previous | Next | Index

Chapter 6

Connect ODBC for Informix

Connect ODBC for Informix supports two separate drivers. Connect ODBC for Informix (the "Informix driver") supports multiple connections to the Informix database system versions 5.x, 6.x, or 7.x in the Windows 9x, Windows NT, and UNIX environments.

Connect ODBC for Informix 9 (the "Informix 9 driver") supports multiple connections to the Informix database system versions 7.x and 9.x in the Windows 9x, Windows NT, and UNIX environments.

See the README file shipped with your DataDirect product for the file names of the Informix drivers.

System Requirements

The following section lists requirements for all supported platforms.

Windows 9x and Windows NT

Both Informix and Informix 9 are supported on Windows 9x and Windows NT.

Informix

To access remote Informix 5.x, 6.x, or 7.x databases through the Informix driver, you need one of the following:

Note: The DataDirect Informix driver does not work with versions earlier than 7.2 of the previous INFORMIX-Connect product.

Use the SETNET32.EXE utility supplied by Informix 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.

Informix 9

To access remote Informix 7.x or 9 databases through the Informix 9 driver, you need one of the following:

Note: The DataDirect Informix 9 driver does not work with versions earlier than 9.1.3 of the previous INFORMIX-Connect product.

Use the SETNET32.EXE utility supplied by Informix 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.

UNIX (AIX, HP-UX, and Solaris for SPARC)

The environment variable INFORMIXDIR must be set to the directory where you have installed the Informix client.

For example, the following syntax is valid for C-shell users: 

setenv INFORMIXDIR /databases/informix

For Bourne- or Korn-shell users, the following syntax is valid: 

INFORMIXDIR=/databases/informix;export INFORMIXDIR

In addition, the INFORMIXSERVER variable must be set to the name of the Informix server (as defined in your $INFORMIXDIR/ext/sqlhosts file). For further details, refer to the Informix documentation.

Informix

To access remote Informix 5.x, 6.x, or 7.x databases through the Informix driver, you need INFORMIX-Client Software Developer's Kit for UNIX platforms, version 2.x, from Informix.

Note: The DataDirect Informix driver does not work with versions earlier than 7.23 of the previous products, INFORMIX-Connect and ESQL/C .

Informix 9

To access remote Informix 7.x or 9 databases through the Informix 9 driver, you need INFORMIX-Client Software Developer's Kit for UNIX platforms, version 2.x, from Informix.

Note: The DataDirect Informix 9 driver for Solaris and HP-UX does not work with versions earlier than 9.1.3 of the previous products, INFORMIX-Connect and ESQL/C. The driver for AIX does not work with versions earlier than 9.1.4 of INFORMIX-Connect and ESQL/C.

Configuring Data Sources

Under Windows, data sources are configured and modified through the ODBC Administrator.

In the UNIX environment, there is no ODBC Administrator. To configure a data source in the UNIX environment, you must edit the system information file using the attributes in Table 6-1. You must also edit this file to perform a translation. See Appendix H, "The UNIX Environment," for information about editing the file.

To configure an Informix data source:

  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 the appropriate Informix driver and click Finish to display the ODBC Informix Driver Setup dialog box.

  3. At any point during the configuration process, you can click Test Connect to attempt to connect to the data source using the connection properties specified in the Driver Setup dialog box. A logon dialog box is displayed; see "Connecting to a Data Source Using a Logon Dialog Box" for details.

  4. Specify values for the following; then, click Apply:

    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 7 files on Server number 1."

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

  5. Click the Connection tab to configure additional, optional settings for the data source.

  6. Specify values for the following; then, click Apply.

    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, and/or sesoctcp.

  7. Click the Advanced tab to configure additional, optional settings for the data source.

  8. Specify values for the following; then, click Apply:

    Cursor Behavior: Holds cursor at the current position when the transaction ends if you select Preserve. Otherwise, leave this set to Close. Selecting Preserve may impact the performance of your database operations.

    Cancel Detect Interval: Lets you 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 the request has completed execution.

    Enable Insert Cursors: Determines whether the driver can use Insert cursors during inserts governed by parameters. 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 during driver setup.

    When the check box is checked (the default), the driver requests the database list from the Informix server. When cleared, the driver uses the list that was entered by the user at driver setup.

    Application Using Threads: A setting that 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.

    Trim Blank From Index Name: Specifies whether or not the leading space should be trimmed from a system-generated index name. This option is provided to address problems with applications that cannot process a leading space in index names. When this box is checked (the default), the driver trims the leading space.

    Translate: Click Translate to display the Select Translator dialog box, which lists the translators specified in the ODBC Translators section of the system information. DataDirect provides a translator named "OEM to 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.

  9. 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 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. The dialog box is as follows:

In this 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. 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 returned from the Informix server. If cleared, the names displayed are returned from the user-entered list.

  2. Type the name of the host machine on which the Informix server 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 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 in 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 the 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

Table 6-1 gives the long and short names for each attribute, as well as a description.

To configure a data source in the UNIX environment, you must edit the system information file. This file accepts only long names for attributes. See Appendix H, "The UNIX Environment," for information about this file.

The defaults listed in the table 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 the default.

Table 6-1. Informix Connection String Attributes 

Attribute

Description

ApplicationUsing
Threads (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.

CancelDetect
Interval (CDI)

Lets you 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}. This attribute determines whether cursors will be preserved or closed at the end of each transaction. The initial default is 0 (close). 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)

The name of the database to which you want to connect.

DataSourceName (DSN)

A string that identifies an Informix data source configuration in the system information. Examples include "Accounting" or "INFORMIX-Serv1."

EnableInsert
Cursors (EIC)

EnableInsertCursors={0 | 1}. Determines whether the driver can use Insert cursors during inserts governed by parameters. The initial default value is 1 (driver uses Insert cursors). 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.

GetDBListFrom
Informix (GDBLFI)

GetDBListFromInformix={0 | 1}. This attribute 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)


The 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}. The 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)


The name of the service as it appears on the host machine. This service is assigned by the system administrator.

TrimBlankFrom
IndexName (TBFIN)

TrimBlankFromIndexName={0 | 1}. Specifies whether or not the leading space should be trimmed from a system-generated index name. This option is provided to address problems with applications that cannot process a leading space in index names. When set to 1 (the default), the driver trims the leading space. When set to 0, the driver does not trim the space.

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.

Data Types

Table 6-2 shows how the Informix data types map to the standard ODBC data types.

Table 6-2. Informix Data Types 

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

Varchar1

SQL_VARCHAR

1 Not supported for Standard Engine Databases

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

Informix 9

Table 6-3 shows how the Informix 9 data types map to the standard ODBC data types. These types are in addition to the Informix data types described in Table 6-2.

Table 6-3. Informix 9 Data Types 

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

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, then transactions are not supported by the driver (the driver is always in auto-commit mode).

Informix supports page-level and row-level locking.

See Appendix D, "Locking and Isolation Levels," for details.

ODBC Conformance Level

See Appendix C, "ODBC API and Scalar Functions," for a list of the API functions supported by the Informix drivers. In addition, the following functions are supported:

The driver also supports scrollable cursors with SQLFetchScroll or SQLExtendedFetch. The driver supports the core SQL grammar.

Number of Connections and Statements Supported

The Informix drivers support multiple connections and multiple statements per connection to the Informix database system.


Title | Contents | Previous | Next | Index

Connect ODBC Reference

© 1999, MERANT Solutions, Inc. All rights reserved.