Oracle8i SQLJ Developer's Guide and Reference Release 3 (8.1.7) Part Number A83723-01 |
Library | Service | Contents | Index

## 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 `DataSource`s 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.

.

Note: Be aware of the auto commit status of the connections you establish. If you use the `Oracle` class, then auto commit is off, unless you specify it explicitly. If you use the `DefaultContext` or a connection context with `DriverManager`-style constructors, then the auto-commit setting must always be specified explicitly. If you use the `DataSource` mechanism, then the autocommit setting is inherited from the underlying `DataSource`. In most environments the `DataSource` object originates from JDBC, and the auto commit option is on. To avoid unexpected behavior, always check the auto commit setting.

#### 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 `DataSource`s in a JDK 1.2 environment with the Java Extension classes, or in a J2EE environment. However, you can also use `DataSource`s under JDK 1.1.x with the Java extension classes.

All the SQLJ runtime libraries support `DataSource`s. 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.

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index