7
Advanced Language Features

This chapter discusses advanced SQLJ language features for use in coding your application. For more basic topics, see Chapter 3, "Basic Language Features".

The following topics are discussed:

Connection Contexts

SQLJ supports the concept of connection contexts, allowing strongly typed connections for use with different sets of SQL entities. You can think of a connection context as being associated with a particular set of SQL entities such as tables, views, and stored procedures. SQLJ lets you declare additional connection context classes so that you can use each class for connections that use a particular set of SQL entities. Different instances of a single connection context class are not required to use the same physical entities or connect to the same schema, but will at least use sets of entities with the same names and datatypes.

Note:

For an overview of connection basics, focusing on situations where you are using just a single set of SQL entities and a single connection context class, see "Connection Considerations".

Connection Context Concepts

If your application uses different sets of SQL entities, then you will typically want to declare and use one or more additional connection context classes, as discussed in "Overview of SQLJ Declarations". Each connection context class can be used for a particular set of interrelated SQL entities, meaning that all the connections you define using a particular connection context class will use tables, views, stored procedures, and so on, that have the same names and use the same datatypes.

An example of a set of SQL entities is the set of tables and stored procedures used by the Human Resources department. Perhaps they use tables EMPLOYEES and DEPARTMENTS and stored procedures CHANGE_DEPT and UPDATE_HEALTH_PLAN. Another set of SQL entities might be the set of tables and procedures used by the Payroll department, perhaps consisting of the table EMPS (another table of employees, but different than the one used by HR) and the stored procedures GIVE_RAISE and CHANGE_WITHHOLDING.

The advantage in tailoring connection context classes to sets of SQL entities is in the degree of online semantics-checking that this allows. Online checking verifies that all the SQL entities appearing in SQLJ statements that use a given connection context class match SQL entities found in the exemplar schema used during translation. An exemplar schema is a database account that SQLJ connects to for online checking of all the SQLJ statements that use a particular connection context class. You provide exemplar schemas to the translator through the SQLJ command-line -user, -password, and -url options. (See "Connection Options" for information about these options.) An exemplar schema might or might not be the same account your application will use at runtime.

If you have SQLJ statements that use a broad and perhaps unrelated group of SQL entities, but you use only a single connection context class for these statements, then the exemplar schema you provide must be very general. It must contain all the tables, views, and stored procedures used throughout all the statements. Alternatively, if all the SQLJ statements using a given connection context class use a tight, presumably interrelated, set of SQL entities, then you can provide a more specific exemplar schema that allows more thorough and meaningful semantics-checking.

Notes:

Be aware that a connection context class declaration does not define a set of SQL entities to be used with the declared connection context class, and it is permissible to use the same connection context class for connections that use disparate and unrelated sets of entities. How you use your connection context classes is at your discretion. All that limits the SQL entities you can use with a particular connection context class are the set of entities available in the exemplar schema (if you use online semantics-checking during translation) and the set of entities available in the schema you connect to at runtime, using instances of the connection context class.
If you use qualified SQL names in your application--names such as SCOTT.EMP, which specifies the schema where the entity resides--then the exemplar schema (if you use online checking) and runtime schema must have permission to access resources by these fully qualified names.
It is possible to use a single connection context class, even for connections to databases from different vendors, as long as each schema you connect to has entities that are accessible by the same names and that use compatible datatypes.

Connection Context Logistics

Declaring a connection context class results in the SQLJ translator defining a class for you in the translator-generated code. In addition to any connection context classes that you declare, there is always the default connection context class:

sqlj.runtime.ref.DefaultContext

When you construct a connection context instance, specify a particular schema (user name, password, and URL) and a particular session and transaction in which SQL operations will execute. You typically accomplish this by specifying a user name, password, and database URL as input to the constructor of the connection context class. The connection context instance manages the set of SQL operations performed during the session.

In each SQLJ statement, you can specify a connection context instance to use, as discussed in "Specifying a Connection Context Instance for a SQLJ Clause".

The following example shows basic declaration and use of a connection context class, MyContext, to connect to two different schemas. For typical usage, assume these schemas include a set of SQL entities with common names and datatypes.

Declaration:

#sql context MyContext;

Executable code:

MyContext mctx1 = new MyContext
     ("jdbc:oracle:thin@localhost:1521:ORCL", "scott", "tiger", false);
MyContext mctx2 =  new MyContext
     ("jdbc:oracle:thin@localhost:1521:ORCL", "brian", "mypasswd", false);

Note that connection context class constructors specify a boolean auto-commit parameter. This is further discussed in "More About Declaring and Using a Connection Context Class".

In addition, note that you can connect to the same schema with different connection context instances. In the example above, both mctx1 and mctx2 could specify scott/tiger if desired. During runtime, however, one connection context instance would not see changes to the database made from the other until the changes are committed. The only exception to this would be if both connection context instances were created from the same underlying JDBC connection instance. (One of the constructors of any connection context class takes a JDBC connection instance as input.)

More About Declaring and Using a Connection Context Class

This section gives a detailed example of how to declare a connection context class, then define a database connection using an instance of the class.

A connection context class has constructors for opening a connection to a database schema, given any of the following (as with the DefaultContext class):

URL (String), user name (String), password (String), auto-commit (boolean)
URL (String), java.util.Properties object, auto-commit (boolean)
URL (String fully specifying connection and including user name and password), auto-commit setting (boolean)
JDBC connection object (Connection)

SQLJ connection context object

Notes:

When using the constructor that takes a JDBC connection object, do not initialize the connection context instance with a null JDBC connection.
The auto-commit setting determines whether SQL operations are automatically committed. For more information, see "Basic Transaction Control".
If a connection context class is declared with a data source with clause, then it incorporates a different set of constructors. See "Standard Data Source Support" for more information.

Declaring the Connection Context Class

The following declaration creates a connection context class:

#sql context OrderEntryCtx <implements_clause> <with_clause>;

This results in the SQLJ translator generating a class that implements the sqlj.runtime.ConnectionContext interface and extends some base class (probably an abstract class) that also implements the ConnectionContext interface. This base class would be a feature of the particular SQLJ implementation you are using.

The implements clause and with clause are optional, specifying additional interfaces to implement and variables to define and initialize, respectively. See "Declaration IMPLEMENTS Clause" and "Declaration WITH Clause". For information about data source with clauses in particular, see "Standard Data Source Support".

The following is an example of what the SQLJ translator generates (with method implementations omitted):


class OrderEntryCtx implements sqlj.runtime.ConnectionContext 
      extends ...
{ 
   public OrderEntryCtx(String url, Properties info, boolean autocommit)
          throws SQLException {...} 
   public OrderEntryCtx(String url, boolean autocommit) 
          throws SQLException {...}   
   public OrderEntryCtx(String url, String user, String password, 
          boolean autocommit) throws SQLException {...} 
   public OrderEntryCtx(Connection conn) throws SQLException {...} 
   public OrderEntryCtx(ConnectionContext other) throws SQLException {...} 

   public static OrderEntryCtx getDefaultContext() {...} 
   public static void setDefaultContext(OrderEntryCtx ctx) {...} 
}

