2 Working with TimesTen Data Stores

Package imports

You must import the standard JDBC package in any Java program that use JDBC:

import java.sql.*;

If you are going to use data sources or pooled connections, you must also import the standard extended JDBC package:

import javax.sql.*;

You must import the TimesTen JDBC package:

import com.timesten.jdbc.*;

To use XA data sources for JTA, you must also import the following TimesTen package:

import com.timesten.jdbc.xa.*;

Support for interfaces in the java.sql package

TimesTen supports the java.sql interfaces shown in Table 2-1.

Support for classes in the java.sql package

TimesTen supports the following java.sql classes:

• Date

• DriverManager

• DriverPropertyInfo

• Time

• Timestamp

• Types

• DataTruncation

• SQLException

• SQLWarning

Support for interfaces in the javax.sql package

TimesTen supports the following javax.sql interfaces:

• DataSource is implemented by TimesTenDataSource.

• PooledConnection is implemented by ObservableConnection.

• ConnectionPoolDataSource is implemented by ObservableConnectionDS.

• XADataSource is implemented by TimesTenXADataSource (in package com.timesten.jdbc.xa).

TimesTen JDBC extensions

For most scenarios, you can use standard JDBC functionality as supported by TimesTen.

TimesTen also provides the following extensions in the com.timesten.jdbc package for TimesTen-specific features.

Additional TimesTen classes and interfaces

In addition to implementations discussed previously, TimesTen provides the following classes and interfaces in the com.timesten.jdbc package. Features supported by these classes and interfaces are discussed later in this chapter.

Additional TimesTen Interfaces

• Use TimesTenTypes for TimesTen type extensions (for REF CURSORs).

• Use ClientFailoverEventListener (and also the ClientFailoverEvent class below) for automatic client failover features. See "JDBC support for automatic client failover".

• Use TimesTenVendorCode for vendor codes used in SQL exceptions.

Additional TimesTen Classes

• Use ClientFailoverEvent (and also the ClientFailoverEventListener interface above) for automatic client failover features.

Load the TimesTen driver

The TimesTen JDBC driver must be loaded before it is available for making connections with a TimesTen data store. The TimesTen JDBC driver is:

com.timesten.jdbc.TimesTenDriver

If you are using the DriverManager interface to connect to TimesTen, call the Class.forName() method to load the TimesTen JDBC driver. This method creates an instance of the TimesTen driver and registers it with the driver manager. If you are using the TimesTenDataSource interface, you are not required to call Class.forName().

To identify and load the TimesTen driver:

Class.forName("com.timesten.jdbc.TimesTenDriver");

Create a connection URL for the data store and specify connection attributes

To create a JDBC connection, you must specify a TimesTen connection URL for the data store. The format of a TimesTen connection URL is as follows:

jdbc:timesten:{direct|client}:dsn=DSNname;[DSNattributes;]

The default is direct.

For example, the following creates a direct connection to the sample data store:

String URL = "jdbc:timesten:direct:dsn=sampledb_1121";

You can programmatically set or override the connection attributes in the DSN description by specifying attributes in the connection URL.

Refer to "Connection attributes for Data Manager DSNs" in Oracle TimesTen In-Memory Database Operations Guide for introductory information about connection attributes. General connection attributes require no special privilege. First connection attributes are set when the database is first loaded, and persist for all connections. Only the instance administrator can load a database with changes to first connection attribute settings. Refer to "Data Store Attributes" in Oracle TimesTen In-Memory Database Reference for specific information about any particular connection attribute, including required privilege.

For example, to set the LockLevel DSN general connection attribute to 1, create a URL as follows:

String URL = "jdbc:timesten:direct:dsn=sampledb_1121;LockLevel=1";

Connect to the data store

After you have defined a URL, you can use the getConnection() method of either DriverManager or TimesTenDataSource to connect to the TimesTen data store.

If you use the DriverManager.getConnection() method, specify the driver URL to connect to the data store.

import java.sql.*;
...
Connection conn = DriverManager.getConnection(URL);

To use the TimesTenDataSource method getConnection(), first create a data source. Then use the TimesTenDataSource method setUrl() to set the URL and getConnection() to connect:

import com.timesten.jdbc.TimesTenDataSource;
import java.sql.*;
...
TimesTenDataSource ds = new TimesTenDataSource();
ds.setUrl(URL);
Connection conn = ds.getConnection();

