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

## Connection Considerations

When deciding what database connection or connections you will need for your SQLJ application, consider the following:

- Will you need just one database connection or multiple connections?

- If using multiple connections (possibly to multiple schemas), will each connection use SQL entities of the same name--tables of the same name, columns of the same name and datatypes, stored procedures of the same name and signature, and so on?

- Will you need different connections for translation and runtime, or will the same suffice for both?

A SQLJ executable statement can specify a particular connection context instance (either of `DefaultContext` or of a declared connection context class) for its database connection. Alternatively, it can omit the connection context specification and, thereby, use the default connection (an instance of `DefaultContext` that you previously set as the default).

Note: If your database operations will use different sets of SQL entities, then you will typically want to declare and use additional connection context classes. This is discussed in "Connection Contexts".

### Single Connection or Multiple Connections Using DefaultContext

This section discusses scenarios where you will use connection instances of only the `DefaultContext` class.

This is typical if you are using a single connection, or multiple connections that use SQL entities with the same names and datatypes.

#### Single Connection

For a single connection, typically use one instance of the `DefaultContext` class, specifying the database URL, user name, and password when you construct your `DefaultContext` object.

You can use the `connect()` method of the `oracle.sqlj.runtime.Oracle` class to accomplish this. This method has several signatures, including ones that allow you to specify user name, password, and URL, either directly or using a properties file. In the following example, the properties file `connect.properties` is used:

    Oracle.connect(MyClass.class, "connect.properties");

(Where `MyClass` is the name of your class. There is an example of `connect.properties` in `[Oracle Home]/sqlj/demo`, and also in "Set Up the Runtime Connection".)

You must edit `connect.properties` appropriately and package it with your application. In this example, you must also import the `oracle.sqlj.runtime.Oracle` class.

Alternatively, you can specify user name, password, and URL directly:

    Oracle.connect("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");

In this example, the connection will use the JDBC Thin driver to connect user `scott` (password `tiger`) to a database on the machine `localhost` through port 1521, where `orcl` is the SID of the database to connect to on that machine.

Either of these examples creates an instance of the `DefaultContext` class and installs it as your default connection. It is not necessary to do anything with the `DefaultContext` instance directly.

Once you have completed these steps, you do not need to specify the connection for any of the SQLJ executable statements in your application if you want them all to use the default connection.

Note that in using a Thin driver, the URL must include the hostname, port number, and SID, as in the preceding example. In using an OCI driver, you can specify an Oracle SID, or no SID if you intend to use the client's default account. Alternatively, you can use name-value pairs (see the Oracle8i JDBC Developer's Guide and Reference for more information). The first example here will connect to the database with SID `orcl`; the second example will connect to the client's default account:

    jdbc:oracle:oci8:@orcl
    jdbc:oracle:oci8:@

Notes: `Oracle.connect()` will not set your default connection if one had already been set. In that case, it returns `null`. (This functionality allows you to use the same code on a client or in the server.) If you do want to override your default connection, use the static `setDefaultContext()` method of the `DefaultContext` class, as described in the next section. The `Oracle.connect()` method defaults to a `false` setting of the auto-commit flag; however, it also has signatures that let you set it explicitly. See "More About the Oracle Class". For general information about auto-commit functionality, see "Basic Transaction Control". (In Oracle JDBC, the auto-commit flag defaults to `true`.) You can optionally specify `getClass()`, instead of `MyClass.class`, in the `Oracle.connect()` call, as long as you are not calling `getClass()` from a static method. This is done in some of the SQLJ demo applications.

#### Multiple Connections

For multiple connections, you can create and use additional instances of the `DefaultContext` class, while optionally still using the default connection created under "Single Connections" above.

You can use the `Oracle.getConnection()` method to instantiate `DefaultContext`, as in the following examples.

First, consider a case where you want most statements to use the default connection created above, but other statements to use a different connection. You must create one additional instance of `DefaultContext`:

    DefaultContext ctx = Oracle.getConnection (
       "jdbc:oracle:thin:@localhost2:1521:orcl2", "bill", "lion");

(Or `ctx` could also use the `scott`/`tiger` schema, if you want to perform multiple sets of operations on the same schema.)

When you want to use the default connection, it is not necessary to specify a connection context:

    #sql { SQL operation };