Creating a Connection Context Instance

Continuing the preceding example, instantiate the OrderEntryCtx class with the following syntax:

OrderEntryCtx myOrderConn = new OrderEntryCtx
                            (url, username, password, autocommit);

For example:

OrderEntryCtx myOrderConn = new OrderEntryCtx
   ("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger", true);

This is accomplished in the same way as instantiating the DefaultContext class. All connection context classes, including DefaultContext, have the same constructor signatures.

Notes:

You typically must register your JDBC driver prior to constructing a connection context instance. See "Driver Selection and Registration for Runtime".
If a connection context class is declared with a data source with clause, then it incorporates a different set of constructors. See "Standard Data Source Support" for more information.

Specifying a Connection Context Instance for a SQLJ Clause

Recall that the basic SQLJ statement syntax is as follows:

#sql <[<conn><, ><exec>]> { SQL operation };

Specify the connection context instance inside square brackets following the #sql token. For example, in the following SQLJ statement, the connection context instance is myOrderConn from the previous example:

#sql [myOrderConn] { UPDATE TAB2 SET COL1 = :w WHERE :v < COL2 };

In this way, you can specify an instance of either the DefaultContext class or any declared connection context class.

Closing a Connection Context Instance

It is advisable to close all connection context instances when you are done. Each connection context class includes a close() method, as discussed for the DefaultContext class in "Closing Connections".

In closing a connection context instance that shares the underlying connection with another connection instance, you might want to keep the underlying connection open. See "Closing Shared Connections".

Example of Multiple Connection Contexts

The following is an example of a SQLJ application using multiple connection contexts. It implicitly uses an instance of the DefaultContext class for one set of SQL entities, and uses an instance of the declared connection context class DeptContext for another set of SQL entities.

This example uses the static Oracle.connect() method to establish a default connection, then constructs an additional connection by using the static Oracle.getConnection() method to pass another DefaultContext instance to the DeptContext constructor. As previously mentioned, this is just one of several ways you can construct a SQLJ connection context instance.

import java.sql.SQLException;
import oracle.sqlj.runtime.Oracle;

// declare a new context class for obtaining departments
#sql context DeptContext;

#sql iterator Employees (String ename, int deptno);

class MultiSchemaDemo 
{
  public static void main(String[] args) throws SQLException 
  {
    /* if you're using a non-Oracle JDBC Driver, add a call here to
       DriverManager.registerDriver() to register your Driver
    */

    // set the default connection to the URL, user, and password
    // specified in your connect.properties file
    Oracle.connect(MultiSchemaDemo.class, "connect.properties");

    // create a context for querying department info using
    // a second connection
    DeptContext deptCtx = 
      new DeptContext(Oracle.getConnection(MultiSchemaDemo.class, 
                     "connect.properties"));

    new MultiSchemaDemo().printEmployees(deptCtx);
    deptCtx.close();
  }

  // performs a join on deptno field of two tables accessed from
  // different connections. 
  void printEmployees(DeptContext deptCtx) throws SQLException
  {
    // obtain the employees from the default context
    Employees emps;
    #sql emps = { SELECT ename, deptno FROM emp }; 

    // for each employee, obtain the department name
    // using the dept table connection context
    while (emps.next()) {
      String dname;
      int deptno = emps.deptno();
      #sql [deptCtx] { 
        SELECT dname INTO :dname FROM dept WHERE deptno = :deptno
      };
      System.out.println("employee: " +emps.ename() +
                         ", department: " + dname);
    }
    emps.close();
  }
}

Implementation and Functionality of Connection Context Classes

This section discusses how SQLJ implements connection context classes, including the DefaultContext class, and what noteworthy methods they contain.

As mentioned earlier, the DefaultContext class and all generated connection context classes implement the ConnectionContext interface.

Note:

Subclassing connection context classes is not permitted in the SQLJ specification and is not supported by Oracle SQLJ.

ConnectionContext Interface

Each connection context class implements the sqlj.runtime.ConnectionContext interface.

Basic methods specified by this interface include the following:

close(boolean CLOSE_CONNECTION/KEEP_CONNECTION)--Releases all resources used in maintaining this connection and closes any open connected profiles. It might or might not close the underlying JDBC connection, depending on whether CLOSE_CONNECTION or KEEP_CONNECTION is specified. These are static boolean constants of the ConnectionContext interface.

For further discussion, see "Closing Shared Connections".
getConnection()--Returns the underlying JDBC connection object for this connection context instance.
getExecutionContext()--Returns the default ExecutionContext instance for this connection context instance. For more information, see "Execution Contexts".

Additional Connection Context Class Methods

In addition to the methods specified and defined in the ConnectionContext interface, each connection context class defines the following methods:

YourCtxClass getDefaultContext()--This is a static method that returns the default connection context instance for a given connection context class.
setDefaultContext(YourCtxClass connctxinstance)--This is a static method that defines the given connection context instance as the default connection context instance for its class.

Although it is true that you can use an instance of only the DefaultContext class as your default connection, it might still be useful to designate an instance of a declared connection context class as the default context for that class, using the setDefaultContext() method. Then you could conveniently retrieve it using the getDefaultContext() method of the particular class. This would allow you, for example, to specify a connection context instance for a SQLJ executable statement as follows.

Declaration:

#sql context MyContext;

Executable code:

...
MyContext myctx1 = new MyContext(url, user, password, autocommit);
...
MyContext.setDefaultContext(myctx1);
...
#sql [MyContext.getDefaultContext()] { SQL operations };
...

Additionally, each connection context class defines methods for control of SQLJ statement caching. The following are static methods:

setDefaultStmtCacheSize(int)
int getDefaultStmtCacheSize()

And the following are instance methods:

setStmtCacheSize(int)
int getStmtCacheSize()

By default, statement caching is enabled. See "Connection Context Methods for Statement Caching (Oracle-Specific Code)" for more information. (This is a subsection under "Statement Caching", which provides an overview of statement caching.)

Using the IMPLEMENTS Clause in Connection Context Declarations

There might be situations where it is useful to implement an interface in your connection context declarations. For general information and syntax, see "Declaration IMPLEMENTS Clause".

You might, for example, want to define an interface that exposes just a subset of the functionality of a connection context class. More specifically, you might want the capability of a class that has getConnection() functionality, but does not have other functionality of a connection context class.

You can create an interface called HasConnection, for example, that specifies a getConnection() method, but does not specify other methods found in a connection context class. You can then declare a connection context class but expose only the getConnection() functionality by assigning a connection context instance to a variable of the type HasConnection, instead of to a variable that has the type of your declared connection context class.

The declaration will be as follows (presuming HasConnection is in package mypackage):

#sql public context MyContext implements mypackage.HasConnection;

Then you can instantiate a connection instance as follows:

HasConnection myConn = new MyContext (url, username, password, autocommit);

For example:

HasConnection myConn = new MyContext 
         ("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger", true);

Semantics-Checking of Your Connection Context Usage

