Execution Contexts

An execution context is an instance of the sqlj.runtime.ExecutionContext class and provides a context in which SQL operations are executed. An execution context instance is associated either implicitly or explicitly with each SQL operation in your SQLJ application.

The ExecutionContext class contains methods for execution control, execution status, and execution cancellation, which function in the following ways:

• Execution control operations of a given execution context instance modify the semantics of subsequent SQL operations executed using that instance.

• Execution status operations of a given execution context instance describe the results of the most recent SQL operation that completed using that instance.

• Execution cancellation operations of a given execution context instance terminate the SQL operation that is currently executing using that instance.

Relation of Execution Contexts to Connection Contexts

Each connection context instance implicitly has its own default execution context instance, which you can retrieve by using the getExecutionContext() method of the connection context instance.

A single execution context instance will be sufficient for a connection context instance except in the following circumstances:

• You are using multiple threads with a single connection context instance.

  When using multithreading, each thread must have its own execution context instance.

• You want to use different SQL execution control operations on different SQLJ statements that employ the same connection context instance.

• You want to retain different sets of SQL status information from multiple SQL operations that employ the same connection context instance.

  As you execute successive SQL operations that employ the same execution context instance, the status information from each operation overwrites the status information from the previous operation.

Although execution context instances may appear to be associated with connection context instances (given that each connection context instance has its own default execution context instance, and that you can specify a connection context instance and an execution context instance together for a particular SQLJ statement), they actually operate independently. You can employ different execution context instances in statements that employ the same connection context instance, and vice versa.

For example, you would use multiple execution context instances with a single connection context instance if you were using multithreading (with a separate execution context instance for each thread). And you would use multiple connection context instances with a single execution context instance if you wanted the same set of SQL control parameters to apply to all of the connection context instances. (See "ExecutionContext Methods" for information about SQL control settings.)

To employ different execution context instances with a single connection context instance, you must create additional instances of the ExecutionContext class and specify them appropriately with your SQLJ statements. This is described in "Creating and Specifying Execution Context Instances".

Creating and Specifying Execution Context Instances

To employ an execution context instance other than the default with a given connection context instance, you must construct another execution context instance. This is convenient, as there are no input parameters for the ExectionContext constructor:

ExecutionContext myExecCtx = new ExecutionContext();

You can then specify this execution context instance for use with any particular SQLJ statement, much as you would specify a connection context instance. The general syntax is as follows:

#sql [<conn_context><, ><exec_context>] { SQL operation };

For example, if you declare and instantiate a connection context class MyConnCtxClass and create an instance myConnCtx, you can use the following statement:

#sql [myConnCtx, myExecCtx] { DELETE FROM emp WHERE sal > 30000 };

You can subsequently use different execution context instances with myConnCtx, or different connection context instances with myExecCtx.

You can optionally specify an execution context instance while using the default connection context instance, as follows:

#sql [myExecCtx] { DELETE FROM emp WHERE sal > 30000 };

Execution Context Synchronization

ExecutionContext methods (discussed in "ExecutionContext Methods") are all synchronized methods. Therefore, generally speaking, anytime a SQLJ statement tries to use an execution context instance that is already in use, the second statement will be blocked until the first statement completes.

In a client application, this typically involves multithreading situations. A thread that tries to use an execution context instance currently in use by another thread will be blocked.

To avoid such blockage, you must specify a separate execution context instance for each thread that you use, as discussed in "Multithreading in SQLJ".

The exception to the preceding discussion is for recursion, which is typically encountered only in the server. Multiple SQLJ statements are allowed to simultaneously use the same execution context instance if this situation results from recursive calls. An example of this is where a SQLJ stored procedure (or function) has a call to another SQLJ stored procedure (or function). If both use the default execution context instance, as is typical, then the SQLJ statements in the second procedure will use this execution context while the SQLJ call statement from the first procedure is also still using it. This is allowed, and is further discussed in "Recursive SQLJ Calls in the Server".

ExecutionContext Methods

This section lists the methods of the ExecutionContext class, categorized as status methods, control methods, and cancellation methods.

