Connection Contexts

SQLJ supports connection contexts for connecting to different types of database schemas from the same application.

Connection Context Concepts

When connecting to different schema types you will typically want to declare one or more connection context classes, as discussed in "Overview of SQLJ Declarations". Each connection context class can be used for a particular type of schema, meaning that all the connections you define using a particular connection context class will use the same set of SQL objects (such as tables, views, and stored procedures). Note, however, that a connection context declaration does not define a type of schema that the connection context class is used for, and it is permissible to use the same connection context class for different schema types.

An example of a schema type 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 schema type 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 stored procedures GIVE_RAISE and CHANGE_WITHHOLDING.

The advantage in tailoring connection context classes to database schemas is in the degree of online semantics-checking that this allows. To avoid semantics errors when doing online checking, all of the SQL objects used in SQLJ statements that use a given connection context class must match SQL objects found in the exemplar schema you provide for online checking of that connection context class. (The exemplar schema is the database connection you provide using the SQLJ translator -user, -password, and -url options. See "Connection Options" for information about these options.)

If you have SQLJ statements that relate to a variety of schemas but use a single connection context class, then the exemplar schema you provide for this connection context class must be very general, containing all of the tables, views, and stored procedures that are used in any of the schemas. Alternatively, if all of the SQLJ statements using a given connection context class use a set of SQL objects belonging to a single schema type, then you can provide a more meaningful exemplar schema which allows more accurate semantics-checking.

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 (username, password, and URL) and a particular session and transaction in which SQL operations will execute. You typically accomplish this by specifying a username, 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 Connection Context Instances and Execution Context Instances".

The following example shows basic declaration and use of a connection context class, MyContext, to connect to two different schemas (for typical usage, we will assume these schemas are of the same schema type):

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 "Declaring and Using a Connection Context Class").

Also note that you can connect to the identical schema with different connection context instances. During runtime, however, one connection does not see changes to the database made from the other connection until the changes are committed. In the example above, both mctx1 and mctx2 could connect to scott/tiger if desired.

Note: When some SQLJ documentation uses the term connection context, the term refers to a connection context class rather than a particular connection context instance. Remember that a connection context instance defines a particular connection, specifying a database URL, username, and password. A connection context class can be used for any number of such instances, typically to the same schema type. For clarity, this document specifies whether it is discussing a connection context class or a connection context instance.

When to Declare Connection Contexts

The following are examples of situations where you will typically declare one or more connection context classes:

• if you are connecting to different kinds of databases from the same application (such as Oracle8 and Sybase)

• if you are connecting to the same kind of database (such as Oracle8) but are using multiple types of schemas from the same application (in other words, you are using different sets of SQL objects such as tables, views, and stored procedures)

The program in "Example of Multiple Connection Contexts" demonstrates the use of multiple contexts. It uses the default context to access a table of employees and a user-defined context to access employee department information. By using distinct contexts, it is possible for you to store the employee and department information in different schemas or even physically different databases.

For an overview of single connections vs. multiple connections, see "Connection Considerations".

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), username (String), password (String), auto-commit (boolean)

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

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

• JDBC Connection object only

• SQLJ connection context instance only

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 ConnectionContext. 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); 
} 

Instantiating a Connection Object

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 using one of its constructors, as discussed in "More About the DefaultContext Class".

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

Specifying a Connection 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 object is myOrderConn from the previous example:

#sql [myOrderConn] { UPDATE TAB2 SET COL1 = :w WHERE :v < COL2 };

This is the same way you might specify instances of DefaultContext if you require multiple connections but to the same type of database schema (as discussed in "Connection Considerations").

Note: Your default connection must be an instance of the DefaultContext class, not of a declared connection context class. This means that any executable statement that uses an instance of a declared connection context class must explicitly specify the appropriate connection instance, as shown above.

Example of Multiple Connection Contexts

The following is an example of a SQLJ application using multiple connection contexts. It implicitly uses an object of the DefaultContext class for one type of schema and uses an instance of the declared connection context class DeptContext for another type of schema.

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 current 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 the 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. Additionally, it closes the underlying JDBC connection if you pass in CLOSE_CONNECTION, a static boolean constant of the ConnectionContext interface. It does not close the underlying JDBC connection if you pass in the boolean KEEP_CONNECTION, which is also a static constant of ConnectionContext.

  If you do not pass in a parameter, the default is CLOSE_CONNECTION. If you do not explicitly close a connection context instance, the finalizer will close it with a KEEP_CONNECTION setting.

  Specify one of these constants as follows (assume a connection context instance ctx):

  ctx.close(ConnectionContext.CLOSE_CONNECTION);
  
  

  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 the strong typing of connection context classes, with each class typically used with a particular type of schema. This allows SQLJ semantics-checking to verify during translation that you are using your connection context instances correctly in your code. The type of schema specifies such properties as names and privileges associated with tables and views, the datatypes of their rows, and names and definitions of stored procedures.

To use online semantics-checking during translation, provide an example of the type of schema for any particular connection context class. You accomplish this by setting the SQLJ -user, -password, and -url options. These schema examples are sometimes referred to as exemplar schemas. 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 performs the following:

• 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)

• verifies that items in the SQL operations match the set of items 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 identical names and types, and with privileges set appropriately.

If no appropriate exemplar schema is available during translation for one of your connection context classes, or if it is inconvenient to connect to a schema of that type during translation, then you do not have to specify SQLJ options (-user, -password, -url) for that particular connection context class. In that case, SQLJ statements specifying connection objects of that connection context class are only semantically checked to the extent possible.

Note: Remember that the exemplar schema you specify in your translator option settings does not specify the actual schema that SQLJ allows in your code at runtime. The exemplar schema only furnishes the translator with an example of the type of schema that you use for a particular connection context class.

---