TimesTen Features and Operations in Your Application

Using Java Wrapper Functionality for Statements

TimesTen exposes TimesTen-specific implementations through standard java.sql.Wrapper functionality.

You can use Wrapper to retrieve statement objects that implement the TimesTenStatement, TimesTenPreparedStatement, and TimesTenCallableStatement interfaces and provide access to TimesTen-specific features. See Management of TimesTen Database Connections for similar discussion and an example regarding connection objects.

Working with TimesTen Result Sets: Hints and Restrictions

This section points out what you should know when using ResultSet objects from TimesTen.

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.

Note:

In TimesTen, any operation that ends your transaction, such as a commit or rollback, closes all cursors associated with the connection.

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.
Typically, use the ResultSet method close() to close a result set as soon as you are done with it. For performance reasons, this is especially important for result sets used for both read and update operations and for result sets used in pooled connections.

TimesTen result sets also support standard try-with-resource functionality using java.lang.AutoCloseable.
Calling the ResultSet method getString() is more costly in terms of 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. 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. Also see Use the ResultSet Method getString() Sparingly.

In addition, for dates and timestamps, the ResultSet native methods getDate() and getTimestamp() have better performance than getString().
Application performance is affected by the choice of getXXX() calls and by any required data transformations after invocation.
JDBC ignores the setting for the ConnectionCharacterSet attribute. It returns data in UTF-16 encoding.

Fetching Multiple Rows of Data

Fetching multiple rows of data can increase the performance of a client/server application that connects to a database set with Read Committed isolation level.

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

You can specify the number of rows to be prefetched by calling the TimesTenConnection method setTtPrefetchCount(). This enables a TimesTen extension that establishes prefetch at the connection level so that all of the statements on the connection use the same prefetch setting..

Note:

The TimesTen prefetch count extension provides no benefit for an application using a direct connection to the database.

When you set the prefetch count to 0, TimesTen uses a default prefetch count according to the isolation level you have set for the database, and sets the prefetch count to that value. With Read Committed isolation level, the default prefetch value is 5. With Serializable isolation level, the default is 128. The default prefetch value is a good 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.

See the Connection interface entry in Support for Interfaces in the java.sql Package. Refer to Oracle TimesTen In-Memory Database JDBC Extensions Java API Reference.

The following example uses a setTtPrefetchCount() call to set the prefetch count to 10, then uses a getTtPrefetchCount() call to return the prefetch count in the count variable.

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

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

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

Optimizing Query Performance

A TimesTen extension enables applications to optimize read-only query performance in client/server applications by calling the TimesTenConnection method setTtPrefetchClose() with a setting of true.

All transactions should be committed when executed, including read-only transactions. With a setTtPrefetchClose(true) call, the server automatically closes the cursor and commits the transaction after the server has prefetched all rows of the result set for a read-only query. This enhances performance by reducing the number of network round-trips between client and server.

The client should still close the result set and commit the transaction, but those calls are executed in the client and do not require a network round trip between the client and server.

Note:

Do not use multiple statement handles for the same connection with a setTtPrefetchClose(true) call. The server may fetch all rows from the result set, commit the transaction, and close the statement handle before the client is finished, resulting in the closing of all statement handles.
A true setting is ignored for TimesTen direct connections and for SELECT FOR UPDATE statements.
Use getTtPrefetchClose() to get the current setting (true or false).

The following example shows usage of setTtPrefetchClose(true).

import com.timesten.sql;
...
con = DriverManager.getConnection ("jdbc:timesten:client:" + DSN);
stmt = con.createStatement();
...
con.setTtPrefetchClose(true);
rs = stmt.executeQuery("select * from t");
while(rs.next())
{
// do the processing
}
rs.close();
con.commit();

Parameter Binding and Statement Execution

This discusses how to bind input or output parameters for SQL statements.

The following topics are covered.

Note:

The term "bind parameter" as used in TimesTen developer guides (in keeping with ODBC terminology) is equivalent to the term "bind variable" as used in TimesTen PL/SQL documents (in keeping with Oracle Database PL/SQL terminology).

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.

The following example shows the basics of an executeQuery() call on a PreparedStatement object. It executes a prepared SELECT statement and displays the returned result set.

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();
}

This next example shows how a single parameterized statement can be substituted for four separate statements.

Rather than execute a similar INSERT statement with different values:

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)");

