Skip navigation.
Downloads | Product Documentation | Support

WebLogic Type 4 JDBC Drivers

# The Informix Driver

The following sections describe how to configure and use the BEA WebLogic Type 4 JDBC Informix driver:

- Informix Database Version Support
- Informix Driver Classes
- Informix URL
- Informix Connection Properties
- Performance Considerations
- ResultSetMetaDataOptions
- Data Types
- SQL Escape Sequences
- Using Scrollable Cursors
- Parameter Metadata Support
- ResultSet MetaData Support
- Rowset Support
- Blob and Clob Searches
- FILETOBLOB Feature Support
- Auto-Generated Keys Support

# Informix Database Version Support

The BEA WebLogic Type 4 JDBC Informix driver (the "Informix driver") supports Informix Dynamic Server 9.4 and higher.

# Informix Driver Classes

The driver classes for the BEA WebLogic Type 4 JDBC Informix driver are:

>     XA: weblogic.jdbcx.informix.InformixDataSource

>     Non-XA: weblogic.jdbc.informix.InformixDriver

Use these driver classes when configuring a JDBC data source in your WebLogic Server domain.

# Informix URL

To connect to an Informix database, use the following URL format:

>     jdbc:bea:informix://dbserver1:1543;informixServer=dbserver1;databaseName=dbname

# Informix Connection Properties

Table 3-1 lists the JDBC connection properties supported by the Informix driver, and describes each property. You can use these connection properties in a JDBC data source configuration in your WebLogic Server domain. To specify a property, use the following form in the JDBC data source configuration:

>     property=value

Note: All connection property names are case-insensitive. For example, Password is the same as password.Required properties are noted as such. The data type listed for each connection property is the Java data type used for the property value in a JDBC data source.

Table 3-1 Informix Connection String Properties

Property | Description
--- | ---
CodePageOverride OPTIONAL | The code page the driver uses when converting character data. The specified code page overrides the default database code page. All character data retrieved from or written to the database is converted using the specified code page. The value must be a string containing the name of a valid code page supported by your Java Virtual Machine, for example, CodePageOverride=CP950.
ConnectionRetryCount OPTIONAL | The number of times the driver retries connections to a database servers (primary and alternate) until a successful connection is established. Valid values are 0 and any positive integer. If set to 0, the driver does not retry a connection to the list of database servers if a connection is not established on the driver's first pass through the list. The default is 0.
ConnectionRetryDelay OPTIONAL | The number of seconds the driver will wait between connection retry attempts when ConnectionRetryCount is set to a positive integer. The default is 3.
DatabaseName OPTIONAL | The name of the database to which you want to connect. If this property is not specified, a connection is established to the specified server without connecting to a particular database. A connection that is established to the server without connecting to the database allows an application to use CREATE DATABASE and DROP DATABASE SQL statements. These statements require that the driver cannot be connected to a database. An application can connect to the database after the connection is established by executing the DATABASE SQL statement. Refer to your IBM Informix documentation for details on using the CREATE DATABASE, DROP DATABASE, and DATABASE SQL statements.
InformixServer | The name of the Informix database server to which you want to connect.
InsensitiveResultSetBufferSize OPTIONAL | {-1 | 0 | x}. Determines the amount of memory used by the driver to cache insensitive result set data. If set to -1, the driver caches all insensitive result set data in memory. If the size of the result set exceeds available memory, an OutOfMemoryException is generated. Because the need to write result set data to disk is eliminated, the driver processes the data more efficiently. If set to 0, the driver caches all insensitive result set data in memory, up to a maximum of 2 GB. If the size of the result set data exceeds available memory, the driver pages the result set data to disk. Because result set data may be written to disk, the driver may have to reformat the data to write it correctly to disk. If set to x, where x is a positive integer, the driver caches all insensitive result set data in memory, using this value to set the size (in KB) of the memory buffer for caching insensitive result set data. If the size of the result set data exceeds the buffer size, the driver pages the result set data to disk. Because the result set data may be written to disk, the driver may have to reformat the data to write it correctly to disk. Specifying a buffer size that is a power of 2 results in more efficient memory use. The default is 2048 (KB)
LoginTimeout OPTIONAL | The maximum time in seconds that attempts to create a database connection will wait. A value of `0` specifies that the timeout is the default system timeout if there is one; otherwise it specifies that there is no timeout.
Password | A case-insensitive password used to connect to your Informix database. A password is required only if security is enabled on your database. If so, contact your system administrator to get your password.
PortNumber | The TCP port on which the database server listens for connections. The default varies depending on operating system.
ServerName | Specifies either the IP address or the server name (if your network supports named servers) of the primary database server. For example, 122.23.15.12 or InformixServer.
User | The case-insensitive default user name used to connect to your Informix database. A user name is required only if security is enabled on your database. If so, contact your system administrator to get your user name.

## Informix Limitation for Prepared Statements

