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
• Execution Contexts
• Multithreading in SQLJ
• Iterator Class Implementation and Advanced Functionality
• Advanced Transaction Control
• SQLJ and JDBC Interoperability
• Support for Dynamic SQL

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:

1. A new logical JDBC connection is acquired from the present data source.
2. 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:

1. Instantiate a unique execution context instance for use with each thread.
2. 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
• Using the IMPLEMENTS Clause in Iterator Declarations
• Support for Subclassing of Iterator Classes
• Result Set Iterators
• Scrollable Iterators

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 "The Scrollable Interface" below.

Scrollable Iterator Sensitivity

You can declare scrollable iterators, like scrollable result sets, to have sensitivity to changes to the underlying data. By default, scrollable iterators in Oracle SQLJ have a sensitivity setting of INSENSITIVE, meaning they do not detect any such changes in the underlying data. You can, however, use a declaration with clause to alter this setting. The following example expands an earlier example to specify sensitivity:

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

Important: The implements clause must precede the with clause.

The SQLJ standard also allows a setting of ASENSITIVE, but in Oracle SQLJ this is undefined. Setting sensitivity to ASENSITIVE results instead in the default setting, INSENSITIVE, being used.

Given the preceding declaration, MyScrIter instances will be sensitive to data changes, subject to factors such as the fetch size window. For general information about the behavior of sensitive scrollable JDBC result sets, which underlie sensitive scrollable iterators, see the Oracle9i JDBC Developer's Guide and Reference.

The Scrollable Interface

This section documents some key methods of the sqlj.runtime.Scrollable interface.

You can provide hints about the fetch direction to scrollable iterators. The following methods are defined on scrollable iterators as well as on execution contexts. Use an ExecutionContext instance to provide the default direction to be used in creation of scrollable iterators.

• setFetchDirection(int) --Gives the SQLJ runtime a hint as to the direction in which rows are processed. The direction should be one of sqlj.runtime.ResultSetIterator.FETCH_FORWARD, FETCH_REVERSE, or FETCH_UNKNOWN.

  If you do not specify a value for the direction on the ExecutionContext, then FETCH_FORWARD will be used as a default.

• int getFetchDirection()--Retrieves the current direction for fetching rows of data (one of the integer constants described immediately above).

There are also a number of scrollable iterator methods that will return information about the current position of the iterator object in the underlying result set. All these methods will return false whenever the result set underlying the iterator contains no rows:

• boolean isBeforeFirst()--Indicates whether the iterator object is before the first row in the result set.
• boolean isFirst()--Indicates whether the iterator object is on the first row of the result set.
• boolean isLast()--Indicates whether the iterator object is on the last row of the result set. Note that calling the method isLast() may be expensive, because the JDBC driver might have to fetch ahead one row to determine whether the current row is the last row in the result set.
• boolean isAfterLast()--Indicates whether the iterator object is after the last row in the result set.

  Note: Additional methods for navigation, also defined in the Scrollable interface, are available as well. These are described in "Scrollable Named Iterators" below.

Scrollable Named Iterators

Named iterators use navigation methods, defined in the Scrollable interface, to move through the rows of a result set. As described earlier in this manual, non-scrollable iterators have only the following method for navigation:

• boolean next()--Moves the iterator object to the next row in the result set.

(See "Using Named Iterators" for more information.)

Additional navigation methods are available for scrollable named iterators. These methods function similarly to the next() method, in that they try to position the iterator on an actual row of the result set. They return true if the iterator ends up on a valid row and false if it does not. Additionally, if you attempt to position the iterator object before the first row or after the last row in the result set, this leaves the iterator object in the "before first" or "after last" position, respectively.

The following methods are supported:

• boolean previous()--Moves the iterator object to the previous row in the result set.
• boolean first()--Moves the iterator object to the first row in the result set.
• boolean last()--Moves the iterator object to the last row in the result set.
• boolean absolute(int) -- Moves the iterator object to the given row number in the result set. The first row is row 1, the second is row 2, and so on. If the given row number is negative, the iterator object moves to a row position relative to the end of the result set. For example, calling absolute(-1) positions the iterator object on the last row, absolute(-2) indicates the next-to-last row, and so on.
• boolean relative(int)--Moves the iterator object a relative number of rows, either positive or negative from the current position. Calling relative(0) is valid, but does not change the iterator position.

The methods beforeFirst() and afterLast() return void, because they never place the iterator object on an actual row of the result set.

• void beforeFirst()--Moves the iterator object to the front of the result set, before the first row. This has no effect if the result set contains no rows.
• void afterLast()--Moves the iterator object to the end of the result set, after the last row. This has no effect if the result set contains no rows.

Scrollable Positional Iterators

