|
|
The following sections describe how to configure and use the WebLogic Type 4 JDBC Informix driver:
The WebLogic Type 4 JDBC Informix driver (the "Informix driver") supports the following databases:
The driver classes for the 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.
To connect to an Informix database, use the following URL format:
jdbc:bea:informix://hostname:port[;property=value[;...]]hostname is the TCP/IP address or TCP/IP host name of the server to which you are connecting. See Using IP Addresses for details on using IP addresses.| Note: | Untrusted applets cannot open a socket to a machine other than the originating host. |
port is the number of the TCP/IP port. property=value specifies connection properties. For a list of connection properties and their valid values, see Informix Connection Properties.jdbc:bea:informix://server4:1526;informixServer=ol_test;
DatabaseName=ACCT01;User=test;Password=secret
Table 4-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. |
The code page the driver uses when converting character data. The specified code page overrides the default database code page or column collation. All Character data returned 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 JVM, for example,
CodePageOverride=CP950.
|
|
The number of times the driver retries connections to the database server until a successful connection is established. Valid values are 0 and any positive integer.
If a connection is not successfully established on the driver’s first pass through the list of database servers, the driver retries all the servers in the list only once.
If an application sets a login timeout value (for example, using DataSource.loginTimeout or DriverManager.loginTimeout), the login timeout takes precedence over this property. For example, if the login timeout expires, any connection attempts to alternate servers stop.
|
|
The number of seconds the driver waits between connection retry attempts when ConnectionRetryCount is set to a positive integer.
|
|
|
If set to 1 (the default), the driver checks the data type being requested against the data type of the table column storing the data. If a conversion between the requested type and column type is not defined, the driver generates an "unsupported data conversion" exception regardless of the data type of the column value.
|
|
|
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.
|
|
Sets the Informix DBDate server option for formatting literal date values when inserting, updating, and retrieving data in DATE columns. Using this property, you can customize the following items:
where D is a 2-digit day field, M is a 2-digit month field, Y2 is a 2-digit year field, and Y4 is a 4-digit year field.
Optionally, a separator character may be specified as the last character of the value. Valid separator characters are:
|
|
Specifies the size (in bytes) of the fetch buffer that the driver uses when retrieving data from the database. Valid values are any positive integer from 1 to 32767.
Decreasing the fetch buffer size reduces memory consumption, but means more network round trips, which decreases performance. Increasing the fetch buffer size improves performance because fewer network round trips are needed to return data from the database.
where A is the number of rows your application returns when executing Select statements and B is the number of row columns typically returned when executing Select statements.
See Performance Considerations for information about configuring this property for optimal performance.
|
|
Specifies one or multiple SQL commands to be executed by the driver after it has established the connection to the database and has performed all initialization for the connection. For example:
Multiple commands must be separated by semicolons. In addition, if this property is specified in a connection URL, the entire value must be enclosed in parentheses when multiple commands are specified. For example:
|
|
{-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.
|
|
{true | false}. Determines whether the driver uses its internal conversion algorithm or the JVM conversion algorithm when converting double or float values to string values.
If set to true, the driver uses the JVM algorithm when converting double or float values to string values.
If set to false (the default), the driver uses its internal algorithm when converting double or float values to string values. Setting the property to false improves performance; however, slight rounding differences can occur when compared to the same conversion using the JVM algorithm. These differences are within the allowable error of the double and float data types.
|
|
{positive integer | -1 | 0}. Sets the default query timeout (in seconds) for all statements created by a connection.
If set to a positive integer, the driver uses the value as the default timeout for any statement created by the connection. To override the default timeout value set by this connection option, call the Statement.setQueryTimeout() method to set a timeout value for a particular statement.
|
|
{0 | 1}. The Informix driver can return table name information in the ResultSet metadata for Select statements if your application requires that information.
If set to 0 (the default) and the ResultSetMetaData.getTableName() method is called, the driver does not perform additional processing to determine the correct table name for each column in the result set. In this case, the getTableName() method may return an empty string for each column in the result set.
If set to 1 and the ResultSetMetaData.getTableName() method is called, the driver performs additional processing to determine the correct table name for each column in the result set. The 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.
See Performance Considerations for information about configuring this property for optimal performance.
|
|
{true | false} Controls how the Informix server interprets double quote (") characters in SQL statements.
If set to true, the driver sets the Informix DELIMIDENT server option, causing the Informix server to interpret strings enclosed in double quotes as identifiers, not as string literals.
If set to false, the driver does not set the Informix DELIMIDENT server option, and the Informix server interprets strings enclosed in double quotes as string literals, not as identifiers.
|
|
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”.
Setting the following connection properties for the Informix driver as described in the following list can improve performance for your applications:
Decreasing the fetch buffer size reduces memory consumption, but means more network round trips, which decreases performance. Increasing the fetch buffer size improves performance because fewer network round trips are needed to return data from the database. To determine the optimal value, use the formula X = A * B * 50, where A is the number of rows your application returns when executing Select statements and B is the number of row columns typically returned when executing Select statements.
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.
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.
Table 4-2 lists the data types supported by the Informix driver and how they are mapped to the JDBC data types.
See GetTypeInfo, for more information about data types.
See SQL Escape Sequences for JDBC for information about the SQL escape sequences supported by the Informix driver.
Informix supports the Read Committed, Read Uncommitted, Repeatable Read, and Serializable isolation levels. The default is Read Committed.
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. |
The Informix driver supports returning parameter metadata as described in this section.
The Informix driver supports returning parameter metadata for Insert and Update statements.
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:
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.
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 = ?"
When parameter metadata is requested for a column defined as an approximate numeric data type, the driver returns a scale of 255, which indicates the column has an approximate numeric data type and has no scale. For example, suppose we create a table where col2 is an approximate numeric data type with a precision of 20:
CREATE table fooTest(col1 int, col2 decimal(20))
The driver returns parameter metadata that indicates that col2 has a data type of decimal, a precision of 20, and a scale of 255.
The Informix driver does not support returning parameter metadata for stored procedure arguments.
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.
The Informix driver supports any JSR 114 implementation of the RowSet interface, including:
J2SE 1.4 or higher is required to use rowsets with the driver.
See http://www.jcp.org/en/jsr/detail?id=114 for more information about JSR 114.
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.
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.
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.
An application can return values of auto-generated keys when it executes an Insert statement. How you return these values depends on whether you are using an Insert statement that contains parameters:
Insert statement that contains no parameters, the Informix driver supports the following form of the Statement.execute() and Statement.executeUpdate() methods to instruct the driver to return values of auto-generated keys:Statement.execute(String sql, int autoGeneratedKeys) Statement.execute(String sql, int[] columnIndexes)Statement.execute(String sql, String[] columnNames)Statement.executeUpdate(String sql, int autoGeneratedKeys) Statement.executeUpdate(String sql, int[] columnIndexes)Statement.executeUpdate(String sql, String[] columnNames)Insert statement that contains parameters, the Informix driver supports the following form of the Connection.prepareStatement() method to instruct the driver to return values of auto-generated keys:
An application can retrieve values of auto-generated keys using the Statement.getGeneratedKeys() method. This method returns a ResultSet object with a column for each auto-generated key.
The new Database connection property can be used as a synonym of the DatabaseName connection property.
If both the Database and DatabaseName connection properties are specified in a connection URL, the last of either property positioned in the connection URL is used. For example, if your application specifies the following connection URL, the value of the Database connection property would be used instead of the value of the DatabaseName connection property.
jdbc:bea:informix://server1:2003;InformixServer=ol_test;
DatabaseName=jdbc;Database=acct;User=test;Password=secret
|