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 will typically not 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 purely 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, you 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):

• URL (String), user name (String), password (String), auto-commit (boolean)

• URL (String), java.util.Properties object, auto-commit (boolean)

• URL (String fully specifying connection and including user name and password), auto-commit setting (boolean)

• JDBC connection object (Connection or OracleConnection)

• SQLJ connection context object

  Notes: When using the constructor that takes a JDBC connection object, do not initialize the connection context instance with a null JDBC connection. The auto-commit setting determines whether SQL operations are automatically committed. For more information, see "Basic Transaction Control".

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. "Declaration IMPLEMENTS Clause" and "Declaration WITH Clause" discuss these.

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.

Note: You typically must register your JDBC driver prior to constructing a connection context instance. See "Driver Selection and Registration for Runtime".

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.

Note: Your default connection must be an instance of the DefaultContext class, not of a 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:

• close(boolean CLOSE_CONNECTION/KEEP_CONNECTION)--Releases all resources used in maintaining this connection and closes any open connected profiles. It might or might not close the underlying JDBC connection, depending on whether CLOSE_CONNECTION or KEEP_CONNECTION is specified. These are static boolean constants of the ConnectionContext interface.

  For further discussion, see "Closing Shared Connections".

• getConnection()--Returns the underlying JDBC connection object for this connection context instance.

• getExecutionContext()--Returns the default ExecutionContext instance for this connection context instance. For more information, see "Execution Contexts".

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:

• getDefaultContext()--This is a static method that returns the default connection context instance for a given connection context class.

• setDefaultContext(Your_Ctx_Class conn_ctx_instance)--This is a static method that defines the default context instance for a given connection context class.

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, auto-commit);
...
MyContext.setDefaultContext(myctx1);
...
#sql [MyContext.getDefaultContext()] { SQL operations };
...

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 entities 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 the SQLJ -user, -password, and -url options. (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 examines each SQLJ statement in your code that specifies an instance of the connection context class and checks its SQL operations (such as what tables you access and what stored procedures you use).

• It verifies that entities in the SQL operations match the set of entities existing in the exemplar schema.

It is the responsibility of the application developer 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 you need not 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.

DataSource Support

The JDBC 2.0 extended API specifies the use of DataSources and JNDI as a portable alternative to the current 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.

Associating A Connection Context With A DataSource

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

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

Any connection context that you declare with a dataSource property, such as EmpCtx above, provides the following new constructors:

• public EmpCtx()--Looks up the DataSource for "jdbc/EmpDB" and then calls the getConnection() method on it to obtain a connection.

• public EmpCtx(String user, String password)--Looks up the DataSource for "jdbc/EmpDB" and then calls the getConnection(user,password) on it to obtain a connection.

• public EmpCtx(ConnectionContext ctx)--Delegates to ctx to obtain a connection.

Any connection context declared with a dataSource property, such as EmpCtx above, will omit the following DriverManager-based constructors:

• public EmpCtx(Connection conn)

• public EmpCtx(String url, String user, String password, boolean autoCommit)

• public EmpCtx(String url, boolean autoCommit)

• public EmpCtx(String url, java.util.Properties info, boolean autoCommit)

• public EmpCtx(String url, boolean autoCommit)

Auto Commit Mode for DataSource Connections

Unlike the DriverManager-based constructors they replace, the new DataSource-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.

.

Associating a DataSource with the DefaultContext

If the SQLJ program accesses the default connection context, and the default context has not 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 name:

jdbc/defaultDataSource

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

Providing DataSourceSupport

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 DataSource object.

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

All the SQLJ runtime libraries support DataSources. However, if you use the runtime12ee.zip you always need to have javax.sql.* and javax.naming.* in your CLASSPATH or the runtime will not load. By contrast, the other libraries--runtime.zip, runtime11.zip, and runtime12.zip--use reflection to retrieve DataSource objects.

---