Copyright

DataDirect Connect ODBC for Informix

© 2002 DataDirect Technologies. All rights reserved.

DataDirect, DataDirect Connect, and SequeLink are registered trademarks, and Client/Server MiddleWare, DataDirect Connect ADO, DataDirect Connect Integrator, DataDirect Connect JDBC, DataDirect Connect ODBC, DataDirect Connect OLE DB, DataDirect Connect Premium, DataDirect Reflector, and DataDirect SequeLink Integrator are trademarks of DataDirect Technologies. All other trademarks are the property of their respective owners.


About Connect ODBC for Informix

Connect ODBC for Informix (the "Informix driver") supports multiple connections to the following Informix database servers:

•	Informix OnLine 7.x

•	Informix SE 7.2x

•	Informix Dyanmic Server with Universial Data Option 9.1.4 and higher

•	Informix Dynamic Server 9.2x and 9.3x

The Informix driver is supported in the Windows and UNIX (not including Linux) environments.

See the README file shipped with your DataDirect product for the file name of the Informix driver.


Driver Requirements

To access supported remote Informix databases through the Informix driver, you need one of the following:

•	Informix Connect for Windows platforms, version 2.x

•	Informix Client Software Development Kit for Windows platforms, version 2.x

Use the Setnet32 utility supplied by Informix to define servers and the location of the INFORMIX directory. Use Ilogin to test your connection to the Informix server. The path to the ISQLT09A.DLL must be in your PATH environment variable.


Configuring Data Sources

On Windows, data sources are configured and modified through the ODBC Administrator. 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 Informix driver 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. 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.

7	Click Test Connect to attempt to connect to the data source using the connection properties specified in the Driver Setup dialog box.

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


Test Connection

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. Note that the information you enter in the Logon dialog box during a test connect is not saved.

•	If the driver can connect, it releases the connection and displays a "connection established" message. Click OK.

•	If the driver cannot connect because of an improper environment or incorrect connection value, it will display an appropriate error message.

Verify that the all required client software is properly installed. If it is not, you will see the message:

Specified driver could not be loaded due to system error [xxx].

Click OK.


Connecting to Data Source Using a Logon Dialog
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, perform the following steps:

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 check box on the Advanced tab. If the box is selected, 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 Data Source 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 the default.

ApplicationUsingThreads (AUT): ApplicationUsingThreads={0 | 1}. Ensures that the driver works with multi-threaded applications.

When set to 1 (the initial default), the driver is thread-safe.

When using the driver with single-threaded applications, you can set this option to 0 to avoid additional processing required for ODBC thread-safety standards.

CancelDetectInterval (CDI): A value in seconds that determines how often the driver checks whether a query has been canceled using SQLCancel. If the driver determines that SQLCancel has been issued, the query is canceled. This attribute determines whether long-running queries in threaded applications are canceled if the application issues a SQLCancel. If set to 0 (the initial default), queries are not canceled even if SQLCancel is issued.

For example, if CancelDetectInterval is set to 5, then for every pending request, the driver checks every five seconds to see whether the application has canceled execution of the query using SQLCancel.

CursorBehavior (CB): CursorBehavior={0 | 1}. Determines whether cursors will be preserved or closed at the end of each transaction.

When set to 0 (the initial default), cursors will be closed at the end of each transaction.

When set to 1, cursors will be held at the current position when the transaction ends. This value 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."

EnableInsertCursors (EIC): EnableInsertCursors={0 | 1}. Determines whether the driver can use Insert cursors during inserts governed by parameters.

When set to 0, the driver does not use Insert cursors.

When set to 1 (the initial default), the 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.

GetDBListFromInformix (GDBLFI): GetDBListFromInformix={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 0, the driver uses the list that was entered by the user at driver setup.

When set to 1 (the initial default), the driver requests the database list from the Informix server.

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}. Specifies 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.

TrimBlankFromIndexName (TBFIN): TrimBlankFromIndexName={0 | 1}. Determines whether 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 0, the driver does not trim the space.

When set to 1 (the initial default), the driver trims the leading space.

UseDefaultLogin (UDL): UseDefaultLogin={0 | 1}. Determines from where the logon information is read.

When set to 0 (the initial default), logon information is read from the system information, the connection string, or the Logon to Informix dialog box.

When set to 1, the Logon ID and Password are read directly from the Informix registry.


Data Types

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

Informix	ODBC
Blob	SQL_LONGVARBINARY
Boolean	SQL_BIT
Byte*	SQL_LONGVARBINARY
Char	SQL_CHAR
Clob	SQL_LONGVARCHAR
Date	SQL_TYPE_DATE
Datetime year to fraction(5)	SQL_TYPE_TIMESTAMP
Datetime year to fraction(f)**	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)**	SQL_TYPE_TIME
Decimal	SQL_DECIMAL
Float	SQL_DOUBLE
Int8	SQL_BIGINT
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)**	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)**	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)**	SQL_INTERVAL_MINUTE_TO_SECOND
Interval second(p) to second	SQL_INTERVAL_SECOND
Interval second(p) to fraction(f)**	SQL_INTERVAL_SECOND
Interval fraction to fraction(f)**	SQL_VARCHAR
Lvarchar	SQL_VARCHAR
Money	SQL_DECIMAL
Serial	SQL_INTEGER
Serial8	SQL_BIGINT
Smallfloat	SQL_REAL
Smallint	SQL_SMALLINT
Text*	SQL_LONGVARCHAR
Varchar*	SQL_VARCHAR
* Not supported for Standard Engine Databases

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

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