General FETCH syntax for positional iterators was described earlier, in "Using Positional Iterators". For example:

#sql { FETCH :iter INTO :x, :y, :z };

This is actually an abbreviated version of the following syntax.

#sql { FETCH NEXT FROM :iter INTO :x, :y, :z  };

This suggests the pattern for alternatively moving to the previous, first, or last row in the result set. (Unfortunately, JDBC 2.0--after which the movement methods were modeled--uses previous(), whereas the FETCH syntax, which is patterned after SQL, employs PRIOR. In case you should forget this inconsistency, the Oracle9i SQLJ translator will also accept FETCH PREVIOUS.)

#sql { FETCH PRIOR FROM :iter INTO :x, :y, :z  };
#sql { FETCH FIRST FROM :iter INTO :x, :y, :z  };
#sql { FETCH LAST FROM :iter INTO :x, :y, :z  };

There is also syntax to pass a numeric value for absolute or relative movements, to move to a particular (absolute) row, or to move forward or backward from the current position:

#sql { FETCH ABSOLUTE :n FROM :iter INTO :x, :y, :z  };
#sql { FETCH RELATIVE :n FROM :iter INTO :x, :y, :z  };

Note: In all of the preceding cases, the iterator endFetch() method returns true whenever the FETCH fails to move to a valid row and retrieve values.

Note that you must use a host expression to specify the movement. You cannot simply use a constant for the numeric value. Thus, instead of:

#sql { FETCH RELATIVE 0 FROM :iter INTO :x, :y, :z };

you must write the following:

#sql { FETCH RELATIVE :(0) FROM :iter INTO :x, :y, :z  };

Incidentally, this command leaves the position of the iterator unchanged and--if the iterator is on a valid row--just populates the variables.

Note: Alternatively, you can navigate through a scrollable positional iterator through a combination of the navigation methods described in "Scrollable Named Iterators", and FETCH CURRENT syntax described in "FETCH CURRENT Syntax: from JDBC Result Sets to SQLJ Iterators" below.

FETCH CURRENT Syntax: from JDBC Result Sets to SQLJ Iterators

Consider a situation where you have an existing JDBC program that you want to rewrite in SQLJ with as little modification as possible.

Your JDBC result set will use only movement methods, such as next(), previous(), absolute(), and so on. You can immediately model this in SQLJ through a named iterator. However, this also implies that all columns of the SQL result set must have a proper name. In practice many (if not all) columns of the result set will require introduction of alias names. This is unacceptable if the query text is to remain untouched.

The alternative, to avoid change to the query source, is to define a positional iterator type for the result set. However, this approach forces changes to the control-flow logic of the program. Consider the following JDBC code sample:

ResultSet rs = ... // execute ...query...;
while (rs.next()) {
   x := rs.getXxx(1); y:=rs.getXxx(2);
   ...process...
}

This translates along the following lines to SQLJ:

MyIter iter;
#sql iter = { ...query... };
while(true) {
   #sql { FETCH :iter INTO :x, :y };
   if (iter.endFetch()) break;
   ...process...
}

The transformations to the program logic will become even more difficult when considering arbitrary movements on scrollable iterators. Because positional iterators implement all the movement commands of named iterators, it is possible to exploit this and use RELATIVE :(0) to populate variables from the iterator:

MyIter iter;
#sql iter = { ...query... };
while (iter.next()) {
   #sql { FETCH RELATIVE :(0) FROM :iter INTO :x, :y };
   ...process...
}

Now, you can preserve both the original query and the original program logic. Unfortunately, there still is one drawback to this approach--the iterator type MyIter must implement the Scrollable interface, even if this property is not really needed. To address this, Oracle9i SQLJ introduces the following syntax extension:

#sql { FETCH CURRENT FROM :iter INTO :x, :y, :z  };

Given this syntax, you can rewrite the JDBC example in SQLJ for scrollable as well as non-scrollable iterators:

AnyIterator ai;
#sql ai = { ...query... };
while (ai.next()) {
   #sql { FETCH CURRENT FROM :ai INTO :x, :y };
   ...process...
}

Scrollable Result Set Iterators

In Oracle9i SQLJ, support for weakly typed result set iterators is extended to add a scrollable result set iterator type. The definition follows.

package sqlj.runtime;
public interface ScrollableResultSetIterator
                 extends ResultSetIterator
                 implements Scrollable
{ }

Because this type extends sqlj.runtime.ResultSetIterator, it supports the methods described in "Result Set Iterators".

Because it also implements the sqlj.runtime.Scrollable interface, it supports the methods described in "The Scrollable Interface" and "Scrollable Named Iterators".

