2 Working with TimesTen Databases in JDBC

This chapter describes the basic procedures for writing a Java application to access data. Before attempting to write a TimesTen application, be sure you have completed the following prerequisite tasks:

Prerequisite task What you do
Create a database. Follow the procedures described in "Managing TimesTen Databases" in Oracle TimesTen In-Memory Database Operations Guide.
Configure the Java environment. Follow the procedures described in "Setting the environment for Java development".
Compile and execute the TimesTen Java demos. Follow the procedures described in "About the TimesTen Java demos".

After you have successfully executed the TimesTen Java demos, your development environment is set up correctly and ready for you to create applications that access a database.

The following topics are covered in this chapter:

Key JDBC classes and interfaces

This section discusses important standard and TimesTen-specific JDBC packages, classes, and interfaces. The following topics are covered:

For reference information on standard JDBC, see the following for information about the java.sql package (the first for Java 6, the second for Java 5.0):

http://docs.oracle.com/javase/6/docs/api/java/sql/package-summary.html
http://docs.oracle.com/javase/1.5.0/docs/api/java/sql/package-summary.html

For reference information on TimesTen JDBC extensions, refer to Oracle TimesTen In-Memory Database JDBC Extensions Java API Reference.

Note:

It is recommended that you use Java 6 with TimesTen. Java 6 is a more capable API, especially for handling LOBs.

Package imports

You must import the standard JDBC package in any Java program that uses 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 as indicated in Table 2-1, with TimesTen-specific support and restrictions noted.

Also see "TimesTen JDBC extensions".

Table 2-1 Supported java.sql interfaces

Interface in java.sql Remarks on TimesTen support

Blob

  • The position() method, which returns the position where a specified byte pattern or BLOB pattern begins, is not supported.

  • Java 5.0 does not support free() or getBinaryStream(pos,length), but TimesTen provides them as extensions. They are standard in Java 6.

CallableStatement

  • You must pass parameters to CallableStatement by position, not by name.

  • You cannot use SQL escape syntax.

  • There is no support for Array, Struct, or Ref.

  • There is no support for Calendar for setDate(), getDate(), setTime(), or getTime().

Clob

  • The position() method, which returns the position where a specified character pattern or CLOB pattern begins, is not supported.

  • Java 5.0 does not support free() or getCharacterStream(pos,length), but TimesTen provides them as extensions. They are standard in Java 6.

Connection

  • There is no support for savepoints.

DatabaseMetaData

  • There are no restrictions.

NClob

  • NClob support applies only for Java 6 (ttjdbc6.jar).

  • The position() method, which returns the position where a specified character pattern or NCLOB pattern begins, is not supported.

ParameterMetaData

  • The JDBC driver cannot determine whether a column is nullable and always returns parameterNullableUnknown from calls to isNullable().

  • The getScale() method returns 1 for VARCHAR, NVARCHAR, and VARBINARY data types if they are INLINE. (Scale is of no significance to these data types.)

PreparedStatement

  • There is no support for getMetaData() in PreparedStatement.

  • There is no support for Array, Struct, or Ref.

  • There is no support for the Calendar type in setDate(), getDate(), setTime(), or getTime().

ResultSet

  • There is support for getMetaData() in ResultSet.

  • You cannot have multiple open ResultSet objects per statement.

  • You cannot specify the holdability of a result set, so a cursor cannot remain open after it has been committed.

  • There is no support for scrollable or updatable result sets.

  • There is no support for Array, Struct, or Ref.

  • There is no support for the Calendar type in setDate(), getDate(), setTime(), or getTime().

  • See "Working with TimesTen result sets: hints and restrictions".

ResultSetMetaData

  • The getPrecision() method returns 0 for undefined precision.

  • The getScale() method returns -127 for undefined scale.

RowId

  • RowId support applies only for Java 6 (ttjdbc6.jar).

  • The ROWID data type can be accessed using the RowId interface.

  • Output and input/output rowids can be registered as Types.ROWID.

  • Metadata methods return Types.ROWID and RowId as applicable.