Status Methods

Use the following methods of an execution context instance to obtain status information about the most recent SQL operation that completed using that instance:

• getWarnings()--Returns a java.sql.SQLWarning object containing the warnings reported by the most recent SQL operation that completed using this execution context instance. The SQLWarning object is initially created for the first warning reported, and any subsequent warnings are chained to the same object. The SQLWarning object ultimately represents all warnings generated during the execution of the SQL operation and the subsequent outputting of parameters to the output host expressions.

• getUpdateCount()--Returns an int specifying the number of rows updated by the last SQL operation that completed using this execution context instance. Zero (0) is returned if the last SQL operation was not a DML statement. The constant QUERY_COUNT is returned if the last SQL operation produced an iterator or result set. The constant EXCEPTION_COUNT is returned if the last SQL operation terminated before completing execution, or if no operation has yet been attempted using this execution context instance.

• getNextResultSet()--Under some circumstances a SQL procedure call can return multiple result sets (these are known as side-channel result sets and are not supported by Oracle8i). In such a case, this method returns the next result set, in a JDBC ResultSet object. Further calls move to and return subsequent result sets. When there are no further side-channel result sets, null is returned. This method implicitly closes each open result set that it had previously obtained.

Control Methods

Use the following methods of an execution context instance to control the operation of future SQL operations executed using that instance (operations that have not yet started).

• getMaxFieldSize()--Returns an int specifying the maximum amount of data (in bytes) that would be returned from a SQL operation subsequently using this execution context instance. (This can be modified using the setMaxFieldSize() method.) This applies only to columns of type BINARY, VARBINARY, LONGVARBINARY, CHAR, VARCHAR, or LONGVARCHAR.

  By default this parameter is set to 0, meaning there is no size limit.

• setMaxFieldSize()--Takes an int as input to modify the field-size maximum.

• getMaxRows()--Returns an int specifying the maximum number of rows that can be contained by any SQLJ iterator or JDBC result set created using this execution context instance. (This can be modified using the setMaxRows() method.) If the limit is exceeded, the excess rows are silently dropped without any error report or warning.

  By default this parameter is set to 0, meaning there is no row limit.

• setMaxRows()--Takes an int as input to modify the row maximum.

• getQueryTimeout()--Returns an int specifying the timeout limit, in seconds, for any SQL operation that uses this execution context instance. (This can be modified using the setQueryTimeout() method.) If a SQL operation exceeds this limit, a SQL exception is thrown.

  By default this parameter is set to 0, meaning there is no query timeout limit.

• setQueryTimeout()--Takes an int as input to modify the query timeout limit.

Cancellation Method

The following method can be used to cancel SQL operations in a multithreading environment.

• cancel()--This method is used by one thread to cancel a SQL operation currently being executed by another thread. It cancels the most recent operation that has started but not completed using this execution context instance. This method has no effect if no statement is currently being executed using this execution context instance.

Example: Use of ExecutionContext Methods

The following code demonstrates the use of some ExecutionContext methods.

ExecutionContext execCtx =
   DefaultContext.getDefaultContext().getExecutionContext();

// Wait only 3 seconds for operations to complete
execCtx.setQueryTimeout(3);

// delete using execution context of default connection context
#sql { DELETE FROM emp WHERE sal > 10000 };

System.out.println
     ("removed " + execCtx.getUpdateCount() + " employees");

Relation of Execution Contexts to Multithreading

Do not use multiple threads with a single execution context. If you do, and two SQLJ statements try to use the same execution context simultaneously, the second statement will be blocked until the first statement completes. Furthermore, status information from the first operation will likely be overwritten before it can be retrieved.

Therefore, if you are using multiple threads with a single connection context instance, perform the following:

• Instantiate a unique execution context instance for use with each thread.

• Specify execution contexts with your #sql statements so that each thread uses its own execution context (see "Specifying an Execution Context" above).

If you are using a different connection context instance with each thread, then no instantiation and specification of execution context instances is necessary, because each connection context instance implicitly has its own default execution context instance.

See "Multithreading in SQLJ" for more information about multithreading.

---