Furthermore, scrollable result set iterators support the FETCH CURRENT syntax described in "FETCH CURRENT Syntax: from JDBC Result Sets to SQLJ Iterators".

Consider the following JDBC code:

Statement st = conn.createStatement("SELECT ename, empid FROM emp");
ResultSet rs = st.executeQuery();
while (rs.next()) { 
   x = rs.getString(1); 
   y = rs.getInt(2);
}
rs.close();

You can use a SQLJ result set iterator in writing equivalent code, as follows:

sqlj.runtime.ResultSetIterator rsi;
#sql rsi = { SELECT ename, empid FROM emp };
while (rsi.next()) {
   #sql { FETCH CURRENT FROM :rsi INTO :x, :y };
}
rsi.close();

To take advantage of scrollability features, you could also write the following code:

sqlj.runtime.ScrollableResultSetIterator srsi;
#sql srsi = { SELECT ename, empid FROM emp };
srsi.afterLast();
while (srsi.previous()) {
   #sql { FETCH CURRENT FROM :srsi INTO :x, :y };
}
srsi.close();

Advanced Transaction Control

SQLJ supports the SQL SET TRANSACTION statement to specify the access mode and isolation level of any given transaction. Standard SQLJ supports READ ONLY and READ WRITE access mode settings, but Oracle JDBC does not support READ ONLY. (You can set permissions to have the same effect, however.) Supported settings for isolation level are SERIALIZABLE, READ COMMITTED, READ UNCOMMITTED, and REPEATABLE READ. Oracle SQL, however, does not support READ UNCOMMITTED or REPEATABLE READ.

READ WRITE is the default access mode in both standard SQL and Oracle SQL.

READ COMMITTED is the default isolation level in Oracle SQL; SERIALIZABLE is the default in standard SQL.

Access modes and isolation levels are briefly described below. For more information, see the Oracle9i SQL Reference. You might also consult any guide to standard SQL for additional conceptual information.

For an overview of transactions, including SQLJ support for the basic transaction control operations COMMIT and ROLLBACK, see "Basic Transaction Control".

SET TRANSACTION Syntax

In SQLJ, the SET TRANSACTION statement has the following syntax:

#sql { SET TRANSACTION <access_mode>, <ISOLATION LEVEL isolation_level> };

If you do not specify a connection context instance, then the statement applies to the default connection.

If you use SET TRANSACTION, it must be the first statement in a transaction (in other words, the first statement since your connection to the database or your most recent COMMIT or ROLLBACK), preceding any DML statements.

In standard SQLJ, any access mode or isolation level you set will remain in effect across transactions until you explicitly reset it at the beginning of a subsequent transaction.

In a standard SQLJ SET TRANSACTION statement, you can optionally specify the isolation level first, or specify only the access mode, or only the isolation level. Following are some examples:

#sql { SET TRANSACTION READ WRITE };

#sql { SET TRANSACTION ISOLATION LEVEL SERIALIZABLE };

#sql { SET TRANSACTION READ WRITE, ISOLATION LEVEL SERIALIZABLE };

#sql { SET TRANSACTION ISOLATION LEVEL READ COMMITTED, READ WRITE };

You can also specify a particular connection context instance for a SET TRANSACTION statement, as opposed to having it apply to the default connection:

#sql [myCtxt] { SET TRANSACTION ISOLATION LEVEL SERIALIZABLE };

Note that in SQLJ, both the access mode and the isolation level can be set in a single SET TRANSACTION statement. This is not true in other Oracle SQL tools such as Server Manager or SQL*Plus, where a single statement can set one or the other, but not both.

Access Mode Settings

The READ WRITE and READ ONLY access mode settings (where supported) have the following functionality:

• READ WRITE (default)--In a READ WRITE transaction, the user is allowed to update the database. SELECT, INSERT, UPDATE, and DELETE are all legal.
• READ ONLY (supported by Oracle JDBC as of Oracle9i release 2)--In a READ ONLY transaction, the user is not allowed to update the database. SELECT is legal, but INSERT, UPDATE, DELETE, and SELECT FOR UPDATE are not.

Isolation Level Settings

The READ COMMITTED, SERIALIZABLE, READ UNCOMMITTED, and REPEATABLE READ isolation level settings (where supported) have the following functionality:

• READ UNCOMMITTED (not supported by Oracle9i)--Dirty reads, non-repeatable reads, and phantom reads are all allowed. See below for definitions of the italicized terms.
• READ COMMITTED (default for Oracle9i)--Dirty reads are prevented; non-repeatable reads and phantom reads are allowed. If the transaction contains DML statements that require row locks held by other transactions, then any of the statements will block until the row lock it needs is released by the other transaction.
• REPEATABLE READ (not supported by Oracle9i)--Dirty reads and non-repeatable reads are prevented; phantom reads are allowed.
• SERIALIZABLE--Dirty reads, non-repeatable reads, and phantom reads are all prevented. Any DML statements in the transaction cannot update any resource that might have had changes committed after the transaction began. Such DML statements will fail.