A significant feature of SQLJ is strong typing of connections, with each connection context class typically used for operations on a particular set of interrelated SQL entities. This doesn't mean that all the connection instances of a single class use the same physical entities, but that they use entities that have the same properties, such as names and privileges associated with tables and views, datatypes of their rows, and names and definitions of stored procedures. This strong typing allows SQLJ semantics-checking to verify during translation that you are using your SQL operations correctly, with respect to your database connections.

To use online semantics-checking during translation, provide a sample schema (that includes an appropriate set of SQL entities) for each connection context class. These sample schemas are referred to as exemplar schemas. Provide exemplar schemas through an appropriate combination of the SQLJ -user, -password, and -url options. Following are two examples, one for the DefaultContext class and one for a declared connection context class, where the user, password, and URL are all specified through the -user option:

-user=scott/tiger@jdbc:oracle:oci:@
-user@MyContext=scott/tiger@jdbc:oracle:oci:@

(For information about these SQLJ options, see "Connection Options".)

During semantics-checking, the translator connects to the specified exemplar schema for a particular connection context class and accomplishes the following:

It examines each SQLJ statement in your code that specifies an instance of the connection context class and checks its SQL operations (such as what tables you access and what stored procedures you use).
It verifies that entities in the SQL operations match the set of entities existing in the exemplar schema.

It is your responsibility to pick an exemplar schema that represents the runtime schema in appropriate ways. For example, it must have tables, views, stored functions, and stored procedures with names and datatypes that match what are used in your SQL operations, and with privileges set appropriately.

If no appropriate exemplar schema is available during translation for one of your connection context classes, then it is not necessary to specify SQLJ translator options (-user, -password, -url) for that particular connection context class. In that case, SQLJ statements specifying connection objects of that connection context class are semantically checked only to the extent possible.

Note:

Remember that the exemplar schema you specify in your translator option settings does not specify the schema to be used at runtime. The exemplar schema furnishes the translator only with a set of SQL entities to compare against the entities you use in your SQLJ executable statements.

Standard Data Source Support

The JDBC 2.0 extended API specifies the use of data sources and JNDI as a portable alternative to the DriverManager mechanism for obtaining JDBC connections. It permits database connections to be established through a JNDI name lookup. This name is bound to a particular database and schema prior to program runtime through a javax.sql.DataSource object, typically installed through a GUI JavaBeans deployment tool. The name can be bound to different physical connections without any source code changes simply by rebinding the name in the directory service.

SQLJ uses the same mechanism to create connection context instances in a flexible and portable way. Data sources can also be implemented using a connection pool or distributed transaction service, as defined by the JDBC 2.0 extended API.

For more information about data sources, see the Oracle9i JDBC Developer's Guide and Reference.

Associating a Connection Context with a Data Source

In SQLJ it is natural to associate a connection context class with a logical schema, in much the same way that a data source name serves as a symbolic name for a JDBC connection. Combine both concepts by adding the data source name to the connection context declaration.

#sql context EmpCtx with (dataSource="jdbc/EmpDB");

Any connection context class that you declare with a dataSource property provides additional constructors. To continue the EmpCtx example, the following constructors are provided:

public EmpCtx()--Looks up the data source for jdbc/EmpDB and then calls the getConnection() method on the data source to obtain a connection.
public EmpCtx(String user, String password)--Looks up the data source for jdbc/EmpDB and calls the getConnection(user,password) method on the data source to obtain a connection.
public EmpCtx(ConnectionContext ctx)--Delegates to ctx to obtain a connection.

Any connection context class declared with a dataSource property also omits a number of DriverManager-based constructors. Continuing the EmpCtx example, the following constructors are omitted:

public EmpCtx(Connection conn)
public EmpCtx(String url, String user, String password, boolean autoCommit)
public EmpCtx(String url, boolean autoCommit)
public EmpCtx(String url, java.util.Properties info, boolean autoCommit)
public EmpCtx(String url, boolean autoCommit)

Auto-Commit Mode for Data Source Connections

Unlike the DriverManager-based constructors they replace, the new data-source-based constructors do not include an explicit auto-commit parameter. They always use the auto-commit mode defined by the data source.

Data sources are configured to have a default auto-commit mode depending on the deployment scenario. For example, data sources in the server and middle tier typically have auto-commit off; those on the client may have it on. However, it is also possible to configure data sources with a specific auto-commit setting. This permits data sources to be configured for a particular application and deployment scenario. Contrast this with JDBC URLs that may specify only a single database/driver configuration.

Programs can verify and possibly override the current auto-commit setting with the JDBC connection that underlies their connection context instance.

Note:

Be aware of the auto-commit status of the connections you establish.

If you use the Oracle class, auto-commit is off unless you turn it on explicitly.
If you use DefaultContext or a connection context class with DriverManager-style constructors, then the auto-commit setting must always be specified explicitly.
If you use the data source mechanism, then the auto-commit setting is inherited from the underlying data source. In most environments, the data source object originates from JDBC, and the auto-commit option is on. To avoid unexpected behavior, always check the auto-commit setting.

Associating a Data Source with the Default Context

If a SQLJ program accesses the default connection context, and the default context has not yet been set, then the SQLJ runtime will use the SQLJ default data source to establish its connection. The SQLJ default data source is bound to the JNDI name "jdbc/defaultDataSource".

This mechanism provides a portable means to define and install a default JDBC connection for the default SQLJ connection context.

Data Source Support Requirements

For your program to use data sources, you must supply the packages javax.sql.* and javax.naming.*, and an InitialContext provider in your Java environment. The latter is required to obtain the JNDI context in which the SQLJ runtime can look up the data source object.

Typically, you would use data sources in a JDK 1.2.x environment with the Java Extension classes, or in a J2EE environment. However, you can also use data sources under JDK 1.1.x with the Java Extension classes.

All SQLJ runtime libraries provided by Oracle support data sources. However, if you use the runtime12ee library you must have javax.sql.* and javax.naming.* in your classpath in order for the runtime to load. By contrast, the other runtime libraries use reflection to retrieve DataSource objects.

SQLJ-Specific Data Sources

As of Oracle9i release 2, Oracle SQLJ provides SQLJ-specific data source support in the runtime12ee library. Currently, SQLJ-specific data sources can be used in client-side or middle-tier applications, but not inside the server.

SQLJ-specific data sources extend JDBC data source functionality with methods that return SQLJ connection context instances. This enables a SQLJ developer to manage connection contexts just as a JDBC developer manages connections. In general, each SQLJ-specific data source interface or class is based on a corresponding standard JDBC data source interface or Oracle JDBC data source class.

The rest of this section describes the SQLJ-specific data source interfaces and classes, then concludes with examples of their use.

SQLJ Data Source Interfaces

The sqlj.runtime.ConnectionContextFactory interface acts as a base interface for SQLJ data source functionality. It is implemented by a set of more specialized Oracle data source interfaces that add support for features such as connection pooling, connection caching, or distributed transactions.

The ConnectionContextFactory interface specifies methods, listed below, to return SQLJ connection context instances. The getDefaultContext() methods return a sqlj.runtime.ref.DefaultContext instance for the SQLJ default context. The getContext() methods return a sqlj.runtime.ConnectionContext instance--specifically, an instance of a user-declared connection context class that is specified in the method call.

