2 Working with TimesTen Databases in JDBC

Preparing SQL statements and setting input parameters

SQL statements that are to be executed more than once should be prepared in advance by calling the Connection method prepareStatement(). For maximum performance, prepare parameterized statements.

Be aware of the following:

• The TimesTen binding mechanism (early binding) differs from that of Oracle Database (late binding). TimesTen requires the data types before preparing queries. As a result, there will be an error if the data type of each bind parameter is not specified or cannot be inferred from the SQL statement. This would apply, for example, to the following statement:

  SELECT 'x' FROM DUAL WHERE ? = ?;
  

  You could address the issue as follows, for example.

  SELECT 'x' from DUAL WHERE CAST(? as VARCHAR2(10)) = CAST(? as VARCHAR2(10));
  

• By default (when connection attribute PrivateCommands=0), TimesTen shares prepared statements between connections, so subsequent prepares of the same statement on different connections execute very quickly.

• Application performance is influenced by the choice of setXXX() calls and by any required data transformations before invocation. For example, for time, dates, and timestamps, the PreparedStatement native methods setTime(), setDate() and setTimestamp() have better performance than setString().

• For TT_TINYINT columns, use setShort() or setInt() instead of setByte() to use the full range of TT_TINYINT (0-255).

• Settings using setObject(java.util.Calendar) and setDate(java.util.Date) are mapped to TIMESTAMP.

PreparedStatement pSel = conn.prepareStatement("select cust_num, " +
                         "region, name, address " +
                         "from customer" +
                         "where region = ?");
pSel.setInt(1,1);

try {
  ResultSet rs = pSel.executeQuery();

  while (rs.next()) {
    System.out.println("\n Customer number: " + rs.getInt(1));
    System.out.println(" Region: " + rs.getString(2));
    System.out.println(" Name: " + rs.getString(3));
    System.out.println(" Address: " + rs.getString(4));
  }
} 
catch (SQLException ex) {
   ex.printStackTrace();
}

Statement.execute("insert into t1 values (1, 2)");
Statement.execute("insert into t1 values (3, 4)");
Statement.execute("insert into t1 values (5, 6)");
Statement.execute("insert into t1 values (7, 8)");

PreparedStatement pIns = conn.PreparedStatement("insert into t1 values (?,?)");

pIns.setInt(1, 1);
pIns.setInt(2, 2);
pIns.executeUpdate();

pIns.setInt(1, 3);
pIns.setInt(2, 4);
pIns.executeUpdate();

pIns.setInt(1, 5);
pIns.setInt(2, 6);
pIns.executeUpdate();

pIns.setInt(1, 7);
pIns.setInt(2, 8);
pIns.executeUpdate();

conn.commit();
pIns.close();

TimesTen shares prepared statements automatically after they have been committed. For example, if two or more separate connections to the database each prepare the same statement, then the second, third, ... , nth prepared statements return very quickly because TimesTen remembers the first prepared statement.

Connection conn = null;
...
// [Code to open connection. See "Connect to the database"...]
...

// Disable auto-commit
conn.setAutoCommit(false);

    // Report any SQLWarnings on the connection
    // See "Reporting errors and warnings"

// Prepare a parameterized INSERT and a SELECT Statement
PreparedStatement pIns = 
                  conn.prepareStatement("insert into customer values (?,?,?,?)");

PreparedStatement pSel = conn.prepareStatement
    ("select cust_num, region, name, " +
    "address from customer");

// Data for first INSERT statement
pIns.setInt(1, 100);
pIns.setString(2, "N");
pIns.setString(3, "Fiberifics");
pIns.setString(4, "123 any street");

// Execute the INSERT statement
pIns.executeUpdate();

// Data for second INSERT statement
pIns.setInt(1, 101);
pIns.setString(2, "N");
pIns.setString(3, "Natural Foods Co.");
pIns.setString(4, "5150 Johnson Rd");

// Execute the INSERT statement
pIns.executeUpdate();

// Commit the inserts
conn.commit();

// Done with INSERTs, so close the prepared statement
pIns.close();

// Report any SQLWarnings on the connection. 
reportSQLWarnings(conn.getWarnings());

// Execute the prepared SELECT statement
ResultSet rs = pSel.executeQuery();

System.out.println("Fetching result set...");
while (rs.next()) {
    System.out.println("\n Customer number: " + rs.getInt(1));
    System.out.println(" Region: " + rs.getString(2));
    System.out.println(" Name: " + rs.getString(3));
    System.out.println(" Address: " + rs.getString(4));
}

// Close the result set.
rs.close();

// Commit the select - yes selects must be committed too
conn.commit();

// Close the select statement - we are done with it
pSel.close();

Connection conn1 = null;
Connection conn2 = null;
Connection conn3 = null;
.....
PreparedStatement pIns1 = conn1.prepareStatement
                  ("insert into t1 values (?,?)");

PreparedStatement pIns2 = conn2.prepareStatement
                  ("insert into t1 values (?,?)");

PreparedStatement pIns3 = conn3.prepareStatement
                  ("insert into t1 values (?,?)");

Working with output and input/output parameters

"Preparing SQL statements and setting input parameters" shows how to prepare a statement and set input parameters using PreparedStatement methods. TimesTen also supports output and input/output parameters, for which you use java.sql.CallableStatement instead of PreparedStatement, as follows.