A dirty read occurs when transaction B accesses a row that was updated by transaction A, but transaction A later rolls back the updates. As a result, transaction B sees data that was never actually committed to the database.

A non-repeatable read occurs when transaction A retrieves a row, transaction B subsequently updates the row, and transaction A later retrieves the same row again. Transaction A retrieves the same row twice but sees different data.

A phantom read occurs when transaction A retrieves a set of rows satisfying a given condition, transaction B subsequently inserts or updates a row such that the row now meets the condition in transaction A, and transaction A later repeats the conditional retrieval. Transaction A now sees an additional row; this row is referred to as a "phantom".

You can think of the four isolation level settings being in a progression:

SERIALIZABLE > REPEATABLE READ > READ COMMITTED > READ UNCOMMITTED

If a desired setting is unavailable to you--such as REPEATABLE READ or READ UNCOMMITTED if you use Oracle9i--use a "greater" setting (one further to the left) to ensure having at least the level of isolation that you want.

Using JDBC Connection Class Methods

You can optionally access and set the access mode and isolation level of a transaction, using methods of the underlying JDBC connection instance of your connection context instance. SQLJ code using these JDBC methods is not portable, however.

Following are the Connection class methods for access mode and isolation level settings:

• public abstract int getTransactionIsolation()--Returns the current transaction isolation level as one of the following constant values:
  TRANSACTION_NONE
TRANSACTION_READ_COMMITTED
TRANSACTION_SERIALIZABLE
TRANSACTION_READ_UNCOMMITTED
TRANSACTION_REPEATABLE_READ
• public abstract void setTransactionIsolation(int)--Sets the transaction isolation level, taking as input one of the preceding constant values.
• public abstract boolean isReadOnly()--Returns true if the transaction is READ ONLY; returns false if the transaction is READ WRITE.
• public abstract void setReadOnly(boolean)--Sets the transaction access mode to READ ONLY if true is input; sets the access mode to READ WRITE if false is input.

SQLJ and JDBC Interoperability

As described in "Introduction to SQLJ", SQLJ statements are typically used for static SQL operations. Oracle9i has extensions to support dynamic SQL as well, but another alternative is to use JDBC code within your SQLJ application for dynamic operations (which would be more portable). And there might be additional scenarios where using JDBC code in your SQLJ application might be useful or even required.

Because of this, SQLJ allows you to use SQLJ and JDBC statements concurrently and provides interoperability between SQLJ and JDBC constructs.

Two kinds of interactions between SQLJ and JDBC are particularly useful:

• between SQLJ connection contexts and JDBC connections
• between SQLJ iterators and JDBC result sets

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

SQLJ Connection Context and JDBC Connection Interoperability

SQLJ allows you to convert, in either direction, between SQLJ connection context instances and JDBC connection instances.

Note: When converting between a SQLJ connection context and a JDBC connection, bear in mind that the two objects are sharing the same underlying physical connection. See "About Shared Connections".

Converting from Connection Contexts to JDBC Connections

If you want to perform a JDBC operation through a database connection that you have established in SQLJ (for example, if your application calls a library routine that returns a JDBC connection object), then you must convert the SQLJ connection context instance to a JDBC connection instance.

Any connection context instance in a SQLJ application, whether an instance of the sqlj.runtime.ref.DefaultContext class or of a declared connection context class, contains an underlying JDBC connection instance and a getConnection() method that returns that JDBC connection instance. Use the JDBC connection instance to create JDBC statement objects if you want to use JDBC operations.

Following is an example of how to use the getConnection() method.

Imports:

import java.sql.*;

Executable code:

DefaultContext ctx = new DefaultContext 
         ("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger", true);
...
(SQLJ operations through SQLJ ctx connection context instance)
...
Connection conn = ctx.getConnection();
...
(JDBC operations through JDBC conn connection instance)
...

The connection context instance can be an instance of the DefaultContext class or of any connection context class that you have declared.

To retrieve the underlying JDBC connection of your default SQLJ connection, you can use getConnection() directly from a DefaultContext.getDefaultContext() call, where getDefaultContext() returns a DefaultContext instance that you had previously initialized as your default connection, and getConnection() returns its underlying JDBC connection instance. In this case, because you do not have to use the DefaultContext instance explicitly, you can also use the Oracle.connect() method. This method implicitly creates the instance and makes it the default connection.

