7 Advanced Language Features

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, which have the same names and use the same data types.

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

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. An exemplar schema may or may not be the same account your application will use at run time.

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 enables more thorough and meaningful semantics-checking.

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 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. 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 data types.

#sql context MyContext;

...
MyContext mctx1 = new MyContext
  ("jdbc:oracle:thin:@localhost:5221/myservice", "HR", "hr", false);
MyContext mctx2 =  new MyContext
  ("jdbc:oracle:thin@localhost:5221/myservice", "brian", "mypasswd", false);

Note that connection context class constructors specify a boolean auto-commit parameter. In addition, note that you can connect to the same schema with different connection context instances. In the preceding example, both mctx1 and mctx2 can specify HR/hr if desired. However, during run time, 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 Java Database Connectivity (JDBC) connection instance. One of the constructors of any connection context class takes a JDBC connection instance as input.

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 that take any of the following input parameter sets (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

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.

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:5221/myservice", "HR", "hr", true);

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

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.

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 an instance of the declared DeptContext connection context class 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 
  {
    // 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 first_name, department_id FROM employees }; 

    // 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 departments WHERE department_id = :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.

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 may close the underlying JDBC connection, depending on whether CLOSE_CONNECTION or KEEP_CONNECTION is specified. These are static boolean constants of the ConnectionContext interface.

  See Also:

  "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.

  See Also:

  "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 enable you, for example, to specify a connection context instance for a SQLJ executable statement as follows:

#sql context MyContext;

...
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 the static methods:

• setDefaultStmtCacheSize(int)

• int getDefaultStmtCacheSize()

The following are the instance methods:

• setStmtCacheSize(int)

• int getStmtCacheSize()

By default, statement caching is enabled.

Using the IMPLEMENTS Clause in Connection Context Declarations

There may be situations where it is useful to implement an interface in your connection context declarations. For example, you may want to define an interface that exposes just a subset of the functionality of a connection context class. More specifically, you may want a class that has the 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 HasConnection type, instead of to a variable that has the type of your declared connection context class.

Assuming HasConnection is in the mypackage package, the declaration will be as follows:

#sql public context MyContext implements mypackage.HasConnection;

You can then instantiate a connection instance as follows:

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

For example:

HasConnection myConn = new MyContext 
   ("jdbc:oracle:thin:@localhost:5221/myservice", "HR", "hr", 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 does not mean that all the connection instances of a single class use the same physical entities. Instead, they use entities that have the same properties, such as names and privileges associated with tables and views, data types 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, which 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=HR/hr@jdbc:oracle:oci:@
-user@MyContext=HR/hr@jdbc:oracle:oci:@

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 run-time schema in appropriate ways. For example, it must have tables, views, stored functions, and stored procedures with names and data types 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 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.

Standard Data Source Support

The JDBC 2.0 extended application programming interface (API) specifies the use of data sources and Java Naming and Directory Interface (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 run time through a javax.sql.DataSource object, typically installed through a graphical user interface (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.

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. For example:

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

• EmpCtx(): Looks up the data source for jdbc/EmpDB and then calls the getConnection() method on the data source to obtain a connection.

• 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.

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

• EmpCtx(Connection conn)

• EmpCtx(String url, String user, String password, boolean autoCommit)

• EmpCtx(String url, boolean autoCommit)

• EmpCtx(String url, java.util.Properties info, boolean autoCommit)

• EmpCtx(String url, boolean autoCommit)

Auto-Commit Mode for Data Source Connections

The constructors based on data source, unlike those base on DriverManager, 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.

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 run time 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 javax.sql.* and javax.naming.* packages and an InitialContext provider in your Java environment. The latter is required to obtain the JNDI context in which the SQLJ run time can look up the data source object.

All SQLJ run-time 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 run time to load. By contrast, the other run-time libraries use reflection to retrieve DataSource objects.

SQLJ-Specific Data Sources

The Oracle SQLJ implementation 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 data source class.

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 the following methods to return SQLJ connection context instances:

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

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, it returns 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 enable 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, then they are obtained from the underlying data source that generates the connection. If you do not specify an auto-commit setting, then the default is false unless it was explicitly set to true for the underlying data source.

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:

• interface SqljDataSource extends javax.sql.DataSource, ConnectionContextFactory { }

• interface SqljDataSource extends javax.sql.ConnectionPoolDataSource, ConnectionContextFactory { }

• interface SqljXADataSource extends javax.sql.XADataSource, ConnectionContextFactory { }

SQLJ Data Source Classes

Oracle provides SQLJ-specific counterparts for the following JDBC data source classes: OracleDataSource, OracleConnectionPoolDataSource, OracleXADataSource, OracleConnectionCacheImpl, OracleXAConnectionCacheImpl, and OracleOCIConnectionPool.

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:

• class OracleSqljDataSource extends oracle.jdbc.pool.OracleDataSource implements ConnectionContextFactory

• class OracleSqljConnectionPoolDataSource extends oracle.jdbc.pool.OracleConnectionPoolDataSource implements ConnectionContextFactory

• abstract class OracleSqljXADataSource extends oracle.jdbc.xa.OracleXADataSource implements ConnectionContextFactory

• class OracleSqljOCIConnectionPool extends oracle.jdbc.pool.OracleOCIConnectionPool implements ConnectionContextFactory

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

• 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 the getDefaultContext() and getContext() methods. 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 the data source 
SqljXADataSource sqljDS = new OracleSqljXADataSource(); 
sqljDS.setUser("HR"); 
sqljDS.setPassword("hr"); 
sqljDS.setServerName("myserver"); 
sqljDS.setDatabaseName("orcl"); 
sqljDS.setDataSourceName("jdbc/OracleSqljXADS"); 

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

In a middle-tier 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="HR" 
     password="hr" 
     url="jdbc:oracle:thin:@myhost:5221/myservice" 
/>

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 MyCtx class, 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 Java Server Pages (JSP) pages. The original beans, ConnBean and ConnCacheBean in oracle.jsp.dbutil, are documented in the Oracle Containers for J2EE JSP Tag Libraries and Utilities Reference.

The Oracle SQLJ implementation 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.

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, and the latter returns a sqlj.runtime.ConnectionContext instance, specifically, an instance of the class specified in the ContextClass property (by default, DefaultContext).

However, note that the getDefaultContext() and getContext() methods are implemented differently between SqljConnBean and 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.

  • 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.

  • 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.

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, demonstrates the use of SqljConnCacheBean, its ContextClass bean property, and its getContext() method:

<%@ 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="HR"/> 
     <jsp:setProperty name="cbean" property="Password" value="hr"/> 
     <jsp:setProperty name="cbean" property="URL"
                      value="jdbc:oracle:thin:@myhost:5221/myservice"/> 
     <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("employee_id"); 
   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 first_name, salary, TO_CHAR(hire_date, 'DD-MON-YYYY') 
                           INTO :ename, :sal, :hireDate 
                           FROM HR.employees WHERE UPPER(employee_id) = UPPER(:empno) 
             }; 
             sb.append("<BLOCKQUOTE><BIG><B><PRE>\n"); 
             sb.append("Name       : " + ename + "\n"); 
             sb.append("Salary     : " + sal + "\n"); 
             sb.append("Date hired : " + hireDate); 
             sb.append("</PRE></B></BIG></BLOCKQUOTE>"); 
          } catch (java.sql.SQLException e) { 
              sb.append("<P> SQL error: <PRE> " + e + " </PRE> </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> 

SQLJ Support for Global Transactions

A distributed transaction, sometimes referred to as a global transaction, is a set of two or more related transactions that must be managed in a coordinated way. The transactions that constitute a distributed transaction might be in the same database, but more typically are in different databases and often in different locations. Each individual transaction of a distributed transaction is referred to as a transaction branch.

The X/Open Distributed Transaction Processing (DTP) architecture defines a standard architecture that enables multiple but related transactions belonging to the same resource manager or different resource managers to work as a single unit. It coordinates the work between an application program (AP) and a resource manager (RM) into global transactions. Either all the transactions are committed or rolled back.

The Oracle XA library is an external interface that enables transaction managers other than Oracle server to coordinate global transactions. XA library use supports non-Oracle resource managers, in distributed transactions. This is particularly useful in transactions between several databases and resources. The implementation of the Oracle XA library conforms to the X/Open Distributed Transaction Processing (DTP) software architecture's XA interface specification. The Oracle XA Library is installed as part of Oracle Database Enterprise Edition.

Figure 7-1 Global Transaction

Description of "Figure 7-1 Global Transaction"

Following is an example of Distributed Transaction Processing (DTP) model:

The transaction manager is an external middle tier component residing outside Oracle Database. It provides an API for specifying the boundaries of the transaction and manages commit and recovery. The TM implements a two-phase commit engine to provide an all-or-none semantics across distributed RMs.

A resource manager controls a shared, recoverable resource that can be returned to a consistent state after a failure. For example, Oracle is a resource manager.

The javax.sql.XADataSource interface outlines standard functionality of XA data sources. An XA data source is a factory for XA connections. Oracle JDBC implements the XADataSource interface though the OracleXADatasource class. The getConnection( ) method of the OracleXADatasource class returns an XA connection to the underlying data source. In SQLJ, connections to the database can be obtained through the DefaultContext class or the ConnectionContext class. For multiple connections that use different SQL entities, it is advantageous to use connection context declarations to define additional connection context classes.

The code snippet shows how to create an XADatasource first and then a JDBC connection from the datasource through the following steps:

• Start XA Resource1

• Start XA Resource2

• Perform DML operations with the first Connection object

• End XA Resource1

• End XA Resource2

• Prepare Resource1

• Prepare Resource2

• Commit 1

• Commit 2

Example: Creating an XADatasource and using it to create a JDBC connection

import javax.sql.*;
import javax.transaction.*;
import javax.transaction.xa.*;
...
import oracle.jdbc.driver.*;
import oracle.jdbc.xa.OracleXid;
import oracle.jdbc.xa.OracleXAException;
import oracle.jdbc.xa.client.*;
…………
#sql context MyContext;
#sql iterator Iterator2 (String job_id, String job_title);
#sql iterator Iterator3 (String region_id, String region_name);
…………
class XA3mod{
public static void main (String args [])throws SQLException{
try{
/*create an XADataSource instance*/
OracleXADataSource oxds = new OracleXADataSource();
oxds.setURL(url);
oxds.setUser("hr");
oxds.setPassword("hr");
/*get an XA connection to the underlying data source*/
javax.sql.XAConnection pc1  = oxds.getXAConnection();
/*use the same data source */
javax.sql.XAConnection pc2  = oxds.getXAConnection();
/*get the Physical Connections*/
java.sql.Connection conn1 = pc1.getConnection();
java.sql.Connection conn2 = pc2.getConnection();
/*an application may access data through multiple database connections. Each database connection is enlisted 
with the transaction manager as a transactional resource. The transaction manager obtains an XAResource
 for each connection participating in a global transaction */
XAResource oxar1 = pc1.getXAResource();
XAResource oxar2 = pc2.getXAResource();
/*create the Xids With the Same Global Ids. The Xid interface is a Java mapping of the X/Open transaction 
identifier XID structure*/
Xid xid1 = createXid(1);
Xid xid2 = createXid(2);
/*start the Resources. This would start work on behalf of a transaction branch specified in xid1 and xid2. 
The transaction manager uses the start method to associate the global transaction with the resource, 
and it uses the end method to disassociate the transaction from the resource */
oxar1.start (xid1, XAResource.TMNOFLAGS);
oxar2.start (xid2, XAResource.TMNOFLAGS);
/*Do something with conn1 */
DoSomeWork (conn1);
/*END both the branches */
xar1.end(xid1, XAResource.TMSUCCESS);
xar2.end(xid2, XAResource.TMSUCCESS);
/*Prepare the RMs. The Oracle XA library interface follows the two-phase commit protocol. Preparing the transactions 
is the first step in this protocol. The two phase commit protocol is explained in detail in the glossary section. */
int prp1 =  oxar1.prepare (xid1);
int prp2 =  oxar2.prepare (xid2);
boolean do_commit = true;
if(!((prp1==XAResource.XA_OK)||(prp1==XAResource.XA_RDONLY)))
            do_commit = false;
if(!((prp2==XAResource.XA_OK)||(prp2==XAResource.XA_RDONLY)))
            do_commit = false;
/*issue a commit on all transactions only if all the transactions completed without and errors. Rollback even 
if a single transaction failed.*/
if (prp1 == XAResource.XA_OK)
           if (do_commit)
           oxar1.commit (xid1, false);
           else
              oxar1.rollback (xid1);
if (prp2 == XAResource.XA_OK)
           if (do_commit)
              oxar2.commit (xid2, false);
           else
              oxar2.rollback (xid2);
/* close connections */
conn1.close(); conn1 = null;
conn2.close(); conn2 = null;
pc1.close();   pc1 = null;
pc2.close();   pc2 = null;
} catch (XAException xae){
if (xae instanceof OracleXAException) {
System.out.println("XA Error is " + ((OracleXAException)xae).getXAError());
System.out.println("SQL Error is " +((OracleXAException)xae).getOracleError());
}
}
} //end class

The following examples explain the different SQLJ methods that can accept a JDBC connection obtained from an OracleXADatasource.

Using Oracle.connect( ) method with a JDBC connection obtained from an XA Datasource:

private static void DoSomeWork (java.sql.Connection conn) throws SQLException{
String chr = "XA_CERT";
Oracle.connect(conn);
#sql  {insert into xa_test values (1,:chr)};
try{
     Iterator3 iter = null;
     #sql iter = {SELECT id,name FROM xa_test};
     while (iter.next( )){
     System.out.print(iter.id());
     System.out.print(" ");
     System.out.println(iter.name());
     }
}
catch (Exception e){
     System.out.println(e);
     e.printStackTrace();
     }
}

Using Oracle.getConnection( ) method with a JDBC connection obtained from an XA Datasource

private static void DoSomeWork (java.sql.Connection conn) throws SQLException{
String chr = "XA_CERT";
DefaultContext  ctx = Oracle.getConnection(conn);
#sql [ctx]  {insert into xa_test values (1,:chr)};
try{
    Iterator3 iter = null;
    #sql [ctx] iter = {SELECT id,name FROM xa_test};
    while (iter.next( )){
    System.out.print(iter.id());
    System.out.print(" ");
    System.out.println(iter.name());
    }
}
catch (Exception e){
    System.out.println(e);
    e.printStackTrace();
}
}

Using DefaultContext Constructor with a JDBC connection obtained from an XA Datasource

private static void DoSomeWork (java.sql.Connection conn) throws SQLException{
String chr = "XA_CERT";
DefaultContext  ctx = new DefaultContext(conn)
#sql [ctx]  {insert into xa_test values (1,:chr)};
try{
Iterator3 iter = null;
#sql [ctx] iter = {SELECT id,name FROM xa_test};
while (iter.next( )){
System.out.print(iter.id());
System.out.print(" ");
System.out.println(iter.name());
}
}
catch (Exception e){
System.out.println(e);
e.printStackTrace();
}
}

Using DefaultContext Constructor by passing a ConnectionContext to it. The ConnectionContext is created through the JDBC connection obtained from an XA Datasource

private static void DoSomeWork (java.sql.Connection conn) throws SQLException{
String chr = "XA_CERT";
MyContext myctx1= new MyContext (conn);
DefaultContext  ctx = new DefaultContext(myctx1);
#sql [ctx]  {insert into xa_test values (1,:chr)};
try{
    Iterator3 iter = null;
    #sql [ctx] iter = {SELECT id,name FROM xa_test};
    while (iter.next( )){
    System.out.print(iter.id());
    System.out.print(" ");
    System.out.println(iter.name());
    }
}
catch (Exception e){
    System.out.println(e);
    e.printStackTrace();
}
}

Using Oracle.connect( ) method by passing a ConnectionContext to it. The ConnectionContext is created through the JDBC connection obtained from an XA Datasource

private static void DoSomeWork (java.sql.Connection conn) throws SQLException{
String chr = "XA_CERT";
MyContext myctx1= new MyContext (conn);
Oracle.connect(myctx1);
#sql {insert into xa_test values (1,:chr)};
try{
    Iterator3 iter = null;
    #sql iter = {SELECT id,name FROM xa_test};
    while (iter.next( )){
    System.out.print(iter.id());
    System.out.print(" ");
    System.out.println(iter.name());
    }
}
catch (Exception e){
    System.out.println(e);
    e.printStackTrace();
    }
}

Using Oracle. getConnection( ) method by passing a ConnectionContext to it. The ConnectionContext is created through the JDBC connection obtained from an XA Datasource

private static void DoSomeWork (java.sql.Connection conn) throws SQLException{
String chr = "XA_CERT";
MyContext myctx1= new MyContext (conn);
DefaultContext  ctx = Oracle.getConnection(myctx1);
#sql [ctx]  {insert into xa_test values (1,:chr)};
try{
    Iterator3 iter = null;
    #sql [ctx] iter = {SELECT id,name FROM xa_test};
    while (iter.next( )){
    System.out.print(iter.id());
    System.out.print(" ");
    System.out.println(iter.name());
    }
}
catch (Exception e){
    System.out.println(e);
    e.printStackTrace();
}
}

The setDefaultContext( ) method of the DefaultContext class can also be used to set a context which was created through the JDBC connection obtained from an XA Datasource

DefaultContext.setDefaultContext(ctx);

Using the ConnectionContext constructor by passing a JDBC connection obtained from an XA Datasource

private static void DoSomeWork (java.sql.Connection conn) throws SQLException{
String chr = "XA_CERT";
                        MyContext myctx1= new MyContext (conn);
#sql [myctx1]  {insert into xa_test values (1,:chr)};
try{
    Iterator3 iter = null;
    #sql [myctx1] iter = {SELECT id,name FROM xa_test};
    while (iter.next( )){
    System.out.print(iter.id());
    System.out.print(" ");
    System.out.println(iter.name());
    }
}
catch (Exception e){
    System.out.println(e);
    e.printStackTrace();
}
}

Using the ConnectionContext constructor by passing a ConnectionContext to it. The ConnectionContext is created through the JDBC connection obtained from an XA Datasource

private static void DoSomeWork (java.sql.Connection conn) throws SQLException{
String chr = "XA_CERT";
MyContext myctx= new MyContext (conn);
MyContext myctx1= new MyContext (myctx);
#sql [myctx1]  {insert into xa_test values (1,:chr)};
try{
    Iterator3 iter = null;
    #sql [myctx1] iter = {SELECT id,name FROM xa_test};
    while (iter.next( )){
    System.out.print(iter.id());
    System.out.print(" ");
    System.out.println(iter.name());
    }
}
catch (Exception e){
    System.out.println(e);
    e.printStackTrace();
}
}

Connecting to PDBs

A pluggable database (PDB) enables an Oracle Database to contain a portable collection of schemas, schema objects, and nonschema objects that appears to an Oracle client as a separate database. A multitenant container database (CDB) is an Oracle Database that includes one or more PDBs. SQLJ applications can connect to a PDB using a service, whose PLUGGABLE DATABASE property is set to the relevant PDB.

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 use the same connection context instance.

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

  As you execute successive SQL operations that use 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 use different execution context instances in statements that use 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.

To use 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 use 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. For example:

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, then you can use the following statement:

#sql [myConnCtx, myExecCtx] { DELETE FROM employees WHERE salary > 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 employees WHERE salary > 30000 };

Execution Context Synchronization

ExecutionContext methods are all synchronized methods. Therefore, for ISO standard code generation, anytime a statement tries to use 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.

The preceding discussion does not apply for default Oracle-specific code generation. 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.

Execution Context Methods

The following sections list public methods of the ExecutionContext class and provide an example:

• Status Methods

• Control Methods

• Cancellation Method

• Update Batching Methods

• Savepoint Methods

• Close Method

• Example: Using 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 employees WHERE salary > 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, then 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.

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.

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:

• 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.

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.

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

Using the IMPLEMENTS Clause in Iterator Declarations

There may be situations where it will be useful to implement an interface in your iterator declaration. For example, you may have an iterator class where you want to restrict access to one or more columns. 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 EmpIterIntfc interface that has ename() and empno() methods, but no sal() method. Then you can use the following iterator declaration instead of the preceding declaration (presuming EmpIterIntfc is in the mypackage package):

#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, then they will not have access to the SAL column.

Support for Extending Iterator Classes

SQLJ supports the ability to extend 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 run time 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 sqlj.runtime.ResultSetIterator type 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 enables you to use SQLJ scrollable iterator functionality. 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.

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 the FETCH CURRENT syntax.

If you want to use JDBC to process a result set iterator instance, you can use its getResultSet() method and 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.

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. The Oracle SQLJ implementation supports this functionality.

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. Following 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.

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 the Oracle SQLJ implementation have a sensitivity setting of INSENSITIVE, meaning they do not detect any such changes in the underlying data. However, you can use a 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);

The SQLJ standard also allows a setting of ASENSITIVE, which means accepting the default sensitivity of the Database. But, in Oracle, if you set sensitivity to ASENSITIVE, then it results 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.

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 run time 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 in the previous point).

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 isLast() method may be expensive, because the JDBC driver may 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.

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, nonscrollable iterators have only the following method for navigation:

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

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, then 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.

• 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(). The FETCH syntax, which is patterned after SQL, employs PRIOR. In case you forget this inconsistency, the Oracle Database 12c Release 1 (12.1) SQLJ translator will also accept FETCH PREVIOUS.

The syntax are:

#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. The syntax are:

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

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

#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. If the iterator is on a valid row, then the command just populates the variables.

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 columns of the result set, if not all, 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 MyIter iterator type must implement the Scrollable interface, even if this property is not really needed. To address this, the Oracle SQLJ implementation supports 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 nonscrollable iterators:

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

Scrollable Result Set Iterators

Support in the Oracle SQLJ implementation for weakly typed result set iterators includes a scrollable result set iterator type:

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 first_name, employee_id FROM employees");
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 first_name, employee_id FROM employees };
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 first_name, employee_id FROM employees };
srsi.afterLast();
while (srsi.previous()) {
   #sql { FETCH CURRENT FROM :srsi INTO :x, :y };
}
srsi.close();

SET TRANSACTION Syntax

The SQLJ 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, then it must be the first statement in a transaction, preceding any DML statements. In other words, the first statement since your connection to the database or your most recent COMMIT or ROLLBACK.

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 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, you are not allowed to update the database. SELECT, INSERT, UPDATE, and DELETE are all legal.

• READ ONLY (also supported by the Oracle JDBC implementation): In a READ ONLY transaction, you are 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: Dirty reads, nonrepeatable reads, and phantom reads are all allowed.

• READ COMMITTED (default): Dirty reads are prevented, and nonrepeatable 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: Dirty reads and nonrepeatable reads are prevented, and phantom reads are allowed.

• SERIALIZABLE: Dirty reads, nonrepeatable 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 nonrepeatable 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 Oracle Database 12c Release 1 (12.1), 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:

• 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

• abstract void setTransactionIsolation(int): Sets the transaction isolation level, taking as input one of the preceding constant values.

• abstract boolean isReadOnly(): Returns true if the transaction is READ ONLY. Returns false if the transaction is READ WRITE.

• 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 Connection Context and JDBC Connection Interoperability

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

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.

import java.sql.*;

...
DefaultContext ctx = new DefaultContext 
      ("jdbc:oracle:thin:@localhost:5221/myservice", "HR", "hr", 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.

Following is an example:

import java.sql.*;

...
Connection conn = Oracle.connect
   ("jdbc:oracle:thin:@localhost:5221/myservice", 
    "HR", "hr").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. Alternatively, you can use Oracle SQLJ extensions for dynamic SQL operations.

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();
}

For a rework of this example using SQLJ dynamic SQL functionality with FETCH functionality from a result set iterator, refer to Example 5: Dynamic SQL with FETCH from Result Set Iterator.

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.

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

SQLJ Iterator and JDBC Result Set Interoperability

SQLJ enables 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:

import java.sql.*;
...
#sql public iterator MyIterator (String ename, float sal);
...
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.

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:

import java.sql.*;

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

...
MyIterator iter;
...
#sql iter = { SELECT * FROM employees };
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 sqlj.runtime.ResultSetIterator type to receive query data.

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:

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

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

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 String type or a string-valued Java expression that is interpreted at run time. 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.

Meta Bind Expressions: General Usage and Restrictions

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

• Table name

• Column name in a SELECT statement (without the column alias, if specified)

• All or part of a WHERE clause condition

• Role, schema, catalog, or package name in a data definition language (DDL) or DML statement

• 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 noncomment 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 }

Note that 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:

• Java identifier of the String type

• 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. 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, then the :: token acts as a separator between the Java bind expression and the SQL code. If there is no SQL replacement code, then the closing braces (}) acts as a terminator. In either case, there is no ambiguity.

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 ({ }) 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.

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.

