8.2 Execution Contexts
A SQLJ executable statement contains an embedded SQL statement, which is said to run within an execution context. Each SQLJ executable statement uses an execution context that is either implicitly or explicitly associated with it. An execution context is an object of the class sqlj.runtime.ExecutionContext. The ExecutionContext class provides a number of useful methods that you can use to control how the SQL statement is run. You can also use these methods to get information on the results of the previously executed SQL statement, and to enable batch processing (see Chapter 10 for details on batch processing).
Execution contexts are also used in multithreaded SQLJ applications, as you will see later in this chapter. An execution context should not be confused with a connection context: a connection context is used to specify a connection to a database; an execution context is used to run a SQL statement. I know all this may sound confusing, but it will make more sense once you look at some examples.
8.2.1 The Default Execution Context
A default execution context is created for every connection context. When you create a default connection context via a call to the Oracle.connect( ) method, a default execution context is also created. Therefore, if you create five connection contexts, you will also get five execution contexts.
No special effort on your part is required to use a connection's default execution context when executing a SQL statement. For example, the following statement updates customer #1 using the default execution context associated with the default connection context:
#sql { UPDATE customers SET first_name = 'John', last_name = 'Doe' WHERE id = 1 }; There is nothing special about this example: it simply uses the default execution context associated with the default connection context. The following example is another variation on this same theme, and uses the default execution context associated with the connection context conn_context, created earlier in this chapter, to perform the same update:
#sql [conn_context] { UPDATE customers SET first_name = 'John', last_name = 'Doe' WHERE id = 1 }; You can retrieve the default execution context for a connection by using the getExecutionContext( ) method of the connection context object. The following statement declares an execution context named exec_context, and retrieves the default execution context from the default connection context using the getDefaultContext( ) and getExecutionContext( ) methods:
ExecutionContext exec_context = DefaultContext.getDefaultContext.getExecutionContext( );
The following example does much the same thing, but uses a connection that you've created. It declares an execution context named exec_context2, and retrieves the default execution context from conn_context using the getExecutionContext( ) method:
ExecutionContext exec_context2 = conn_context.getExecutionContext( );
Once you have your execution context, you can use the execution context methods. These are described in the next section.
8.2.2 Execution Context Methods
The ExecutionContext class provides a number of useful methods that may be used in your SQLJ programs by importing the sqlj.runtime.ExecutionContext class. The more useful methods in the ExecutionContext class are as follows:
-
getWarnings( )
-
getUpdateCount( )
-
setQueryTimeout( )
-
getQueryTimeout( )
-
setMaxRows( )
-
getMaxRows( )
The following sections describe each of these methods in detail.
8.2.2.1 getWarnings( )
The getWarnings( ) method returns a java.sql.SQLWarning object that contains the first warning from the most recently executed SQLJ statement within the execution context. The following statement declares a SQLWarning object named sql_warning and copies the results of a call to the getWarnings( ) method into it:
SQLWarning sql_warning = exec_context.getWarnings( );
The SQLWarning class contains the method getSQLState( ), which returns a string containing the text of the SQL warning. The following statement makes use of this method to display the SQL warning string for the sql_warning object just created:
System.out.println(sql_warning.getSQLState( ));
8.2.2.2 getUpdateCount( )
The getUpdateCount( ) method returns an int value indicating the number of rows modified by the last SQLJ statement executed within the specified execution context. For example, the following statement displays the number of rows modified by the last SQLJ statement executed in the exec_context execution context:
System.out.println(exec_context.getUpdateCount( ));
8.2.2.3 setQueryTimeout( )
The setQueryTimeout( ) method changes the amount of time a query will wait before a timeout occurs; setQueryTimeout( ) accepts an int value that represents the timeout duration in seconds. The default is 0, which means that there is no timeout. The following statement sets the query timeout to 30 seconds for the execution context named exec_context:
exec_context.setQueryTimeout(30);
8.2.2.4 getQueryTimeout( )
The getQueryTimeout( ) method returns an int value that represents the amount of time in seconds a query will wait before a timeout occurs. The following statement displays the query timeout for the execution context named exec_context:
System.out.println(exec_context.getQueryTimeout( ));
8.2.2.5 setMaxRows( )
The setMaxRows( ) method changes the maximum number of rows that may be returned by an iterator. The default is 0, which means that there is no maximum number of rows set. The following statement sets the maximum number of rows to 30 for the execution context named exec_context:
exec_context.setMaxRows(30);
8.2.2.6 getMaxRows( )
The getMaxRows( ) method returns an int value that represents the maximum number of rows that may be returned by an iterator. The following statement displays the maximum number of rows that the exec_context execution context allows to be stored in an iterator:
System.out.println(exec_context.getMaxRows( ));
8.2.3 Creating a New Execution Context
Execution contexts may be created using the ExecutionContext class. As you will soon see, creating execution contexts is very important when writing multithreaded programs. The following statement creates an execution context named exec_context:
ExecutionContext exec_context = new ExecutionContext( );
When you create an execution context, it isn't necessarily tied to a particular connection context: you can use it with any connection context. The only execution context tied to a particular connection context is the default execution context for that connection context. The next section shows you how to specify which execution context to use in a SQLJ statement.
8.2.4 Specifying an Execution Context
You can specify connection contexts and execution contexts together in an embedded SQL operation. The following syntax illustrates how to do this:
#sql [connection_context_name, execution_context_name] { SQL_statement }; The syntax elements are as follows:
- connection_context_name
-
The name of the connection context to use when executing the statement
- execution_context_name
-
The name of the execution context in which to execute the statement
- SQL_statement
-
The embedded SQL statement that you want to execute[2]
[2] In the case of a FETCH statement, the connection context and execution context originally used to populate the iterator is used.
 | You can specify both a connection context and an execution context. If you do, the connection context must come first. |