(See "Connection Considerations" for an introduction to connection context instances and default connections. See "More About the Oracle Class" for information about the Oracle.connect() method.)

Following is an example.

Imports:

import java.sql.*;

Executable code:

...
Connection conn = Oracle.connect
   ("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger").getConnection();
...
(JDBC operations through JDBC conn connection instance)
...

Example: JDBC and SQLJ Connection Interoperability for Dynamic SQL

Following is a sample method that uses the underlying JDBC connection instance of the default SQLJ connection context instance to perform dynamic SQL operations in JDBC. The dynamic operations are performed using JDBC java.sql.Connection, java.sql.PreparedStatement, and java.sql.ResultSet objects. (For information about such basic features of JDBC programming, see the Oracle9i JDBC Developer's Guide and Reference.)

Alternatively, you can use Oracle SQLJ extensions for dynamic SQL operations. See "Support for Dynamic SQL" for general information. For a rework of this example using SQLJ dynamic SQL functionality with FETCH functionality from a result set iterator, see Example 5: Dynamic SQL with FETCH from Result Set Iterator.

import java.sql.*;

public static void projectsDue(boolean dueThisMonth) throws SQLException {
   // Get JDBC connection from previously initialized SQLJ DefaultContext.
   Connection conn = DefaultContext.getDefaultContext().getConnection();

   String query = "SELECT name, start_date + duration " +
                  "FROM projects WHERE start_date + duration >= sysdate";
   if (dueThisMonth)
      query += " AND to_char(start_date + duration, 'fmMonth') " +
               " = to_char(sysdate, 'fmMonth') ";
   PreparedStatement pstmt = conn.prepareStatement(query);
   ResultSet rs = pstmt.executeQuery();
   while (rs.next()) {
      System.out.println("Project: " + rs.getString(1) + " Deadline: " +
                         rs.getDate(2));
   }
   rs.close();
   pstmt.close();
}

Converting from JDBC Connections to Connection Contexts

If you initiate a connection as a JDBC Connection instance but later want to use it as a SQLJ connection context instance (for example, if you want to use it in a context expression to specify the connection to use for a SQLJ executable statement), you can convert the JDBC connection instance to a SQLJ connection context instance.

The DefaultContext class and all declared connection context classes have a constructor that takes a JDBC connection instance as input and constructs a SQLJ connection context instance.

For example, presume you instantiated and defined the JDBC connection instance conn and want to use the same connection for an instance of a declared SQLJ connection context class MyContext. You can do this as follows:

...
#sql context MyContext;
...
MyContext myctx = new MyContext(conn);
...

About Shared Connections

A SQLJ connection context instance and the associated JDBC connection instance share the same underlying physical connection. As a result, the following is true:

• When you get a JDBC connection instance from a SQLJ connection context instance (using the connection context getConnection() method), the Connection instance inherits the state of the connection context instance. Among other things, the Connection instance will retain the auto-commit setting of the connection context instance.
• When you construct a SQLJ connection context instance from a JDBC connection instance (using the connection context constructor that takes a connection instance as input), the connection context instance inherits the state of the Connection instance. Among other things, the connection context instance will retain the auto-commit setting of the Connection instance. By default, a JDBC connection instance has an auto-commit setting of true, but you can alter this through the setAutoCommit() method of the Connection instance.
• Given a SQLJ connection context instance and associated JDBC connection instance, calls to methods that alter session state in one instance will also affect the other instance, because it is actually the underlying shared session that is being altered.
• Because there is just a single underlying physical connection, there is also a single underlying set of transactions. A COMMIT or ROLLBACK operation in one connection instance will affect any other connection instances that share the same underlying connection.

  Note: It is also possible for multiple SQLJ connection context instances to be created from the same JDBC connection instance and, therefore, to share the same underlying physical connection. This might be useful, for example, if you want to share the same set of transactions between program modules. The preceding notes apply to this situation as well.

Closing Shared Connections

When you get a JDBC connection instance from a SQLJ connection context instance (using the getConnection() method) or you create a SQLJ connection context instance from a JDBC connection instance (using the connection context constructor), you must close only the connection context instance. By default, calling the close() method of a connection context instance closes the associated JDBC connection instance and the underlying physical connection, thereby freeing all resources associated with the connection.

If you want to close a SQLJ connection context instance without closing the associated JDBC connection instance (if, for example, the Connection instance is being used elsewhere, either directly or by another connection context instance), then you can specify the boolean constant KEEP_CONNECTION to the close() method, as follows (assume a connection context instance ctx):

ctx.close(ConnectionContext.KEEP_CONNECTION);

If you do not specify KEEP_CONNECTION, then the associated JDBC connection instance is closed by default. You can also specify this explicitly:

ctx.close(ConnectionContext.CLOSE_CONNECTION);

KEEP_CONNECTION and CLOSE_CONNECTION are static constants of the sqlj.runtime.ConnectionContext interface.

If you do not explicitly close a connection context instance, then it will be closed by the finalizer during garbage collection with KEEP_CONNECTION, meaning the resources of the JDBC connection instance would not be freed until released explicitly or by garbage collection.

If you close only the JDBC connection instance, this will not close the associated SQLJ connection context instance. The underlying physical connection would be closed, but the resources of the connection context instance would not be freed until garbage collection.

Notes: If the same underlying JDBC connection is shared by multiple connection context instances, then use KEEP_CONNECTION when closing all but the last remaining open connection context instance. An error message will be issued if you try to close a connection context instance whose underlying JDBC connection has already been closed, or if you try to close the underlying connection when it has already been closed. If you encounter this, verify that the JDBC connection is not being closed independently by JDBC code, and that all preceding close() calls on SQLJ connection context instances that use the underlying connection use the KEEP_CONNECTION parameter.

SQLJ Iterator and JDBC Result Set Interoperability

SQLJ allows you to convert in either direction between SQLJ iterators and JDBC result sets. For situations where you are selecting data in a SQLJ statement but do not care about strongly typed iterator functionality, SQLJ also supports a weakly typed iterator, which you can convert to a JDBC result set.

Converting from Result Sets to Named or Positional Iterators

There are a number of situations where you might find yourself manipulating JDBC result sets. For example, another package might be implemented in JDBC and provide access to data only through result sets, or might require ResultSetMetaData information because it is a routine written generically for any type of result set. Or your SQLJ application might invoke a stored procedure that returns a JDBC result set.

If the dynamic result set has a known structure, it is typically desirable to manipulate it as an iterator to use the strongly typed paradigm that iterators offer.

In SQLJ, you can populate a named or positional iterator object by converting an existing JDBC result set object. This can be thought of as casting a result set to an iterator, and the syntax reflects this, as follows.

#sql iter = { CAST :rs };

This binds the result set object rs into the SQLJ executable statement, converts the result set, and populates the iterator iter with the result set data.

Following is an example. Assume myEmpQuery() is a static Java function in a class called RSClass, with a predefined query that returns a JDBC result set object.

Imports and declarations:

import java.sql.*;
...
#sql public iterator MyIterator (String ename, float sal);
...

Executable code:

ResultSet rs;
MyIterator iter;
...
rs = RSClass.myEmpQuery();
#sql iter = { CAST :rs };
...
(process iterator)
...
iter.close();
...

This example could have used a positional iterator instead of a named iterator; the functionality is identical.

The following rules apply when converting a JDBC result set to a SQLJ iterator and processing the data:

• To convert to a positional iterator, the result set and iterator must have the same number of columns, and the types must map correctly.
• To convert to a named iterator, the result set must have at least as many columns as the iterator, and all columns of the iterator must be matched by name and type. If the result set and iterator do not have the same number of columns, then the SQLJ translator will generate a warning unless you use the -warn=nostrict option setting.
• The result set being cast must implement the java.sql.ResultSet interface. The class oracle.jdbc.OracleResultSet implements this interface, as does any standard result set class.
• The iterator receiving the cast must be an instance of an iterator class that was declared as public.
• Do not access data from the result set, either before or after the conversion. Access data from the iterator only.
• When you are finished, close the iterator, not the result set. Closing the iterator will also close the result set, but closing the result set will not close the iterator. When interoperating with JDBC, always close the SQLJ entity.

Converting from Named or Positional Iterators to Result Sets

You might also encounter situations where you want to define a query using SQLJ but ultimately need a result set. (SQLJ offers more natural and concise syntax, but perhaps you want to do dynamic processing of the results, or perhaps you want to use an existing Java method that takes a result set as input.)

So that you can convert iterators to result sets, every SQLJ iterator class, whether named or positional, is generated with a getResultSet() method. This method can be used to return the underlying JDBC result set object of an iterator object.

Following is an example showing use of the getResultSet() method.

Imports and declarations:

import java.sql.*;

#sql public iterator MyIterator (String ename, float sal);
...

Executable code:

MyIterator iter;
...
#sql iter = { SELECT * FROM emp };
ResultSet rs = iter.getResultSet();
...
(process result set)
...
iter.close();
...

The following rules apply when converting a SQLJ iterator to a JDBC result set and processing the data.

• When writing iterator data to a result set, you should access data only through the result set. Do not attempt to directly access the iterator, either before or after the conversion.
• When you finish, close the original iterator, not the result set. Closing the iterator will also close the result set, but closing the result set will not close the iterator. When interoperating with JDBC, always close the SQLJ entity.

Using and Converting Weakly Typed Iterators (ResultSetIterator)

You might have a situation similar to what is discussed in "Converting from Named or Positional Iterators to Result Sets", but where you do not require the strongly typed functionality of the iterator. All you might care about is being able to use SQLJ syntax for the query and then processing the data dynamically from a result set.

For such circumstances, you can directly use the type sqlj.runtime.ResultSetIterator to receive query data. See "Result Set Iterators" for general information about the result set iterator types.

In using SQLJ statements and ResultSetIterator functionality instead of using JDBC statements and standard result set functionality, you enable yourself to use the more concise SELECT syntax of SQLJ.

Following is an example of how to use and convert a weakly typed result set iterator.

Imports:

import sqlj.runtime.*;
import java.sql.*;
...

Executable code:

ResultSetIterator rsiter;
...
#sql rsiter = { SELECT * FROM table };
ResultSet rs = rsiter.getResultSet();
...
(process result set)
...
rsiter.close();
...

Note: Oracle SQLJ permits navigation through a result set iterator using the next() method and FETCH CURRENT syntax, as described in "FETCH CURRENT Syntax: from JDBC Result Sets to SQLJ Iterators". Furthermore, for scrollable result set iterators, additional navigation methods are supported. These methods are described in "Scrollable Named Iterators".

Support for Dynamic SQL

Oracle9i SQLJ includes extensions to support dynamic SQL--operations that are not predefined and can change in real-time. Dynamic SQL expressions embedded in SQLJ statements are referred to as meta bind expressions and are described immediately below.

Note: Using JDBC code is still an option for dynamic SQL in Oracle9i, and might be preferable if code portability is a concern, but Oracle9i SQLJ support for dynamic SQL permits use of SQLJ as a single, simplified API for data access. (SQLJ-JDBC interaction is discussed under "SQLJ and JDBC Interoperability".)

Meta Bind Expressions

Meta bind expressions are used for dynamic SQL in SQLJ statements, where otherwise static SQL clauses would appear. A meta bind expression contains a Java identifier of type String or a string-valued Java expression that is interpreted at runtime. In addition, so that SQLJ can perform online semantics-checking, a meta bind expression can optionally include static SQL replacement code to be used for checking during translation.

This section describes usage, restrictions, syntax, and behavior for meta bind expressions.

Meta Bind Expressions--General Usage and Restrictions

You can use a meta bind expression in place of any of the following:

• a table name
• a column name in a SELECT-list (without the column alias, if one was specified)
• all or part of a WHERE clause condition
• a role, schema, catalog, or package name in a DDL or DML statement
• a SQL literal value or SQL expression

Be aware of the following restrictions on meta bind expressions, enforced to ensure that the SQLJ translator can properly determine the nature of the SQL operation and can perform syntactic analysis of the SQLJ statement as a whole:

• A meta bind expression cannot be the first non-comment of the SQL operation within a SQLJ statement.
• A meta bind expression cannot contain the INTO token of a SQLJ SELECT INTO statement and cannot expand to become the INTO-list of a SELECT INTO statement.
• A meta bind expression cannot appear in any of the following kinds of SQL/SQLJ instructions or clauses: CALL, VALUES, PSM SET, COMMIT, ROLLBACK, FETCH INTO, or CAST.

Meta Bind Expressions--Syntax and Behavior

Following is the general syntax for meta bind expressions:

:{ Java_bind_expression }

or:

:{ Java_bind_expression :: SQL_replacement_code }

Spaces are optional.

There can be multiple meta bind expressions within the SQL instructions of a SQLJ statement.

Java Bind Expression

A Java bind expression can be either of the following:

• a Java identifier of type String
• a Java expression that evaluates to a character string

Java bind expressions within meta bind expressions are subject to standard Java lexing rules, and have syntax similar to that of SQLJ host expressions (described in "Java Host Expressions, Context Expressions, and Result Expressions"). However, unlike host expressions, Java bind expressions within meta bind expressions are not enclosed within parentheses. This is because if there is SQL replacement code, the "::" token acts as a separator between the Java bind expression and the SQL code; if there is no SQL replacement code, the closing "}" acts as a terminator. In either case, there is no ambiguity.

Note: There can be no mode specifiers (IN, OUT, or INOUT) within a Java bind expression, or between ":" and "{" of the meta bind expression.

SQL Replacement Code

A SQL replacement code clause consists of a sequence of zero or more SQL tokens, with the following requirements and restrictions:

• It is subject to SQL lexing rules.
• Braces--"{" and "}"--must occur in matching pairs (with the exception of those that are part of a SQL comment, constant, or identifier).
• There can be no SQLJ host expressions or nested meta bind expressions within the SQL instructions.

  Note: It is permissible for the SQL replacement code to be empty.

Translation-Time Behavior

Whenever there is SQL replacement code (even if only an empty string) in a meta bind expression, then the meta bind expression is replaced by the SQL code during translation. The purpose of SQL replacement code is to enable the SQLJ translator to perform online semantics-checking.

If any meta bind expression within a SQLJ statement has no SQL replacement code clause, then the SQLJ translator cannot perform online semantics-checking on the statement--it is only checked syntactically.

Runtime Behavior

At runtime, each meta bind expression is replaced by the evaluation of its Java bind expression.

If a Java bind expression evaluates to null, then the dynamic SQL statement as a whole becomes undefined.

SQLJ Dynamic SQL Examples

This section provides examples of dynamic SQL usage in SQLJ code.

Example 1

...
int x = 10;
int y = x + 10;
int z = y + 10;
String table = "new_Emp";
#sql { INSERT INTO :{table :: emp} VALUES (:x, :y, :z) };
...

During translation, the SQL operation becomes:

INSERT INTO emp VALUES (10, 20, 30);

SQLJ can perform online semantics-checking against a schema that has an emp table. (Perhaps new_Emp only exists in the runtime schema, and is not created until the application executes.)

During runtime, the SQL operation becomes:

INSERT INTO new_Emp VALUES (10, 20, 30);

Example 2

...
String table = "new_Emp";
String query = "ename LIKE 'S%' AND sal>1000";
#sql myIter = { SELECT * FROM :{table :: emp2} 
                         WHERE :{query :: ename='SCOTT'} };