It is more efficient to prepare a single parameterized INSERT statement and use PreparedStatement methods setXXX() to set the row values before each execute.

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.

The following example prepares INSERT and SELECT statements, executes the INSERT twice, executes the SELECT, and prints the returned result set. For a working example, see the level1 sample application. (Refer to About TimesTen Quick Start and Sample Applications regarding the sample applications.)

Connection conn = null;
...
// [Code to open connection. See Connecting 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();

The next example prepares three identical parameterized INSERT statements for three separate connections. The first prepared INSERT for connection conn1 is shared (inside the TimesTen internal prepared statement cache) with the conn2 and conn3 connections, speeding up the prepare operations for pIns2 and pIns3:

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 (?,?)");

Note:

All optimizer hints, such as join ordering, indexes and locks, must match for the statement to be shared in the internal TimesTen prepared statement cache. Also, if the prepared statement references a temp table, it is only shared within a single connection.

Working with Output and Input/Output Parameters

You can prepare a statement and set input parameters using PreparedStatement methods.

See Preparing SQL Statements and Setting Input Parameters. TimesTen also supports output and input/output parameters, for which you use java.sql.CallableStatement instead of PreparedStatement, as follows.

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)
```
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.
Use the appropriate CallableStatement method getXXX() to get the output value of an output or input/output parameter, specifying the parameter position.

Note:

Check for SQL warnings before processing output parameters. In the event of a warning, output parameters are undefined. See Error Handling.
You cannot pass parameters to a CallableStatement object by name. You must set parameters by position. You cannot use the SQL escape syntax.
The registerOutParameter() signatures specifying the parameter by name are not supported. You must specify the parameter by position.
SQL structured types are not supported.

This example shows how to use a callable statement with an output parameter. In the TimesTenCallableStatement instance, a PL/SQL block calls a function RAISE_SALARY that calculates a new salary and returns it as an integer. Assume a Connection instance conn. (Refer to Overview of PL/SQL features in the Oracle TimesTen In-Memory Database PL/SQL Developer's Guide.)

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 (who 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.)

Note:

This discussion applies only to SQL statements issued directly from ODBC, not through PL/SQL, for example.
"TimesTen mode" for binding duplicate parameters, and the DuplicateBindMode connection attribute, are deprecated.

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

Working with Associative Arrays

This section shows how to bind and use associative arrays in TimesTen.

Overview of 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 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.

Note:

The following types are not supported in binding associative arrays: LOBs, REF CURSORs, TIMESTAMP, ROWID.
Associative array binding is not allowed in passthrough statements.
General bulk binding of arrays is not supported in TimesTen JDBC. Varrays and nested tables are not supported as bind parameters.
Associative array parameters are not supported with JDBC batch execution. (See Use Arrays of Parameters for Batch Execution.)

TimesTen Methods for Associative Arrays

TimesTen provides extensions 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 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();
```

Note:

The elemMaxLen parameter is ignored for types other than CHAR, VARCHAR, BINARY, or VARBINARY. For any of those types, you can use a value of 0 to instruct the driver to set the maximum length of each element based on the actual length of data that is bound. If elemMaxLen is set to a positive value, then wherever the actual data length is greater than elemMaxLen, the data is truncated to a length of elemMaxLen.
If curLen is smaller than the actual number of elements in the associative array, only curLen elements are bound.

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.
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.

Type Mapping for Associative Arrays

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 certain 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).

Table 2-4 documents JDBC default mappings for associative array elements.

Table 2-4 JDBC Default Mappings for Associative Array Elements

Return type	SQL type
`Integer[]`	`TINYINT`, `SMALLINT`, `TT_INTEGER`
`Long[]`	`BIGINT`
`BigDecimal[]`	`NUMBER`
`Float[]`	`BINARY_FLOAT`
`Double[]`	`BINARY_DOUBLE`
`String[]`	`CHAR`, `VARCHAR`, `NCHAR`, `NVARCHAR`
`Timestamp[]`	`DATE`

Binding Associative Arrays

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();

The next example is more complete, showing the mechanism for binding an associative array.

    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);

Working with REF CURSORs

REF CURSOR is a PL/SQL concept, a handle to a cursor over a SQL result set that 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.

TimesTen supports standard JDBC REF CURSORs as well as TimesTen REF CURSORs.

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

