Informix JDBC Driver Programmer's Guide Programming with Informix JDBC Driver

Informix JDBC Driver complies with the JDBC API specification for sending queries to a database and retrieving the results. The driver supports almost all the methods of the Statement, PreparedStatement, CallableStatement, ResultSet, and ResultSetMetaData interfaces.

The batch update feature is similar to Informix multiple SQL prepare statements. For example, Informix supports the following statement:

PREPARE stmt FROM "insert into tab values (1); insert into tab values(2);

update table tab set col = 3 where col =2";

The batch update feature in Informix JDBC Driver follows the Sun Microsystems JDBC 2.0 specification, with these exceptions:

• SQL statements

The following commands cannot be put into multistatement PREPARE statements:

• SELECT (except SELECT INTO TEMP) statement
• DATABASE statements
• CONNECTION statements

For more details, refer to Informix Guide to SQL: Syntax.

• Statement.executeBatch() return value

The return value differs from the Sun Microsystems JDBC 2.0 specification in the following ways:

• Only the update count of the first statement executed in the batch is returned.
• When errors occur in a batch update executed in a Statement object, no rows are affected by the statement; the statement is not executed. Calling BatchUpdateException.getUpdateCounts() returns 0 in this case.
• When errors occur in a batch update executed in a PreparedStatement object, rows that were successfully inserted or updated on the database server do not revert to their pre-updated state. However, the statements are not always commited; they are still subject to the underlying autocommit mode.

The BatchUpdate.java example file shows how to send batch updates to the database server.

The scroll cursors feature in Informix JDBC Driver follows the Sun Microsystems JDBC 2.0 specification, with these exceptions:

• Scroll sensitivity

The Informix database server implementation of scroll cursors places the rows fetched in a temporary table. If another process changes a row (assuming the row is not locked) and the row is fetched again, the changes are not visible to the client. This behavior is similar to the scroll-insensitive description in the JDBC 2.0 specification. To see updated rows, your client application must close and reopen the cursor.

• Client-side scrolling

The JDBC specification implies that the scrolling can happen on the client-side result set. The 2.0 release of Informix JDBC Driver supports the scrolling of the result set only according to how the database server supports scrolling.

• Concurrency types

The JDBC 2.0 specification states that the result set can have two types of concurrency: read-only and updatable. A result set that uses read-only concurrency does not allow updates of its contents. A result set that is updatable allows updates and may use database write locks to mediate access to the same data item by different transactions.

The Informix database server does not support updatable scroll cursors. If a cursor is declared as SCROLL, you cannot issue an UPDATE WHERE CURRENT OF statement.

• Updates

The JDBC specifications provide a series of methods that an application can use to update a fetched result set if its concurrency type is CONCUR_UPDATABLE. According to this specification, rows can be updated, inserted, and deleted from the result set. However, Informix JDBC Driver supports only a read-only result set.

The ScrollCursor.java example file shows how to retrieve a result set with a scroll cursor.

This section describes the Informix-specific information you need to know to use Informix JDBC Driver to query an Informix database and process the results.

The Informix JDBC Driver implementation of the Statement.execute() method returns a single ResultSet object. This implementation differs from the JDBC API specification, which states that the method can return multiple ResultSet objects.

Be sure to always explicitly close a Statement, PreparedStatement, and CallableStatement object by calling the appropriate close() method in your Java program when you have finished processing the results of an SQL statement. This closure immediately deallocates the resources that have been allocated to execute your SQL statement. Although the ResultSet.close() method closes the ResultSet object, it does not deallocate the resources allocated to the Statement, PreparedStatement, or CallableStatement objects.

Important: For best results, always call ResultSet.close() and Statement.close() methods to indicate to Informix JDBC Driver that you are done with the statement or result set. Otherwise, your program might not release all its resources on the database server.

Important: The same Statement or ResultSet instance cannot be accessed concurrently across threads. You can, however, share a Connection object between multiple threads.

For example, if one thread executes the Statement.executeQuery() method on a Statement object, and another thread executes the Statement.executeUpdate() method on the same Statement object, the results of both methods are unexpected and depend on which method was executed last.

Similarly, if one thread executes the method ResultSet.next() and another thread executes the same method on the same ResultSet object, the results of both methods are unexpected and depend on which method was executed last.

Example of Sending a Query to an Informix Database

The following example from the SimpleSelect.java program shows how to use the PreparedStatement interface to execute a SELECT statement that has one input parameter:

try

{

PreparedStatement pstmt = conn.prepareStatement("Select * from x "

+ "where a = ?;");

pstmt.setInt(1, 11);

ResultSet r = pstmt.executeQuery();

while(r.next())

{

short i = r.getShort(1);

System.out.println("Select: column a = " + i);

}

r.close();

pstmt.close();

}

catch (SQLException e)

{

System.out.println("ERROR: Fetch statement failed: " + e.getMessage());

}

The program first uses the Connection.prepareStatement() method to prepare the SELECT statement with its single input parameter. It then assigns a value to the parameter using the PreparedStatement.setInt() method and executes the query with the PreparedStatement.executeQuery() method.

The program returns resulting rows in a ResultSet object, through which the program iterates with the ResultSet.next() method. The program retrieves individual column values with the ResultSet.getShort() method, since the data type of the selected column is SMALLINT.

Finally, both the ResultSet and PreparedStatement objects are explicitly closed with the appropriate close() method.

For more information on which getXXX() methods retrieve individual column values, refer to Supported ResultSet.getXXX() Methods.

Valid escape syntax for SQL statements is as follows:

You can put any of this syntax in an SQL statement, as follows:

executeUpdate("insert into tab1 values( {d '1999-01-01'} )");

Everything inside the brackets is converted into a valid Informix SQL statement and returned to the calling function.

The following JDBC API methods are not supported by Informix JDBC Driver and cannot be used in a Java program that connects to an Informix database:

• CallableStatement.registerOutParameter()
• Connection.isReadOnly()
• Connection.setCatalog()
• Connection.setReadOnly()
• PreparedStatement.setRef(int, Ref)
• PreparedStatement.setUnicodeStream()
• ResultSet.cancelRowUpdate()
• ResultSet.deleteRow()
• ResultSet.getFetchSize()
• ResultSet.getRef(int)
• ResultSet.getRef(String)
• ResultSet.getUnicodeStream()
• ResultSet.insertRow()
• ResultSet.moveToCurrentRow()
• ResultSet.moveToInsertRow()
• ResultSet.refreshRow()
• ResultSet.rowDeleted()
• ResultSet.rowInserted()
• ResultSet.rowUpdated()
• ResultSet.setFetchSize()
• ResultSet.updateRow()
• ResultSet.updateXXX()
• Statement.cancel()
• Statement.setMaxFieldSize()
• Statement.setQueryTimeout()

The following JDBC API methods behave differently than specified by the JavaSoft specification:

• CallableStatement.execute()

Method returns a single result set.

• PreparedStatement.execute()

Method returns a single result set.

• ResultSetMetaData.getCatalogName()

Method always returns the value "".

• ResultSetMetaData.getTableName()

Method always returns the value "".

• ResultSetMetaData.getSchemaName()

Method always returns the value "".

• ResultSetMetaData.isDefinitelyWriteable()

Method always returns TRUE.

• ResultSetMetaData.isReadOnly()

Method always returns FALSE.

• ResultSetMetaData.isWriteable()

Method always returns TRUE.

• Statement.execute()

Method returns a single result set.