SQLJ and JDBC Interoperability

As described in "Introduction to SQLJ", you can use SQLJ statements for static SQL operations, but not for dynamic operations. You can, however, use JDBC statements for dynamic SQL operations, and there might be situations where your application will require both static and dynamic SQL operations. 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:

• between SQLJ connection contexts and JDBC connections

• between SQLJ iterators and JDBC result sets

For general information about JDBC functionality, see the Oracle8i 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 physical database connection. See "About Shared Connections".

Converting from Connection Contexts to JDBC Connections

If you want to perform a dynamic SQL operation through a database connection that you have established in SQLJ (for example, an operation where the name of the table to select from is not determined until runtime), 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 any dynamic SQL 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);
...
(static operations through SQLJ ctx connection context instance)
...
Connection conn = ctx.getConnection();
...
(dynamic 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();
...
(dynamic 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. 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 Oracle8i JDBC Developer's Guide and Reference.)

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 or OracleConnection 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 database 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 database session that is being altered.

• Because there is just a single underlying database connection, there is also a single underlying set of transactions. A COMMIT or ROLLBACK operation in one connection instance will affect any other connection instances that share the same underlying connection.

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

Closing Shared Connections

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

Note, however, that closing the JDBC connection instance will not close the associated SQLJ connection context instance. The underlying database connection would be closed, but the resources of the connection context instance would not be freed until garbage collection.

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.

SQLJ Iterator and JDBC Result Set Interoperability

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

Converting from Result Sets to Named or Positional Iterators

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

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

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

#sql iter = { CAST :rs };

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

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

Imports and declarations:

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

Executable code:

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

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

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

• To convert to a positional iterator, the result set and iterator must have the same number of columns, and the types must map correctly.

• To convert to a named iterator, the result set must have at least as many columns as the iterator, and all columns of the iterator must be matched by name and type. (If the result set and iterator do not have the same number of columns, then the SQLJ translator will generate a warning unless you use the -warn=nostrict option setting.)

• The result set being cast must implement the java.sql.ResultSet interface. (The class oracle.jdbc.driver.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.

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:

• 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 at any time 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, so that you need not declare a named or positional iterator class.

In using ResultSetIterator 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.

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.

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.

Following is an example of how to use and convert a weakly typed 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();
...

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

• There is no data-access functionality in a ResultSetIterator object. You must convert it to a result set to access the query data.

• When you finish, close the ResultSetIterator object, not the result set. Closing the ResultSetIterator will also close the result set, but closing the result set will not close the ResultSetIterator. When interoperating with JDBC, always close the SQLJ entity.

  Note: Because ResultSetIterator objects are intended only to be converted to JDBC result sets, they are not supported as host expressions in SQLJ.

---