Statement

  • There are no restrictions.

  • In TimesTen, the cancel() method delegates to the ODBC function SQLCancel. For details about the TimesTen implementation of that function, see "Supported ODBC functions" in Oracle TimesTen In-Memory Database C Developer's Guide.

  • See "Managing cache groups" for special TimesTen functionality of the getUpdateCount() method with cache groups.


Support for classes in the java.sql package

TimesTen supports the following java.sql classes.

  • DataTruncation

  • Date

  • DriverManager

  • DriverPropertyInfo

  • Time

  • Timestamp

  • Types

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

Important:

The TimesTen JDBC driver itself does not implement a database connection pool. The ObservableConnection and ObservableConnectionDS classes simply implement standard Java EE interfaces, facilitating the creation and management of database connection pools according to the Java EE standard.

A sample TimesTen connection pool package is shipped as part of the Quick Start demos. This is located in the following directory:

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.

Note:

You can register a StatementEventListener instance in TimesTen; however, StatementEvent instances are not supported.

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.

Table 2-2 TimesTen JDBC extensions

Interface Extends Remarks

TimesTenBlob

Blob

You can cast Blob instances to TimesTenBlob. This includes features to indicate whether a LOB is an Oracle Database passthrough LOB, free LOB resources (absent in Java 5.0), and get a binary stream with position and length specifications (a signature absent in Java 5.0).

See "Working with LOBs".

TimesTenCallableStatement

CallableStatement

Supports PL/SQL REF CURSORs. See "Working with REF CURSORs".

Supports associative array binds with methods to set input parameters and to register and get output parameters. See "Binding associative arrays".

TimesTenClob

Clob

You can cast Clob instances to TimesTenClob. This includes features to indicate whether a LOB is an Oracle Database passthrough LOB, free LOB resources (absent in Java 5.0), and get a character stream with position and length specifications (a signature absent in Java 5.0).

See "Working with LOBs".

TimesTenConnection

Connection

Provides capabilities such as prefetching rows to improve performance, listening to events for automatic client failover, setting the track number for parallel replication schemes where you specify replication tracks, and checking database validity.

See "Fetching multiple rows of data", "General Client Failover Features", "Features for use with replication", and "Check database validity".

Supplies factory methods createBLOB() and createCLOB() for Java 5.0. (LOB factory methods are absent in Java 5.0.) See "Working with LOBs".

TimesTenNClob

NClob

You can cast NClob instances to TimesTenNClob (Java 6 only). This includes features to indicate whether a LOB is an Oracle Database passthrough LOB.

See "Working with LOBs".

TimesTenPreparedStatement

PreparedStatement

Supports DML returning. See "Working with DML returning (RETURNING INTO clause)".

Supports associative array binds with a method to set input parameters. See "Binding associative arrays".

TimesTenStatement

Statement

Provides capabilities for specifying a query threshold. See "Setting a threshold duration for SQL statements".


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.

Managing TimesTen database connections

The type of DSN you create depends on whether your application connects directly to the database or connects by a client. If you intend to connect directly to the database, create a DSN as described in "Creating a Data Manager DSN on UNIX" or "Creating a Data Manager DSN on Windows" in Oracle TimesTen In-Memory Database Operations Guide. If you intend to create a client connection to the database, create a DSN as described in "Creating and configuring client DSNs on Windows" or "Creating and configuring client DSNs on UNIX" in Oracle TimesTen In-Memory Database Operations Guide.

After you have created a DSN, your application can connect to the database. This section describes how to create a JDBC connection to a database using either the JDBC direct driver or the JDBC client driver.

The operations described in this section are based on the level1 demo. Refer to "About the TimesTen Java demos".

This following topics are covered here:

Note:

Loading the TimesTen driver (Java 5.0):

It is recommended that you use Java 6 with TimesTen. If you use Java 5.0, however, you must explicitly load the TimesTen driver so that it is available for making database connections (otherwise TimesTen returns an error when the application attempts to connect). This step is not required with Java 6.

