Oracle9i SQLJ Developer's Guide and Reference
Release 1 (9.0.1)
Part Number A90212-01
Go To Documentation Library
Home		 Go To Product List
Solution Area		 Go To Table Of Contents
Contents		 Go To Index
Index

Go to previous page Go to next page

7
Advanced Language Features

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

The following topics are discussed:

Connection Contexts

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

Note:

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

Connection Context Concepts

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

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

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

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

Notes:

  • Be aware that a connection context class declaration does not define a set of SQL entities to be used with the declared connection context class, and it is permissible to use the same connection context class for connections that use disparate and unrelated sets of entities. How you use your connection context classes is at your discretion. All that limits the SQL entities you can use with a particular connection context class are the set of entities available in the exemplar schema (if you use online semantics-checking during translation) and the set of entities available in the schema you connect to at runtime, using instances of the connection context class.

  • If you use qualified SQL names in your application--names such as SCOTT.EMP, which specifies the schema where the entity resides--then the exemplar schema (if you use online checking) and runtime schema must have permission to access resources by these fully qualified names.

  • It is possible to use a single connection context class, even for connections to databases from different vendors, as long as each schema you connect to has entities that are accessible by the same names and that use compatible datatypes.

 

Connection Context Logistics

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

sqlj.runtime.ref.DefaultContext

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

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

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

Declaration: 

#sql context MyContext;

Executable code: 

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

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

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

More About Declaring and Using a Connection Context Class

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

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

Declaring the Class

The following declaration creates a connection context class: 

#sql context OrderEntryCtx <implements clause> <with clause>;

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

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

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


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

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

Creating a Connection Context Instance

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

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

For example: 

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

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

Notes:

 

Specifying a Connection Context Instance for a SQLJ Clause

Recall that the basic SQLJ statement syntax is as follows: 

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

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

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

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

Closing a Connection Context Instance

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

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

Example of Multiple Connection Contexts

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

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

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

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

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

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

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

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

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

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

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

Implementation and Functionality of Connection Context Classes

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

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

Note:

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

ConnectionContext Interface

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

Basic methods specified by this interface include the following:

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:

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

Declaration: 

#sql context MyContext;

Executable code: 

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

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

And the following are instance methods:

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

Use of the IMPLEMENTS Clause in Connection Context Declarations

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

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

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

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

#sql public context MyContext implements mypackage.HasConnection;

Then you can instantiate a connection instance as follows: 

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

For example: 

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

Semantics-Checking of Your Connection Context Usage

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

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

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

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

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

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

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

Note:

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

Data Source Support

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

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

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

Associating A Connection Context With A Data Source

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

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

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

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:

Auto-Commit Mode for Data Source Connections

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

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

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

Note:

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

  • If you use the Oracle class, auto-commit is off unless you turn it on explicitly.

  • If you use DefaultContext or a connection context class with DriverManager-style constructors, then the auto-commit setting must always be specified explicitly.

  • If you use the data source mechanism, then the auto-commit setting is inherited from the underlying data source. In most environments, the data source object originates from JDBC, and the auto-commit option is on. To avoid unexpected behavior, always check the auto-commit setting.

 

Associating a Data Source with the Default Context

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

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

Data Source Support Requirements

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

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

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

Execution Contexts

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

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

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:

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

For example, it is useful to use multiple execution context instances with a single connection context instance if you use multithreading, with a separate execution context instance for each thread. And you can use multiple connection context instances with a single explicit execution context instance if your program is single-threaded and you want the same set of SQL control parameters to apply to all the connection context instances. (See "ExecutionContext Methods" for information about SQL control settings.)

To employ different execution context instances with a single connection context instance, you must create additional instances of the ExecutionContext class and specify them appropriately with your SQLJ statements.

Creating and Specifying Execution Context Instances

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

ExecutionContext myExecCtx = new ExecutionContext();

You can then specify this execution context instance for use with any particular SQLJ statement, much as you would specify a connection context instance. The next example shows this syntax. 

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

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

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

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

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

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

Notes:

  • If you specify a connection context instance without an execution context instance, then the default execution context instance of that connection context instance is used.

  • If you specify an execution context instance without a connection context instance, then the execution context instance is used with the default connection context instance of your application.

  • If you specify no connection context instance and no execution context instance, then SQLJ uses your default connection and its default execution context instance.

 

Execution Context Synchronization

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

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

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