If anything causes a change to a database table or procedure, such as adding an index, or recompiling the procedure, all existing JDBC PreparedStatements that access it must be re-prepared before they can be used again. This is a limitation of the Informix database management system. WebLogic Server caches, retains, and reuses application PreparedStatements along with pooled connections, so if your application uses prepared statements that access tables or procedures that are dropped and recreated or for which the definition is changed, re-execution of a cached prepared statement will fail once. WebLogic Server will then remove the defunct prepared statement from the cache and replace it when the application asks for the statement again.

To avoid any PreparedStatement failure due to table or procedure changes in the DBMS while WebLogic Server is running, set the Statement Cache Size to 0. WebLogic will make a new PreparedStatement for each request. However, with the statement cache disabled, you will lose the performance benefit of statement caching.

For information about setting the Statement Cache Size, see "Increasing Performance with the Statement Cache".

# Performance Considerations

Setting the following connection properties for the Informix driver as described in the following list can improve performance for your applications:

## InsensitiveResultSetBufferSize

To improve performance when using scroll-insensitive result sets, the driver can cache the result set data in memory instead of writing it to disk. By default, the driver caches 2 MB of insensitive result set data in memory and writes any remaining result set data to disk. Performance can be improved by increasing the amount of memory used by the driver before writing data to disk or by forcing the driver to never write insensitive result set data to disk. The maximum cache size setting is 2 GB.

## MaxPooledStatements

To improve performance, the driver's own internal prepared statement pooling should be enabled when the driver does not run from within an application server or from within another application that does not provide its own prepared statement pooling. When the driver's internal prepared statement pooling is enabled, the driver caches a certain number of prepared statements created by an application. For example, if the `MaxPooledStatements` property is set to 20, the driver caches the last 20 prepared statements created by the application. If the value set for this property is greater than the number of prepared statements used by the application, all prepared statements are cached.

# ResultSetMetaDataOptions

By default, the Informix driver skips the additional processing required to return the correct table name for each column in the result set when the `ResultSetMetaData.getTableName()` method is called. Because of this, the `getTableName()` method may return an empty string for each column in the result set. If you know that your application does not require table name information, this setting provides the best performance.

See ResultSet MetaData Support for more information about returning ResultSet metadata.

# Data Types

Table 3-2 lists the data types supported by the Informix driver and how they are mapped to the JDBC data types.

Table 3-2 Informix Data Types

Informix Data Type | JDBC Data Type
--- | ---
blob | BLOB
boolean | BIT
byte | LONGVARBINARY
clob | CLOB
char | CHAR
date | DATE
datetime hour to second | TIME
datetime year to day | DATE
datetime year to fraction(5) | TIMESTAMP
datetime year to second | TIMESTAMP
decimal | DECIMAL
float | FLOAT
int8 | BIGINT
integer | INTEGER
lvarchar | VARCHAR
money | DECIMAL
nchar | CHAR
nvarchar | VARCHAR
serial | INTEGER
serial8 | BIGINT
smallfloat | REAL
smallint | SMALLINT
text | LONGVARCHAR
varchar | VARCHAR

See GetTypeInfo for more information about data types.

# SQL Escape Sequences

See SQL Escape Sequences for JDBC for information about the SQL escape sequences supported by the Informix driver.

# Isolation Levels

Informix supports the Read Committed, Read Uncommitted, Repeatable Read, and Serializable isolation levels. The default is Read Committed.

# Using Scrollable Cursors

The Informix driver supports scroll-sensitive result sets, scroll-insensitive result sets, and updatable result sets.

Note: When the Informix driver cannot support the requested result set type or concurrency, it automatically downgrades the cursor and generates one or more SQLWarnings with detailed information.

# Parameter Metadata Support

The Informix driver supports returning parameter metadata as described in this section.

## Insert and Update Statements

The Informix driver supports returning parameter metadata for Insert and Update statements.

## Select Statements

The Informix driver does not support returning parameter metadata for stored procedure arguments.

The Informix driver supports returning parameter metadata for Select statements that contain parameters in ANSI SQL 92 entry-level predicates, for example, such as COMPARISON, BETWEEN, IN, LIKE, and EXISTS predicate constructs. Refer to the ANSI SQL reference for detailed syntax.

Parameter metadata can be returned for a Select statement if one of the following conditions is true:

- The statement contains a predicate value expression that can be targeted against the source tables in the associated FROM clause. For example:

>     SELECT * FROM foo WHERE bar > ?

In this case, the value expression "bar" can be targeted against the table "foo" to determine the appropriate metadata for the parameter.

- The statement contains a predicate value expression part that is a nested query. The nested query's metadata must describe a single column. For example:

>     SELECT * FROM foo WHERE (SELECT x FROM y WHERE z = 1) < ?

The following Select statements show further examples for which parameter metadata can be returned:

>     SELECT col1, col2 FROM foo WHERE col1 = ? and col2 > ?

>     SELECT ... WHERE colname = (SELECT col2 FROM t2 WHERE col3 = ?)

>     SELECT ... WHERE colname LIKE ?

>     SELECT ... WHERE colname BETWEEN ? and ?

>     SELECT ... WHERE colname IN (?, ?, ?)