The following are the TimesTen JDBC drivers (for direct and client/server connections, respectively):

com.timesten.jdbc.TimesTenDriver
com.timesten.jdbc.TimesTenClientDriver

If you are using the DriverManager interface to connect to TimesTen with Java 5.0, 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 direct driver, for example:

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

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_1122;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 Database 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 DB
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.

Open and close a direct connection

Example 2-1 shows the general framework for an application that uses the DriverManager class to create a direct 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.)

Example 2-1 Connecting, executing SQL, and disconnecting

String URL = "jdbc:timesten:dsn=sampledb_1122";
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"
}

Check database validity

Applications can call the following TimesTenConnection method to detect whether the database is valid:

boolean isDataStoreValid() throws java.sql.SQLException

It returns true if the database is valid, or false if the database is in an invalid state, such as due to system or application failure.

Access control for connections

In order for any user (other than the instance administrator) to connect to a database, the CREATE SESSION privilege must be granted. This is a system privilege so must be granted to the user by the instance administrator or someone with ADMIN privilege, 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.

To create a JMS/XLA connection and execute JMS/XLA functionality, a user must be granted the XLA privilege, discussed in "Access control impact on XLA", in addition to the CREATE SESSION privilege.

Managing TimesTen data

This section provides detailed information on working with data in a TimesTen database. It includes the following topics:

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

Notes:

Example 2-2 Executing an update

This example uses the executeUpdate() method on the Statement object to execute an INSERT statement to insert data into the customer table in the current schema. The connection must have been opened, which is not shown.

Connection conn = null;
Statement stmt = null;
...
// [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) {
    ...
}

Example 2-3 Executing a query

This example uses an executeQuery() call on the Statement object to execute a SELECT statement on the customer table in the current schema and display the returned java.sql.ResultSet instance:

Statement stmt = null;
. . . . . .
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.

Important:

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.

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

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

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.

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

Example 2-4 Setting a prefetch count

The following code 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();

Binding parameters and executing statements

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

Notes:

  • Use the Statement, PreparedStatement, or CallableStatement method close() to close a statement you have finished using it.

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

  • Access control privileges are checked both when SQL is prepared and when it is executed in the database. Refer to "Considering TimesTen features for access control" for related information.

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

Example 2-5 Prepared statement for querying

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

Example 2-6 Prepared statement for updating

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

Example 2-7 Prepared statements for updating and querying

This 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 demo. (Refer to "About the TimesTen Java demos" regarding the demos.)

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

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

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

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

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

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

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

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

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

// Commit the inserts
conn.commit();

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

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

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

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

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

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

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

Example 2-8 Prepared statements for multiple connections

This 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

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

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

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

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

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

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

    void registerOutParameter(int paramIndex,
                              int sqlType,
                              int ignore, //This parameter is ignored by TimesTen.
                              int maxLength)
    
  2. Use the appropriate CallableStatement method setXXX(), where XXX indicates the data type, to set the input value of an input/output parameter. Specify the parameter position and data value.

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

Important:

Check for SQL warnings before processing output parameters. In the event of a warning, output parameters are undefined. See "Handling errors" for general information about errors and warnings.

Notes:

In TimesTen:
  • 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.

Example 2-9 Using an output parameter in a callable statement

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 Oracle TimesTen In-Memory Database PL/SQL Developer's Guide for information about PL/SQL.)

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

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

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

Binding duplicate parameters in SQL statements

TimesTen supports two distinct modes for binding duplicate parameters in a SQL statement:

  • Oracle mode: Multiple occurrences of the same parameter name are considered to be distinct parameters.

  • Traditional TimesTen mode, as in earlier releases: Multiple occurrences of the same parameter name are considered to be multiple occurrences of the same parameter.

You can choose the desired mode through the DuplicateBindMode TimesTen general connection attribute. DuplicateBindMode=0 (the default) is for the Oracle mode, and DuplicateBindMode=1 is for the TimesTen mode. Because this is a general connection attribute, different connections to the same database can use different values. Refer to "DuplicateBindMode" in Oracle TimesTen In-Memory Database Reference for additional information about this attribute.

