Become a Control Freak - with ExecutionContexts
At times you need to obtain additional information about a SQL statement that just ran or you need to control exactly how you want SQL statements to be executed. Take the following examples:

• The statement might have resulted in a warning (not an exception) that you want to inspect.
• An UPDATE or DELETE statement reports the number of rows that was changed or - respectively - removed.
• You want to set a timeout or designate a prefetch size for queries.
• You want to batch several SQL statements and have them all executed together, rather than pay a round trip to the database for each one. In particular, you want to combine the repeated execution of a DML statement, such as an INSERT, an UPDATE or a DELETE that takes on different values for its bind-variables.

All of this - and more - is available on the sqlj.runtime.ExecutionContext. Every connection context (remember, this is the SQLJ equivalent to JDBC connections) has an associated ExecutionContext which can be accessed with getExecutionContext(). Your static DefaultContext also has one. Consider this example:

import sqlj.runtime.ref.DefaultContext;
import sqlj.runtime.ExecutionContext;
...
#sql { UPDATE emp SET sal = sal * 2 };
ExecutionContext ec = DefaultContext.getDefaultContext().getExecutionContext();
System.out.println( ec.getUpdateCount() + " employees are rejoicing.");

String[] badGuys = { "HORRIBLE", "TERRIBLE", "NOGOOD", "VERYBAD" };
ec.setBatching(true);
for (int i=0; i<badGuys.length; i++) {
#sql { DELETE FROM emp WHERE ename = :(badGuys[i]) }; }
ec.executeBatch();

Being Well Connected - Explicitly
Until now you've been brainwashed. You were led to believe that there's only a single, static connection in your SQLJ program, set once with DefaultContext.setDefaultContext(...) and then forgotten about. Even though this makes great marketing copy, it's not the (only) way the world works.

If you're connecting to more than one schema, if you're running an applet in a browser or if you're connecting to the database in a multithreaded program, then you should - nay, you must - use explicit SQLJ connections.

Don't worry: it's really easy. You just put the connection context instance (or an expression evaluating to one) in those square brackets:

[context].

import sqlj.runtime.ref.DefaultContext;
...
DefaultContext ctx =
new DefaultContext("jdbc:oracle:oci8:@", "scott", "tiger", false);
#sql [ctx] { UPDATE emp SET sal = sal * 2 };

Rolling Your Own Connection
Sometimes you need to distinguish between different schemas or databases. Consider a program that establishes connections to two different database schemas. The PILOTS schema has personnel data, flight hours and so on for pilots, and the JETS schema contains maintenance and flight data of aircraft. Your program is working on two different sets of tables, views and stored procedures. Now you need to verify your SQLJ statements against both of these schemas. Typed connection contexts are what let you do this. First, declare two different context types:

#sql context Pilots;
#sql context Jets;

At runtime, you connect to each of the schemas using the appropriate type:

Pilots pconn = new Pilots("jdbc:oracle:oci8:@","pilots","ace",false);
Jets jconn = new Jets("jdbc:oracle:oci8:@","jets","stratos",false);

#sql [pconn] { INSERT INTO pilot VALUES ( .... ) }; // Pilots context
#sql [jconn] { UPDATE maintenance SET status = Checkup( .... ) }; // Jets context

sqlj -user@Pilots=pilots/ace -user@Jets=jets/stratos MyFile.sqlj

Not so quick - SQLJ and JDBC are actually close-knit buddies. JDBC connections and SQLJ connection contexts are mutually convertible and so are java.sql.ResultSets and SQLJ iterators. Let's look at the specifics.

Connecting from JDBC to SQLJ
All connection context constructors and initializers can take an existing JDBC connection. Example:

java.sql.Connection conn = DriverManager.getConnection(....);
DefaultContext ctx = new DefaultContext(conn);

Connecting from SQLJ to JDBC
All SQLJ connection contexts have the getConnection() method that allows you to retrieve an underlying JDBC connection. For example, if your program uses the static default context, the following will do:

java.sql.Connection conn =
DefaultContext.getDefaultContext().getConnection();

SomeIterator iter;
java.sql.ResultSet rs = stmt.executeQuery();
#sql iter = { CAST :rs };

Passing Iterators fromSQLJ to JDBC
This one's a breeze. You just call the iterator's getResultSet() method and - voilˆ - your JDBC ResultSet.

Exercise 1: Why might it be useful to convert a JDBC result set into a SQLJ iterator or vice versa?

Jump into the Fray
Hope you enjoyed the journey, even though we've not yet seen all the vistas. We stopped short of covering the new fun JDBC 2.0 features that made it into SQLJ, such as support for various SQL LOB types, named SQL types, DataSources and scrollable iterators. Nor did we look at how to put iterator subclassing to use or how connection caching meshes with SQLJ or even speculate what directions SQLJ might take in the future. Is there a place to learn more? In my totally biased opinion (Oracle pays my bills, after all) the vastest amount of information on SQLJ is on the Oracle Technology Network site at http://technet.oracle.com. You'll find the world's most humongous SQLJ manual that answers more questions than you could ever dream of, as well as demos, samples, papers, an FAQ and so on. Beware, though, the Oracle-specific is happily stirred in with the generic.

Better yet, do it. If you want to get involved on the standards side, subscribe to the SQLJ partners mailing list at sqlj-ext@eng.sun.com. Or, if you want to hack the SQLJ reference implementation - yep, all source, all public domain - go to www.sqlj.org. Or download one of the SQLJ implementations from the IBM, Informix, or Oracle Web site and get started.

Summary of SQLJ Syntax

• Connection context declaration and use:

  #sql context CtxType;
  ...
  CtxTypectx = new CtxType(url, user, pwd, auto-commit);
  #sql [ctx] { ...sql statement... };

• Setting the translate-time connection for CtxType:

  sqlj -user@CtxType=user/pwd ...

• ExecutionContext declaration and use:

  import sqlj.runtime.ExecutionContext;
  ...
  ExecutionContext ec = new ExecutionContext();
  ... set properties on ec ...
  #sql [ec] { ...sql statement... };
  ... retrieve warnings, update count, side-channel results, etc. from ec ...

• Using an explicit ExecutionContext and an explicit connection context:

  #sql [ctx, ec] { ...sql statement... };

• Casting SQLJ iterators to JDBC result sets:

  SqljIterator iter;
  #sql iter = { CAST :jdbc_result_set };