There are two exceptions to the preceding discussion:

ExecutionContext Methods

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

Status Methods

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

Control Methods

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

Cancellation Method

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

Update Batching Methods

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

Example: Use of ExecutionContext Methods

The following code demonstrates the use of some ExecutionContext methods: 

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

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

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

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

Relation of Execution Contexts to Multithreading

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

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

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

  2. Specify execution contexts with your #sql statements so that each thread uses its own execution context. (See "Creating and Specifying Execution Context Instances".)

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

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

Note:

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

Multithreading in SQLJ

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

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

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

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

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

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

For a complete multithreading sample application, see "Multithreading--MultiThreadDemo.sqlj".

Important:

The preceding paragraph does not apply if you use Oracle-specific code generation, through the SQLJ translator -codegen=oracle setting. 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. 

Iterator Class Implementation and Advanced Functionality

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

Implementation and Functionality of Iterator Classes

Any named iterator class you declare will be generated by the SQLJ translator to implement the sqlj.runtime.NamedIterator interface. Classes implementing the NamedIterator interface have functionality that maps iterator columns to database columns by name, as opposed to 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, as opposed to by name.

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

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

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

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

As discussed in "Using Positional Iterators", use a FETCH INTO statement together with the endFetch() method to advance through the rows of a positional iterator and retrieve the data. A FETCH INTO statement implicitly calls the next() method. Do not explicitly use the next() method in a positional iterator unless you are using the special FETCH CURRENT syntax (described in "From JDBC Result Sets to SQLJ Iterators -- 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 and is discussed in "SQLJ Iterator and JDBC Result Set Interoperability".

Note:

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

Use of the IMPLEMENTS Clause in Iterator Declarations

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

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

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

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

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

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

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

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

Support for Subclassing of Iterator Classes

SQLJ supports the ability to subclass iterator classes. This feature can be very useful in allowing you to add functionality to your queries and query results. See "Subclassing Iterators--SubclassIterDemo.sqlj" for an example of an iterator subclass that treats rows of a query as individual objects and writes them into a Java vector.

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

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

Result Set Iterators

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

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

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

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

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

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

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

Note:

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

Scrollable Iterators

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

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

Declaring Scrollable Iterators

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

implements sqlj.runtime.Scrollable

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

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

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

Scrollable Iterator Sensitivity

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

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

Important:

The implements clause must precede the with clause. 

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

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

The Scrollable Interface

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

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

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:

Scrollable Named Iterators

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

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

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

The following methods are supported:

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

Scrollable Positional Iterators

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

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

This is actually an abbreviated version of the following syntax. 

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

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

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

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

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

Note:

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

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

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

you must write the following: 

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

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

Note:

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

From JDBC Result Sets to SQLJ Iterators -- FETCH CURRENT Syntax

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

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

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

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

This translates along the following lines to SQLJ: 

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

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

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

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

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

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

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

Scrollable Result Set Iterators

In Oracle9i SQLJ, support for weakly typed result set iterators is extended to add a scrollable result set iterator type: 

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 "From JDBC Result Sets to SQLJ Iterators -- FETCH CURRENT Syntax".

Consider the following JDBC code: 

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

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

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

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

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

Notes:

Scrollability and FETCH CURRENT functionality for result set iterators was not supported in Oracle8i SQLJ releases. 

Advanced Transaction Control

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

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

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

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

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

SET TRANSACTION Syntax

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

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

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

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

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

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

#sql { SET TRANSACTION READ WRITE };

#sql { SET TRANSACTION ISOLATION LEVEL SERIALIZABLE };

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

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

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

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

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

Access Mode Settings

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

Isolation Level Settings

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

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

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

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

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

SERIALIZABLE > REPEATABLE READ > READ COMMITTED > READ UNCOMMITTED

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

Using JDBC Connection Class Methods

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

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

SQLJ and JDBC Interoperability

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

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

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

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

SQLJ Connection Context and JDBC Connection Interoperability

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

Note:

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

Converting from Connection Contexts to JDBC Connections

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

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

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

Imports: 

import java.sql.*;

Executable code: 

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

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

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

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

Following is an example.

Imports: 

import java.sql.*;

Executable code: 

...
Connection conn = Oracle.connect(
     "jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger").getConnection();
...
(JDBC operations through JDBC conn connection instance)
...
Example: JDBC and SQLJ Connection Interoperability for Dynamic SQL

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

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

import java.sql.*;

public static void projectsDue(boolean dueThisMonth) throws SQLException {

   // Get JDBC connection from previously initialized SQLJ DefaultContext.
   Connection conn = DefaultContext.getDefaultContext().getConnection();

   String query = "SELECT name, start_date + duration " +
                  "FROM projects WHERE start_date + duration >= sysdate";
   if (dueThisMonth)
      query += " AND to_char(start_date + duration, 'fmMonth') " +
               " = to_char(sysdate, 'fmMonth') ";

   PreparedStatement pstmt = conn.prepareStatement(query);
   ResultSet rs = pstmt.executeQuery();
   while (rs.next()) {
      System.out.println("Project: " + rs.getString(1) + " Deadline: " +
                         rs.getDate(2));
   }
   rs.close();
   pstmt.close();
}

Converting from JDBC Connections to Connection Contexts

If you initiate a connection as a JDBC Connection instance but later want to use it as a SQLJ connection context instance (for example, if you want to use it in a context expression to specify the connection to use for a SQLJ executable statement), then 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:

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 (presume you have been using a connection context instance ctx): 

ctx.close(ConnectionContext.KEEP_CONNECTION);

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

ctx.close(ConnectionContext.CLOSE_CONNECTION);

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

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

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

Notes:

  • If the same underlying JDBC connection is shared by multiple connection context instances, then use KEEP_CONNECTION when closing all but the last remaining open connection context instance.

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

 

SQLJ Iterator and JDBC Result Set Interoperability

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

Converting from Result Sets to Named or Positional Iterators

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

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

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

#sql iter = { CAST :rs };

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

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

Imports and declarations: 

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

Executable code: 

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

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

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

For a complete example of how SQLJ and JDBC can interoperate in the same program, see "Interoperability with JDBC--JDBCInteropDemo.sqlj".

Converting from Named or Positional Iterators to Result Sets

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

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

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

Imports and declarations: 

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

Executable code: 

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

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

Using and Converting Weakly Typed Iterators (ResultSetIterator)

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

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

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

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

Imports: 

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

Executable code: 

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

Note:

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

Support for Dynamic SQL

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

Note:

In Oracle8i SQLJ releases, you must use JDBC code for dynamic SQL functionality. Using JDBC code is still an option in Oracle9i, and may be preferable if code portability is a concern, but new Oracle9i SQLJ support for dynamic SQL permits use of SQLJ as a single, simplified API for data access. (SQLJ-JDBC interaction is discussed under "SQLJ and JDBC Interoperability".) 

Meta Bind Expressions

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

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

Meta Bind Expressions--General Usage and Restrictions

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

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:

Meta Bind Expressions--Syntax and Behavior

Following is the general syntax for meta bind expressions: 

:{ Java_bind_expression }

or: 

:{ Java_bind_expression :: SQL_replacement_code }

Spaces are optional.

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

Java Bind Expression

A Java bind expression can be either of the following:

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

Note:

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

SQL Replacement Code

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

Translation-Time Behavior

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

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

Runtime Behavior

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

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

SQLJ Dynamic SQL Examples

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

Note:

See "Dynamic SQL--DynamicDemo.sqlj" for a full sample application of dynamic SQL functionality of SQLJ, PL/SQL, and JDBC . 

Example 1

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

During translation, the SQL operation becomes: 

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

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

During runtime, the SQL operation becomes: 

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

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

During translation, the SQL operation becomes: 

SELECT * FROM emp2 WHERE ename='SCOTT';

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

During runtime, the SQL operation becomes: 

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

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

During translation, the SQL operation becomes: 

UPDATE emp SET sal = sal * 1.12;

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

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

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

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

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

Example 5: Dynamic SQL with FETCH from Result Set Iterator

This example is a rework of "Example: JDBC and SQLJ Connection Interoperability for Dynamic SQL", using SQLJ statements instead of JDBC statements. This example also uses FETCH CURRENT functionality, as described in "From JDBC Result Sets to SQLJ Iterators -- FETCH CURRENT Syntax", 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();
}

Go to previous page Go to next page
Oracle
Copyright © 1996-2001, Oracle Corporation.
All Rights Reserved.
Go To Documentation Library
Home		 Go To Product List
Solution Area		 Go To Table Of Contents
Contents		 Go To Index
Index