The rest of this section provides details for each mode, considering the following query:

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

Note:

This discussion applies only to SQL statements issued directly from JDBC (not through PL/SQL, for example).
Oracle mode for duplicate parameters

In the Oracle mode, where DuplicateBindMode=0, multiple occurrences of the same parameter name in a SQL statement are considered to be different parameters. 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.

TimesTen mode for duplicate parameters

In the TimesTen mode, where DuplicateBindMode=1, SQL statements containing duplicate parameters are parsed such that only distinct parameter names are considered as separate parameters. The application binds a value only for each unique parameter, and no unique parameter can be left unbound.

Binding is based on the position of the first occurrence of a parameter name. Subsequent occurrences of the parameter name are bound to the same value, and are not given parameter position numbers.

For the SQL statement above, the two occurrences of a are considered to be a single parameter, so cannot be bound separately:

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

Note that in the TimesTen mode, parameter b is considered to be in position 2, not position 3.

Binding duplicate parameters in PL/SQL

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

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

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

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

And the same for the following CALL statement:

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

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

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

Binding associative arrays

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

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

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

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

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

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

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

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

Notes:

Note the following restrictions in TimesTen:
  • 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 provides extensions, described below, through the interfaces TimesTenPreparedStatement and TimesTenCallableStatement to support associative array binds. Refer to Oracle TimesTen In-Memory Database JDBC Extensions Java API Reference for additional information about any of the methods described here.

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

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

    Specify the following:

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

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

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

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

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

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

    For example (assuming a TimesTenPreparedStatement instance pstmt):

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

Notes:

  • 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-3 for the default mappings.

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

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

Table 2-3 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


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

Example 2-10 Binding an associative array

This is a more complete example 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.

An application can receive a REF CURSOR 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.

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

Important:

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

The following example demonstrates this usage.

Example 2-11 Using a REF CURSOR

This example shows how to use a callable statement with a REF CURSOR. In the CallableStatement instance, a PL/SQL block opens a cursor and executes a query. The TimesTenCallableStatement method getCursor() is used to return the cursor, which is registered as TimesTenTypes.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();
...

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.

Important:

Check for SQL warnings after executing the TimesTen prepared statement. In the event of a warning, output parameters are undefined. See "Handling errors" for general information about errors and warnings.

Example 2-12 DML returning

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.

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.0. 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.0. Takes a String or RowId object in Java 6.

Note:

You cannot use getBytes() or setBytes() for ROWID parameters that are PL/SQL parameters or passthrough parameters (parameters passed to Oracle Database when using the TimesTen Application-Tier Database Cache). Use getString() and setString(), or use getObject() and setObject() with a RowId object (Java 6 only) or String object.

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

Refer to "ROWID data type" and "ROWID" 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.

Working with LOBs

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

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

Notes:

  • TimesTen does not support CLOBs if the database character set is TIMESTEN8.

  • This section discusses LOB support in both Java 5.0 and Java 6. It is recommended, however, that you use Java 6 with TimesTen. This is a more standard and complete implementation. In particular, Java 5.0 does not support NCLOBs.

You can also refer to the following.

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 in size and a CLOB or NCLOB up to 4 MB. LOBs in TimesTen have essentially the same functionality as in Oracle Database, except as noted otherwise. (See "Differences between TimesTen LOBs and Oracle Database LOBs".)

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

LOB objects in JDBC

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

Important:

  • 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 simple SELECT statement, the locator is read-only. Read-only and writable locators behave as follows:

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

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

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

  1. The LOB column contains "XY".

  2. Select locator L1 for update.

  3. Select locator L2 for update.

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

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

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

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

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

Differences between TimesTen LOBs and Oracle Database LOBs