1. Use the method registerOutParameter() to register an output or input/output parameter, specifying the parameter position (position in the statement) and data type.

   This is the standard method as specified in the CallableStatement interface:

   void registerOutParameter(int parameterIndex, int sqlType, int scale)
   

   Be aware, however, that if you use this standard version for CHAR, VARCHAR, NCHAR, NVARCHAR, BINARY, or VARBINARY data, TimesTen allocates memory to hold the largest possible value. In many cases this is wasteful.

   Instead, you can use the TimesTen extended interface TimesTenCallableStatement, which has a registerOutParameter() signature that enables you to specify the maximum data length. For CHAR, VARCHAR, NCHAR, and NVARCHAR, the unit of length is number of characters. For BINARY and VARBINARY, it is bytes.

   void registerOutParameter(int paramIndex,
                             int sqlType,
                             int ignore, //This parameter is ignored by TimesTen.
                             int maxLength)
   

2. Use the appropriate CallableStatement method setXXX(), where XXX indicates the data type, to set the input value of an input/output parameter. Specify the parameter position and data value.

3. Use the appropriate CallableStatement method getXXX() to get the output value of an output or input/output parameter, specifying the parameter position.

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Types;
import com.timesten.jdbc.TimesTenCallableStatement;
...
// Prepare to call a PL/SQL stored procedure RAISE_SALARY
CallableStatement cstmt = conn.prepareCall
                          ("BEGIN :newSalary := RAISE_SALARY(:name, :inc); end;");
      
// Declare that the first param (newSalary) is a return (output) value of type int
cstmt.registerOutParameter(1, Types.INTEGER);
 
// Raise Leslie's salary by $2000 (she wanted $3000 but we held firm)
cstmt.setString(2, "LESLIE"); // name argument (type String) is the second param
cstmt.setInt(3, 2000); // raise argument (type int) is the third param
 
// Do the raise
cstmt.execute();

// Check warnings. If there are warnings, output parameter values are undefined.
SQLWarning wn;
boolean warningFlag = false;
if ((wn = cstmt.getWarnings() ) != null) {
   do {
        warningFlag = true;
        System.out.println(wn);
        wn = wn.getNextWarning();
   } while(wn != null);
}      
      
// Get the new salary back
if (!warningFlag) {
   int new_salary = cstmt.getInt(1);
   System.out.println("The new salary is: " + new_salary);
}

// Close the statement and connection
cstmt.close();
conn.close();
...

Binding duplicate parameters in SQL statements

In TimesTen, multiple occurrences of the same parameter name in a SQL statement are considered to be distinct parameters. (This is consistent with Oracle Database support for binding duplicate parameters.)

Consider this query:

SELECT * FROM employees
  WHERE employee_id < :a AND manager_id > :a AND salary < :b;

When parameter position numbers are assigned, a number is given to each parameter occurrence without regard to name duplication. The application must, at a minimum, bind a value for the first occurrence of each parameter name. For any subsequent occurrence of a given parameter name, the application has the following choices.

• It can bind a different value for the occurrence.

• It can leave the parameter occurrence unbound, in which case it takes the same value as the first occurrence.

In either case, each occurrence still has a distinct parameter position number.

To use a different value for the second occurrence of a in the SQL statement above:

pstmt.setXXX(1, ...); /* first occurrence of :a */
pstmt.setXXX(2, ...); /* second occurrence of :a */
pstmt.setXXX(3, ...); /* occurrence of :b */

To use the same value for both occurrences of a:

pstmt.setXXX(1, ...); /* both occurrences of :a */
pstmt.setXXX(3, ...); /* occurrence of :b */

Parameter b is considered to be in position 3 regardless.

Binding duplicate parameters in PL/SQL

The preceding discussion does not apply to PL/SQL, which has its own semantics. In PL/SQL, you bind a value for each unique parameter name. An application executing the following block, for example, would bind only one parameter, corresponding to :a.

DECLARE
   x NUMBER;
   y NUMBER;
BEGIN
   x:=:a;
   y:=:a;
END;

An application executing the following block would also bind only one parameter:

BEGIN
   INSERT INTO tab1 VALUES(:a, :a);
END

And the same for the following CALL statement:

...CALL proc(:a, :a)...

An application executing the following block would bind two parameters, with :a as parameter #1 and :b as parameter #2. The second parameter in each INSERT statement would take the same value as the first parameter in the first INSERT statement, as follows.

BEGIN
   INSERT INTO tab1 VALUES(:a, :a);
   INSERT INTO tab1 VALUES(:b, :a);
END

Binding associative arrays

TimesTen JDBC supports associative arrays, formerly known as index-by tables or PL/SQL tables, as IN, OUT, or IN OUT bind parameters to TimesTen PL/SQL. Associative arrays enable arrays of data to be passed efficiently between a JDBC application and the database.

An associative array is a set of key-value pairs. In TimesTen, for associative array binding (but not for use of associative arrays only within PL/SQL), the keys, or indexes, must be integers (BINARY_INTEGER or PLS_INTEGER). The values must be simple scalar values of the same data type. For example, there could be an array of department managers indexed by department numbers. Indexes are stored in sort order, not creation order.