Using the CallableStatement method registerOutParameter(), register the REF CURSOR OUT parameter as type java.sql.Types.REF_CURSOR (for standard REF CURSORs) or as type TimesTenTypes.CURSOR (for TimesTen REF CURSORs). In the registerOutParameter() call, specify the parameter position of the REF CURSOR (position in the statement).
Retrieve the REF CURSOR using the CallableStatement method getObject() (for standard REF CURSORs) or the TimesTenCallableStatement method getCursor() (for TimesTen REF CURSORs), casting the return as ResultSet. Specify the parameter position of the REF CURSOR.

Refer to Oracle TimesTen In-Memory Database JDBC Extensions Java API Reference for additional information about TimesTen JDBC APIs. See PL/SQL REF CURSORs in Oracle TimesTen In-Memory Database PL/SQL Developer's Guide for additional information about REF CURSORs.

Note:

For passing REF CURSORs between PL/SQL and an application, TimesTen supports only OUT REF CURSORs, from PL/SQL to the application.

The following examples show how to use a callable statement with a TimesTen REF CURSOR then a standard REF CURSOR.

TimesTen REF CURSOR:

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import com.timesten.jdbc.TimesTenCallableStatement;
import com.timesten.jdbc.TimesTenTypes;
...
Connection conn = null;
CallableStatement cstmt = null;
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 cursor, statement, and connection.
cursor.close();
cstmt.close();
conn.close();
...

Standard REF CURSOR:

...
Connection conn = null;
CallableStatement cstmt = null;
ResultSet rs;
...
cstmt = conn.prepareCall
             (" begin open :x for select tblname,tblowner from tables; end;");
cstmt.registerOutParameter(1, Types.REF_CURSOR); 
cstmt.execute(); 
rs = cstmt.getObject(1, ResultSet.class);
while(rs.next()){
  System.out.println(rs.getString(1));
}

// Close the result set, statement, and connection.
rs.close();
cstmt.close();
conn.close();
...

Note:

If you are evaluating the callable statement with different parameter values in a loop, close the cursor each time at the end of the loop. The typical use case is to prepare the statement, then, in the loop, set parameters, execute the statement, process the cursor, and close the cursor.

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 output parameters.

TimesTenPreparedStatement, an extension of the standard PreparedStatement interface, supports DML returning. Use the TimesTenPreparedStatement method registerReturnParameter() to register the return parameters.

void registerReturnParameter(int paramIndex, int sqlType)

As with the registerOutParameter() method discussed in Working with Output and Input/Output Parameters, this method has a signature that enables you to optionally specify a maximum size for CHAR, VARCHAR, NCHAR, NVARCHAR, BINARY, or VARBINARY data. This avoids possible inefficiency where TimesTen would otherwise allocate memory to hold the largest possible value. For CHAR, VARCHAR, NCHAR, and NVARCHAR, the unit of size is number of characters. For BINARY and VARBINARY, it is bytes.

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 when using RETURNING INTO in TimesTen JDBC.

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.

Refer to Oracle TimesTen In-Memory Database JDBC Extensions Java API Reference for additional information about the TimesTen JDBC classes, interfaces, and methods discussed here.

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.

Tip:

Check for SQL warnings after executing the TimesTen prepared statement. In the event of a warning, output parameters are undefined. See Error Handling.

This example shows how to use DML returning with a TimesTenPreparedStatement instance, returning the name and age for a row that is inserted.

      import java.sql.ResultSet;
      import java.sql.SQLException;
      import java.sql.SQLWarning;
      import java.sql.Types;
      import com.timesten.jdbc.TimesTenPreparedStatement;
 
      Connection conn = null;
      ...
 
      // 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 Rowids

Each row in a 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.

TimesTen supports the java.sql.RowId interface and Types.ROWID type.

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

RowId getRowId(int columnIndex)
RowId getRowId(String columnLabel)

You can use the following PreparedStatement method to set a rowid:

setRowId(int parameterIndex, RowId x)

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 Pseudocolumn in Oracle TimesTen In-Memory Database SQL Reference for additional information about rowids and the ROWID data type, including usage and lifecycle.

Note:

TimesTen does not support the PL/SQL type UROWID.

Large Objects (LOBs)

TimesTen Classic supports LOBs (large objects), specifically CLOBs (character LOBs), NCLOBs (national character LOBs), and BLOBs (binary LOBs).