...

During translation, the SQL operation becomes:

SELECT * FROM emp2 WHERE ename='SCOTT';

SQLJ can perform online semantics-checking against a schema that has an emp2 table.

During runtime, the SQL operation becomes:

SELECT * FROM new_Emp WHERE ename LIKE 'S%' AND sal>1000;

Example 3

...
double raise = 1.12;
String col = "comm";
String whereQuery = "WHERE "+col+" IS NOT null";
for (int i=0; i<5; i++)
{
   #sql { UPDATE :{"emp"+i :: emp} 
          SET :{col :: sal} = :{col :: sal} * :raise :{whereQuery ::} };
}
...

During translation, the SQL operation becomes:

UPDATE emp SET sal = sal * 1.12;

SQLJ can perform online semantics-checking against a schema that has an emp table. There is no WHERE clause during translation, because the SQL replacement code is empty.

During runtime, the SQL operation is executed five times, becoming:

UPDATE emp0 SET comm = comm * 1.12 WHERE comm IS NOT null;
UPDATE emp1 SET comm = comm * 1.12 WHERE comm IS NOT null;
UPDATE emp2 SET comm = comm * 1.12 WHERE comm IS NOT null;
UPDATE emp3 SET comm = comm * 1.12 WHERE comm IS NOT null;
UPDATE emp4 SET comm = comm * 1.12 WHERE comm IS NOT null;