MTS Support

The Informix driver can take advantage of Microsoft Transaction Server (MTS) capabilities, specifically, the Distributed Transaction Coordinator (DTC) using the XA Protocol. To enable the DTC support, the clients must be INFORMIX-Connect version 2.20 or higher. For a general discussion of MTS and DTC, refer to the help file of the "Microsoft Transaction Server SDK."

To enable support for the DTC, you must perform the following actions:

1	Use the Setnet32 utility supplied by Informix to define:

•	The INFORMIXDIR environment variable, which identifies the location of the client programs, library files, message files, header files, and other Informix software components

•	The INFORMIXSERVER environment variable, which identifies the default database server

•	An Informix server, which identifies either an existing Informix database server or a new one

•	A host name, which identifies the host computer with the database server you want to use

•	A user name, which identifies a user name for an account on the currently selected host computer

•	A password for the specified user name, if required

When enlisting in a distributed transaction, the Informix clients only use the defaults specified in Setnet32.

2	Run the regcopy utility provided with INFORMIX-Connect to copy the registry entries created by Setnet32 to an area in the registry that is accessible by the DTC. The DTC is a service, and services do not search for configuration information in the Windows registry where Setnet32 stores client products environment variables. Therefore, if you do not run regcopy after setting the defaults in Setnet32, enlistment in a distributed transaction will fail.

For information on using the Setnet32 and regcopy utilities, see the Informix documentation.


Persisting a Result Set as an XML Data File

This driver allows you to persist a result set as an XML data file with embedded schema. To implement XML persistence, a client application must do the following:

1	Turn on STATIC cursors. For example:

SQLSetStmtAttr (hstmt, SQL_ATTR_CURSOR_TYPE, SQL_CURSOR_STATIC, SQL_IS_INTEGER)

Note regarding Step 1: A result set can be persisted as an XML data file only if the result set is generated using STATIC cursors. Otherwise, the following error is returned:

Driver only supports XML persistence when using driver’s static cursors.

2	Execute a SQL statement. For example:

SQLExecDirect (hstmt, "Select * from GTABLE", SQL_NTS)

3	Persist the result set as an XML data file. For example:

SQLSetStmtAttr (hstmt, SQL_PERSIST_AS_XML, "c:\temp\GTABLE.XML", SQL_NTS)

Note regarding Step 3: A new statement attribute is available to support XML persistence, SQL_PERSIST_AS_XML. A client application must call SQLSetStmtAttr with this new attribute as an argument. See the following table for the definition of valid arguments for SQLSetStmtAttr.

Argument	Definition
StatementHandle	The handle of the statement that contains the result set to persist as XML.
Attribute	SQL_PERSIST_AS_XML. This new statement attribute can be found in the file qesqlext.h, which is installed with the driver.
ValuePtr	Pointer to a URL that specifies the full path name of the XML data file to be generated. The directory specified in the path name must exist, and if the specified file name exists, the file will be overwritten.
StringLength	The length of the string pointed to by ValuePtr or SQL_NTS if ValuePtr points to a null terminated string.

A client application can choose to persist the data at any time that the statement is in an executed or cursor-positioned state. At any other time, the driver returns the following message:

Function Sequence Error

Using the Windows XML Persistence Demo Tool

On Windows, Connect ODBC is shipped with an XML persistence demo tool. This tool is installed in the Connect ODBC installation directory.

The tool has a graphical user interface and allows you to persist data as an XML data file. To use this tool, take the following steps:

1	From the DataDirect Technologies Connect ODBC 4.10 program group, select XML Persistence Demo. The XML Persistence dialog box appears.

2	First, you must connect to the database. Click Connect. The Select Data Source dialog box appears.

3	You must either select an existing data source or create a new one. Take one of the following actions:

•	Select an existing data source and click OK.

•	Create a new file data source by clicking New. The Create New Data Source dialog box appears. Follow the instructions in the dialog box.

•	Create a new machine data source by clicking the Machine Data Source tab and clicking New. The Create New Data Source dialog box appears. Follow the instructions in the dialog box.

4	After you have connected to a database, type a SQL Select statement in the Query text box of the XML Persistence dialog box. Then, click Persist. The Save As dialog box appears.

5	Specify a name and location for the XML data file that will be created. Then, click OK.

Note that the Status box in the XML Persistence dialog box displays whether or not the action failed or succeeded.

6	Click Disconnect to disconnect from the database.

7	Click Close to exit the tool.


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.


ODBC Conformance Level

The API functions supported are listed in Supported ODBC Functions, found in the General Help section of Connect ODBC Help. In addition, the following functions are supported:

•	SQLProcedures

•	SQLColumnPrivileges

•	SQLTablePrivileges

•	SQLPrimaryKeys

