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.
Topics in this chapter are:
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:
https://download.oracle.com/javase/1.5.0/docs/api/
For reference information on TimesTen JDBC extensions, refer to Oracle TimesTen In-Memory Database JDBC Extensions Java API Reference.
You must import the standard JDBC package in any Java program that use JDBC:
import java.sql.*;
If you are going to use data sources or pooled connections, you must also import the standard extended JDBC package:
import javax.sql.*;
You must import the TimesTen JDBC package:
import com.timesten.jdbc.*;
To use XA data sources for JTA, you must also import the following TimesTen package:
import com.timesten.jdbc.xa.*;
TimesTen supports the java.sql interfaces shown in Table 2-1.
Table 2-1 Supported java.sql interfaces
| Interface in java.sql | Remarks on TimesTen support |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Note: This support applies only when using Java 6 (
|
TimesTen supports the following java.sql classes:
Date
DriverManager
DriverPropertyInfo
Time
Timestamp
Types
DataTruncation
SQLException
SQLWarning
TimesTen supports the following javax.sql interfaces:
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. TheObservableConnection 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:
It is permissible to register aStatementEventListener instance in TimesTen; however, StatementEvent instances are not supported.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 |
|---|---|---|
|
|
Provides capabilities such as prefetching rows to improve performance, listening to events for automatic client failover, and setting the track number for parallel replication. See "Fetching multiple rows of data", "General Client Failover Features", and "Setting up user-specified parallel replication". |
|
|
|
Provides capabilities for specifying a query threshold. |
|
|
|
Supports DML returning. |
|
|
|
Supports PL/SQL REF CURSORs. |
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.
Use ClientFailoverEvent (and also the ClientFailoverEventListener interface above) for automatic client failover features.
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, the 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:
The TimesTen JDBC driver must be loaded before it is available for making database connections. The following is the TimesTen JDBC driver:
com.timesten.jdbc.TimesTenDriver
If you are using the DriverManager interface to connect to TimesTen, call the Class.forName() method to load the TimesTen JDBC driver. This method creates an instance of the TimesTen driver and registers it with the driver manager. If you are using the TimesTenDataSource interface, you are not required to call Class.forName().
To identify and load the TimesTen driver:
Class.forName("com.timesten.jdbc.TimesTenDriver");
Note:
If the TimesTen JDBC driver is not loaded, TimesTen returns an error when the application attempts to connect to the database.To create a JDBC connection, you must specify a TimesTen connection URL for the database. The format of a TimesTen connection URL is as follows:
jdbc:timesten:{direct|client}:dsn=DSNname;[DSNattributes;]
The default is direct.
For example, the following creates a direct connection to the sample database:
String URL = "jdbc:timesten:direct:dsn=sampledb_1121";
You can programmatically set or override the connection attributes in the DSN description by specifying attributes in the connection URL.
Refer to "Connection attributes for Data Manager DSNs or Server DSNs" in Oracle TimesTen In-Memory Database Operations Guide for introductory information about connection attributes. General connection attributes require no special privilege. First connection attributes are set when the database is first loaded, and persist for all connections. Only the instance administrator can load a database with changes to first connection attribute settings. Refer to "Connection Attributes" in Oracle TimesTen In-Memory Database Reference for specific information about any particular connection attribute, including required privilege.
For example, to set the LockLevel general connection attribute to 1, create a URL as follows:
String URL = "jdbc:timesten:direct:dsn=sampledb_1121;LockLevel=1";
After you have defined a URL, you can use the getConnection() method of either DriverManager or TimesTenDataSource to connect to the database.
If you use the DriverManager.getConnection() method, specify the driver URL to connect to the database.
import java.sql.*; ... Connection conn = DriverManager.getConnection(URL);
To use the TimesTenDataSource method getConnection(), first create a data source. Then use the TimesTenDataSource method setUrl() to set the URL and getConnection() to connect:
import com.timesten.jdbc.TimesTenDataSource;
import java.sql.*;
...
TimesTenDataSource ds = new TimesTenDataSource();
ds.setUrl("jdbc:timesten:direct:<dsn>");
Connection conn = ds.getConnection();
The TimesTen user name and password can be set in the DSN within the URL in the setUrl() call, but there are also TimesTenDataSource methods to set them separately, as well as to set the Oracle password (as applicable):
TimesTenDataSource ds = new TimesTenDataSource();
ds.setUser(myttusername); // User name to log in to TimesTen.
ds.setPassword(myttpwd); // Password to log in to TimesTen.
ds.setUrl("jdbc:timesten:direct:<dsn>");
ds.setOraclePassword(myorapwd); // Password to log in to Oracle.
Connection conn = ds.getConnection();
Either the DriverManager.getConnection() method or the ds.getConnection() method returns a Connection object (conn in this example) that you can use as a handle to the database. See the level1 demo for an example on how to use the DriverManager method getConnection(), and the level2 and level3 demos for examples of using the TimesTenDataSource method getConnection(). Refer to "About the TimesTen Java demos".
Note:
If the TimesTen JDBC driver is not loaded, TimesTen returns an error when the application attempts to connect to the database. See "Load the TimesTen driver".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.
Example 2-1 shows the general framework for an application that uses the DriverManager class to create a direct driver connection to the sample database, execute some SQL, and then close the connection. See the level1 demo for a working example. (See "About the TimesTen Java demos" regarding the demos.)
Example 2-1 Connecting, executing SQL, and disconnecting
String URL = "jdbc:timesten:dsn=sampledb_1121";
Connection conn = null;
try {
Class.forName("com.timesten.jdbc.TimesTenDriver");
} catch (ClassNotFoundException ex) {
// See "Handling errors"
}
try {
// Open a connection to TimesTen
conn = DriverManager.getConnection(URL);
// Report any SQLWarnings on the connection
// See "Reporting errors and warnings"
// Do SQL operations
// See "Managing TimesTen data" below
// Close the connection to TimesTen
conn.close();
// Handle any errors
} catch (SQLException ex) {
// See "Handling errors"
}
Privilege to connect to a database must be explicitly granted to every user other than the instance administrator, through the CREATE SESSION privilege. This is a system privilege so must be granted by an administrator to the user, either directly or through the PUBLIC role. Refer to "Managing Access Control" in Oracle TimesTen In-Memory Database Operations Guide for additional information and examples.
This section provides detailed information on working with data in a TimesTen database. It includes the following topics:
"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:
See "Working with TimesTen result sets: hints and restrictions" for details about what you should know when working with result sets generated by TimesTen.
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.
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 also be opened, which is not shown.
Connection conn; Statement stmt; ... // [Code to open connection. See "Connect to the database"...] ... try { stmt = conn.createStatement(); int numRows = stmt.executeUpdate("insert into customer values" + "(40, 'West', 'Big Dish', '123 Signal St.')"); } catch (SQLException ex) { ... }
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;
. . . . . .
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();
}
Use ResultSet objects to process query results. In addition, some methods and built-in procedures return TimesTen data in the form of a ResultSet object. This section describes what you should know when using ResultSet objects from TimesTen.
TimesTen does not support multiple open ResultSet objects per statement. TimesTen cannot return multiple ResultSet objects from a single Statement object without first closing the current result set.
TimesTen does not support holdable cursors. You cannot specify the holdability of a result set, essentially whether a cursor can remain open after it has been committed.
ResultSet objects are not scrollable or updatable, so you cannot specify ResultSet.TYPE_SCROLL_SENSITIVE or ResultSet.CONCUR_UPDATABLE.
Use the ResultSet method close() to close a result set as soon as you are done with it. For performance reasons, this is especially important for result sets used for both read and update operations and for result sets used in pooled connections.
Calling the ResultSet method getString() is more costly in terms of performance if the underlying data type is not a string. Because Java strings are immutable, getString() must allocate space for a new string each time it is called. Do not use getString() to retrieve primitive numeric types, like byte or int, unless it is absolutely necessary. For example, it is much faster to call getInt() on an integer column. Also see "Use the ResultSet method getString() sparingly".
In addition, it is generally true for dates and timestamps that ResultSet native methods getDate() and getTimestamp() will have better performance than getString().
Application performance is influenced by the choice of getXXX() calls and by any required data transformations after invocation.
JDBC ignores the setting for the ConnectionCharacterSet attribute. It returns data in UTF-16 encoding.
Fetching multiple rows of data can increase the performance of an application that connects to a database set with Read Committed isolation level.
You can specify the number of rows to be prefetched as follows.
Call the Statement or ResultSet method setFetchSize(). These are the standard JDBC calls, but the limitation is that they only affect one statement at a time.
Call the TimesTenConnection method setTtPrefetchCount(). This enables a TimesTen extension that establishes prefetch on a connection level so that all of the statements on the connection use the same prefetch setting.
This section describes the connection-level prefetch implemented in TimesTen.
Note:
You can use the TimesTen prefetch count extension only with direct-linked applications.When the prefetch count is set to 0, TimesTen uses a default value, depending on the isolation level you have set for the database. With Read Committed isolation level, the default prefetch value is 5. With Serializable isolation level, the default is 128. The default prefetch value is the optimum setting for most applications. Generally, a higher value may result in better performance for larger result sets, at the expense of slightly higher resource use.
To disable prefetch, set the prefetch count to 1.
Call the TimesTenConnection method getTtPrefetchCount() to check the current prefetch value.
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 prefech count to 10 for this connection
conn.setTtPrefetchCount(10);
// Return the prefetch count to the 'count' variable.
int count = conn.getTtPrefetchCount();
This sections discusses how to bind input or output parameters for SQL statements. The following topics are covered:
Note:
Array binding, the ability to bind associative arrays (index-by tables) and varrays (variable size arrays) into PL/SQL statements, is not supported in TimesTen JDBC.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.
Notes:
It is generally true for time, dates, and timestamps that PreparedStatement native methods setTime(), setDate() and setTimestamp() will have better performance than setString().
Application performance is influenced by the choice of setXXX() calls and by any required data transformations before invocation.
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 realize 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; ... // [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 with the conn2 and conn3 connections and speeds up the prepare operations for pIns2 and pIns3:
Connection conn1;
Connection conn2;
Connection conn3;
.....
PreparedStatement pIns1 = conn1.prepareStatement
("insert into t1 values (?,?)");
PreparedStatement pIns2 = conn2.prepareStatement
("insert into t1 values (?,?)");
PreparedStatement pIns3 = conn3.prepareStatement
("insert into t1 values (?,?)");
Note:
All tuning options, such as join ordering, indexes and locks, must match for the statement to be shared. Also, if the prepared statement references a temp table, it is only shared within a single connection."Preparing SQL statements and setting input parameters" shows how to prepare a statement and set input parameters using PreparedStatement methods. TimesTen also supports OUT and IN OUT parameters, for which you use java.sql.CallableStatement instead of PreparedStatement, as follows.
Use the method registerOutParameter() to register an OUT or IN OUT parameter, specifying the parameter position (position in the statement) and data type.
This is the standard method as specified in the CallableStatement interface:
void registerOutParameter(int parameterIndex, int sqlType, int scale)
Be aware, however, that if you use this standard version for CHAR, VARCHAR, NCHAR, NVARCHAR, BINARY, or VARBINARY data, TimesTen will allocate memory to hold the largest possible value. In many cases this is wasteful.
Instead, you can use the TimesTen extended interface TimesTenCallableStatement, which includes a registerOutParameter() signature that enables you to specify the maximum data length. For CHAR, VARCHAR, NCHAR, and NVARCHAR, the unit of length is number of characters. For BINARY and VARBINARY, it is bytes.
void registerOutParameter(int paramIndex, int sqlType, int ignore, //This parameter is ignored by TimesTen. int maxLength)
Use the appropriate CallableStatement method setXXX(), where XXX indicates the data type, to set the input value of an IN OUT parameter. Specify the parameter position and data value.
Use the appropriate CallableStatement method getXXX() to get the output value of an OUT or IN OUT 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 OUT parameter in a callable statement
This example shows how to use a callable statement with an OUT parameter. In the TimesTenCallableStatement instance, a PL/SQL block calls a function RAISESAL 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 RAISESAL (raise salary)
CallableStatement cstmt = conn.prepareCall
("BEGIN :newSalary := RAISESAL(:name, :inc); end;");
// Declare that the first param (newSalary) is a return (output) value of type int
cstmt.registerOutParameter(1, Types.INTEGER);
// Raise Leslie's salary by $2000 (she wanted $3000 but we held firm)
cstmt.setString(2, "LESLIE"); // name argument (type String) is the second param
cstmt.setInt(3, 2000); // raise argument (type int) is the third param
// Do the raise
cstmt.execute();
// Check warnings. If there are warnings, values of OUT parameters are undefined.
SQLWarning wn;
boolean warningFlag = false;
if ((wn = cstmt.getWarnings() ) != null) {
do {
warningFlag = true;
System.out.println(wn);
wn = wn.getNextWarning();
} while(wn != null);
}
// Get the new salary back
if (!warningFlag) {
int new_salary = cstmt.getInt(1);
System.out.println("The new salary is: " + new_salary);
}
// Close the statement and connection
cstmt.close();
conn.close();
...
TimesTen supports either of two modes for binding duplicate parameters in a SQL statement:
Oracle mode, where multiple occurrences of the same parameter name are considered to be distinct parameters.
Traditional TimesTen mode, as in earlier releases, where 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 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 concurrent 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).In the Oracle mode, 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.
In the TimesTen mode, 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.
The preceding discussion does not apply within PL/SQL. Instead, PL/SQL semantics apply, whereby you bind a value for each unique parameter. 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
REF CURSOR is a PL/SQL concept, where a REF CURSOR is a handle to a cursor over a SQL result set and can be passed between PL/SQL and an application. In TimesTen, the cursor can be opened in PL/SQL, then the REF CURSOR can be passed to the application for processing of the result set.
An application can receive a REF CURSOR, as an OUT parameter, as follows:
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).
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.
Important:
For passing REF CURSORs between PL/SQL and an application, TimesTen supports onlyOUT 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-10 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;
CallableStatement cstmt;
ResultSet cursor;
...
// Use a PL/SQL block to open the cursor.
cstmt = conn.prepareCall
(" begin open :x for select tblname,tblowner from tables; end;");
cstmt.registerOutParameter(1, TimesTenTypes.CURSOR);
cstmt.execute();
cursor = ((TimesTenCallableStatement)cstmt).getCursor(1);
// Use the cursor as you would any other ResultSet object.
while(cursor.next()){
System.out.println(cursor.getString(1));
}
// Close the statement
cstmt.close();
conn.close();
...
You can use a RETURNING INTO clause, referred to as DML returning, with an INSERT, UPDATE, or DELETE statement to return specified items from a row that was affected by the action. This eliminates the need for a subsequent SELECT statement and separate round trip, in case, for example, you want to confirm what was affected by the action.
With TimesTen, DML returning is limited to returning items from a single-row operation. The clause returns the items into a list of OUT parameters.
TimesTenPreparedStatement, an extension of the standard PreparedStatement interface, supports DML returning. Use the TimesTenPreparedStatement method registerReturnParameter() to register the return parameters.
void registerReturnParameter(int paramIndex, int sqlType)
As with the registerOutParameter() method discussed in "Working with OUT and IN OUT parameters", this method includes a signature that enables you to optionally specify a maximum size for CHAR, VARCHAR, NCHAR, NVARCHAR, BINARY, or VARBINARY data. This avoids possible inefficiency where TimesTen would otherwise allocate memory to hold the largest possible value. For CHAR, VARCHAR, NCHAR, and NVARCHAR, the unit of size is number of characters. For BINARY and VARBINARY, it is bytes.
void registerReturnParameter(int paramIndex, int sqlType, int maxSize)
Use the TimesTenPreparedStatement method getReturnResultSet() to retrieve the return parameters, returning a ResultSet instance.
Be aware of the following restrictions.
The getReturnResultSet() method must not be invoked more than once. Otherwise, the behavior is indeterminate.
ResultSetMetaData is not supported for the result set returned by getReturnResultSet().
Streaming methods such as getCharacterStream() are not supported for the result set returned by getReturnResultSet().
There is no batch support for DML returning.
SQL syntax and restrictions for the RETURNING INTO clause in TimesTen are documented as part of the "INSERT", "UPDATE", and "DELETE" documentation in Oracle TimesTen In-Memory Database SQL Reference.
Refer to "RETURNING INTO Clause" in Oracle Database PL/SQL Language Reference for general information about DML returning.
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.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;
...
// 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);
}
}
}
Each row in a table has a unique identifier known as its rowid. An application can retrieve the rowid of a row from the ROWID pseudocolumn. A rowid value can be represented in either binary or character format, with the binary format taking 12 bytes and the character format 18 bytes.
For Java 6, TimesTen supports the java.sql.RowId interface and Types.ROWID type.
You can use any of the following ResultSet methods to retrieve a rowid:
byte[] getBytes(int columnIndex)
String getString(int columnIndex)
Object getObject(int columnIndex)
Returns a String object in Java 5. Returns a RowId object in Java 6.
You can use any of the following PreparedStatement methods to set a rowid:
setBytes(int parameterIndex, byte[] x)
setString(int parameterIndex, String x)
setRowId(int parameterIndex, RowId x) (Java 6 only)
setObject(int parameterIndex, Object x)
Takes a String object in Java 5. Takes a String or RowId object in Java 6.
Note:
You cannot usegetBytes() or setBytes() for ROWID parameters that are PL/SQL parameters or passthrough parameters (parameters passed to Oracle when using the Oracle In-Memory 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 specification" in Oracle TimesTen In-Memory Database SQL Reference for additional information about rowids and the ROWID data type, including usage and lifecycle.
Note:
Oracle TimesTen In-Memory Database does not support the PL/SQL typeUROWID.TimesTen supports private and public synonyms (aliases) for database objects such as tables, views, sequences, and PL/SQL objects. Synonyms are often used for security to mask object names and object owners, or for convenience to simplify SQL statements.
To create a private synonym for table foo in your schema:
CREATE SYNONYM synfoo FOR foo;
To create a public synonym for foo:
CREATE PUBLIC SYNONYM pubfoo FOR foo;
A private synonym exists in the schema of a specific user and shares the same namespace as database objects such as tables, views, and sequences. A private synonym cannot have the same name as a table or other object in the same schema.
A public synonym does not belong to any particular schema, is accessible to all users, and can have the same name as any private object.
To create a synonym you must have the CREATE SYNONYM or CREATE PUBLIC SYNONYM privilege, as applicable. To use a synonym you must have appropriate privileges to access the underlying object.
For general information about synonyms, see "Understanding synonyms" in Oracle TimesTen In-Memory Database Operations Guide. For information about the CREATE SYNONYM and DROP SYNONYM statements, see "SQL Statements" in Oracle TimesTen In-Memory Database SQL Reference.
This section discusses autocommit and manual commits or rollbacks, assuming a JDBC Connection object myconn and Statement object mystmt.
A TimesTen connection has autocommit enabled by default, but it is recommended that you disable it. You can use the Connection method setAutoCommit() to enable or disable autocommit.
To disable autocommit:
myconn.setAutoCommit(false); // Report any SQLWarnings on the connection // See "Reporting errors and warnings"
If autocommit is disabled, you must use the Connection method commit() to manually commit transactions, or the rollback() method to roll back changes. For example:
myconn.commit();
Or:
myconn.rollback();
Note:
On some UNIX platforms it is necessary to setTHREADS_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 driver connection to the database, TimesTen functions share stack space with your application. In multithreaded environments, it is important to avoid overrunning the stack allocated to each thread because consequences can result that are unpredictable and difficult to debug. The amount of stack space consumed by TimesTen calls varies depending on the SQL functionality used. Most applications should set thread stack space to at least 16 KB on 32-bit systems and between 34 KB to 72 KB on 64-bit systems.
The amount of stack space allocated for each thread is specified by the operating system when threads are created. On Windows, you can use the TimesTen debug driver and link your application against the Visual C++ debug C library to enable stack probes that raise an identifiable exception if a thread attempts to grow its stack beyond the amount allocated.
Note:
In multithreaded applications, a thread that issues requests on different connection handles to the same database may encounter lock conflict with itself. TimesTen resolves these conflicts with lock timeouts.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;
Preceding sections discussed key features for managing TimesTen data. This section covers the following additional features:
TimesTen supports each of the following syntax formats from any of its programming interfaces to call PL/SQL procedures (procname) or PL/SQL functions (funcname) that are standalone or part of a package, or to call TimesTen built-in procedures (procname):
CALL procname[(argumentlist)] CALL funcname[(argumentlist)] INTO :returnparam CALL funcname[(argumentlist)] INTO ?
TimesTen JDBC also supports each of the following syntax formats:
{ CALL procname[(argumentlist)] }
{ ? = [CALL] funcname[(argumentlist)] }
{ :returnparam = [CALL] funcname[(argumentlist)] }
You can execute procedures and functions through the CallableStatement interface, with a prepare step first when appropriate (such as when a result set is returned).
The following example calls the TimesTen built-in procedure ttCkpt. (Also see Example 2-12 below for a more complete example with JDBC syntax.)
CallableStatement.execute("call ttCkpt")
The following example calls the TimesTen built-in procedure ttDataStoreStatus. A prepare call is used because this procedure produces a result set. (Also see Example 2-13 below for a more complete example with JDBC syntax.)
CallableStatement cStmt;
cStmt = conn.prepareCall("call ttDataStoreStatus");
cStmt.execute();
These examples call a PL/SQL procedure myproc with two parameters:
cStmt.execute("{ call myproc(:param1, :param2) }");
cStmt.execute("{ call myproc(?, ?) }");
The following shows several ways to call a PL/SQL function myfunc:
cStmt.execute("CALL myfunc() INTO :retparam");
cStmt.execute("CALL myfunc() INTO ?");
cStmt.execute("{ :retparam = myfunc() }");
cStmt.execute("{ ? = myfunc() }");
See "CALL" in Oracle TimesTen In-Memory Database SQL Reference for details about CALL syntax.
Note:
A user's own procedure takes precedence over a TimesTen built-in procedure with the same name.Example 2-12 Executing a ttCkpt call
This example calls the ttCkpt procedure to initiate a fuzzy checkpoint.
Connection conn;
CallableStatement cStmt;
.......
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-13 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();
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.
In TimesTen you can set the SqlQueryTimeout general connection attribute to specify the timeout period (in seconds) for any connection, and hence any statement. If you set SqlQueryTimeout in the DSN specification, its value becomes the default value for all subsequent connections to the database. 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 execute a large number of UPDATE, DELETE, or INSERT statements, 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-wait and aSqlQueryTimeout are specified, the lesser of the two values causes a timeout first. Regarding lock timeouts, in Oracle TimesTen In-Memory Database Reference you can refer to information about the ttLockWait built-in procedure in "ttLockWait" and about the LockWait general connection attribute in "LockWait". Or refer to "Check for deadlocks and timeouts" in the Oracle TimesTen In-Memory Database Troubleshooting Procedures Guide.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.
This section discusses features related to the use of IMDB Cache:
Note:
TheOraclePassword attribute maps to the Oracle password. You can use the TimesTenDataSource method setOraclePassword() to set the Oracle password. See "Connect to the database" for an example.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 includes samples of code that accomplish these steps:
Creation of a prepared statement (a PreparedStatement instance thePassThroughStatement) that calls ttOptSetFlag using a bind parameter for passthrough level.
Definition of 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();
}
Also see "ttOptSetFlag" in Oracle TimesTen In-Memory Database Reference for more information about that built-in procedure, and "Setting a passthrough level" in Oracle In-Memory Database Cache User's Guide for information about the meaning and effect of each passthrough level.
In TimesTen, following the execution of a FLUSH CACHE GROUP, LOAD CACHE GROUP, REFRESH CACHE GROUP, or UNLOAD CACHE GROUP statement, the Statement method getUpdateCount() returns the number of cache instances that were flushed, loaded, refreshed, or unloaded.
For related information, see "Determining the number of cache instances affected by an operation" in Oracle In-Memory Database Cache User's Guide.
For applications that have very predictable transactional dependencies and do not require the commit order on the replica database to be the same as that on the originating database, TimesTen supports parallel replication. This feature allows replication of multiple user-specified tracks of transactions in parallel. See "Increasing replication throughput for other replication schemes" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide for general information about parallel replication.
User-specified parallel replication is enabled through the TimesTen data store attributes ReplicationParallelism and ReplicationApplyOrdering, as described in "Data store attributes" in Oracle TimesTen In-Memory Database Reference. The track number of transactions for a connection can be specified through the TimesTen general connection attribute ReplicationTrack, the ALTER SESSION parameter REPLICATION_TRACK, or in JDBC through the following TimesTenConnection method:
void setReplicationTrack(int track)
TimesTenConnection also has the corresponding getter method:
int getReplicationTrack()
Note:
The track number setting will hold for the lifetime of the connection, unless it is specifically reset.To find the track number that is in use, you can call the TimesTenConnection method getReplicationTrack() or call the TimesTen built-in procedure ttConfiguration, which returns current TimesTen attribute settings, including ReplicationTrack.
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, SQL DML statement, or SQL 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:
Connecting to a database. Refer to "Access control for connections".
Setting connection attributes. Refer to "Create a connection URL for the database and specify connection attributes".
Configuring and managing JMS/XLA. Refer to "Access control impact on XLA".
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.
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.
TimesTen can return a fatal error, a non-fatal error, or a warning.
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 transaction and, to avoid out-of-memory conditions, disconnect from the database. Shared memory from the old TimesTen instance will not be freed until all active connections at the time of the error have disconnected.
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.
If no checkpoint or transaction log files exist and the AutoCreate attribute is set, TimesTen creates an empty database.
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 and requires the application to check for and identify them.
When a database is affected by a non-fatal error, an error may be returned and the application should take appropriate action. 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.
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".
Note:
If aResultSet, Statement, PreparedStatement, CallableStatement or Connection operation results in a database error, it is a good practice to call the close() method for that object.TimesTen returns warnings when something unexpected occurs that you may want to know about. 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
You should always include code that checks for warnings, as they can indicate application problems.
You should check for and report all errors and warnings that can be returned on every call. This saves considerable time and effort during development and debugging. A SQLException object is generated if there are one or more database access errors and a SQLWarning object is generated if there are one or more warning messages. A single call may return multiple errors or warnings or both, so your application should report all errors or warnings in the returned SQLException or SQLWarning objects.
Multiple errors or warnings are returned in linked chains of SQLException or SQLWarning objects. Example 2-14 and Example 2-15 demonstrate how you might iterate through the lists of returned SQLException and SQLWarning objects to report all of the errors and warnings, respectively.
Example 2-14 Printing exceptions
This 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-15 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;
}
In some situations it may be desirable to respond to a specific SQL state or TimesTen error code. You can use the SQLException method getSQLState() to return the SQL99 state error string, and getErrorCode() to return TimesTen error codes, as shown in Example 2-16.
Also refer to the entry for TimesTenVendorCode in Oracle TimesTen In-Memory Database JDBC Extensions Java API Reference for error information.
Example 2-16 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
In some situations, such as recovering from a deadlock or timeout condition, you may want to explicitly roll back the transaction using the Connection method rollback(), as in the following example.
Example 2-17 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();
}
The XACT_ROLLBACKS column of the SYS.MONITOR table indicates the number of transactions that were rolled back. Refer to "SYS.MONITOR" in Oracle TimesTen In-Memory Database System Tables and Limits Reference for additional information.
A transaction rollback consumes resources and the entire transaction is in effect wasted. To avoid unnecessary rollbacks, design your application to avoid contention and check the application or input data for potential errors before submitting it.
Note:
If your application fails in the middle of an active transaction, TimesTen automatically rolls back the transaction.Automatic client failover, used in High Availability scenarios when failure of a TimesTen node results in failover (transfer) to an alternate node, automatically reconnects applications to the new 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 mode. 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.
You can refer to "Automatic client failover" in Oracle TimesTen In-Memory Database C Developer's Guide for related information. You can also refer to "Using Oracle Clusterware to Manage Active Standby Pairs" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide for information about Oracle Clusterware.
This section discusses general TimesTen JDBC features related to client failover, and functionality relating specifically to pooled connections.
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 will be 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:
The ClientFailoverEvent class. This class is used to represent events that occur during a client failover: begin, end, abort, or retry.
The ClientFailoverEventListener interface. An application interested in client failover events must include 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).
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.
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 would not implement and register an instance of ClientFailoverEventListener, that being a TimesTen extension.
If, in a failover situation, an application attempts to use objects created on the failed connection, then JDBC will throw a SQL exception. The vendor-specific exception code will be set to TimesTenVendorCode.TT_ERR_FAILOVERINVALIDATION.
Detecting a failover through this mechanism is referred to as synchronous detection. The following example demonstrates this.
Example 2-18 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 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:
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-19 Asynchronous detection of automatic client failover
private class MyCFListener implements ClientFailoverEventListener {
// Skeletal example
/* Applications can build state system to track states during failover.
You may want to add methods that talks about readiness of this Connection
for processing.
*/
public void notify(ClientFailoverEvent event) {
// Process connection failover type
switch(event.getTheFailoverType()) {
case TT_FO_CONNECTION:
// Process session fail over
System.out.println("This should be a connection failover type " +
event.getTheFailoverType());
break;
default:
break;
}
// Process connection failover events
switch(event.getTheFailoverEvent()) {
case BEGIN:
System.out.println("This should be a BEGIN event " +
event.getTheFailoverEvent());
/* Applications cannot use Statement, PreparedStatement, ResultSet,
etc. created on the failed Connection any longer.
*/
// ...
break;
case END:
System.out.println("This should be an END event " +
event.getTheFailoverEvent());
/* Applications may want to re-create Statement and PreparedStatement
objects at this point as needed.
*/
break;
case ABORT:
System.out.println("This should be an ABORT event " +
event.getTheFailoverEvent());
break;
case ERROR:
System.out.println("This should be an ERROR event " +
event.getTheFailoverEvent());
break;
default:
break;
}
}
}
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
At runtime you must register an instance of your failover event listener class with the TimesTen connection object, so that TimesTen will be 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-20 Registering the client failover listener
try {
// Assume this is a client/server connection; register for conn failover.
Class.forName("com.timesten.jdbc.TimesTenClientDriver");
String url = "jdbc:timesten:client:" + theDsn;
theConnection = (TimesTenConnection)DriverManager.getConnection(url);
theConnection.addConnectionEventListener(theCFListener);
// ...
/* Additional logic goes here; connection failover listener will be
called if there is a fail over.
*/
// ...
}
catch (ClassNotFoundException cnfex) {
cnfex.printStackTrace();
}
catch (SQLException sqlex) {
sqlex.printStackTrace();
}
The TimesTenConnection interface defines the following method to deregister a failover event listener instance:
void removeConnectionEventListener (ClientFailoverEventListener listener)