This section provides a brief overview of LOBs and discusses their use in JDBC, covering the following topics:

You can also refer to the following.

LOB Data Types in Oracle TimesTen In-Memory Database SQL Reference
Oracle Database SecureFiles and Large Objects Developer's Guide for general information about programming with LOBs (but not specific to TimesTen functionality)

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 a LOB object contains a logical pointer to the LOB data rather than the data itself.

Tip:

Because LOB objects do not remain valid past the end of the transaction in TimesTen, it is not feasible to use them with autocommit enabled. You would receive errors about LOBs being invalidated.
LOB manipulations through APIs that use LOB locators result in usage of TimesTen temporary space. Any significant number of such manipulations may necessitate a size increase for the TimesTen temporary data region. See TempSize in Oracle TimesTen In-Memory Database Reference.

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 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.

The LOB column contains "XY".
Select locator L1 for update.
Select locator L2 for update.
Write "Z" through L1 at offset 1.
Read through locator L1. This would return "ZY".
Read through locator L2. This would return "XY", because L2 remains read-consistent until it is used for a write.
Write "W" through L2 at offset 2.
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

There are significant differences between LOBs in TimesTen and LOBs in Oracle Database.

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.

Tip:

In TimesTen, creation of a LOB object results in creation of a database transaction if one is not already in progress. You must execute a commit or rollback to close the transaction.

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.

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.

Note:

For methods that write data to a LOB, the size of the LOB does not change other than in the circumstance where from the specified position there is less space available in the LOB than there is data to write. In that case, the LOB size increases enough to accommodate the data.
If the value specified for the position at which to write to a LOB is greater than LOB length + 1, the behavior is undefined.

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

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.

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.

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.

Using CALL to Execute Procedures and Functions

TimesTen supports each of the following syntax formats from any of its programming interfaces to call PL/SQL procedures (procname) or PL/SQL functions (funcname) that are standalone or part of a package, or to call TimesTen built-in procedures (procname).

CALL procname[(argumentlist)]

CALL funcname[(argumentlist)] INTO :returnparam

CALL funcname[(argumentlist)] INTO ?

TimesTen JDBC also supports each of the following syntax formats:

{ CALL procname[(argumentlist)] }

{ ? = [CALL] funcname[(argumentlist)] }

{ :returnparam = [CALL] funcname[(argumentlist)] }

You can execute procedures and functions through the CallableStatement interface, with a prepare step first when appropriate (such as when a result set is returned).

The following example calls the TimesTen built-in procedure ttCkpt. (Also see the ttCkpt example later in this section for a more complete example with JDBC syntax.)

CallableStatement.execute("call ttCkpt")

The following example calls the TimesTen built-in procedure ttDataStoreStatus. A prepare call is used because this procedure produces a result set. (Also see the ttDataStoreStatus example later in this section for a more complete example with JDBC syntax.)

CallableStatement cStmt = null;
cStmt = conn.prepareCall("call ttDataStoreStatus");
cStmt.execute();

The following examples call a PL/SQL procedure myproc with two parameters.

cStmt.execute("{ call myproc(:param1, :param2) }");

cStmt.execute("{ call myproc(?, ?) }");

The following shows several ways to call a PL/SQL function myfunc.

cStmt.execute("CALL myfunc() INTO :retparam");

cStmt.execute("CALL myfunc() INTO ?");

cStmt.execute("{ :retparam = myfunc() }");

cStmt.execute("{ ? = myfunc() }");

See CALL in Oracle TimesTen In-Memory Database SQL Reference.

Note:

A user's own procedure takes precedence over a TimesTen built-in procedure with the same name, but it is best to avoid such naming conflicts.

This example calls the ttCkpt procedure to initiate a fuzzy checkpoint.

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

Be aware that the ttCkpt built-in procedure requires ADMIN privilege. Refer to ttCkpt in Oracle TimesTen In-Memory Database Reference.

The following example calls the ttDataStoreStatus procedure and prints out the returned result set. For built-in procedures that return results, you can use the getXXX() methods of the ResultSet interface to retrieve the data, as shown.

Contrary to the advice given in Working with TimesTen Result Sets: Hints and Restrictions, this example uses a getString() call on the ResultSet object to retrieve the Context field, which is a binary. This is because the output is printed, rather than used for processing. If you do not want to print the Context value, you can achieve better performance by using the getBytes() method instead.

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 Database: " + 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();

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. One way is to set a timeout and the other way is to set a threshold.