Be aware of the following:

  • A key difference between the TimesTen LOB implementation and the Oracle Database implementation is that in TimesTen, LOB objects do not remain valid past the end of the transaction. All LOB objects are invalidated after a commit or rollback, whether explicit or implicit. This includes after any autocommit, or after any DDL statement if TimesTen DDLCommitBehavior is set to 0 (the default), for Oracle Database behavior.

  • 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 Java 6 Connection methods createBlob(), createClob(), and createNClob().

For a Java 5.0 environment (not recommended), where there are no standard LOB factory methods, the following are specified by the TimesTen extension TimesTenConnection interface.

  • createBLOB()

  • createCLOB()

Java 5.0 does not support NCLOBs.

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

Important:

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 (Java 6 only) 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 (Java 6 only) object.

  • PreparedStatement setter methods: There is a setBlob() method, setClob() method, and setNClob() method (Java 6 only) 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 (Java 6 only) 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 (Java 6 only) output parameter.

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

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

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

TimesTen LOB interface methods

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

The following list summarizes Blob features.

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

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

    Note: The free() method does not exist in Java 5.0 but is provided as a TimesTen extension. It is standard in Java 6.

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

    Note: The getBinaryStream(pos,length) signature does not exist in Java 5.0 but is provided as a TimesTen extension. It is standard in Java 6.

  • 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 (Java 6 only) 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.

    Note: The free() method does not exist in Java 5.0 but is provided as a TimesTen extension. It is standard in Java 6.

  • Retrieve the CLOB or NCLOB as an ASCII stream.

    Note: The getCharacterStream(pos,length) signature does not exist in Java 5.0 but is provided as a TimesTen extension. It is standard in Java 6.

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

Notes:

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

  • The read() method of an InputStream or Reader object returns 0 (zero) if the length of the buffer used in the method call is 0, regardless of the amount of data in the InputStream or Reader object. Therefore, usage such as the following is problematic if the CLOB length may be 0, such as if it were populated using the SQL EMPTY_CLOB() function:

    java.io.Reader r = myclob.getCharacterStream();
    char[] buf = new char[myclob.length()]; //buf for r.read() call
    

    Normally when you call read(), -1 is returned if the end of the stream is reached. But in the preceding case, -1 is never returned. Be aware of this when you use streams returned by the BLOB getBinaryStream() method, which returns InputStream, the CLOB getAsciiStream() method, which returns InputStream, or the CLOB getCharacterStream() method, which returns Reader.

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

LOB prefetching

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

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

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

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

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

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

Passthrough LOBs

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

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

    boolean isPassthrough()

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

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

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

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.

Note:

All open cursors on the connection are closed upon transaction commit or rollback in TimesTen.

You can refer to "Transaction overview" in Oracle TimesTen In-Memory Database Operations Guide for additional information about transactions.

Setting autocommit

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

Disable autocommit as follows:

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

Manually committing or rolling back changes

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

myconn.commit();

Or:

myconn.rollback();

Using COMMIT and ROLLBACK SQL statements

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

mystmt.execute("COMMIT");

Managing multiple threads

Note:

On some UNIX platforms it is necessary to set THREADS_FLAG, as described in "Set the THREADS_FLAG variable (UNIX only)" in Oracle TimesTen In-Memory Database Installation Guide.

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

When your application has a direct 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, as this can cause a program to crash in unpredictable ways that are 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.

Note:

In multithreaded applications, a thread that issues requests on different connection handles to the same database may encounter lock conflicts with itself. TimesTen returns lock timeout and deadlock errors in this situation.

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 additional TimesTen data management features

Preceding sections discussed key features for managing TimesTen data. This section covers the following additional features:

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-13 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-14 below 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 for details about CALL syntax.

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.

Example 2-13 Executing a ttCkpt call

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 for additional information.

Example 2-14 Executing a ttDataStoreStatus call

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

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.

Setting a timeout duration for SQL statements

In TimesTen you can set the SqlQueryTimeout general connection attribute to specify the timeout period (in seconds) for the connection, and therefore any statement on the connection. (Also see "SqlQueryTimeout" in Oracle TimesTen In-Memory Database Reference.) A value of 0 indicates no timeout. Despite the name, this timeout value applies 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.

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