When you want to use the additional connection, specify `ctx` as the connection:

    #sql [ctx] { SQL operation };

Next, consider situations where you want to use multiple connections where each of them is a named `DefaultContext` instance. This allows you to switch your default back and forth, for example.

The following statements establish multiple connections to the same schema (in case you want to use multiple database sessions or transactions, for example). Instantiate the `DefaultContext` class for each connection you will need:

    DefaultContext ctx1 = Oracle.getConnection (
       "jdbc:oracle:thin:@localhost1:1521:orcl1", "scott", "tiger");
    DefaultContext ctx2 = Oracle.getConnection (
       "jdbc:oracle:thin:@localhost1:1521:orcl1", "scott", "tiger");

This creates two connection context instances that would use the same schema, connecting to `scott/tiger` on database SID `orcl1` on the machine `localhost1`, using the Oracle JDBC Thin driver.

Now consider a case where you would want multiple connections to different schemas. Again, instantiate the `DefaultContext` class for each connection you will need:

    DefaultContext ctx1 = Oracle.getConnection (
       "jdbc:oracle:thin:@localhost1:1521:orcl1", "scott", "tiger");
    DefaultContext ctx2 = Oracle.getConnection (
       "jdbc:oracle:thin:@localhost2:1521:orcl2", "bill", "lion");

This creates two connection context instances that both use the Oracle JDBC Thin driver but use different schemas. The `ctx1` object connects to `scott/tiger` on database SID `orcl1` on the machine `localhost1`, while the `ctx2` object connects to `bill/lion` on database SID `orcl2` on the machine `localhost2`.

There are two ways to switch back and forth between these connections for the SQLJ executable statements in your application:

- If you switch back and forth frequently, then you can specify the connection for each statement in your application:

      #sql [ctx1] { SQL operation };
      ...
      #sql [ctx2] { SQL operation };

  Note: Remember to include the square brackets around the connection context instance name; they are part of the syntax.

or:

- If you use either of the connections several times in a row within your code flow, then you can periodically use the static `setDefaultContext()` method of the `DefaultContext` class to reset the default connection. This way, you can avoid specifying connections in your SQLJ statements.

      DefaultContext.setDefaultContext(ctx1);
      ...
      #sql { SQL operation };   // These three statements all use ctx1
      #sql { SQL operation };
      #sql { SQL operation };
      ...
      DefaultContext.setDefaultContext(ctx2);
      ...
      #sql { SQL operation };   // These three statements all use ctx2
      #sql { SQL operation };
      #sql { SQL operation };
      ...

  Note: Because the preceding statements do not specify connection contexts, at translation time they will all be checked against the default connection context.

### Closing Connections

It is advisable to close your connection context instances when you are done, preferably in a `finally` clause (in case your application terminates with an exception) and a `try/catch` block.

The `DefaultContext` class (as well as any other connection context class) includes a `close()` method. Calling this method closes the SQLJ connection context instance and, by default, also closes the underlying JDBC connection instance and the physical database connection.

In addition, the `oracle.sqlj.runtime.Oracle` class has a static `close()` method to close the default connection only.

In the following example, presume `ctx` is an instance of any connection context class:

    ...
    finally
    {
       ctx.close();
    }
    ...

or (if the `finally` clause is not within a `try/catch` block):

    ...
    finally
    {
       try { ctx.close(); } catch(SQLException ex) {...}
    }
    ...

Or, to close the default connection, the `Oracle` class also provides a `close()` method:

    ...
    finally
    {
       Oracle.close();
    }
    ...

Always commit or roll back any pending changes before closing the connection. Whether there would be an implicit `COMMIT` operation as the connection is closed is not specified in the JDBC standard and may vary from vendor to vendor. For Oracle, there is an implicit `COMMIT` when a connection is closed, and an implicit `ROLLBACK` when a connection is garbage-collected without being closed, but it is not advisable to rely on these mechanisms.

Note: It is also possible to close a connection context instance without closing the underlying connection (in case the underlying connection is shared). See "Closing Shared Connections".

### Multiple Connections Using Declared Connection Context Classes

For multiple connections that use different sets of SQL entities, it is advantageous to use connection context declarations to define additional connection context classes. Having a separate connection context class for each set of SQL entities that you use allows SQLJ to do more rigorous semantics-checking of your code.