Run-Time Behavior

At run time, 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 run-time schema and is not created until the application executes.

During run time, 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='HR'} };
...

During translation, the SQL operation becomes:

SELECT * FROM emp2 WHERE ename='HR';

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

During run time, 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 run time, 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 run-time behaviors of Example 3 and Example 4 are identical. However, a difference occurs during translation, 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 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();
}

Generating Plan Baselines

You can generate plan baselines for all the SQL statements that are supported by SPM. The generated log file reports the unsupported statements, if any.

Parameters

When specifying plan baseline options, SQLJ generates SQL files with calls to the dbms_spm_internal.create_sql_plan_baseline procedure. This procedure has the following parameters:

Command-Line and Property File Options

Use the following command-line options for generating plan baseline SQL statements:

• plan_baseline

• plan_prefix

• plan_run

• plan_fixed

• plan_enabled

var ORA_SPM_PARSE_SCHEMA varchar2(30);
exec :ORA_SPM_PARSE_SCHEMA:='HR';

<filename>_<filetype>_<sequence_no>

-plan_baseline= <true/false/module_name>

sqlj.plan_baseline=<true/false/module_name>

sqlj test.sqlj –plan_baseline=true -user=HR/hr

Select first_name from employees;
Select employee_id from employees;

var ORA_SPM_PARSE_SCHEMA varchar2(30);
exec :ORA_SPM_PARSE_SCHEMA:='HR';
begin
    dbms_application_info.set_module(’default','');
end;
 
BEGIN 
  BEGIN 
    d := SYS.DBMS_SPM.DROP_SQL_PLAN_BASELINE( PLAN_NAME => 'mypackage_test_sqlj_0000') ; 
    EXCEPTION 
    WHEN OTHERS THEN NULL; 
  END; 
  c:=SYS.DBMS_SPM_INTERNAL.CREATE_SQL_PLAN_BASELINE( 
   'Select first_name from employees /*mypackage_test_sqlj_0000*/', 
   :ORA_SPM_PARSE_SCHEMA, 
   'mypackage_test_sqlj_0000', 
   'no', 
   'no'); 
END ; 
/
BEGIN 
  BEGIN 
    d := SYS.DBMS_SPM.DROP_SQL_PLAN_BASELINE( PLAN_NAME => 'mypackage_test_sqlj_0001') ; 
    EXCEPTION 
    WHEN OTHERS THEN NULL; 
  END; 
  c:=SYS.DBMS_SPM_INTERNAL.CREATE_SQL_PLAN_BASELINE( 
   'Select employee_id from employees /*mypackage_test_sqlj_0001*/', 
   :ORA_SPM_PARSE_SCHEMA, 
   'mypackage_test_sqlj_0001', 
   'no', 
   'no'); 
END ; 
/

Select first_name from employees;
Select employee_id from employees;

sqlj –plan_name=mybaseline -user=HR/hr test.sqlj

var ORA_SPM_PARSE_SCHEMA varchar2(30) ; 
exec :ORA_SPM_PARSE_SCHEMA:='HR'; 
begin
    dbms_application_info.set_module(’mybaseline,'');
end;
 
BEGIN 
  BEGIN 
    d := SYS.DBMS_SPM.DROP_SQL_PLAN_BASELINE( PLAN_NAME => 'mypackage_test_sqlj_0000') ; 
    EXCEPTION 
    WHEN OTHERS THEN NULL; 
  END; 
  c:=SYS.DBMS_SPM_INTERNAL.CREATE_SQL_PLAN_BASELINE( 
   'Select first_name from employees /*mypackage_test_sqlj_0000*/', 
   :ORA_SPM_PARSE_SCHEMA, 
   'mypackage_test_sqlj_0000', 
   'no', 
   'no'); 
END ; 
/
BEGIN 
  BEGIN 
    d := SYS.DBMS_SPM.DROP_SQL_PLAN_BASELINE( PLAN_NAME => 'mypackage_test_sqlj_0001') ; 
    EXCEPTION 
    WHEN OTHERS THEN NULL; 
  END; 
  c:=SYS.DBMS_SPM_INTERNAL.CREATE_SQL_PLAN_BASELINE( 
   'Select employee_id from employees /*mypackage_test_sqlj_0001*/', 
   :ORA_SPM_PARSE_SCHEMA, 
   'mypackage_test_sqlj_0001', 
   'no', 
   'no'); 
END ; 
/

-plan_prefix=<name>

sqlj.plan_prefix=<name>

<name>_<sequence_no>

Select first_name from employees;
Select employee_id from employees;

var ORA_SPM_PARSE_SCHEMA varchar2(30);
exec :ORA_SPM_PARSE_SCHEMA:='HR';
begin
    dbms_application_info.set_module(’default','');
end;
BEGIN 
  BEGIN 
    d := SYS.DBMS_SPM.DROP_SQL_PLAN_BASELINE( PLAN_NAME => 'myprefix_0000') ; 
    EXCEPTION 
    WHEN OTHERS THEN NULL; 
  END; 
  c:=SYS.DBMS_SPM_INTERNAL.CREATE_SQL_PLAN_BASELINE( 
   'Select first_name from employees /*myprefix_0000*/', 
   :ORA_SPM_PARSE_SCHEMA, 
   'myprefix_0000', 
   'no', 
   'no'); 
END ; 
/
BEGIN 
  BEGIN 
    d := SYS.DBMS_SPM.DROP_SQL_PLAN_BASELINE( PLAN_NAME => 'myprefix_0001') ; 
    EXCEPTION 
    WHEN OTHERS THEN NULL; 
  END; 
  c:=SYS.DBMS_SPM_INTERNAL.CREATE_SQL_PLAN_BASELINE( 
   'Select employee_id from employees /*myprefix_0001*/', 
   :ORA_SPM_PARSE_SCHEMA, 
   'myprefix_0001', 
   'no', 
   'no'); 
END ; 

-plan_run=<yes|no>

sqlj.plan_run=<yes|no>

-plan_fixed = <yes|no>

sqlj.plan_fixed=<yes|no>

-plan_enabled = <yes|no>

sqlj.plan_enabled=<yes|no>

Generated SQL File

At the end of precompilation of the SQLJ file with options described in Command-Line and Property File Options, a SQL file is generated. This SQL file contains the SQL statements for creating SPM plans for each SQL statement in the SQLJ file.

This section contains the following topics:

• Generated SQL File Name

• Generated SQL File Format

<filename>_<filetype>_bln.sql

<filename>_sqlj_bln.sql

#sql  {select * from employees };
#sql  {select manager_id from employees };

sqlj test.sqlj –plan_baseline=mybaseline –plan_prefix=myprefix -userid=HR/hr

var ORA_SPM_PARSE_SCHEMA varchar2(30) ;
exec :ORA_SPM_PARSE_SCHEMA:='HR';
begin
    dbms_application_info.set_module(’default','');
end;
BEGIN 
  BEGIN 
    d := SYS.DBMS_SPM.DROP_SQL_PLAN_BASELINE( PLAN_NAME => 'myprefix_0000') ; 
    EXCEPTION 
    WHEN OTHERS THEN NULL; 
  END; 
  c:=SYS.DBMS_SPM_INTERNAL.CREATE_SQL_PLAN_BASELINE( 
   'Select first_name from employees /*myprefix_0000*/', 
   :ORA_SPM_PARSE_SCHEMA, 
   'myprefix_0000', 
   'no', 
   'no'); 
END ; 
/
BEGIN 
  BEGIN 
    d := SYS.DBMS_SPM.DROP_SQL_PLAN_BASELINE( PLAN_NAME => 'myprefix_0001') ; 
    EXCEPTION 
    WHEN OTHERS THEN NULL; 
  END; 
  c:=SYS.DBMS_SPM_INTERNAL.CREATE_SQL_PLAN_BASELINE( 
   'Select employee_id from employees /*myprefix_0001*/', 
   :ORA_SPM_PARSE_SCHEMA, 
   'myprefix_0001', 
   'no', 
   'no'); 
END ;

Generated Log File

At the end of precompilation a SQL file is generated. This section describes the following details of the log file:

• Generated Log File Name

• Generated Log File Format

<filename>_<filetype>_bln.log

<filename>_sqlj_bln.log

#sql  {select * from employees };
#sql  {select manager_id from employees };

sqlj test.sqlj –plan_baseline=true userid=HR/hr

MODULE default 
    SOURCE SQL_0
       select * from employees;
    PLAN NAME 
       mypackage_test_sqlj_0000 
    STATUS Success 
 
/******************************************/ 
/******************************************/ 
MODULE default 
    SOURCE SQL_1
       select manager_id from employees 
    PLAN NAME 
       mypackage_test_sqlj_0001 
    STATUS Success 
 
/******************************************/

sqlj test.sqlj –plan_baseline=true  userid=HR/hr –plan_prefix=myprefix

MODULE default 
    SOURCE SQL_0
       select * from employees;
    PLAN NAME 
       myprefix_0000 
    STATUS Success 
 
/******************************************/ 
/******************************************/ 
MODULE default 
    SOURCE SQL_1
       select manager_id from employees 
    PLAN NAME 
       myprefix_0001 
    STATUS Success 
 
/******************************************/

Generated Java File

At the end of precompilation a Java file is generated.

Suppose, the test.sqlj file is a part of the package mypackage and it contains the following SQL statements:

#sql {select * from employees };

If you precompile the file with the following command:

sqlj test.sqlj –plan_baseline=mybaseline –plan_prefix=myprefix -userid=HR/hr

Then the generated Java file has an identifier appended to the SQL statement as follows:

try {
   String theSqlTS = "select first_name from employees  /*mybaseline_test_sqlj_0001*/";
   __sJT_st = __sJT_ec.prepareOracleStatement(__sJT_cc,"0Select",theSqlTS);
   // execute query
   iter = new Iter(new sqlj.runtime.ref.OraRTResultSet(__sJT_ec.oracleExecuteQuery(),__sJT_st,"0Select",null));
  } finally { __sJT_ec.oracleCloseQuery(); }