|
Both the connection context name and the execution context name are optional. If you specify a connection context without specifying an execution context, then the default execution context for that connection context is used to perform the SQL statement. Similarly, if you specify an execution context without specifying a connection context, then the default connection context is used. If you specify neither a connection context nor an execution context, then the default connection and execution contexts are used.
| SQLJ statements that use the same connection context share the same database transaction even if they use different execution contexts. |
|
In the following example, only an execution context is specified. The execution context exec_context is used together with the default connection context to update customer #1:
#sql [exec_context] { UPDATE customers SET first_name = 'John', last_name = 'Doe' WHERE id = 1 }; In this next example, both a connection context and an execution context are specified. The connection context conn_context is used along with the execution context exec_context to update customer #2:
#sql [conn_context, exec_context] { UPDATE customers SET first_name = 'Jean', last_name = 'Smith' WHERE id = 2 }; Because this example uses a connection context different from that used in the first example, it also uses a different database transaction. The following example uses the same connection context as the previous example (conn_context), but a different execution context:
#sql [conn_context, exec_context2] { UPDATE customers SET first_name = 'Fred', last_name = 'Doe' WHERE id = 2 }; Because this example uses the same connection context as the previous one, it also shares the same database transaction. Therefore, the customer name for customer #2 will be changed from "Jean Smith" to "Fred Doe", overwriting the previous update.
8.2.5 Example Program: ContextExample2.sqlj
This section contains a complete program that illustrates the transactional behavior of execution contexts. The program ContextExample2.sqlj (Example 8-2) performs the following major steps:
-
Creates a connection context, named conn_context, that connects to the fundamental_user schema.
-
Creates two execution contexts, named exec_context and exec_context2.
-
Displays all rows in the customers table using the program's displayCustomers( ) method.
-
Updates the customer name to "John Doe" for customer #1 using conn_context and exec_context.
-
Updates the customer name to "Jean Smith" for customer #1 using conn_context and exec_context2. This overwrites the previous update, illustrating that SQLJ statements that use the same connection context share the same database transaction.
-
Displays all the rows in the customers table, showing the change made to customer #1.
-
Rolls back the changes made to the customers table.
Example 8-2. ContextExample2.sqlj
/* The program ContextExample2.sqlj illustrates how to create and use execution context objects. */ import java.sql.*; import oracle.sqlj.runtime.Oracle; import sqlj.runtime.ref.DefaultContext; import sqlj.runtime.ExecutionContext; public class ContextExample2 { // declare the iterator class #sql private static iterator CustomerIteratorClass ( int id, String first_name, String last_name ); public static void main(String [] args) { try { // connect to database using a connection context // named conn_context DefaultContext conn_context = Oracle.getConnection( "jdbc:oracle:thin:@localhost:1521:orcl", "fundamental_user", "fundamental_password" ); // create an execution context named exec_context ExecutionContext exec_context = new ExecutionContext( ); // create a second execution context named exec_context2 ExecutionContext exec_context2 = new ExecutionContext( ); // display all customers using conn_context and exec_context System.out.println("List of customers using connection context " + "conn_context and execution context exec_context."); displayCustomers(conn_context, exec_context); // update customer using conn_context and exec_context System.out.println("Updating customer 1 name to John Doe using " + "conn_context and exec_context."); #sql [conn_context, exec_context] { UPDATE customers SET first_name = 'John', last_name = 'Doe' WHERE id = 1 }; // update customer using conn_context and exec_context2, System.out.println("Updating customer 1 name to Jean Smith using " + "conn_context and exec_context2."); #sql [conn_context, exec_context2] { UPDATE customers SET first_name = 'Jean', last_name = 'Smith' WHERE id = 1 }; // display all customers using the conn_context and exec_context System.out.println("List of customers using conn_context " + "and exec_context."); displayCustomers(conn_context, exec_context); // perform an explicit ROLLBACK using conn_context and exec_context #sql [conn_context, exec_context] { ROLLBACK }; // close the connection context conn_context.close( ); } catch ( SQLException e ) { System.err.println("SQLException " + e); System.exit(1); } } // end of main( ) private static void displayCustomers( DefaultContext conn_context, ExecutionContext exec_context ) throws SQLException { // instantiate a named iterator object CustomerIteratorClass customer_iterator; #sql [conn_context, exec_context] customer_iterator = { SELECT id, first_name, last_name FROM customers ORDER BY id }; // access the contents of the iterator while (customer_iterator.next( )) { System.out.println("Customer:"); System.out.println("id = " + customer_iterator.id( )); System.out.println("first_name = " + customer_iterator.first_name( )); System.out.println("last_name = " + customer_iterator.last_name( )); } // end of while loop // close the iterator customer_iterator.close( ); } // end of displayCustomers( ) } The output from ContextExample2.sqlj is as follows:
List of customers using connection context conn_context and execution context exec_context. Customer: id = 1 first_name = John last_name = Smith Customer: id = 2 first_name = Cynthia last_name = Stevens Customer: id = 3 first_name = Steve last_name = Seymour Customer: id = 4 first_name = Gail last_name = Williams Customer: id = 5 first_name = Doreen last_name = Heyson Updating customer 1 name to John Doe using conn_context and exec_context. Updating customer 1 name to Jean Smith using conn_context and xec_context2. List of customers using conn_context and exec_context. Customer: id = 1 first_name = Jean last_name = Smith Customer: id = 2 first_name = Cynthia last_name = Stevens Customer: id = 3 first_name = Steve last_name = Seymour Customer: id = 4 first_name = Gail last_name = Williams Customer: id = 5 first_name = Doreen last_name = Heyson