Example 4

...
double raise = 1.12;
String col = "comm";
String whereQuery = "WHERE "+col+" IS NOT null";
for (int i=0; i<10; i++)
{
   #sql { UPDATE :{"emp"+i} 
          SET :{col :: sal} = :{col :: sal} * :raise :{whereQuery ::} };
}
...

The runtime behaviors of Example 4 and Example 3 are identical. A difference occurs during translation, however, where SQLJ cannot perform online semantics-checking for Example 4 because there is no SQL replacement code for the first meta bind expression, :{"emp"+i}.

Example 5: Dynamic SQL with FETCH from Result Set Iterator

This example is a rework of "Example: JDBC and SQLJ Connection Interoperability for Dynamic SQL", using SQLJ statements instead of JDBC statements. This example also uses FETCH CURRENT functionality, as described in "FETCH CURRENT Syntax: from JDBC Result Sets to SQLJ Iterators", from a result set iterator.

import java.sql.*;

public static void projectsDue(boolean dueThisMonth) throws SQLException {

   ResultSetIterator rsi;
   String andClause = (dueThisMonth) ? 
                       " AND to_char(start_date + duration, 'fmMonth' ) " 
                       + " = to_char(sysdate, 'fmMonth') " 
                       : "";
   #sql rsi = { SELECT name, start_date + duration FROM projects
                WHERE start_date + duration >= sysdate :{andClause :: } };
   while (rsi.next())
   {
      String name = null;
      java.sql.Date deadline = null;
      #sql { FETCH CURRENT FROM :rsi INTO :name, :deadline };
      System.out.println("Project: " + name + "Deadline: " + deadline);
   } 
   rsi.close();
}