SQLJ and JDBC Interoperability

As described in "Introduction to SQLJ", you can use SQLJ statements for static SQL operations but must use JDBC statements for dynamic SQL operations. There may 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.

Converting from Connection Contexts to JDBC Connections

If you want to perform a dynamic SQL operation through a database connection 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 java.sql.Connection instance and a getConnection() method that returns that Connection instance. Use the 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 java.sql.Connection instance but later want to use it as a SQLJ connection context instance (for example, if you want to use it in a context expression to specify the connection to use for a SQLJ executable statement), you can convert the JDBC Connection instance to a SQLJ connection context instance.

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

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

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

About Shared Connections

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

• When you get a JDBC java.sql.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 this can be modified using 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.

Closing Shared Connections

Whether you get a JDBC java.sql.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 only need to close 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 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 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 may find yourself manipulating JDBC result sets. For example, another package may be implemented in JDBC and may provide access to data only through result sets, or may require ResultSetMetaData information because it is a routine written generically for any kind of result set. Or your SQLJ application may 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. Presume 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 empname, float empsal);
...

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, the SQLJ translator will generate a warning unless you use the -warn=nostrict option setting.)

• The result set being cast must implement the interface java.sql.ResultSet. (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 JDBC Result Sets

You may 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 empname, float empsal);
...

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 may have a situation similar to what is discussed in "Converting from Named or Positional Iterators to JDBC Result Sets", but where you do not at any time require the strongly typed functionality of the iterator. All you may 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 interface sqlj.runtime.ResultSetIterator to receive query data, and avoid having to 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.

---