Setting a threshold duration for SQL statements

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

The name of the SNMP trap is ttQueryThresholdWarnTrap. See Oracle TimesTen In-Memory Database Error Messages and SNMP Traps for information about configuring SNMP traps.

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. You can override the threshold for a JDBC Connection object by including the QueryThreshold attribute in the connection URL for the database. For example, to set QueryThreshold to a value of 5 seconds for the myDSN database:

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

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

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

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

Features for use with TimesTen Cache

This section discusses features related to the use of TimesTen Application-Tier Database Cache (TimesTen Cache):

Note:

The OraclePassword attribute maps to the Oracle Database password. You can use the TimesTenDataSource method setOraclePassword() to set the Oracle Database password. See "Connect 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:

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

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

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

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

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

Managing cache groups

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

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

Features for use with replication

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

  • User-defined parallel replication (ReplicationApplyOrdering=1): For applications that use a classic replication scheme, have very predictable transactional dependencies, and do not require that the commit order on the receiver is the same as that on the originating database. You can specify the number of transaction tracks and apply specific transactions to each track. All tracks are read, transmitted and applied in parallel.

See "Configuring parallel replication" in Oracle TimesTen In-Memory Database Replication Guide for additional information and usage scenarios.

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 for additional information.

Note:

User-defined parallel replication is generally not advisable, because special care must be taken to avoid data divergence between replication nodes.

Considering TimesTen features for access control

TimesTen has features to control database access with object-level resolution for database objects such as tables, views, materialized views, sequences, and synonyms. You can refer to "Managing Access Control" in Oracle TimesTen In-Memory Database Operations Guide for introductory information about TimesTen access control.

This section introduces access control as it relates to SQL operations, database connections, and JMS/XLA.

For any query or SQL DML or DDL statement discussed in this document or used in an example, it is assumed that the user has appropriate privileges to execute the statement. For example, a SELECT statement on a table requires ownership of the table, SELECT privilege granted for the table, or the SELECT ANY TABLE system privilege. Similarly, any DML statement requires table ownership, the applicable DML privilege (such as UPDATE) granted for the table, or the applicable ANY TABLE privilege (such as UPDATE ANY TABLE).

For DDL statements, CREATE TABLE requires the CREATE TABLE privilege in the user's schema, or CREATE ANY TABLE in any other schema. ALTER TABLE requires ownership or the ALTER ANY TABLE system privilege. DROP TABLE requires ownership or the DROP ANY TABLE system privilege. There are no object-level ALTER or DROP privileges.

Refer to "SQL Statements" in Oracle TimesTen In-Memory Database SQL Reference for a list of access control privileges and the privilege required for any given SQL statement.

Privileges are granted through the SQL statement GRANT and revoked through the statement REVOKE. Some privileges are automatically granted to all users through the PUBLIC role, of which all users are a member. Refer to "The PUBLIC role" in Oracle TimesTen In-Memory Database SQL Reference for information about this role.

In addition, access control affects the following topics covered in this document:

Notes:

  • Access control cannot be disabled.

  • Access control privileges are checked both when SQL is prepared and when it is executed in the database, with most of the performance cost coming at prepare time.

Handling errors

This section discusses how to check for, identify, and handle errors in a TimesTen Java application.

For a list of the errors that TimesTen returns and what to do if the error is encountered, see "Warnings and Errors" in Oracle TimesTen In-Memory Database Error Messages and SNMP Traps.

This section includes the following topics.

About fatal errors, non-fatal errors, and warnings

When operations are not completely successful, TimesTen can return a fatal error, a non-fatal error, or a warning.

Handling fatal errors

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

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

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

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

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

    • No uncommitted or rolled back transactions are reflected.

Handling non-fatal errors

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

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

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

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

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

Note:

If a ResultSet, Statement, PreparedStatement, CallableStatement or Connection operation results in a database error, it is a good practice to call the close() method for that object.

About warnings

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

  • A checkpoint failure

  • Use of a deprecated TimesTen feature

  • Truncation of some data

  • Execution of a recovery process upon connect

  • Replication return receipt timeout

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

