Key Programming Considerations

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, will you want to use connection context classes other than the default sqlj.runtime.ref.DefaultContext class?
Will you need different connection specifications 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 you will be connecting to different database schema types, then you will typically want to declare and use your own 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 to the same type of database schema. (It is permissible, however, to use different DefaultContext instances with different types of schemas.)

Single Connection

For a single connection, you typically use one instance of the DefaultContext class, specifying the database URL, username, 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 username, 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 username, password, and URL directly:

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

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.

Notes:

Oracle.connect() will not set your default connection if one had already been set. In that case, it returns null. If you do want to override your default connection, use the static setDefaultContext() method of the DefaultContext class, as described in the next section.

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.

In the second 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.

Once you have completed these steps you do not need to specify the connection for any of the SQLJ executable statements in your application.

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.

Multiple Connections Using Declared Connection Context Classes

For multiple connections to different types of database schemas, you typically use connection context declarations to define your own connection context classes. Having a separate connection context class for each type of schema 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 that is 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.

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

URL (String), username (String), password (String)
URL (String), username (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 username and password
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), username (String), password (String)
java.lang.Class object, name of properties file (String), username (String), password (String), auto-commit flag (boolean)
JDBC Connection object
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.

Note:
The auto-commit flag is used to specify whether or not operations are automatically committed. 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.
The auto-commit flag is discussed in "Basic Transaction Control".

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;

The DefaultContext class has three 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; 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 Oracle.connect()).
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() class method).

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

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), 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 object only

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

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 or not SQL operations are automatically committed. For more information, see "Basic Transaction Control".

Connection for Translation and Runtime

You can use different connections for translation and runtime. For example, you might want a different connection for translation than for runtime because you are not developing in the same kind of environment that your application will be running in. Or you may need different connections to specify different drivers, perhaps because you want to use the OCI 8 driver for semantics-checking but must use the Thin driver for runtime because you are developing an applet.

Notes:

Oracle SQLJ supports specified connections for profile customization as well (this could be done through the -P command-line prefix), but the Oracle customizer does not use a connection.

Before specifying your connection, consider which JDBC driver to use and register the appropriate driver class (for Oracle JDBC drivers this is oracle.jdbc.driver.OracleDriver; this is registered automatically if you use Oracle.connect() to create your default connection). See "Selection of the JDBC Driver" for more information.

Connection at Translation Time

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

The -url setting includes which JDBC driver to use.

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

You will usually, but not necessarily, use the same URL, username, and password as you do in your source code for the runtime connection.

Connections at Runtime

Specify a connection or connections for runtime in your SQLJ source code or in a properties file (such as connect.properties) to be read by Oracle.connect(). The connection URL includes which JDBC driver to use. See "Single Connection or Multiple Connections Using DefaultContext".