Combining SQLJ and JDBC 

Use

If your application needs to use both dynamic and static SQL, you can use SQLJ and JDBC together in the same program.

This procedure explains how you can switch between:

·        JDBC connections and SQLJ connection contexts

·        JDBC result sets and SQLJ iterators.

Procedure

Exchanging Connections

Open SQL/SQLJ uses Open SQL/JDBC as its runtime layer. Open SQL/SQLJ and Open SQL/JDBC can share the same database connection and transaction. However, Open SQL/SQLJ cannot run on the same connection as Native or Vendor JDBC.

Using a JDBC Connection with SQLJ

All connection context classes have a constructor that takes an existing JDBC connection as its argument. The SQLJ connection context that is created using this constructor shares the underlying database connection with the relevant JDBC connection.

Closing the SQLJ connection context with the close()method also closes the underlying JDBC connection. Invoking the method close(boolean closeConnection) with the argument ConnectionContext.KEEP_CONNECTION only detaches the SQLJ connection context object from the underlying JDBC connection.

#sql context MyCtx;   //...   Connection conn = ... ; Statement stmt = conn.createStatemnt(); stmt.executeUpdate("INSERT                  INTO employee (id, name)                  VALUES (123, 'Young')");   MyCtx ctx = new MyCtx(conn);   #sql [ctx] { DELETE FROM employee WHERE id = 123 };

SQLJ connection context from JDBC connection. A SQLJ connection context ctx is constructed from the JDBC connection conn. Now ctx and conn share the same database connection. The INSERT and the DELETE statement will be executed on the database connection and share the same transaction.

Getting a JDBC connection from an SQLJ Context

The getConnection() method of the ConnectionContext interface enables you to retrieve the JDBC connection underlying an SQLJ connection context.

#sql context DemoCtx with (dataSource = "jdbc/DEMO");   // ...   DemoCtx ctx = new DemoCtx(); #sql [ctx] { INSERT             INTO employee (id, name)             VALUES (123, 'Young') };   Connection conn = ctx.getConnection(); Statement stmt = conn.createStatemnt(); stmt.executeUpdate(    "DELETE FROM employee WHERE id = 123");

JDBC connection from SQLJ connection context. The JDBC connection underlying the SQLJ connection context ctx retrieved by the getConnection() method. Now ctx and conn share the same database connection. The INSERT and the DELETE statement will be executed on the database connection and share the same transaction.

Exchanging ResultSets and Iterators

Passing ResultSets from JDBC to SQLJ

If you want to convert a JDBC ResultSet to an SQLJ iterator you can do so with an SQLJ CAST statement. In Open SQL/SQLJ, the CAST statement can be applied to any result set iterator visible in the current scope. If compatibility with third party SQLJ translators is required, the CAST statement shall only be applied to public result set iterators. Once the SQLJ ResultSetIterator object has been generated, all operations to fetch data should be done through it.

#sql iterator NamedIterator (String name);   //...   NamedIterator namIter; Connection conn = ...   Statement stmt = conn.createStatement(); ResultSet rs =    stmt.executeQuery("SELECT name FROM employee" );   #sql namIter = { CAST :rs };   while (namIter.next()) {    System.out.println(namIter.name());

SQLJ iterator from JDBC result set. The JDBC result set rs is converted to a SQLJ result set iterator using the SQLJ cast statement.

Passing Iterators from SQLJ to JDBC

All SQLJ result set iterators have the getResultSet() method to retrieve an underlying JDBC ResultSetobject. Once the JDBC ResultSet object has been produced, all operations to fetch data should be done through the JDBC ResultSet object.

#sql iterator NamedIterator (String name);   //...   NamedIterator namIter = null; #sql [ctx] namIter = { SELECT name FROM employee };   ResultSet rs = namIter.getResultSet(); while (rs.next()) {    System.out.println(rs.getString(1)); }

JDBC result set from SQLJ iterator. On SQLJ result set iterator namIter the getResultSet() method is called to convert the result set iterator to a JDBC result set.