Also see "Reporting errors and warnings" immediately below.

Abnormal termination

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

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-15 and Example 2-16 demonstrate how you might iterate through the lists of returned SQLException and SQLWarning objects to report all of the errors and warnings, respectively.

Example 2-15 Printing exceptions

The following method prints out the content of all exceptions in the linked SQLException objects.

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

Example 2-16 Printing warnings

This method prints out the content of all warning in the linked SQLWarning objects.

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 SQL state and the getErrorCode() method to return TimesTen error codes, as shown in Example 2-17.

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

Example 2-17 Catching an error

The TimesTen demos require that you load the demo schema before they are executed. The following catch statement alerts the user that appuser has not been loaded or has not been refreshed by detecting ODBC error S0002 and TimesTen error 907:

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

You can use the TimesTenVendorCode interface to detect the errors by their name, rather than their number.

Consider this example:

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

The following is equivalent:

ex.getErrorCode() == 907

Rolling back failed transactions

In some situations, such as recovering from a deadlock or lock timeout, you should explicitly roll back the transaction using the Connection method rollback(), as in the following example.

Example 2-18 Rolling back a transaction

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();
} catch (SQLException ex) {

  reportSQLExceptions(ex);
}

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

Note:

If your application aborts, crashes, or disconnects in the middle of an active transaction, TimesTen automatically rolls back the transaction.

JDBC support for automatic client failover

Automatic client failover is for use in High Availability scenarios with a TimesTen active standby pair replication configuration. If there is a failure of the active node, failover (transfer) to the new active (original standby) node occurs, and applications are automatically reconnected to the new active node. TimesTen provides features that allow applications to be alerted when this happens, so they can take any appropriate action.

This section discusses TimesTen JDBC extensions related to automatic client failover, covering the following topics:

Note:

Automatic client failover applies only to client/server connections. The functionality described here does not apply to a direct connection.

Automatic client failover is complementary to Oracle Clusterware in situations where Oracle Clusterware is used, though the two features are not dependent on each other.

See "Using automatic client failover" in Oracle TimesTen In-Memory Database Operations Guide for general information about automatic client failover, and "Using automatic client failover in your application" in Oracle TimesTen In-Memory Database C Developer's Guide for related information for developers.

You can also refer to "Using Oracle Clusterware to Manage Active Standby Pairs" in Oracle TimesTen In-Memory Database Replication Guide for information about Oracle Clusterware.

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.

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.

General Client Failover Features

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

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

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

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

  • ClientFailoverEvent class

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

  • ClientFailoverEventListener interface

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

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

  • New methods in the TimesTenConnection interface

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

  • A new constant, TT_ERR_FAILOVERINVALIDATION, in the TimesTenVendorCode interface

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

Client failover features for pooled connections

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

Configuration of automatic client failover

Refer to "Configuring automatic client failover" in Oracle TimesTen In-Memory Database Operations Guide for information.

Note:

Setting any of TTC_Server2, TTC_Server_DSN2, or TCP_Port2 implies the following:
  • You intend to use automatic client failover.

  • You understand that a new thread is created for your application to support the failover mechanism.

  • You have linked your application with a thread library (pthreads on UNIX systems).

Synchronous detection of automatic client failover

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

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

Example 2-19 Synchronous detection of automatic client failover

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.

Implement a client failover event listener

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

  • void notify(ClientFailoverEvent event)

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

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

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

Example 2-20 Asynchronous detection of automatic client failover

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

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

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

  • BEGIN, if the client failover has begun

  • END, if the client failover has completed successfully

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

  • ABORT, if the client failover has aborted

Register the client failover listener instance

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

TimesTenConnection provides the following method for this.

  • void addConnectionEventListener (ClientFailoverEventListener listener)

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

Example 2-21 Registering the client failover listener

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

Remove the client failover listener instance

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

  • void removeConnectionEventListener (ClientFailoverEventListener listener)

Use this method to deregister a listener instance.