Either method returns a Connection object (conn in this example) that you can use as a handle to the data store. See the level1 demo for an example on how to use the DriverManager method getConnection(), and the level2 and level3 demos for examples of using the TimesTenDataSource method getConnection(). Refer to "About the TimesTen Java demos".

Disconnect from the data store

When you are finished accessing the TimesTen data store, call the Connection method close() to close the connection to the data store.

If an error has occurred, you may want to roll back the transaction before disconnecting from the data store. See "Handling non-fatal errors" and "Rolling back failed transactions" for more information.

Opening and closing a direct driver connection

Example 2-1 shows the general framework for an application that uses the DriverManager class to create a direct driver connection to the sample data store; execute some SQL, and then close the connection. See the level1 demo for a working example. (See "About the TimesTen Java demos" regarding the demos.)

String URL = "jdbc:timesten:dsn=sampledb_1121";
Connection conn = null;

try {
     Class.forName("com.timesten.jdbc.TimesTenDriver");
} catch (ClassNotFoundException ex) {
      // See "Handling errors"
}

try {
    // Open a connection to TimesTen
    conn = DriverManager.getConnection(URL);

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

    // Do SQL operations
    // See "Managing TimesTen data"

// Close the connection to TimesTen
   conn.close();

// Handle any errors
} catch (SQLException ex) {
    // See "Handling errors"
}

Access control for connections

Privilege to connect to a TimesTen data store must be explicitly granted to every user other than the instance administrator, through the CREATE SESSION privilege. This is a system privilege so must be granted by an administrator to the user, either directly or through the PUBLIC role. Refer to "Managing Access Control" in Oracle TimesTen In-Memory Database Operations Guide for additional information and examples.

Executing simple SQL statements

"Working with Data in a TimesTen Data Store" in Oracle TimesTen In-Memory Database Operations Guide describes how to use SQL to manage data in a TimesTen data store. This section describes how to use the createStatement() method of a Connection instance, and the executeUpdate() or executeQuery() method of a Statement instance, to execute a SQL statement within a Java application.

Unless statements are prepared in advance, use the execution methods of a Statement object, such as execute(), executeUpdate() or executeQuery(), depending on the nature of the SQL statement and any returned result set.

For SQL statements that are prepared in advance, use the same execution methods of a PreparedStatement object.

The execute() method returns true if there is a result set (for example, on a SELECT) or false if there is no result set (for example, on an INSERT, UPDATE, or DELETE). The executeUpdate() method returns the number of rows affected. For example, when executing an INSERT statement, the executeUpdate() method returns the number of rows inserted. The executeQuery() method returns a result set, so it should only be called when a result set is expected (for example, when executing a SELECT).

Connection conn;
Statement stmt;
...
// [Code to open connection. See "Connect to the data store"...] 
...
try {
    stmt = conn.createStatement();
    int numRows = stmt.executeUpdate("insert into customer values" 
                  + "(40, 'West', 'Big Dish', '123 Signal St.')");
}
catch (SQLException ex) {
    ...
}

Statement stmt;
. . . . . .
try {
  ResultSet rs = stmt.executeQuery("select cust_num, region, " +
                      "name, address from customer");
  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));
    }
  } 
catch (SQLException ex) {
  ex.printStackTrace();
}

Working with TimesTen result sets: hints and restrictions

You use ResultSet objects to process query results. In addition, some methods and built-in procedures return TimesTen data in the form of a ResultSet object. This section describes what you should know when using ResultSet objects from TimesTen.

• TimesTen does not support multiple open ResultSet objects per statement. TimesTen cannot return multiple ResultSet objects from a single Statement object without first closing the current result set.

• TimesTen does not support holdable cursors. You cannot specify the holdability of a result set, essentially whether a cursor can remain open after it has been committed.

• ResultSet objects are not scrollable or updatable, so you cannot specify ResultSet.TYPE_SCROLL_SENSITIVE or ResultSet.CONCUR_UPDATABLE.

• Use the ResultSet method close() to close a result set as soon as you are done with it.