You can declare an associative array type and then an associative array from PL/SQL as in the following example (note the INDEX BY):

declare
   TYPE VARCHARARRTYP IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
   x VARCHARARRTYP;
   ...

Also see "Using associative arrays from applications" in Oracle TimesTen In-Memory Database PL/SQL Developer's Guide.

When you bind an associative array in Java, match the Java type as closely as possible with the array type for optimal performance. TimesTen does, however, support some simple input conversions:

• Strings can be converted to integers or floating point numbers.

• Strings can be converted to DATE data if the strings are in TimesTen DATE format (YYYY-MM-DD HH:MI:SS).

TimesTen provides extensions, described below, through the interfaces TimesTenPreparedStatement and TimesTenCallableStatement to support associative array binds. Refer to Oracle TimesTen In-Memory Database JDBC Extensions Java API Reference for additional information about any of the methods described here.

For an associative array that is a PL/SQL IN or IN OUT parameter, TimesTen provides the setPlsqlIndexTable() method in the TimesTenPreparedStatement interface (for an IN parameter) and in the TimesTenCallableStatement interface (for an IN OUT parameter) to set the input associative array.

• void setPlsqlIndexTable(int paramIndex, java.lang.Object arrayData, int maxLen, int curLen, int elemSqlType, int elemMaxLen)

  Specify the following:

  • paramIndex: Parameter position within the PL/SQL statement (starting with 1)

  • arrayData: Array of values to be bound (which can be an array of primitive types such as int[] or an array of object types such as BigDecimal[])

  • maxLen: Maximum number of elements in the associative array (in TimesTen must be same as curLen)

  • curLen: Actual current number of elements in the associative array (in TimesTen must be same as maxLen)

  • elemSqlType: Type of the associative array elements according to java.sql.Types (such as Types.DOUBLE)

  • elemMaxLen: For CHAR, VARCHAR, BINARY, or VARBINARY associative arrays, the maximum length of each element (in characters for CHAR or VARCHAR associative arrays, or in bytes for BINARY or VARBINARY associative arrays)

  For example (assuming a TimesTenPreparedStatement instance pstmt):

  int maxLen = 3;
  int curLen = 3;
  // Numeric field can be set with int, float, double types.
  // elemMaxLen is set to 0 for numeric types and is ignored.
  // elemMaxLen is specified for VARCHAR types.
  pstmt.setPlsqlIndexTable
        (1, new int[]{4, 5, 6}, maxLen, curLen, Types.NUMERIC, 0);
  pstmt.setPlsqlIndexTable
        (2, new String[]{"Batch1234567890", "2", "3"}, maxLen, curLen,
         Types.VARCHAR, 15);
  pstmt.execute();
  

For an associative array that is a PL/SQL OUT or IN OUT parameter, TimesTen provides two methods in the TimesTenCallableStatement interface: registerIndexTableOutParameter() to register an output associative array, and getPlsqlIndexTable() to retrieve an output associative array. There are two signatures for getPlsqlIndexTable(), one to use the JDBC default Java object type given the associative array element SQL type, and one to specify the type.

• void registerIndexTableOutParameter(int paramIndex, int maxLen, int elemSqlType, int elemMaxLen)

  Specify the following:

  • paramIndex: Parameter position within the PL/SQL statement (starting with 1)

  • maxLen: Maximum possible number of elements in the associative array

  • elemSqlType: Type of the associative array elements according to java.sql.Types (such as Types.DOUBLE)

  • elemMaxLen: For CHAR, VARCHAR, BINARY, or VARBINARY associative arrays, the maximum length of each element (in characters for CHAR or VARCHAR associative arrays, or in bytes for BINARY or VARBINARY associative arrays)

  Note:

  If elemMaxLen has a value of 0 or less, the maximum length for the data type is used.

• java.lang.Object getPlsqlIndexTable(int paramIndex)

  With this method signature, the type of the returned associative array is the JDBC default mapping for the SQL type of the data retrieved. Specify the parameter position within the PL/SQL statement (starting with 1). See Table 2-4 for the default mappings.

• java.lang.Object getPlsqlIndexTable(int paramIndex, java.lang.Class primitiveType)

  With this method signature, in addition to specifying the parameter position, specify the desired type of the returned associative array according to java.sql.Types (such as Types.DOUBLE). It must be a primitive type.

The following code fragment illustrates how to set, register, and retrieve the contents of an IN OUT parameter (assuming a connection conn and TimesTenCallableStatement instance cstmt):

int maxLen = 3;
int curLen = 3;
anonBlock = "begin AssocArrayEx_inoutproc(:o1); end;";
cstmt = (TimesTenCallableStatement) conn.prepareCall(anonBlock); 
cstmt.setPlsqlIndexTable
     (1, new Integer[] {1,2,3}, maxLen, curLen, Types.NUMERIC, 0);
cstmt.registerIndexTableOutParameter(1, maxLen, Types.NUMERIC, 0);
cstmt.execute();
 
