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".

---