See "Connection Contexts" for more information.

### More About the Oracle Class

Oracle SQLJ provides the `oracle.sqlj.runtime.Oracle` class to simplify the process of creating and using instances of the `DefaultContext` class.

The static `connect()` method instantiates a `DefaultContext` object and implicitly installs this instance as your default connection. You do not need to assign or use the `DefaultContext` instance returned by `connect()`. If you had already established a default connection, then `connect()` returns `null`.

The static `getConnection()` method simply instantiates a `DefaultContext` object. Assign the returned instance and use it as desired.

Both methods register the Oracle JDBC driver manager automatically if the `oracle.jdbc.driver.OracleDriver` class is found in your `CLASSPATH`.

The static `close()` method closes the default connection.

#### Signatures of the Oracle.connect() and Oracle.getConnection() Methods

Each method has signatures that take the following parameters as input:

- URL (`String`), user name (`String`), password (`String`)

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

- URL (`String`), `java.util.Properties` object containing properties for the connection

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

- URL (`String`) fully specifying the connection, including user name and password

  The following is an example of the format of a URL string specifying user name (`scott`) and password (`tiger`) when using the Oracle JDBC drivers, in this case the Thin driver:

      "jdbc:oracle:thin:scott/tiger@localhost:1521:orcl"

- URL (`String`), auto-commit flag (`boolean`)

- `java.lang.Class` object for class used to load properties file, name of properties file (`String`)

- `java.lang.Class` object, name of properties file (`String`), auto-commit flag (`boolean`)

- `java.lang.Class` object, name of properties file (`String`), user name (`String`), password (`String`)

- `java.lang.Class` object, name of properties file (`String`), user name (`String`), password (`String`), auto-commit flag (`boolean`)

- JDBC connection object (`Connection` or `OracleConnection`)

- SQLJ connection context object

These last two signatures inherit an existing database connection. When you inherit a connection, you will also inherit the auto-commit setting of that connection.

Some examples of `connect()` and `getConnection()` calls are under "Single Connection or Multiple Connections Using DefaultContext".

Note: The auto-commit flag specifies whether SQL operations are automatically committed. For the `Oracle.connect()` and `Oracle.getConnection()` methods only, the default is `false`. If that is the setting you want, then you can use one of the signatures that does not take auto-commit as input. (However, anytime you use a constructor to create an instance of a connection context class, including `DefaultContext`, you must specify the auto-commit setting.) (In Oracle JDBC, the default for the auto-commit flag is `true`.) The auto-commit flag is discussed in "Basic Transaction Control".

#### Optional Oracle.close() Method Parameters

In using the `Oracle.close()` method to close the default connection, you have the option of specifying whether or not to close the underlying physical database connection. By default it is closed. This is relevant if you are sharing this physical connection between multiple connection objects, either SQLJ connection context instances or JDBC connection instances.

To keep the underlying physical connection open:

    Oracle.close(ConnectionContext.KEEP_CONNECTION);

To close the underlying physical connection:

    Oracle.close(ConnectionContext.CLOSE_CONNECTION);

`KEEP_CONNECTION` and `CLOSE_CONNECTION` are static constants of the `ConnectionContext` interface.

For more information about using these parameters and about shared connections, see "Closing Shared Connections".

### More About the DefaultContext Class

The `sqlj.runtime.ref.DefaultContext` class provides a complete default implementation of a connection context class. As with classes created using a connection context declaration, the `DefaultContext` class implements the `sqlj.runtime.ConnectionContext` interface. (This interface is described in "Implementation and Functionality of Connection Context Classes".)

The `DefaultContext` class has the same class definition that would have been generated by the SQLJ translator from the declaration:

    #sql public context DefaultContext;

#### DefaultContext Methods

The `DefaultContext` class has four methods of note:

- `getConnection()`--Gets the underlying JDBC connection object. This is useful if you must use JDBC in your application for dynamic SQL operations. You can also use the `setAutoCommit()` method of the underlying JDBC connection object to set the auto-commit flag for the connection.

- `setDefaultContext()`--This is a `static` method that sets the default connection your application uses; it takes a `DefaultContext` instance as input. SQLJ executable statements that do not specify a connection context instance will use the default connection that you define using this method (or that you define using the `Oracle.connect()` method).