For both getDefaultContext() and getContext() there are signatures that allow you to specify connection parameters for the JDBC connection that underlies the connection context instance--the auto-commit setting, user and password settings, or all three. If you do not specify the user and password, they are obtained from the underlying data source that generates the connection. If you do not specify an auto-commit setting, the default is false unless it was explicitly set to true for the underlying data source.

DefaultContext getDefaultContext()
DefaultContext getDefaultContext(boolean autoCommit)
DefaultContext getDefaultContext(String user, String password)
DefaultContext getDefaultContext(String user, String password,boolean autoCommit)
ConnectionContext getContext(Class aContextClass)
ConnectionContext getContext(Class aContextClass, boolean autoCommit)
ConnectionContext getContext(Class aContextClass, String user,String password)
ConnectionContext getContext(Class aContextClass, String user, String password, boolean autoCommit)

Each Oracle data source interface that implements ConnectionContextFactory also implements a standard JDBC data source interface to specify methods for the appropriate functionality, such as for basic data sources, connection pooling data sources, or distributed transaction (XA) data sources. Oracle has implemented the SqljDataSource, SqljConnectionPoolDataSource, and SqljXADataSource interfaces, located in the sqlj.runtime package and specified as follows:

public interface SqljDataSource extends javax.sql.DataSource, ConnectionContextFactory { }
public interface SqljDataSource extends javax.sql.ConnectionPoolDataSource, ConnectionContextFactory { }
public interface SqljXADataSource extends javax.sql.XADataSource, ConnectionContextFactory { }

SQLJ Data Source Classes

Oracle provides SQLJ-specific counterparts for the following Oracle JDBC data source classes: OracleDataSource, OracleConnectionPoolDataSource, OracleXADataSource, OracleConnectionCacheImpl, OracleXAConnectionCacheImpl, and OracleOCIConnectionPool. See the Oracle9i JDBC Developer's Guide and Reference for information about these classes.

Oracle SQLJ-specific data source classes are located in two packages: oracle.sqlj.runtime and oracle.sqlj.runtime.client.

The oracle.sqlj.runtime package includes the following:

public class OracleSqljDataSource

 extends oracle.jdbc.pool.OracleDataSource

implements ConnectionContextFactory

Note:

The OracleSqljDataSource class implements the java.io.Serializable interface. It is therefore serializable and can be used in clustered environments such as Oracle9iAS Containers for J2EE (OC4J).

public class OracleSqljConnectionPoolDataSource extends oracle.jdbc.pool.OracleConnectionPoolDataSource implements ConnectionContextFactory
public abstract class OracleSqljXADataSource extends oracle.jdbc.xa.OracleXADataSource implements ConnectionContextFactory;
public class OracleSqljConnectionCacheImpl extends oracle.jdbc.pool.OracleConnectonCacheImpl implements ConnectionContextFactory
public class OracleSqljXAConnectionCacheImpl extends oracle.jdbc.pool.OracleXAConnectonCacheImpl implements ConnectionContextFactory
public class OracleSqljOCIConnectionPool extends oracle.jdbc.pool.OracleOCIConnectonPool implements ConnectionContextFactory

The oracle.sqlj.runtime.client package includes the following:

public class OracleSqljXADataSource extends oracle.jdbc.xa.client.OracleXADataSource implements ConnectionContextFactory

You can use these classes in place of the corresponding JDBC classes that they extend. They include getDefaultContext() and getContext() methods as described in "SQLJ Data Source Interfaces". When you call these methods, the following steps take place for you:

A new logical JDBC connection is acquired from the present data source.
A connection context instance is created from the logical connection, and returned.

Examples: Using SQLJ Data Sources

When used in middle-tier environments, SQLJ-specific data sources, like JDBC data sources, are bound to JNDI locations. You can do the binding explicitly, as in the following example:

//Initialize datasource 
SqljXADataSource sqljDS = new OracleSqljXADataSource(); 
sqljDS.setUser("scott"); 
sqljDS.setPassword("tiger"); 
sqljDS.setServerName("myserver"); 
sqljDS.setDatabaseName("ORCL"); 
sqljDS.setDataSourceName("jdbc/OracleSqljXADS"); 

//Bind the datasource to JNDI 
Context ctx = new InitialContext(); 
ctx.bind("jdbc/OracleSqljXADS");

In a middle-tier Oracle9iAS Containers for J2EE (OC4J) environment, another alternative is to instantiate data sources and bind them to JNDI through settings in the j2ee/home/config/data-sources.xml file. For example, the following <data-source> element in that file creates an OracleSqljXADataSource instance and binds it to the JNDI location jdbc/OracleSqljXADS:

<data-source 
     class="oracle.sqlj.runtime.OracleSqljXADataSource" 
     name="jdbc/OracleSqljXADS" 
     location="jdbc/OracleSqljXADS" 
     xa-location="jdbc/OracleSqljXADS/xa" 
     username="scott" 
     password="tiger" 
     url="jdbc:oracle:thin:@dlsun960:1521:ORCL" 
/>

See the Oracle9iAS Containers for J2EE Services Guide for information about data sources in OC4J.

A SQLJ-specific data source bound to a JNDI location can be looked up and used in creating connection context instances. The following code segment uses information from the preceding <data-source> element to create connection context instances--a DefaultContext instance and an instance of a user-declared class MyCtx, respectively:

sqlj.runtime.SqljDataSource sqljDS; 
InitialContext initCtx = new InitialContext(); 
sqljDS = (sqlj.runtime.SqljDataSource)initCtx.lookup("jdbc/OracleSqljXADS"); 
// getDefaultContext
DefaultContext ctx = sqljDS.getDefaultContext(); 
// getContext
/* Declare MyCtx connection context class. You could optionally use a "with"
   clause to specify  any desired connection parameters not available 
   through the underlying data source.
*/
#sql public static context MyCtx; 
MyCtx ctx = (MyCtx) sqljDS.getContext(MyCtx.class);

SQLJ-Specific Connection JavaBeans for JavaServer Pages

Oracle has implemented a set of JavaBeans for database connections from within JSP pages. The original beans, ConnBean and ConnCacheBean in package oracle.jsp.dbutil, are documented in the Oracle9iAS Containers for J2EE JSP Tag Libraries and Utilities Reference.

As of Oracle9i release 2, Oracle SQLJ provides the following extensions of these JavaBeans in the runtime12ee library for use in SQLJ JSP pages:

oracle.sqlj.runtime.SqljConnBean
oracle.sqlj.runtime.SqljConnCacheBean

ConnBean and ConnCacheBean include methods that return JDBC connection objects. SqljConnBean and SqljConnCacheBean extend this functionality to support a bean property called ContextClass of type String and to return SQLJ connection context instances.

Note:

The SqljConnBean class implements the java.io.Serializable interface. It is therefore serializable and can be used in clustered environments such as Oracle9iAS Containers for J2EE (OC4J).

SqljConnBean and SqljConnCacheBean provide the following methods:

void setContextClass(String contextClassName)
String getContextClass()
DefaultContext getDefaultContext()
ConnectionContext getContext()

The ContextClass property specifies the name of a user-declared connection context class, if you are not using DefaultContext. You can set this property through the setContextClass() method.

To retrieve a connection context instance, use getDefaultContext() or getContext(), as appropriate. The former returns a sqlj.runtime.ref.DefaultContext instance; the latter returns a sqlj.runtime.ConnectionContext instance--specifically, an instance of the class specified in the ContextClass property (by default, DefaultContext).

Note, however, that the getDefaultContext() and getContext() methods are implemented differently between SqljConnBean and SqljConnCacheBean, as described in the following subsections. The discussion concludes with a sample SQLJ JSP page using SqljConnCacheBean.

Behavior of SqljConnBean (Simple Connections)

A SqljConnBean instance can wrap only one logical JDBC connection and one SQLJ connection context instance at any given time.

The first getDefaultContext() or getContext() method call will create and return a connection context instance based on the underlying JDBC connection. This connection context instance will also be stored in the SqljConnBean instance.

Once a connection context instance has been created and stored, the behavior of subsequent getDefaultContext() or getContext() calls will depend on the type of the stored connection context and, for getContext(), on the connection context type specified in the ContextClass property, as follows:

For subsequent getDefaultContext() calls if the stored connection context instance is a DefaultContext instance: The method will keep returning that instance.
For subsequent getDefaultContext() calls if the stored connection context instance is not a DefaultContext instance: The method will close the stored connection context instance and reuse the underlying JDBC connection to create and return a new connection context as a DefaultContext instance (regardless of the previous connection context type). This becomes the new connection context instance stored in the SqljConnBean instance.
For subsequent getContext() calls if the stored connection context instance is of the same type as that specified by the ContextClass property: The method will keep returning that instance.

For subsequent getContext() calls if the stored connection context instance is not of the same type as that specified by ContextClass: The method will close the stored connection context instance and reuse the underlying JDBC connection to create and return a new connection context instance--an instance of what is specified in ContextClass. This becomes the new connection context instance stored in the SqljConnBean instance.

Note:

When SqljConnBean closes a connection context instance, it does so with the KEEP_CONNECTION setting, leaving the underlying JDBC connection intact. See "Closing Shared Connections" for related information.

Behavior of SqljConnCacheBean (Connection Caching)

Unlike with SqljConnBean, the SqljConnCacheBean JavaBean creates and returns a new connection context instance, based on a new logical JDBC connection, for each invocation of getDefaultContext() or getContext(). The connection context type will be DefaultContext for a getDefaultContext() call, or the type specified in the ContextClass property for a getContext() call.

SqljConnCacheBean does not store the connection context instances it creates.

Example: SQLJ JSP Page Using SqljConnCacheBean

The following program, SQLJSelectInto.sqljsp, uses SqljConnCacheBean, its ContextClass bean property, and its getContext() method.

Note:

This example uses the ContextClass property for illustrative purposes. Be aware, however, that DefaultContext is the default value anyway, and that if you want to use DefaultContext, then the value of ContextClass is irrelevant if you use getDefaultContext() instead of getContext().

<%@ page language="sqlj" 
         import="java.sql.*, oracle.sqlj.runtime.SqljConnCacheBean" %> 
<jsp:useBean id="cbean" class="oracle.sqlj.runtime.SqljConnCacheBean"
             scope="session"> 
     <jsp:setProperty name="cbean" property="User" value="scott"/> 
     <jsp:setProperty name="cbean" property="Password" value="tiger"/> 
     <jsp:setProperty name="cbean" property="URL"
                      value="jdbc:oracle:thin:@pdcsun-dev3:1521:view13"/> 
     <jsp:setProperty name="cbean" property="ContextClass"
                      value="sqlj.runtime.ref.DefaultContext"/> 
</jsp:useBean> 
<HTML> 
<HEAD> <TITLE> The SQLJSelectInto JSP  </TITLE> </HEAD> 
<BODY BGCOLOR=white> 
<% String empno = request.getParameter("empno"); 
   if (empno != null) { %> 
      <H3> Employee # <%=empno %> Details: </H3> 
      <% String ename = null;  double sal = 0.0;  String hireDate = null; 
         StringBuffer sb = new StringBuffer(); 
         sqlj.runtime.ref.DefaultContext ctx=null; 
         try { 
           // Make the Connection 
           ctx = (sqlj.runtime.ref.DefaultContext) cbean.getContext(); 
         } catch (SQLException e) { 
         } 
          try { 
             #sql [ctx] { SELECT ename, sal, TO_CHAR(hiredate, 'DD-MON-YYYY') 
                           INTO :ename, :sal, :hireDate 
                           FROM scott.emp WHERE UPPER(empno) = UPPER(:empno) 
             }; 
             sb.append("<BLOCKQUOTE><BIG><B>\n"); 
             sb.append("Name       : " + ename + "\n"); 
             sb.append("Salary     : " + sal + "\n"); 
             sb.append("Date hired : " + hireDate); 
             sb.append("</B></BIG></BLOCKQUOTE>"); 
          } catch (java.sql.SQLException e) { 
              sb.append("<P> SQL error:  " + e + "  </P>\n"); 
          } finally { 
              if (ctx!= null) ctx.close(); 
          } 
      %> 
     <H3><%=sb.toString()%></H3> 
<%} 
%> 
<B>Enter an employee number:</B> 
<FORM METHOD=get> 
<INPUT TYPE="text" NAME="empno" SIZE=10> 
<INPUT TYPE="submit" VALUE="Ask Oracle"); 
</FORM> 
</BODY> 
</HTML>

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 the following features:

Execution control operations modify the semantics of subsequent SQL operations.
Execution status operations describe the results of the most recent SQL operation.
Execution cancellation operations terminate the SQL operation that is currently executing.
Update-batching operations enable and disable update batching, set the batch limit, and get update counts. (For information about update batching, see "Update Batching".)
Savepoint operations set a savepoint, roll back to a savepoint, and release a savepoint.

Closure operations close the execution context instance to avoid resource leakage.

Note:

There is only one execution context class, unlike connection context classes where you declare additional classes as desired. Every execution context is an instance of the ExecutionContext class. So while the term connection context usually refers to a class that you have declared, the term execution context always refers to an instance of the ExecutionContext class. This document specifies connection context class, connection context instance, and execution context instance to avoid confusion.

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 might appear to be associated with connection context instances (given that each connection context instance has a default execution context instance, and 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, it is useful to use multiple execution context instances with a single connection context instance if you use multithreading, with a separate execution context instance for each thread. And you can use multiple connection context instances with a single explicit execution context instance if your program is single-threaded and you want the same set of SQL control parameters to apply to all the connection context instances. (See "Execution Context 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.

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

Notes:

If you specify a connection context instance without an execution context instance, then the default execution context instance of that connection context instance is used.
If you specify an execution context instance without a connection context instance, then the execution context instance is used with the default connection context instance of your application.
If you specify no connection context instance and no execution context instance, then SQLJ uses the default connection and its default execution context instance.

Execution Context Synchronization

ExecutionContext methods (discussed in "Execution Context Methods") are all synchronized methods. Therefore, for ISO standard code generation, anytime a statement tries to use an execution context instance (in essence, tries to use a method of an execution context instance) 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 preceding discussion does not apply for default Oracle-specific code generation, however (-codegen=oracle). For performance reasons, SQLJ performs no additional synchronization against ExecutionContext instances for Oracle-specific generated code. Therefore, you are responsible for ensuring that the same execution context instance will not be used by more than one thread. If multiple threads use the same execution context, then your application, rather than blocking, will experience errors such as incorrect results or NullPointer exceptions.

Another exception to the discussion is for recursion, which is encountered only in the server. Multiple SQLJ statements in the same thread 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".

Execution Context Methods

This section lists the methods of the ExecutionContext class, categorized as status methods, control methods, cancellation method, update batching methods, savepoint methods, and closure method.

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:

SQLWarning getWarnings()--Returns a java.sql.SQLWarning object containing the first warning reported by the most recent SQL operation that completed using this execution context instance. Warnings are returned in a chain--use the getWarnings() method of the execution context instance to get the first warning, then use the getNextWarning() method of each SQLWarning object to get the next warning. The chain contains all warnings generated during the execution of the SQL operation.
int getUpdateCount()--Except when update batching is enabled, this returns an int value 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.

For batch-enabled applications, the value returned by getUpdateCount() would be one of several batch-related constant values--NEW_BATCH_COUNT, ADD_BATCH_COUNT, or EXEC_BATCH_COUNT. See "Execution Context Update Counts" for more information.

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

int getMaxFieldSize()--Returns an int value specifying the maximum amount of data (in bytes) that would be returned from a SQL operation subsequently, using this execution context instance. 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(int)--Takes an int value as input to modify the field-size maximum.
int getMaxRows()--Returns an int value specifying the maximum number of rows that can be contained by any SQLJ iterator or JDBC result set created using this execution context instance. 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(int)--Takes an int value as input to modify the row maximum.
int getQueryTimeout()--Returns an int value specifying the timeout limit, in seconds, for any SQL operation that uses this execution context instance. 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(int)--Takes an int value as input to modify the query timeout limit.
int getFetchSize()--Retrieves the number of rows that is the current fetch size for iterator objects generated from this ExecutionContext object. If this ExecutionContext object has not set a fetch size by calling setFetchSize(), then the value returned is zero. If this ExecutionContext object has set a non negative fetch size by calling the method setFetchSize(), then the return value is the fetch size specified on setFetchSize().
setFetchSize(int)--Gives the SQLJ runtime a hint as to the number of rows that should be fetched when more rows are needed. The number of rows specified affects only iterator objects created using this ExecutionContext object. Specifying zero means that an implementation-dependent default value will be used for the fetch size.
int getFetchDirection()--Retrieves the default direction for fetching data, for scrollable iterator objects that are generated from this ExecutionContext object. If this ExecutionContext object has not set a fetch direction by calling the method setFetchDirection(), the return value is FETCH_FORWARD.
setFetchDirection(int)--Gives the SQLJ runtime a hint as to the direction in which rows of scrollable iterator objects are processed. The hint applies only to scrollable iterator objects that are created using this ExecutionContext object. The default value is:
```
sqlj.runtime.ResultSetIterator.FETCH_FORWARD.
```
This method throws a SQLException if the given direction is not one of FETCH_FORWARD, FETCH_REVERSE, or FETCH_UNKNOWN (int constants).

Cancellation Method

Use the following method to cancel SQL operations in a multithreading environment or to cancel a pending statement batch if update batching is enabled:

cancel()--In a multithreading environment, use this method in one thread to cancel a SQL operation currently executing in 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.

In a batch-enabled environment, use this to cancel a pending statement batch. The batch is emptied, and none of the statements in the batch are executed. After you cancel a batch, the next batchable statement encountered will be added to a new batch. ( "Canceling a Batch" discusses this.)

Update Batching Methods

Use the following methods to control update batching if you want your application to use that performance enhancement feature. These methods, and update batching in general, are further discussed in "Update Batching":

setBatching(boolean)--Takes a boolean value to enable update batching. See "Enabling and Disabling Update Batching" for more information.

Update batching is disabled by default.
boolean isBatching()--Returns a boolean value indicating whether update batching is enabled.

This does not indicate whether there is currently a pending batch, but you can use the getUpdateCount() method described in "Status Methods" to see whether a batch has been newly created, added to, or executed.
int getBatchLimit()--Returns an int value indicating the current batch limit. If there is a batch limit, a pending batch is implicitly executed once it contains that number of statements. See "Setting a Batch Limit" for more information.

By default, the batch limit is set to the ExecutionContext static constant value UNLIMITED_BATCH, meaning there is no batch limit.
setBatchLimit(int)--Takes a positive, nonzero int value as input to set the current batch limit. Two special values you can input are UNLIMITED_BATCH, which means there is no limit, and AUTO_BATCH, which lets the SQLJ runtime dynamically determine a batch limit.
int[] executeBatch()--Executes the pending statement batch, returning an array of int update counts that have meanings as described in "Execution Context Update Counts". See "Explicit and Implicit Batch Execution" for more information. Regarding error conditions, see "Error Conditions During Batch Execution".
int[] getBatchUpdateCounts()--Returns an array of int update counts for the last batch executed, with meanings as described in "Execution Context Update Counts". This method is useful in situations where the batch was executed implicitly.

Savepoint Methods

Starting with Oracle9i release 2, Oracle SQLJ supports JDBC 3.0 savepoints. SQLJ savepoint statements are described in "Using Savepoints". Savepoints are stored in the ExecutionContext instance, which provides the following support methods:

setSavepoint(String)

Specify a desired savepoint name for the current location in the transaction.
rollbackToSavepoint(String)

Roll back changes to the specified savepoint.
releaseSavepoint(String)

Release the specified savepoint (removes it from the execution context).

In your code, however, you would typically use savepoint statements rather than these methods.

Note:

As of Oracle9i release 2, Oracle9i and Oracle9i JDBC do not support release-savepoint functionality.

Closure Method

Oracle SQLJ provides extended functionality with a close() method for the ExecutionContext class:

close()--To avoid resource leakage, use this in the following set of circumstances:
- You are using the Oracle-specific code generation (the default -codegen=oracle setting).
and:
- You explicitly created and used the ExecutionContext instance, instead of using the default instance available through the connection context instance.
and:
- You are not issuing SQLJ rollback or commit statements explicitly using the ExecutionContext instance:
```
#sql [ec] { COMMIT };
#sql [ec] { ROLLBACK };
```
and:
- You are not calling executeBatch() on the ExecutionContext instance.
Under this set of circumstances, a batchable statement might remain open on the ExecutionContext instance, and over time you may run out of database cursors. To avoid this, use the close() method as in the following example:
```
Execution Context ec = new ExecutionContext();
...
try {
   ...
   #sql [ec] { SQL operation };
   ...
} finally { ec.close(); }
```

Note:

When an execution context instance is associated with a connection context instance (instead of being declared explicitly as above), then closing the connection context instance (with or without closing the underlying JDBC connection) will automatically close any statement remaining on the execution context instance.

Example: Using 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, then 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, you should take the following steps:

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 "Creating and Specifying Execution Context Instances".)

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.

Note:

For performance reasons, SQLJ performs no additional synchronization against ExecutionContext instances for Oracle-specific generated code. Therefore, you are responsible for ensuring that the same execution context instance will not be used by more than one thread. If multiple threads use the same execution context, then your application, rather than blocking, will experience errors such as incorrect results or NullPointer exceptions.

Multithreading in SQLJ

This section discusses SQLJ support and requirements for multithreading and the relation between multithreading and execution context instances.

You can use SQLJ in writing multithreaded applications; however, any use of multithreading in your SQLJ application is subject to the limitations of your JDBC driver or proprietary database access vehicle. This includes any synchronization limitations.

You are required to use a different execution context instance for each thread. You can accomplish this in one of two ways:

Specify connection context instances for your SQLJ statements such that a different connection context instance is used for each thread. Each connection context instance automatically has its own default execution context instance.
If you are using the same connection context instance with multiple threads, then declare additional execution context instances and specify execution context instances for your SQLJ statements such that a different execution context instance is used for each thread.

For information about how to specify connection context instances and execution context instances for your SQLJ statements, see "Specifying Connection Context Instances and Execution Context Instances".

If you are using one of the Oracle JDBC drivers, multiple threads can use the same connection context instance if desired (as long as different execution context instances are specified), and there are no synchronization requirements directly visible to the user. Note, however, that data access is sequential--only one thread is accessing data at any given time. Synchronization refers to the control flow of the various stages of the SQL operations executing through your threads. Each statement, for example, can bind input parameters, then execute, then bind output parameters. With some JDBC drivers, special care must be taken not to intermingle these stages.

For ISO standard code generation, if a thread attempts to execute a SQL operation that uses an execution context that is in use by another operation, then the thread is blocked until the current operation completes. If an execution context were shared between threads, the results of a SQL operation performed by one thread would be visible in the other thread. If both threads were executing SQL operations, a race condition might occur--the results of an execution in one thread might be overwritten by the results of an execution in the other thread before the first thread had processed the original results. This is why multiple threads are not allowed to share an execution context instance.

Important:

The preceding paragraph does not apply if you use default Oracle-specific code generation (-codegen=oracle). For performance reasons, SQLJ performs no additional synchronization against ExecutionContext instances for Oracle-specific generated code. Therefore, you are responsible for ensuring that the same execution context instance will not be used by more than one thread. If multiple threads use the same execution context, then your application, rather than blocking, will experience errors such as incorrect results or NullPointer exceptions.

Multithreading--MultiThreadDemo.sqlj

The following is an example of a SQLJ application using multithreading.

A ROLLBACK operation is executed before closing the connection, so the data is not permanently altered.

import java.sql.SQLException;
import java.util.Random;
import sqlj.runtime.ExecutionContext;
import oracle.sqlj.runtime.Oracle;
/**
  Each instance of MultiThreadDemo is a thread that gives all employees
  a raise of some ammount when run.  The main program creates two such 
  instances and computes the net raise after both threads have completed.
  **/
class MultiThreadDemo extends Thread
{
  double raise;
  static Random randomizer = new Random(); 
 
  public static void main (String args[]) 
  {
    try { 
      /* if you're using a non-Oracle JDBC Driver, add a call here to
         DriverManager.registerDriver() to register your Driver
      */
      // set the default connection to the URL, user, and password
      // specified in your connect.properties file
      Oracle.connect(MultiThreadDemo.class, "connect.properties");
      double avgStart = calcAvgSal();
      MultiThreadDemo t1 = new MultiThreadDemo(250.50);
      MultiThreadDemo t2 = new MultiThreadDemo(150.50);
      t1.start();
      t2.start();
      t1.join();
      t2.join();
      double avgEnd = calcAvgSal();
      System.out.println("average salary change: " + (avgEnd - avgStart));
    } catch (Exception e) { 
      System.err.println("Error running the example: " + e);
    }
    try { #sql { ROLLBACK }; Oracle.close(); } catch (SQLException e) { }
  } 
  static double calcAvgSal() throws SQLException
  {
    double avg;
    #sql { SELECT AVG(sal) INTO :avg FROM emp };
    return avg;
  }
  MultiThreadDemo(double raise)
  {
    this.raise = raise;
  }
  public void run()
  {
    // Since all threads will be using the same default connection
    // context, each run uses an explicit execution context instance to
    // avoid conflict during execution
    try {
      delay();
      ExecutionContext execCtx = new ExecutionContext();
      #sql [execCtx] { UPDATE EMP SET sal = sal + :raise };
      int updateCount = execCtx.getUpdateCount();
      System.out.println("Gave raise of " + raise + " to " + 
                          updateCount + " employees");
    } catch (SQLException e) {
      System.err.println("error updating employees: " + e);
    }
  }
  // delay is used to introduce some randomness into the execution order
  private void delay()
  {
    try {
      sleep((long)Math.abs(randomizer.nextInt()/10000000));
    } catch (InterruptedException e) {}
  }
}

Iterator Class Implementation and Advanced Functionality

This section discusses how iterator classes are implemented and what additional functionality is available beyond the essential methods discussed in "Using Named Iterators" and "Using Positional Iterators". The following topics are covered:

Implementation and Functionality of Iterator Classes

Any named iterator class you declare will be generated by the SQLJ translator to implement the sqlj.runtime.NamedIterator interface. Classes implementing the NamedIterator interface have functionality that maps iterator columns to database columns by name (not by position).

Any positional iterator class you declare will be generated by the SQLJ translator to implement the sqlj.runtime.PositionedIterator interface. Classes implementing the PositionedIterator interface have functionality that maps iterator columns to database columns by position (not by name).

Both the NamedIterator interface and the PositionedIterator interface, and therefore all generated SQLJ iterator classes as well, implement or extend the sqlj.runtime.ResultSetIterator interface.

The ResultSetIterator interface specifies the following methods for all SQLJ iterators (both named and positional):

close()--Closes the iterator.
ResultSet getResultSet()--Extracts the underlying JDBC result set from the iterator.
boolean isClosed()--Determines if the iterator has been closed.
boolean next()--Moves to the next row of the iterator (returning true if there is a valid next row to go to).

The PositionedIterator interface adds the following method specification for positional iterators:

boolean endFetch()--Determines if you have reached the last row of a positional iterator.

As discussed in "Using Named Iterators", use the next() method to advance through the rows of a named iterator, and accessor methods to retrieve the data. The SQLJ generation of a named iterator class defines an accessor method for each iterator column, where each method name is identical to the corresponding column name. For example, if you declare a name column, then a name() method will be generated.

As discussed in "Using Positional Iterators", use a FETCH INTO statement together with the endFetch() method to advance through the rows of a positional iterator and retrieve the data. A FETCH INTO statement implicitly calls the next() method. Do not explicitly use the next() method in a positional iterator unless you are using the special FETCH CURRENT syntax (described in "FETCH CURRENT Syntax: from JDBC Result Sets to SQLJ Iterators"). The FETCH INTO statement also implicitly calls accessor methods that are named according to iterator column numbers. The SQLJ generation of a positional iterator class defines an accessor method for each iterator column, where each method name corresponds to the column position.

Use the close() method to close any iterator once you are done with it.

The getResultSet() method is central to SQLJ-JDBC interoperability and is discussed in "SQLJ Iterator and JDBC Result Set Interoperability".

Note:

Alternatively, you can use a ResultSetIterator instance (or a ScrollableResultSetIterator instance) directly as a weakly typed iterator. (ScrollableResultSetIterator extends ResultSetIterator.) This is convenient if you are interested only in converting it to a JDBC result set and you do not need named or positional iterator functionality. You can also access it through SQLJ FETCH CURRENT syntax introduced in Oracle SQLJ release 8.1.7. See "Result Set Iterators".

Using the IMPLEMENTS Clause in Iterator Declarations

There might be situations where it will be useful to implement an interface in your iterator declaration. For general information and syntax, see "Declaration IMPLEMENTS Clause".

You might, for example, have an iterator class where you want to restrict access to one or more columns. As discussed in "Using Named Iterators", a named iterator class generated by SQLJ has an accessor method for each column in the iterator. If you want to restrict access to certain columns, you can create an interface with only a subset of the accessor methods, then expose instances of the interface type to the user instead of exposing instances of the iterator class type.

For example, assume you are creating a named iterator of employee data, with columns ENAME (employee name), EMPNO (employee number), and SAL (salary). Accomplish this as follows:

#sql iterator EmpIter (String ename, int empno, float sal);

This generates a class EmpIter with ename(), empno(), and sal() accessor methods.

Assume, though, that you want to prevent access to the SAL column. You can create an interface EmpIterIntfc that has ename() and empno() methods, but no sal() method. Then you can use the following iterator declaration instead of the declaration above (presuming EmpIterIntfc is in package mypackage):

#sql iterator EmpIter implements mypackage.EmpIterIntfc 
     (String emame, int empno, float sal);

Then if you code your application so that users can access data only through EmpIterIntfc instances, they will not have access to the SAL column.

Support for Subclassing of Iterator Classes

SQLJ supports the ability to subclass iterator classes. This feature can be very useful in allowing you to add functionality to your queries and query results.

The one key requirement of an iterator subclass is that you must supply a public constructor that takes an instance of sqlj.runtime.RTResultSet as input. The SQLJ runtime will call this constructor in assigning query results to an instance of your subclass. Beyond that, you provide functionality as you choose.

You can continue to use functionality of the original iterator class (the superclass of your subclass). For example, you can advance through query results by calling the super.next() method.

Result Set Iterators

You may have situations where you do not require the strongly typed functionality of a SQLJ iterator.

For such circumstances, you can directly use instances of the type sqlj.runtime.ResultSetIterator to receive query data, so that you are not required to declare a named or positional iterator class. Alternatively, you can use the sqlj.runtime.ScrollableResultSetIterator type, which extends ResultSetIterator. This allows you to use SQLJ scrollable iterator functionality, as described in "Scrollable Result Set Iterators".

In using a result set iterator instead of a strongly typed iterator, you are trading the strong type-checking of the SQLJ SELECT operation for the convenience of not having to declare an iterator class.

As discussed in "Iterator Class Implementation and Advanced Functionality", the ResultSetIterator interface underlies all named and positional iterator classes and specifies the getResultSet() and close() methods.

If you want to use SQLJ to process a result set iterator instance, then use a ScrollableResultSetIterator instance, and use FETCH CURRENT syntax as described in "FETCH CURRENT Syntax: from JDBC Result Sets to SQLJ Iterators".

If you want to use JDBC to process a result set iterator instance, you can use its getResultSet() method, as described in "Using and Converting Weakly Typed Iterators (ResultSetIterator)", then process the underlying result set that you retrieve.

If you process a result set iterator through its underlying result set, you should close the result set iterator, not the result set, when you are finished. Closing the result set iterator will also close the result set, but closing the result set will not close the result set iterator.

Note:

Oracle9i SQLJ supports result set iterators for use as host expressions and to represent cursors in FETCH statements. This functionality was not supported in Oracle8i releases.

Scrollable Iterators

The ISO standard for SQLJ supports scrollable iterators, with functionality being patterned after the JDBC 2.0 specification for scrollable JDBC result sets. Oracle9i SQLJ adds support for this functionality.

For general information about scrollable result sets, see the Oracle9i JDBC Developer's Guide and Reference.

Declaring Scrollable Iterators

To characterize an iterator as scrollable, add the following clause to the iterator declaration:

implements sqlj.runtime.Scrollable

This instructs the SQLJ translator to generate an iterator that implements the Scrollable interface. Here is an example of a declaration of a named, scrollable iterator:

#sql public static MyScrIter implements sqlj.runtime.Scrollable
                             (String ename, int empno);

The code that the SQLJ translator generates for the MyScrIter class will automatically support all the methods of the Scrollable interface, described in

7 Advanced Language Features

Connection Contexts

Connection Context Concepts

Connection Context Logistics

More About Declaring and Using a Connection Context Class

Declaring the Connection Context Class

Creating a Connection Context Instance

Specifying a Connection Context Instance for a SQLJ Clause

Closing a Connection Context Instance

Example of Multiple Connection Contexts

Implementation and Functionality of Connection Context Classes

ConnectionContext Interface

Additional Connection Context Class Methods

Using the IMPLEMENTS Clause in Connection Context Declarations

Semantics-Checking of Your Connection Context Usage

Standard Data Source Support

Associating a Connection Context with a Data Source

Auto-Commit Mode for Data Source Connections

Associating a Data Source with the Default Context

Data Source Support Requirements

SQLJ-Specific Data Sources

SQLJ Data Source Interfaces

SQLJ Data Source Classes

Examples: Using SQLJ Data Sources

SQLJ-Specific Connection JavaBeans for JavaServer Pages

Behavior of SqljConnBean (Simple Connections)

Behavior of SqljConnCacheBean (Connection Caching)

Example: SQLJ JSP Page Using SqljConnCacheBean

Execution Contexts

Relation of Execution Contexts to Connection Contexts

Creating and Specifying Execution Context Instances

Execution Context Synchronization

Execution Context Methods

Status Methods

Control Methods

Cancellation Method

Update Batching Methods

Savepoint Methods

Closure Method

Example: Using ExecutionContext Methods

Relation of Execution Contexts to Multithreading

Multithreading in SQLJ

Multithreading--MultiThreadDemo.sqlj

Iterator Class Implementation and Advanced Functionality

Implementation and Functionality of Iterator Classes

Using the IMPLEMENTS Clause in Iterator Declarations

Support for Subclassing of Iterator Classes

Result Set Iterators

Scrollable Iterators

Declaring Scrollable Iterators

7
Advanced Language Features