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.

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:

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:

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:

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.

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:

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:

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:

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:

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:

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:

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:

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.

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 (In the case of a FETCH statement, the connection context and execution context originally used to populate the iterator is used.)

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

TIP: 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:

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:

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.

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.