2 Working with TimesTen Databases

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 and classes 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).

install_dir/quickstart/sample_code/jdbc/connectionpool

TimesTen supports the following javax.sql event and listener:

• When using a PooledConnection instance, you can register a ConnectionEventListener instance to listen for ConnectionEvent occurrences.

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 database connections. The following is the TimesTen JDBC driver:

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 database and specify connection attributes

To create a JDBC connection, you must specify a TimesTen connection URL for the database. 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 database:

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 or Server 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 "Connection 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 general connection attribute to 1, create a URL as follows:

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

Connect to the database

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

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

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("jdbc:timesten:direct:<dsn>");
Connection conn = ds.getConnection();

The TimesTen user name and password can be set in the DSN within the URL in the setUrl() call, but there are also TimesTenDataSource methods to set them separately, as well as to set the Oracle password (as applicable):

TimesTenDataSource ds = new TimesTenDataSource();
ds.setUser(myttusername);                    // User name to log in to TimesTen.
ds.setPassword(myttpwd);                     // Password to log in to TimesTen.
ds.setUrl("jdbc:timesten:direct:<dsn>");
ds.setOraclePassword(myorapwd);              // Password to log in to Oracle.
Connection conn = ds.getConnection();

Either the DriverManager.getConnection() method or the ds.getConnection() method returns a Connection object (conn in this example) that you can use as a handle to the database. 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 database

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

If an error has occurred, you may want to roll back the transaction before disconnecting from the database. 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 database, 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" below

// 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 database 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 Database" in Oracle TimesTen In-Memory Database Operations Guide describes how to use SQL to manage data. 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 statement).

Connection conn;
Statement stmt;
...
// [Code to open connection. See "Connect to the database"...] 
...
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

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

• 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, 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 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 an application that connects to a database set with Read Committed isolation level.

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 database. With Read Committed isolation level, the default prefetch value is 5. With Serializable isolation level, 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();

Binding parameters and executing statements

This sections discusses how to bind input or output parameters for SQL statements. The following topics are covered:

• Preparing SQL statements and setting input parameters

• Working with OUT and IN OUT parameters

• Binding duplicate parameters in SQL statements

• Binding duplicate parameters in PL/SQL

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

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

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

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

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

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

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

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

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

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

// Commit the inserts
conn.commit();

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

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

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

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

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

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

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

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

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 and connection
cstmt.close();
conn.close();
...

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

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

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

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

void registerReturnParameter(int paramIndex, int sqlType)

As with the registerOutParameter() method discussed in "Working with OUT and IN OUT parameters", this method includes 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.

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

For Java 6, TimesTen supports the java.sql.RowId interface and Types.ROWID type.

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)

  Returns a String object in Java 5. Returns a RowId object in Java 6.

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

• setBytes(int parameterIndex, byte[] x)

• setString(int parameterIndex, String x)

• setRowId(int parameterIndex, RowId x) (Java 6 only)

• setObject(int parameterIndex, Object x)

  Takes a String object in Java 5. Takes a String or RowId object in Java 6.

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.

Working with synonyms

TimesTen supports private and public synonyms (aliases) for database objects such as tables, views, sequences, and PL/SQL objects. Synonyms are often used for security to mask object names and object owners, or for convenience to simplify SQL statements.

To create a private synonym for table foo in your schema:

CREATE SYNONYM synfoo FOR foo;

To create a public synonym for foo:

CREATE PUBLIC SYNONYM pubfoo FOR foo;

A private synonym exists in the schema of a specific user and shares the same namespace as database objects such as tables, views, and sequences. A private synonym cannot have the same name as a table or other object in the same schema.

A public synonym does not belong to any particular schema, is accessible to all users, and can have the same name as any private object.

To create a synonym you must have the CREATE SYNONYM or CREATE PUBLIC SYNONYM privilege, as applicable. To use a synonym you must have appropriate privileges to access the underlying object.

For general information about synonyms, see "Understanding synonyms" in Oracle TimesTen In-Memory Database Operations Guide. For information about the CREATE SYNONYM and DROP SYNONYM statements, see "SQL Statements" in Oracle TimesTen In-Memory Database SQL Reference.

Committing or rolling back changes to the database

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

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

myconn.commit();

myconn.rollback();

mystmt.execute("COMMIT");

Managing multiple threads

The level4 demo demonstrates the use of multiple threads. Refer to "About the TimesTen Java demos".

When your application has a direct driver connection to the database, TimesTen functions share stack space with your application. In multithreaded environments, it is important to avoid overrunning the stack allocated to each thread because consequences can result that are unpredictable and difficult to debug. The amount of stack space consumed by TimesTen calls varies depending on the SQL functionality used. Most applications should set thread stack space to at least 16 KB on 32-bit systems and between 34 KB to 72 KB on 64-bit systems.

The amount of stack space allocated for each thread is specified by the operating system when threads are created. On Windows, you can use the TimesTen debug driver and link your application against the Visual C++ debug C library to enable stack probes that raise an identifiable exception if a thread attempts to grow its stack beyond the amount allocated.

Java escape syntax and 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;

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 Example 2-12 below 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 Example 2-13 below for a more complete example with JDBC syntax.)

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

These 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 for details about CALL syntax.

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

• 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

Features for use with IMDB Cache

This section discusses features related to the use of IMDB Cache:

• Setting temporary passthrough level with the ttOptSetFlag built-in procedure

• Managing cache groups

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

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

Setting up user-specified parallel replication

For applications that have very predictable transactional dependencies and do not require the commit order on the replica database to be the same as that on the originating database, TimesTen supports parallel replication. This feature allows replication of multiple user-specified tracks of transactions in parallel. See "Increasing replication throughput for other replication schemes" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide for general information about parallel replication.

User-specified parallel replication is enabled through the TimesTen data store attributes ReplicationParallelism and ReplicationApplyOrdering, as described in "Data store attributes" in Oracle TimesTen In-Memory Database Reference. The track number of transactions for a connection can be specified through the TimesTen general connection attribute ReplicationTrack, the ALTER SESSION parameter REPLICATION_TRACK, or in JDBC through the following TimesTenConnection method:

• void setReplicationTrack(int track)

TimesTenConnection also has the corresponding getter method:

• int getReplicationTrack()

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 if there are one or more database access errors and a SQLWarning object is generated if there are 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 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 timeout condition, you may want to explicitly roll back the transaction using the Connection method rollback(), 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. Refer to "SYS.MONITOR" in Oracle TimesTen In-Memory Database System Tables and Limits Reference for additional information.

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 the application or input data for potential errors before submitting it.

Features and functionality of JDBC support for automatic client failover

This section discusses general TimesTen JDBC features related to client failover, and 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();
      }