• Calling the ResultSet method getString() is more costly with regard to performance if the underlying data type is not a string. Because Java strings are immutable, getString() must allocate space for a new string each time it is called. This makes getString() one of the more costly calls in JDBC. Do not use getString() to retrieve primitive numeric types, like byte or int, unless it is absolutely necessary. For example, it is much faster to call getInt() on an integer column.

  In addition, it is generally true for dates and timestamps that ResultSet native methods getDate() and getTimestamp() will have better performance than getString().

• Application performance is influenced by the choice of getXXX() calls as well as by any required data transformations after invocation.

• JDBC ignores the setting for the ConnectionCharacterSet attribute. It returns data in UTF-16 encoding.

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.

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 data store each prepare the same statement, then the second, third, ... , nth prepare returns very quickly because TimesTen remembers the first prepared statement.

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

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

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

Binding duplicate parameters

This section discusses considerations in binding duplicate parameters in either SQL statements or PL/SQL.

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

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

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

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

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

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

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

Putting it all together: preparing and executing SQL statements

This example prepares INSERT and SELECT statements, executes the INSERT twice, executes the SELECT, and prints the returned result set. The connection must also be opened, which is not shown here but is shown in "Connect to the data store". For a working example, see the level1 demo. (Refer to "About the TimesTen Java demos" regarding the demos.)

Connection conn;
...
// [Code to open connection. See "Connect to the data store"...]
...

// 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. 
// See "Reporting errors and warnings".
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're done with it
pSel.close();

For additional information about using result sets, see "Working with TimesTen result sets: hints and restrictions".

Calling TimesTen built-in procedures

"Built-In Procedures" in Oracle TimesTen In-Memory Database Reference describes the TimesTen built-in procedures that extend standard ODBC functionality, and any privileges they require. You can execute a TimesTen built-in procedure through functionality of the CallableStatement interface.

Execute a built-in procedure as follows:

CallableStatement.execute("{ Call procedure }")

To prepare and execute a built-in procedure, use the format:

CallableStatement cStmt;
cStmt = conn.prepareCall("{ Call procedure }");
cStmt.execute();

For built-in procedures that return results, you can use the getXXX() methods of the ResultSet interface to retrieve the data, as demonstrated in Example 2-9.

Connection conn;
CallableStatement cStmt;
.......
cStmt = conn.prepareCall("{ Call ttCkpt }");
cStmt.execute();
conn.commit();           // commit the transaction

ResultSet rs;

CallableStatement cStmt = conn.prepareCall("{ Call ttDataStoreStatus }");

if (cStmt.execute() == true) {
    rs = cStmt.getResultSet();
    System.out.println("Fetching result set...");
    while (rs.next()) {
      System.out.println("\n Data store: " + rs.getString(1));
      System.out.println(" PID: " + rs.getInt(2));
      System.out.println(" Context: " + rs.getString(3));
      System.out.println(" ConType: " + rs.getString(4));
      System.out.println(" memoryID: " + rs.getString(5));
      }
    rs.close();
  }
cStmt.close();

Working with OUT and IN OUT parameters

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

1. Use the method registerOutParameter() to register an OUT or IN OUT 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 types, TimesTen will allocate memory to hold the largest possible value. In many cases this is wasteful.

   Instead, you can use the TimesTen extended interface TimesTenCallableStatement, which includes a registerOutParameter() signature that allows you to specify the maximum data length in 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 IN OUT parameter. Specify the parameter position and data value.