These mechanisms are described in the following sections:

Setting a Timeout Duration for SQL Statements

TimesTen allows you to specify a SQL timeout, where if the timeout duration is reached, the statement stops executing and an error is thrown.

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.

Note:

If both a lock timeout value and a SQL query timeout value are specified, the lesser of the two values causes a timeout first. Regarding lock timeouts, you can refer to ttLockWait (built-in procedure) or LockWait (general connection attribute) in Oracle TimesTen In-Memory Database Reference, or to Check for Deadlocks and Timeouts in Oracle TimesTen In-Memory Database Monitoring and Troubleshooting Guide.

Setting a Threshold Duration for SQL Statements

TimesTen allows you to set a SQL threshold time period, in seconds. If the threshold is reached, a warning is written to the support log but execution continues.

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.

Features for Use with Cache

This section discusses features related to the use of cache operations.

Setting the Oracle Database Password

The OraclePassword attribute maps to the Oracle Database password. You can use the TimesTenDataSource method setOraclePassword() to set the Oracle Database password.

See Connecting to the Database for an example.

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:

Create a prepared statement (a PreparedStatement instance thePassThroughStatement) that calls ttOptSetFlag using a bind parameter for passthrough level.
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 and PassThrough in Oracle TimesTen In-Memory Database Reference. See Setting a Passthrough Level in Oracle TimesTen In-Memory Database Cache 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 In-Memory Database Cache Guide.

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.

See Determining the Number of Cache Instances Affected by an Operation in Oracle TimesTen In-Memory Database Cache Guide.

Features for Use with Replication

For TimesTen Classic applications that employ replication, you can improve performance by using parallel replication, which uses multiple threads acting in parallel to replicate and apply transactional changes to databases in a replication scheme.

TimesTen supports the following types of parallel replication:

Automatic parallel replication (ReplicationApplyOrdering=0): Parallel replication over multiple threads that automatically enforces transactional dependencies and all changes applied in commit order. This is the default.
Automatic parallel replication with disabled commit dependencies (ReplicationApplyOrdering=2): Parallel replication over multiple threads that automatically enforces transactional dependencies, but does not enforce transactions to be committed in the same order on the subscriber database as on the master database. In this mode, you can optionally specify replication tracks.

See Configuring Parallel Replication in Oracle TimesTen In-Memory Database Replication Guide.

For JDBC applications that use parallel replication and specify replication tracks, you can specify the track number for transactions on a connection through the following TimesTenConnection method. (Alternatively, use the general connection attribute ReplicationTrack or the ALTER SESSION parameter REPLICATION_TRACK.)

void setReplicationTrack(int track)

TimesTenConnection also has the corresponding getter method:

int getReplicationTrack()

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

Using Java Wrapper Functionality for Statements

Working with TimesTen Result Sets: Hints and Restrictions

Fetching Multiple Rows of Data

Optimizing Query Performance

Parameter Binding and Statement Execution

Preparing SQL Statements and Setting Input Parameters

Working with Output and Input/Output Parameters

Binding Duplicate Parameters in SQL Statements

Binding Duplicate Parameters in PL/SQL

Working with Associative Arrays

Overview of Associative Arrays

TimesTen Methods for Associative Arrays

Type Mapping for Associative Arrays

Binding Associative Arrays

Working with REF CURSORs

Working with DML Returning (RETURNING INTO Clause)

Working with Rowids

Large Objects (LOBs)

About LOBs

LOB Objects in JDBC

Differences Between TimesTen LOBs and Oracle Database LOBs

LOB Factory Methods

LOB Getter and Setter Methods

TimesTen LOB Interface Methods

LOB Prefetching

Passthrough LOBs

Using CALL to Execute Procedures and Functions

Setting a Timeout or Threshold for Executing SQL Statements

Setting a Timeout Duration for SQL Statements

Setting a Threshold Duration for SQL Statements

Configuring the Result Set Buffer Size in Client/Server Using JDBC

Features for Use with Cache

Setting the Oracle Database Password

Setting Temporary Passthrough Level with the ttOptSetFlag Built-In Procedure

Determining Passthrough Status

Managing Cache Groups

Features for Use with Replication