- `getDefaultContext()`--This is a `static` method that returns the `DefaultContext` instance currently defined as the default connection for your application (through earlier use of the `setDefaultContext()` method).

- `close()`--Like any connection context class, the `DefaultContext` class includes a `close()` method to close the connection context instance.

The `getConnection()` and `close()` methods are specified in the `sqlj.runtime.ConnectionContext` interface.

Note: On a client, `getDefaultContext()` returns `null` if `setDefaultContext()` was not previously called. In the server, it returns the default connection (the connection to the server itself).

#### DefaultContext Constructors

It is typical to instantiate `DefaultContext` using the `Oracle.connect()` or `Oracle.getConnection()` method. If you want to create an instance directly, however, there are five constructors for `DefaultContext`, which take the following parameters as input:

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

  The following is an example of the format of a URL string specifying user name (`scott`) and password (`tiger`) when using the Oracle JDBC drivers, in this case the Thin driver:

      "jdbc:oracle:thin:scott/tiger@localhost:1521:orcl"

- JDBC connection object

- SQLJ connection context object

The last two inherit an existing database connection. When you inherit a connection, you will also inherit the auto-commit setting of that connection.

Following is an example of constructing a `DefaultContext` instance:

    DefaultContext defctx = new DefaultContext
              ("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger", false);

It is important to note that connection context class constructors, unlike the `Oracle.connect()` method, require an auto-commit setting.

Notes: To use any of the first three constructors above, you must first register your JDBC driver. This happens automatically if you are using an Oracle JDBC driver and call `Oracle.connect()`. Otherwise, see "Driver Selection and Registration for Runtime". Any connection context class that you declare will have the same constructor signatures as the `DefaultContext` class. 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".

#### Optional DefaultContext close() Method Parameters

When you close a connection context instance (of the `DefaultContext` class or any other class), you have the option of specifying whether or not to close the underlying physical database connection. By default it is closed. This is relevant if you are sharing the physical connection between multiple connection objects, either SQLJ connection context instances or JDBC connection instances. The following examples presume a `DefaultContext` instance `defctx`.

To keep the underlying physical connection open:

    defctx.close(ConnectionContext.KEEP_CONNECTION);

To close the underlying physical connection:

    defctx.close(ConnectionContext.CLOSE_CONNECTION);

`KEEP_CONNECTION` and `CLOSE_CONNECTION` are static constants of the `ConnectionContext` interface.

For more information about using these parameters and about shared connections, see "Closing Shared Connections".

### Connection for Translation

If you want to use online semantics-checking during translation, you must specify a database connection for SQLJ to use--these are referred to as exemplar schemas and are further discussed in "Connection Context Concepts"

You can use different connections for translation and runtime; in fact, it is often necessary or preferable to do so. It might be necessary if you are not developing in the same kind of environment that your application will run in. But even if the runtime connection is available during translation, it might be preferable to create an account with a narrower set of resources so that your online checking will be tighter. This would be true if your application uses only a small subset of the SQL entities available in the runtime connection. Your online checking would be tighter and more meaningful if you create an exemplar schema consisting only of SQL entities that your application actually uses.

Use the SQLJ translator connection options (`-url`, `-user`, and `-password`), either on the command line or in a properties file, to specify a connection for translation.

For information about these options, see "Connection Options".

### Connection for Customization

Generally speaking, Oracle customization does not require a database connection; however, Oracle SQLJ does support customizer connections. This is useful in two circumstances:

- If you are using the Oracle customizer with the `optcols` option enabled, then a connection is required. This option allows iterator column type and size definitions for performance optimization.

- If you are using the `SQLCheckerCustomizer`, a specialized customizer that performs semantics-checking on profiles, then a connection is required if you are using an online checker (which is true by default).

The `optcols` option is specific to the Oracle customizer. See "Oracle Customizer Column Definition Option (optcols)".

The `SQLCheckerCustomizer` is invoked through the Oracle customizer harness `verify` option. See "SQLCheckerCustomizer for Profile Semantics-Checking".

Use the customizer harness `user`, `password`, `url`, and `driver` options to specify connection parameters for whatever customizer you are using, as appropriate. See "Customizer Harness Options for Connections".

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