>     SELECT ... WHERE EXISTS(SELECT ... FROM T2 WHERE col1 < ?)

ANSI SQL 92 entry-level predicates in a WHERE clause containing GROUP BY, HAVING, or ORDER BY statements are supported. For example:

>     SELECT * FROM t1 WHERE col = ? ORDER BY 1

Joins are supported. For example:

>     SELECT * FROM t1,t2 WHERE t1.col1 = ?

Fully qualified names and aliases are supported. For example:

>     SELECT a, b, c, d FROM T1 AS A, T2 AS B WHERE A.a = ?
>        and B.b = ?"

# ResultSet MetaData Support

If your application requires table name information, the Informix driver can return table name information in ResultSet metadata for Select statements. By setting the `ResultSetMetaDataOptions` property to 1, the Informix driver performs additional processing to determine the correct table name for each column in the result set when the `ResultSetMetaData.getTableName()` method is called. Otherwise, the `getTableNames()` method may return an empty string for each column in the result set.

The table name information that is returned by the Informix driver depends on whether the column in a result set maps to a column in a table in the database. For each column in a result set that maps to a column in a table in the database, the Informix driver returns the table name associated with that column. For columns in a result set that do not map to a column in a table (for example, aggregates and literals), the Informix driver returns an empty string.

The Select statements for which ResultSet metadata is returned may contain aliases, joins, and fully qualified names. The following queries are examples of Select statements for which the `ResultSetMetaData.getTableName()` method returns the correct table name for columns in the Select list:

>     SELECT id, name FROM Employee

>     SELECT E.id, E.name FROM Employee E

>     SELECT E.id, E.name AS EmployeeName FROM Employee E

>     SELECT E.id, E.name, I.location, I.phone FROM Employee E,

>        EmployeeInfo I WHERE E.id = I.id

>     SELECT id, name, location, phone FROM Employee,

>        EmployeeInfo WHERE id = empId

>     SELECT Employee.id, Employee.name, EmployeeInfo.location,

>        EmployeeInfo.phone FROM Employee, EmployeeInfo

>        WHERE Employee.id = EmployeeInfo.id

The table name returned by the driver for generated columns is an empty string. The following query is an example of a Select statement that returns a result set that contains a generated column (the column named "upper").

>     SELECT E.id, E.name as EmployeeName, {fn UCASE(E.name)}

>        AS upper FROM Employee E

The Informix driver also can return schema name and catalog name information when the `ResultSetMetaData.getSchemaName()` and `ResultSetMetaData.getCatalogName()` methods are called if the driver can determine that information. For example, for the following statement, the Informix driver returns "test" for the catalog name, "test1" for the schema name, and "foo" for the table name:

>     SELECT * FROM test.test1.foo

The additional processing required to return table name, schema name, and catalog name information is only performed if the `ResultSetMetaData.getTableName()`, `ResultSetMetaData.getSchemaName()`, or `ResultSetMetaData.getCatalogName()` methods are called.

# Rowset Support

The Informix driver supports any JSR 114 implementation of the RowSet interface, including:

- CachedRowSets
- FilteredRowSets
- WebRowSets
- JoinRowSets
- JDBCRowSets

See http://www.jcp.org/en/jsr/detail?id=114 for more information about JSR 114.

# Blob and Clob Searches

When searching a Clob value for a string pattern using the Clob.position method, the search pattern must be less than or equal to a maximum value of 4096 bytes. Similarly, when searching a Blob value for a byte pattern using the Blob.position method, the search pattern must be less than or equal to a maximum value of 4096 bytes.

# FILETOBLOB Feature Support

When converting a file to a Blob using the FILETOBLOB feature with the `SERVER` keyword and a file that exists on the server, the conversion works properly with a command similar to the following:

>     st.executeUpdate("INSERT INTO doc_list VALUES (7, FILETOBLOB('c:\\temp\\INSTSRV.EXE', 'SERVER'))");

You cannot use the `FILETOBLOB` function with the `CLIENT` keyword because the function relies on the Informix client software to handle the data transfer from the client side to the server side. With the WebLogic JDBC Driver for Informix, there is no underlying client software so there is no current implementation to handle this type of data transfer.

# Auto-Generated Keys Support

The Informix driver supports retrieving the values of auto-generated keys. An auto-generated key returned by the Informix driver is the value of a SERIAL column or a SERIAL8 column.

- When using an `Insert` statement that contains no parameters, the Informix driver supports the following form of the `Statement.execute` and `Statement.executeUpdate` methods to inform the driver to return the values of auto-generated keys:

>     Statement.execute (String sql, int autoGeneratedKeys)

>     Statement.executeUpdate (String sql, int autoGeneratedKeys).

- When using a `Insert` statement that contains parameters, the Informix driver supports the following form of the `Connection.prepareStatement` method to inform the driver to return the values of auto-generated keys:

>     Connection.prepareStatement (String sql, int autoGeneratedKeys)

The application fetches the values of generated keys from the driver using the `Statement.getGeneratedKeys()` method.