•	SQLForeignKeys

•	SQLProcedureColumns

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 driver supports multiple connections to the Informix database system and multiple statements per connection.


General Tab, ODBC Informix Driver Setup

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

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

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

Database Name: Type 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

Test Connect


Connection Tab, ODBC Informix Driver Setup

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: Type a list of databases that will be displayed in the Logon dialog box if Get DB List From Informix on the Advanced tab is not selected. Separate multiple values with commas (for example, db1, db2, db3).

Default User Name: Type 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 not selected by default; that is, logon information is read from the system information, the connection string, or the Logon to Informix dialog box.

Host Name: Type the name of the machine on which the Informix server resides.

Service Name: Type 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: Type the name of the Informix server as it appears in the sqlhosts file.

Protocol Type: Select the protocol used to communicate with the server.

OK

Cancel

Apply

Test Connect


Advanced Tab, ODBC Informix Driver Setup

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: Select a value other than "0 - None" to allow long-running queries in threaded applications to be canceled if the application issues a SQLCancel. The value you select determines how often (in seconds) the driver checks whether a query has been canceled using SQLCancel. If the driver determines that SQLCancel has been issued, the query is canceled. For example, if you select 5, then for every pending query, the driver checks every five seconds to see whether the application has canceled execution of the query using SQLCancel. The default is 0, which means that queries are not canceled even if a SQLCancel is issued.

Enable Insert Cursors: Select this check box to determine 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: Select this check box to determine 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 selected (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: Select this check box to ensure 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: Select this check box to specify whether 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 selected (the default), the driver trims the leading space.

Translate Button

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

OK

Cancel

Apply

Test Connect


Logon to Informix Dialog
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 check box on the Advanced tab. If the box is selected, the names displayed are returned from the Informix server. If cleared, the names displayed are returned from the user-entered list.

Host Name: Type the name of the host machine on which the Informix server resides.

User Name: If required, type your user name as specified on the Informix server.

Password: If required, type your password.

Options Button

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


Informix Server Options Dialog
Informix Server Options Dialog Box

Service Name: Type the 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: Type the name of the Informix server as it appears in the sqlhosts file.

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


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.


Contacting Technical Support

DataDirect Technologies provides technical support for all registered users of this product, including limited installation support, for the first 30 days. You can register online for your SupportLink user ID and password, which provide access to the password-protected areas of the SupportLink web site, at http://support.datadirect-technologies.com. Select Register on the left toolbar, complete the information on the Register page, and click Submit. Your user ID and password are issued to you by email upon registration.

For support after 30 days, contact us using one of the following methods or purchase further support by enrolling in the SupportLink program. For more information about SupportLink, contact your sales representative.

The DataDirect Technologies web site provides the latest support information through SupportLink Online, our global service network that provides access to support contact details, tools, and valuable information. Our SupportLink users access information using the web and automatic email notification. SupportLink Online includes a knowledge base that allows you to search on keywords for technical bulletins and other information. You also can download product fixes and upgrades for your DataDirect products.

World Wide Web

http://support.datadirect-technologies.com

E-Mail

USA, Canada, Mexico, Australia, and Asia Pacific (except Japan)	datadirect.answerline@datadirect-technologies.com
Japan	jpn.answerline@datadirect.co.jp (beginning March 2002) dd.answerline@merant.co.jp (until March 2002)
All other countries	int.datadirect.answerline@datadirect-technologies.com
Local Telephone Support

Local phone numbers can be found at:

http://support.datadirect-technologies.com/websupport/supportnet/datadirect/
supportnetanswerline.asp

NOTE: You must have a SupportLink user ID and password to access this web site. You can register online for your SupportLink user ID and password at http://support.datadirect-technologies.com. Select Register on the left toolbar, complete the information on the Register page, and click Submit. Your user ID and password are issued immediately and confirmation is sent to you by email.

Answerline support is available 24 hours a day, seven days a week.

Fax Information

Fax US	1 919 461 4527
Fax Int.	+32 (0) 15 32 09 19

Information to Provide

When you contact us, please include this information:

•	The product serial number or a case number. If you do not have a SupportLink contract, we will ask you to speak with a sales representative.

•	Your name and organization. For a first-time call, you may be asked for full customer information, including location and contact details.

•	The version number of your DataDirect product.

•	The type and version of your operating system.

•	Any third-party software or other environment information required to understand the problem.

•	A brief description of the problem, including any error messages that you have received, and the steps preceding the occurrence of the problem. Depending on the complexity of the problem, you may be asked to submit an example so that we can recreate the problem.

•	An assessment of the severity level of the problem.


Windows

•	Windows 98

•	Windows Me

•	Windows NT with Service Pack 5 and higher

•	Windows 2000 with Service Pack 1 and higher

•	Windows XP Home Edition and Professional


UNIX

•	AIX 4.3.3 and higher with the 5.0.2.0 C++ runtime libraries

•	HP-UX 11 and 11i

•	Sun Solaris 7 and 8

•	Linux (Red Hat Linux 6.2, 7.1, and 7.2; Caldera OpenLinux 2.3 and 3.1; SuSE Linux 6.4 and 7.3)