int[]  ret = (int [])cstmt.getPlsqlIndexTable(1, Integer.TYPE);
cstmt.execute();

    TimesTenCallableStatement cstmt = null;
    try {
      // Prepare procedure with associative array in parameter
      cstmt = (TimesTenCallableStatement) 
               conn.prepareCall("begin AssociativeArray_proc(:name, :inc); end;");
      
      // Set up input array and length
      String[] name = {"George", "John", "Thomas", "James", "Bill"};
      Integer[] salaryInc = {10000, null, 5000, 8000, 9007};
      int currentLen = name.length;
      int maxLen = currentLen;
 
      
      // Use elemMaxLen for variable length data types such as 
      // Types.VARCHAR, Types.CHAR.
      int elemMaxLen = 32; 
      
      // set input parameter, name as a VARCHAR
      cstmt.setPlsqlIndexTable 
            (1, name, maxLen, currentLen, Types.VARCHAR, elemMaxLen);
      // set input parameter, salaryInc as a number
      cstmt.setPlsqlIndexTable 
            (2, salaryInc, maxLen, currentLen, Types.NUMERIC, 0);

About LOBs

A LOB is a large binary object (BLOB) or character object (CLOB or NCLOB). In TimesTen, a BLOB can be up to 16 MB and a CLOB or NCLOB up to 4 MB. LOBs in TimesTen have essentially the same functionality as in Oracle Database, except as noted otherwise. (See "Differences between TimesTen LOBs and Oracle Database LOBs".)

LOBs may be either persistent or temporary. A persistent LOB exists in a LOB column in the database. A temporary LOB exists only within an application. There are also circumstances where a temporary LOB is created implicitly by TimesTen. For example, if a SELECT statement selects a LOB concatenated with an additional string of characters, TimesTen creates a temporary LOB to contain the concatenated data.

LOB objects in JDBC

In JDBC, a LOB object—Blob, Clob, or NClob instance—is implemented using a SQL LOB locator (BLOB, CLOB, or NCLOB), which means that a LOB object contains a logical pointer to the LOB data rather than the data itself.

An application can use the JDBC API to instantiate a temporary LOB explicitly, for use within the application, then to free the LOB when done with it. Temporary LOBs are stored in the TimesTen temporary data region.

To update a persistent LOB, your transaction must have an exclusive lock on the row containing the LOB. You can accomplish this by selecting the LOB with a SELECT ... FOR UPDATE statement. This results in a writable locator. With a simple SELECT statement, the locator is read-only. Read-only and writable locators behave as follows:

• A read-only locator is read consistent, meaning that throughout its lifetime, it sees only the contents of the LOB as of the time it was selected. Note that this would include any uncommitted updates made to the LOB within the same transaction before the LOB was selected.

• A writable locator is updated with the latest data from the database each time a write is made through the locator. So each write is made to the most current data of the LOB, including updates that have been made through other locators.

The following example details behavior for two writable locators for the same LOB.

1. The LOB column contains "XY".

2. Select locator L1 for update.

3. Select locator L2 for update.

4. Write "Z" through L1 at offset 1.

5. Read through locator L1. This would return "ZY".

6. Read through locator L2. This would return "XY", because L2 remains read-consistent until it is used for a write.

7. Write "W" through L2 at offset 2.

8. Read through locator L2. This would return "ZW". Prior to the write in the preceding step, the locator was updated with the latest data ("ZY").

Differences between TimesTen LOBs and Oracle Database LOBs

Be aware of the following:

• A key difference between the TimesTen LOB implementation and the Oracle Database implementation is that in TimesTen, LOB objects do not remain valid past the end of the transaction. All LOB objects are invalidated after a commit or rollback, whether explicit or implicit. This includes after any autocommit (making it infeasible to use LOBs with autocommit enabled), or after any DDL statement.

• TimesTen does not support BFILEs, SecureFiles, reads and writes for arrays of LOBs, or callback functions for LOBs.

• TimesTen does not support binding associative arrays of LOBs.

• TimesTen does not support batch processing of LOBs.

• Relevant to BLOBs, there are differences in the usage of hexadecimal literals in TimesTen. see the description of HexadecimalLiteral in "Constants" in Oracle TimesTen In-Memory Database SQL Reference.

LOB factory methods

TimesTen supports the standard Connection methods createBlob(), createClob(), and createNClob().

Refer to Oracle TimesTen In-Memory Database JDBC Extensions Java API Reference for additional information.

LOB getter and setter methods

You can access LOBs through getter and setter methods that are defined by the standard java.sql.ResultSet, PreparedStatement, and CallableStatement interfaces, just as they are for other data types. Use the appropriate getXXX() method to retrieve a LOB result or output parameter or setXXX() method to bind a LOB input parameter:

• ResultSet getter methods: There are getBlob() methods, getClob() methods, and getNClob() methods where you can specify the LOB to retrieve according to either column name or column index.

  You can also use getObject() to retrieve a Blob, Clob, or NClob object.

• PreparedStatement setter methods: There is a setBlob() method, setClob() method, and setNClob() method where you can input the Blob, Clob, or NClob instance and the parameter index to bind an input parameter.

  You can also use setObject() to bind a Blob, Clob, or NClob input parameter.

  There are also setBlob() methods where instead of a Blob instance, you specify an InputStream instance, or an InputStream instance and length.

  There are setClob() and setNClob() methods where instead of a Clob or NClob instance, you specify a Reader instance, or a Reader instance and length.

• CallableStatement getter methods: There are getBlob() methods, getClob() methods, and getNClob() methods where you can retrieve the LOB output parameter according to either parameter name or parameter index.

  You can also use getObject() to retrieve a Blob, Clob, or NClob output parameter.

  You must also register an output parameter from a CallableStatement object. The registerOutParameter() method takes the parameter index along with the SQL type: Types.BLOB, Types.CLOB, or Types.NCLOB.

• CallableStatement setter methods: These are identical to (inherited from) PreparedStatement setter methods.

Refer to Oracle TimesTen In-Memory Database JDBC Extensions Java API Reference for additional information.

TimesTen LOB interface methods

You can cast a Blob instance to com.timesten.jdbc.TimesTenBlob, a Clob instance to com.timesten.jdbc.TimesTenClob, and an NClob instance to com.timesten.jdbc.TimesTenNClob. These interfaces support methods specified by the java.sql.Blob, Clob, and NClob interfaces.

The following list summarizes Blob features.

• The isPassthrough() method, a TimesTen extension, indicates whether the BLOB is a passthrough LOB from Oracle Database.

• Free Blob resources when the application is done with it.

• Retrieve the BLOB value as a binary stream. There are methods to retrieve it in whole or in part.

• Retrieve all or part of the BLOB value as a byte array.

• Return the number of bytes in the BLOB.

• Retrieve a stream to be used to write binary data to the BLOB, beginning at the specified position. This overwrites existing data.

• Specify an array of bytes to write to the BLOB, beginning at the specified position, and return the number of bytes written. This overwrites existing data. There are methods to write either all or part of the array.

• Truncate the BLOB to the specified length.

The following list summarizes Clob and NClob features.

• The isPassthrough() method, a TimesTen extension, indicates whether the CLOB or NCLOB is a passthrough LOB from Oracle Database.

• Free Clob or NClob resources when the application is done with it.

• Retrieve the CLOB or NCLOB as an ASCII stream.

• Retrieve the CLOB or NCLOB as a java.io.Reader object (or as a stream of characters). There are methods to retrieve it in whole or in part.

• Retrieve a copy of the specified substring in the CLOB or NCLOB, beginning at the specified position for up to the specified length.

• Return the number of characters in the CLOB or NCLOB.

• Retrieve a stream to be used to write ASCII characters to the CLOB or NCLOB, beginning at the specified position. This overwrites existing data.

• Specify a Java String value to write to the CLOB or NCLOB, beginning at the specified position. This overwrites existing data. There are methods to write either all or part of the String value.

• Truncate the CLOB or NCLOB to the specified length.

Refer to Oracle TimesTen In-Memory Database JDBC Extensions Java API Reference for additional information.

LOB prefetching

To reduce round trips to the server in client/server connections, LOB prefetching is enabled by default when you fetch a LOB from the database. The default prefetch size is 4000 bytes for BLOBs or 4000 characters for CLOBs or NCLOBs.

You can use the TimesTenConnection property CONNECTION_PROPERTY_DEFAULT_LOB_PREFETCH_SIZE to set a different default value that applies to any statement in the connection. Use a value of -1 to disable LOB prefetching by default for the connection, 0 (zero) to enable LOB prefetching for only metadata by default, or any value greater than 0 to specify the number of bytes for BLOBs or characters for CLOBs and NCLOBs to be prefetched by default along with the LOB locator during fetch operations.

At the statement level, you can use the following TimesTenStatement methods to manipulate the prefetch size and override the default value from the connection:

• setLobPrefetchSize(int): Set a new LOB prefetch value for the statement.

• int getLobPrefetchSize(): Return the current LOB prefetch value that applies to the statement (either a value set in the statement itself or the default value from the connection, as applicable).

Refer to Oracle TimesTen In-Memory Database JDBC Extensions Java API Reference for additional information.

Passthrough LOBs

Passthrough LOBs, which are LOBs in Oracle Database accessed through TimesTen, are exposed as TimesTen LOBs and are supported by TimesTen in much the same way that any TimesTen LOB is supported, but note the following:

• As noted in "TimesTen LOB interface methods", the TimesTenBlob, TimesTenClob, and TimesTenNClob interfaces specify the following method to indicate whether the LOB is a passthrough LOB:

  boolean isPassthrough()

• TimesTen LOB size limitations do not apply to storage of LOBs in the Oracle database through passthrough.

• As with TimesTen local LOBs, a passthrough LOB object does not remain valid past the end of the transaction.

Refer to Oracle TimesTen In-Memory Database JDBC Extensions Java API Reference for additional information.

Setting autocommit

A TimesTen connection has autocommit enabled by default, but for performance reasons it is recommended that you disable it. You can use the Connection method setAutoCommit() to enable or disable autocommit.

Disable autocommit as follows:

myconn.setAutoCommit(false);
// Report any SQLWarnings on the connection.
// See "Reporting errors and warnings".

Manually committing or rolling back changes

If autocommit is disabled, you must use the Connection method commit() to manually commit transactions, or the rollback() method to roll back changes. Consider the following example.

myconn.commit();

Or:

myconn.rollback();

Using COMMIT and ROLLBACK SQL statements

You can prepare and execute COMMIT and ROLLBACK SQL statements the same way as other SQL statements. Using COMMIT and ROLLBACK statements has the same effect as using the Connection methods commit() and rollback(). For example:

mystmt.execute("COMMIT");

Setting a timeout duration for SQL statements

In TimesTen, you can specify this timeout value for a connection, and therefore any statement on the connection, by using either the SQLQueryTimeout general connection attribute (in seconds) or the SQLQueryTimeoutMsec general connection attribute (in milliseconds). The default value of each is 0, for no timeout. (Also see "SQLQueryTimeout" and "SQLQueryTimeoutMsec" in Oracle TimesTen In-Memory Database Reference.)

Despite the names, these timeout values apply to any executable SQL statement, not just queries.

For a particular statement, you can override the SQLQueryTimeout setting by calling the Statement method setQueryTimeout().

The query timeout limit has effect only when the SQL statement is actively executing. A timeout does not occur during the commit or rollback phase of an operation. For those transactions that update, insert or delete a large number of rows, the commit or rollback phases may take a long time to complete. During that time the timeout value is ignored.

See "Choose SQL and PL/SQL timeout values" in Oracle TimesTen In-Memory Database Operations Guide for considerations regarding the SQL query timeout with respect to other timeout settings.

Setting a threshold duration for SQL statements

You can configure TimesTen to write a warning to the support log when the execution of a SQL statement exceeds a specified time duration, in seconds. Execution continues and is not affected by the threshold.

Despite the name, this threshold applies to any JDBC call executing a SQL statement, not just queries.

By default, the application obtains the threshold value from the QueryThreshold general connection attribute setting, for which the default is 0 (no warnings). You can override the threshold for a JDBC Connection object by including the QueryThreshold attribute in the connection URL for the database. For example, to set QueryThreshold to a value of 5 seconds for the myDSN database:

jdbc:timesten:direct:dsn=myDSN;QueryThreshold=5

You can also use the setQueryTimeThreshold() method of a TimesTenStatement object to set the threshold. This overrides the connection attribute setting and the Connection object setting.

You can retrieve the current threshold value by using the getQueryTimeThreshold() method of the TimesTenStatement object.

Refer to Oracle TimesTen In-Memory Database JDBC Extensions Java API Reference for additional information.

Setting temporary passthrough level with the ttOptSetFlag built-in procedure

TimesTen provides the ttOptSetFlag built-in procedure for setting various flags, including the PassThrough flag to temporarily set the passthrough level. You can use ttOptSetFlag to set PassThrough in a JDBC application as in the following sample statement, which sets the passthrough level to 1. The setting affects all statements that are prepared until the end of the transaction.

pstmt = conn.prepareStatement("call ttoptsetflag('PassThrough', 1)");

The example that follows has samples of code that accomplish these steps:

1. Create a prepared statement (a PreparedStatement instance thePassThroughStatement) that calls ttOptSetFlag using a bind parameter for passthrough level.

2. Define a method setPassthrough() that takes a specified passthrough setting, binds it to the prepared statement, then executes the prepared statement to call ttOptSetFlag to set the passthrough level.

  thePassThroughStatement = 
         theConnection.prepareStatement("call ttoptsetflag('PassThrough', ?)");
  ...
  private void setPassthrough(int level) throws SQLException{
    thePassThroughStatement.setInt(1, level);
    thePassThroughStatement.execute();
  }

See "ttOptSetFlag" in Oracle TimesTen In-Memory Database Reference for more information about this built-in procedure.

See "PassThrough" in Oracle TimesTen In-Memory Database Reference for information about that general connection attribute. See "Setting a passthrough level" in Oracle TimesTen Application-Tier Database Cache User's Guide for information about passthrough settings.

Determining passthrough status

You can call the TimesTenPreparedStatement method getPassThroughType() to determine whether a SQL statement is to be executed in the TimesTen database or passed through to the Oracle database for execution:

PassThroughType getPassThroughType()

The return type, TimesTenPreparedStatement.PassThroughType, is an enumeration type for values of the TimesTen PassThrough connection attribute.

You can make this call after preparing the SQL statement. It is useful with PassThrough settings of 1 or 2, where the determination of whether a statement is actually passed through is not made until compilation time.

See "Setting a passthrough level" in Oracle TimesTen Application-Tier Database Cache User's Guide for information about PassThrough settings.

Managing cache groups

In TimesTen, following the execution of a FLUSH CACHE GROUP, LOAD CACHE GROUP, REFRESH CACHE GROUP, or UNLOAD CACHE GROUP statement, the Statement method getUpdateCount() returns the number of cache instances that were flushed, loaded, refreshed, or unloaded.

For related information, see "Determining the number of cache instances affected by an operation" in Oracle TimesTen Application-Tier Database Cache User's Guide.

Handling fatal errors

Fatal errors make the database inaccessible until it can be recovered. When a fatal error occurs, all database connections are required to disconnect. No further operations may complete. Fatal errors are indicated by TimesTen error codes 846 and 994. Error handling for these errors should be different from standard error handling. In particular, the code should roll back the current transaction and, to avoid out-of-memory conditions in the server, disconnect from the database. Shared memory from the old TimesTen instance is not freed until all connections that were active at the time of the error have disconnected. Inactive applications still connected to the old TimesTen instance may have to be manually terminated.

When fatal errors occur, TimesTen performs the full cleanup and recovery procedure:

• Every connection to the database is invalidated, a new memory segment is allocated and applications are required to disconnect.

• The database is recovered from the checkpoint and transaction log files upon the first subsequent initial connection.

  • The recovered database reflects the state of all durably committed transactions and possibly some transactions that were committed non-durably.

  • No uncommitted or rolled back transactions are reflected.

Handling non-fatal errors

Non-fatal errors include simple errors such as an INSERT statement that violates unique constraints. This category also includes some classes of application and process failures.

TimesTen returns non-fatal errors through the normal error-handling process. Application should check for errors and appropriately handle them.

When a database is affected by a non-fatal error, an error may be returned and the application should take appropriate action.

An application can handle non-fatal errors by modifying its actions or, in some cases, by rolling back one or more offending transactions, as described in "Rolling back failed transactions".

Also see "Reporting errors and warnings", which follows shortly.

About warnings

TimesTen returns warnings when something unexpected occurs. Here are some examples of events that cause TimesTen to issue a warning:

• A checkpoint failure

• Use of a deprecated TimesTen feature

• Truncation of some data

• Execution of a recovery process upon connect

• Replication return receipt timeout

You should always have code that checks for warnings, as they can indicate application problems.

Also see "Reporting errors and warnings" immediately below.

Abnormal termination

In some cases, such as with a process failure, an error cannot be returned, so TimesTen automatically rolls back the transactions of the failed process.

General Client Failover Features

TimesTen JDBC support for automatic client failover provides two mechanisms for detecting a failover:

• Synchronous detection, through a SQL exception: After an automatic client failover, JDBC objects created on the failed connection—such as statements, prepared statements, callable statements, and result sets—can no longer be used. A Java SQL exception is thrown if an application attempts to access any such object. By examining the SQL state and error code of the exception, you can determine whether the exception is the result of a failover situation.

• Asynchronous detection, through an event listener: An application can register a user-defined client failover event listener, which is notified of each event that occurs during the process of a failover.

TimesTen JDBC provides the following features, in package com.timesten.jdbc, to support automatic client failover.

• ClientFailoverEvent class

  This class is used to represent events that occur during a client failover: begin, end, abort, or retry.

• ClientFailoverEventListener interface

  An application interested in client failover events must have a class that implements this interface, which is the mechanism to listen for client failover events. At runtime, the application must register ClientFailoverEventListener instances through the TimesTen connection (see immediately below).

  You can use a listener to proactively react to failure detection, such as by refreshing connection pool statement caches, for example.

• Methods in the TimesTenConnection interface

  This interface specifies the methods addConnectionEventListener() and removeConnectionEventListener() to register or remove, respectively, a client failover event listener.

• A constant, TT_ERR_FAILOVERINVALIDATION, in the TimesTenVendorCode interface

  This enables you to identify an event as a failover event.

Client failover features for pooled connections

TimesTen recommends that applications using pooled connections (javax.sql.PooledConnection) or connection pool data sources (javax.sql.ConnectionPoolDataSource) use the synchronous mechanism noted previously to handle stale objects on the failed connection. Java EE application servers manage pooled connections, so applications are not able to listen for events on pooled connections. And application servers do not implement and register an instance of ClientFailoverEventListener, because this is a TimesTen extension.

Implement a client failover event listener

TimesTen JDBC provides the com.timesten.jdbc.ClientFailoverEventListener interface for use in listening for events, highlighted by the following method:

• void notify(ClientFailoverEvent event)

To use asynchronous failover detection, you must create a class that implements this interface, then register an instance of the class at runtime on the TimesTen connection (discussed shortly).

When a failover event occurs, TimesTen calls the notify() method of the listener instance you registered, providing a ClientFailoverEvent instance that you can then examine for information about the event.

The following example shows the basic form of a ClientFailoverEventListener implementation.

   private class MyCFListener implements ClientFailoverEventListener {
      /* Applications can build state system to track states during failover.
         You may want to add methods that talks about readiness of this Connection
         for processing. 
      */
      public void notify(ClientFailoverEvent event) {
         
         /* Process connection failover type */
         switch(event.getTheFailoverType()) {
         case TT_FO_CONNECTION:
            /* Process session fail over */
            System.out.println("This should be a connection failover type " +
                                event.getTheFailoverType());
            break;
            
         default:
            break;
         }
         /* Process connection failover events */
         switch(event.getTheFailoverEvent()) {
         case BEGIN:
            System.out.println("This should be a BEGIN event " +
                                event.getTheFailoverEvent());
            /* Applications cannot use Statement, PreparedStatement, ResultSet,
               etc. created on the failed Connection any longer.
            */
            break;
            
         case END:
            System.out.println("This should be an END event " +
                                event.getTheFailoverEvent());
            
            /* Applications may want to re-create Statement and PreparedStatement
               objects at this point as needed.
            */
            break;
         
         case ABORT:
            System.out.println("This should be an ABORT event " +
                                event.getTheFailoverEvent());
            break;
            
         case ERROR:
            System.out.println("This should be an ERROR event " +
                                event.getTheFailoverEvent());
            break;
            
         default:
            break;
         }
      }
   }

The event.getTheFailoverType() call returns an instance of the nested class ClientFailoverEvent.FailoverType, which is an enumeration type. In TimesTen, the only supported value is TT_FO_CONNECTION, indicating a connection failover.

The event.getTheFailoverEvent() call returns an instance of the nested class ClientFailoverEvent.FailoverEvent, which is an enumeration type where the value can be one of the following:

• BEGIN, if the client failover has begun

• END, if the client failover has completed successfully

• ERROR, if the client failover failed but will be retried

• ABORT, if the client failover has aborted

Register the client failover listener instance

At runtime you must register an instance of your failover event listener class with the TimesTen connection object, so that TimesTen can call the notify() method of the listener class as needed for failover events.

TimesTenConnection provides the following method for this.

• void addConnectionEventListener
(ClientFailoverEventListener listener)

Create an instance of your listener class, then register it using this method. The following example establishes the connection and registers the listener. Assume theDsn is the JDBC URL for a TimesTen Client/Server database and theCFListener is an instance of your failover event listener class.

      try {
         /* Assume this is a client/server conn; register for conn failover. */
         Class.forName("com.timesten.jdbc.TimesTenClientDriver");
         String url = "jdbc:timesten:client:" + theDsn;
         theConnection = (TimesTenConnection)DriverManager.getConnection(url);
         theConnection.addConnectionEventListener(theCFListener);
         /* Additional logic goes here; connection failover listener is
            called if there is a fail over.
         */
      }
      catch (ClassNotFoundException cnfex) {
         cnfex.printStackTrace();
      }
      catch (SQLException sqlex) {
         sqlex.printStackTrace();
      }

Remove the client failover listener instance

The TimesTenConnection interface defines the following method to deregister a failover event listener:

• void removeConnectionEventListener
(ClientFailoverEventListener listener)

Use this method to deregister a listener instance.

Application steps for failover

If you receive any of the error conditions noted at the beginning of automatic client failover discussion in "JDBC support for automatic client failover" in response to an operation in your application, then application failover is in progress. Perform these recovery actions:

1. Roll back all transactions on the connection.

2. Clean up all objects from the previous connection. None of the state or objects associated with the previous connection are preserved.

3. Assuming TTC_NoReconnectOnFailover=0 (the default), sleep briefly, as discussed in the next section, "Failover delay and retry settings". If TTC_NoReconnectOnFailover=1, then you must instead manually reconnect the application to an alternate database or database element.

4. Recreate and reprepare all objects related to your connection.

5. Restart any in-progress transactions from the beginning.

Failover delay and retry settings

The reconnection to another database or database element during automatic client failover may take some time. If your application attempts recovery actions before TimesTen has completed its client failover process, you may receive another failover error condition as listed at the beginning of automatic client failover discussion in "JDBC support for automatic client failover".

Therefore, your application should place all recovery actions within a loop with a short delay before each subsequent attempt, where the total number of attempts is limited. If you do not limit the number of attempts, the application may appear to hang if the client failover process does not complete successfully. For example, your recovery loop could use a retry delay of 100 milliseconds with a maximum number of retries limited to 100 attempts. The ideal values depend on your particular application and configuration.

Example 2-22 illustrates this point.

// Database connection object
Connection        dbConn;
 
// Open the connection  to the database
...
 
// Disable auto-commit
dbConn.setAutoCommit( false ); 
 
...
 
// Prepre the SQL statements
PreparedStatement stmtQuery = dbConn.prepare("SELECT ...");
PreparedStatement stmtUpdate = dbConn.prepare("UPDATE ...");
 
...
 
// Set max retries to 100
int retriesLeft = 100;
// and retry delay to 100 ms
int retryDelay = 100;
 
// Records outcome
boolean success = false;
Boolean needReprepare = false;
 
// Execute transaction with retries until success or retries exhausted
while (  retriesLeft > 0  )
{
    try {
 
        // Do we need to re-prepare
        if ( needReprepare )
        {
            Thread.sleep( retryDelay ); // delay before proceeding
            stmtQuery = dbConn.prepare("SELECT ...");
            stmtUpdate = dbConn.prepare("UPDATE ...");
            needReprepare = false;
        }
 
        // First execute the query
 
        // Set input values
        stmtQuery.setInt(1, ...);
        stmtQuery.setString(2, ...);
 
        // Execute and process results
        ResultSet rs = stmtQuery.executeQuery();
        while (  rs.next()  )
        {
            int val1 = rs.getInt(1);
            String val2 = rs.getString(2);
            ...
        }
        rs.close();
        rs = null;
          
        // Now execute the update
 
        // Set input values
        stmtUpdate.setInt(1,...);
        stmtUpdate.setString(2,...);
 
        // Execute and check number of rows affected
        int updCount = stmtUpdate.executeUpdate();
        if (  updCount < 1  )
        {
            ...
        }
 
        // And finally commit
        dbConn.commit();
 
        // We are done
        success = true;
        break;
 
    } catch ( SQLException sqe ) {
 
       if (  (sqe.getErrorCode() == 47137) ||
             ( (sqe.getErrorCode() == 30105) && (sqe.getSQLState().equals("08006")) )  )
        // connection failover error
        {
            // decrement retry count
            retriesLeft--;
            // rollback the transaction ready for retry
            dbConn.rollback();
            // and indicate that we need to re-prepare
            needReprepare = true;
        }
        else
        {
            // handle other kinds of error
            ...
        }
 
    }
 
} // end of retry loop if (  ! success  ){    // Handle the failure    ...}