3. Use the appropriate CallableStatement method getXXX() to get the output value of an OUT or IN OUT 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 RAISESAL (raise salary)
CallableStatement cstmt = conn.prepareCall
                          ("BEGIN :newSalary := RAISESAL(: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, values of OUT parameters 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
cstmt.close();
      
conn.close();
...

Working with DML returning (RETURNING INTO clause)

You can use a RETURNING INTO clause, referred to as DML returning, with an INSERT, UPDATE, or DELETE statement to return specified items from a row that was affected by the action. This eliminates the need for a subsequent SELECT statement and separate round trip, in case, for example, you want to confirm what was affected by the action.

With TimesTen, DML returning is limited to returning items from a single-row operation. The clause returns the items into a list of OUT parameters.

TimesTenPreparedStatement, an extension of the standard PreparedStatement interface, supports DML returning.

Use the TimesTenPreparedStatement method registerReturnParameter() to register the return parameters. As with the registerOutParameter() method discussed in "Working with OUT and IN OUT parameters" above, this method includes a signature that allows you to optionally specify a maximum size of the data, in bytes. For CHAR, VARCHAR, NCHAR, NVARCHAR, BINARY, or VARBINARY data types, this avoids possible inefficiency where TimesTen would otherwise allocate memory to hold the largest possible value.

void registerReturnParameter(int paramIndex, int sqlType, int maxSize)

Use the TimesTenPreparedStatement method getReturnResultSet() to retrieve the return parameters, returning a ResultSet instance.

Be aware of the following restrictions.

• The getReturnResultSet() method must not be invoked more than once. Otherwise, the behavior is indeterminate.

• ResultSetMetaData is not supported for the result set returned by getReturnResultSet().

• Streaming methods such as getCharacterStream() are not supported for the result set returned by getReturnResultSet().

• There is no batch support for DML returning.

SQL syntax and restrictions for the RETURNING INTO clause in TimesTen are documented as part of the "INSERT", "UPDATE", and "DELETE" documentation in Oracle TimesTen In-Memory Database SQL Reference.

Refer to "RETURNING INTO Clause" in Oracle Database PL/SQL Language Reference for general information about DML returning.

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Types;
import com.timesten.jdbc.TimesTenPreparedStatement;
 
      Connection conn;
 
      ...
 
      // Insert into a table and return results
      TimesTenPreparedStatement pstmt =
        (TimesTenPreparedStatement)conn.prepareStatement
        ("insert into tab1 values(?,?) returning name, age into ?,?");
 
      // Populate table
      pstmt.setString(1,"John Doe");
      pstmt.setInt(2, 65);
 
      /** register returned parameter
       * in this case the maximum size of name is 100 chars
       */
      pstmt.registerReturnParameter(3, Types.VARCHAR, 100);
      pstmt.registerReturnParameter(4, Types.INTEGER);
 
      // process the DML returning statement
      int count = pstmt.executeUpdate();
 
      /* Check warnings; if there are warnings, values of DML RETURNING INTO
         parameters are undefined. */
      SQLWarning wn;
      boolean warningFlag = false;
      if ((wn = pstmt.getWarnings() ) != null) {
        do {
          warningFlag = true;
          System.out.println(wn);
          wn = wn.getNextWarning();
        } while(wn != null);
      }
 
      if (!warningFlag) {
        if (count>0)
        {
          ResultSet rset = pstmt.getReturnResultSet(); //rset not null, not empty
          while(rset.next())
          {
            String name = rset.getString(1);
            int age = rset.getInt(2);
            System.out.println("Name " + name + " age " + age);
          }
        }
      }

Working with PL/SQL REF CURSORs

REF CURSOR is a PL/SQL concept, where a REF CURSOR is a handle to a cursor over a SQL result set and can be passed between PL/SQL and an application. In TimesTen, the cursor can be opened in PL/SQL, then the REF CURSOR can be passed to the application for processing of the result set.

An application can receive a REF CURSOR, as an OUT parameter, as follows:

1. Register the REF CURSOR OUT parameter as type TimesTenTypes.CURSOR (a TimesTen type extension), also specifying the parameter position of the REF CURSOR (position in the statement).

2. Retrieve the REF CURSOR using the getCursor() method defined by the TimesTenCallableStatement interface (a TimesTen JDBC extension), specifying the parameter position of the REF CURSOR. The getCursor() method is used like other getXXX() methods and returns a ResultSet instance.

The following example demonstrates this usage.

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import com.timesten.jdbc.TimesTenCallableStatement;
import com.timesten.jdbc.TimesTenTypes;
...
Connection conn;
CallableStatement cstmt;
ResultSet cursor;
...
// Use a PL/SQL block to open the cursor.
cstmt = conn.prepareCall
             (" begin open :x for select tblname,tblowner from tables; end;");
cstmt.registerOutParameter(1, TimesTenTypes.CURSOR);
cstmt.execute();
cursor = ((TimesTenCallableStatement)cstmt).getCursor(1);

// Use the cursor as you would any other ResultSet object.
while(cursor.next()){
  System.out.println(cursor.getString(1));
}

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

Working with rowids

Each row in a TimesTen database table has a unique identifier known as its rowid. An application can retrieve the rowid of a row from the ROWID pseudocolumn. A rowid value can be represented in either binary or character format, with the binary format taking 12 bytes and the character format 18 bytes.

You can use any of the following ResultSet methods to retrieve a rowid:

• byte[] getBytes(int columnIndex)

• String getString(int columnIndex)

• Object getObject(int columnIndex) (returning a String object)

You can use any of the following PreparedStatement methods to set a rowid:

• setBytes(int parameterIndex, byte[] x)

• setString(int parameterIndex, String x)

• setObject(int parameterIndex, Object x) (taking a String object)

An application can specify literal rowid values in SQL statements, such as in WHERE clauses, as CHAR constants enclosed in single quotes.

Refer to "ROWID data type" and "ROWID specification" in Oracle TimesTen In-Memory Database SQL Reference for additional information about rowids and the ROWID data type, including usage and lifecycle.

Fetching multiple rows of data

Fetching multiple rows of data from a TimesTen data store can increase the performance of an application that connects to a data store set with read committed isolation.

You can specify the number of rows to be prefetched as follows:

• Call the Statement or ResultSet method setFetchSize(). These are the standard JDBC calls, but the limitation is that they only affect one statement at a time.

• Call the TimesTenConnection method setTtPrefetchCount(). This enables a TimesTen extension that establishes prefetch on a connection level so that all of the statements on the connection use the same prefetch setting.

This section describes the connection-level prefetch implemented in TimesTen.

When the prefetch count is set to 0, TimesTen uses a default value, depending on the Isolation level you have set for the data store. In read committed isolation mode, the default prefetch value is 5. In serializable isolation mode, the default is 128. The default prefetch value is the optimum setting for most applications. Generally, a higher value may result in better performance for larger result sets, at the expense of slightly higher resource use.

To disable prefetch, set the prefetch count to 1.

Call the TimesTenConnection method getTtPrefetchCount() to check the current prefetch value.

TimesTenConnection conn =
    (TimesTenConnection) DriverManager.getConnection(url);

// set prefech count to 10 for this connection
conn.setTtPrefetchCount(10);

// Return the prefetch count to the 'count' variable.
int count = conn.getTtPrefetchCount();

Setting a timeout or threshold for executing SQL statements

TimesTen offers two ways to limit the time for SQL statements to execute, applying to any execute(), executeBatch(), executeQuery(), executeUpdate(), or next() call.

• Setting a timeout value for SQL statements

• Setting a threshold value for SQL statements

The former is to set a timeout, where if the timeout duration is reached, the statement stops executing and an error is thrown. The latter is to set a threshold, where if the threshold is reached, an SNMP trap is thrown but execution continues.

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

Escape syntax in SQL functions

When using SQL in JDBC, pay special attention to Java escape syntax. SQL functions such as UNISTR use the backslash (\) character. You should escape the backslash character. For example, using the following SQL syntax in a Java application may not produce the intended results:

INSERT INTO table1 SELECT UNISTR('\00E4') FROM dual;

Escape the backslash character as follows:

INSERT INTO table1 SELECT UNISTR('\\00E4') FROM dual;

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 In-Memory Database Cache User's Guide.

Committing changes

This section discusses autocommit and manual commits or rollbacks, assuming a JDBC Connection object myconn.

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

myconn.commit();

myconn.rollback();

About fatal errors, non-fatal errors, and warnings

TimesTen can return a fatal error, a non-fatal error, or a warning.

Reporting errors and warnings

You should check for and report all errors and warnings that can be returned on every call. This saves considerable time and effort during development and debugging. A SQLException object is generated in case of one or more data store access errors and a SQLWarning object is generated in the case of one or more warning messages. A single call may return multiple errors or warnings or both, so your application should report all errors or warnings in the returned SQLException or SQLWarning objects.

Multiple errors or warnings are returned in linked chains of SQLException or SQLWarning objects. Example 2-14 and Example 2-15 demonstrate how you might iterate through the lists of returned SQLException and SQLWarning objects to report all of the errors and warnings, respectively.

static int reportSQLExceptions(SQLException ex)
  {
    int errCount = 0;

    if (ex != null) {
      errStream.println("\n--- SQLException caught ---");
      ex.printStackTrace();

      while (ex != null) {
        errStream.println("SQL State: " + ex.getSQLState());
        errStream.println("Message: " + ex.getMessage());
        errStream.println("Error Code: " + ex.getErrorCode());
        errCount ++;
        ex = ex.getNextException();
        errStream.println();
      }
    }

    return errCount;
}

static int reportSQLWarnings(SQLWarning wn)
{
    int warnCount = 0;

    while (wn != null) {
      errStream.println("\n--- SQL Warning ---");
      errStream.println("SQL State: " + wn.getSQLState());
      errStream.println("Message: " + wn.getMessage());
      errStream.println("Error Code: " + wn.getErrorCode());

      // is this a SQLWarning object or a DataTruncation object?
      if (wn instanceof DataTruncation) {
        DataTruncation trn = (DataTruncation) wn;
        errStream.println("Truncation error in column: " +
          trn.getIndex());
      }

      warnCount++;
      wn = wn.getNextWarning();
      errStream.println();
    }

    return warnCount;
}

Catching and responding to specific errors

In some situations it may be desirable to respond to a specific SQL state or TimesTen error code. You can use the SQLException method getSQLState() to return the SQL99 SQL state error string, and getErrorCode() to return TimesTen error codes, as shown in Example 2-16.

Also refer to the entry for TimesTenVendorCode in Oracle TimesTen In-Memory Database JDBC Extensions Java API Reference for error information.

catch (SQLException ex) {
  if (ex.getSQLState().equalsIgnoreCase("S0002")) {
    errStream.println("\nError: The table appuser.customer " +
      "does not exist.\n\t Please reinitialize the database.");
  } else if (ex.getErrorCode() == 907) {
    errStream.println("\nError: Attempting to insert a row " +
      "with a duplicate primary key.\n\tPlease reinitialize the database.");
}

ex.getErrorCode() == com.timesten.jdbc.TimesTenVendorCode.TT_ERR_KEYEXISTS

ex.getErrorCode() == 907

Rolling back failed transactions

In some situations, such as recovering from a deadlock or time-out condition, you may want to explicitly roll back the transaction using the Connection.rollback() method, as in the following example.

try {
  if (conn != null && !conn.isClosed()) {
    // Rollback any transactions in case of errors
      if (retcode != 0) {
        try {
          System.out.println("\nEncountered error. Rolling back transaction");
          conn.rollback();
        } catch (SQLException ex) {
          reportSQLExceptions(ex);
        }
      }
   }

    System.out.println("\nClosing the connection\n");
    conn.close();
}

The XACT_ROLLBACKS column of the SYS.MONITOR table indicates the number of transactions that were rolled back.

A transaction rollback consumes resources and the entire transaction is in effect wasted. To avoid unnecessary rollbacks, design your application to avoid contention and check application or input data for potential errors before submitting it, whenever possible.

Features and functionality of JDBC support for automatic client failover

This section discusses general TimesTen JDBC features related to client failover, as well as functionality relating specifically to pooled connections.

Synchronous detection of automatic client failover

If, in a failover situation, an application attempts to use objects created on the failed connection, then JDBC will throw a SQL exception. The vendor-specific exception code will be set to TimesTenVendorCode.TT_ERR_FAILOVERINVALIDATION.

Detecting a failover through this mechanism is referred to as synchronous detection. The following example demonstrates this.

try {

   // ...
   // Execute a query on a previously prepared statement.
   ResultSet theResultSet = theStatement.executeQuery("select * from dual");
   // ...

} catch (SQLException sqlex) {
   sqlex.printStackTrace();
   if (sqlex.getErrorCode() == TimesTenVendorCode.TT_ERR_FAILOVERINVALIDATION) {
   // Automatic client failover has taken place; discontinue use of this object.
   }
}

Asynchronous detection of automatic client failover

Asynchronous failover detection requires an application to implement a client failover event listener and register an instance of it on the TimesTen connection. This section describes the steps involved:

1. Implement a client failover event listener

2. Register the client failover listener instance

3. Remove the client failover listener instance

private class MyCFListener implements ClientFailoverEventListener {
      // Skeletal example
      /* 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;
         }
      }      
   }

try {
         
         // Assume this is a client/server connection; 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 will be
            called if there is a fail over.
         */
         // ...
      }
      catch (ClassNotFoundException cnfex) {
         cnfex.printStackTrace();
      }
      catch (SQLException sqlex) {
         sqlex.printStackTrace();
      }