Oracle9i SQLJ Developer's Guide and Reference
Release 1 (9.0.1)

Part Number A90212-01
Go To Documentation Library
Home
Go To Product List
Solution Area
Go To Table Of Contents
Contents
Go To Index
Index

Go to previous page Go to next page

4
Key Programming Considerations

This chapter discusses key issues to consider before developing and running your SQLJ application, then provides a summary and sample applications. The following topics are discussed:

Selection of the JDBC Driver

You must consider which JDBC driver will be appropriate for your situation and whether it may be advantageous to use different drivers for translation and runtime. You must choose or register the appropriate driver class for each and then specify the driver in your connection URL.


Note:

Your application will require an Oracle JDBC driver if you use the Oracle customizer or Oracle-specific code generation on your application, even if your code does not actually use Oracle-specific features. 


Overview of the Oracle JDBC Drivers

Oracle provides the following JDBC drivers:

Oracle provides JDK 1.2.x-compatible and JDK 1.1.x-compatible versions of the client-side drivers. There are only JDK 1.2.x-compatible versions of the server-side drivers, because the Oracle JVM is a JDK 1.2.x environment.

The rest of this section provides a brief overview of each driver. For more information about the drivers and about which might be most appropriate for your particular situation, see the Oracle9i JDBC Developer's Guide and Reference.

Remember that your choices may differ between translation time and runtime. For example, you may want to use the Oracle JDBC OCI driver at translation time for semantics-checking but the Oracle JDBC Thin driver at runtime.

Core Functionality

The core functionality of all these drivers is the same. They support the same feature set, syntax, programming interfaces, and Oracle extensions.

All Oracle JDBC drivers are supported by the oracle.jdbc.OracleDriver class.

OCI Driver

The Oracle JDBC OCI driver accesses the database by calling the Oracle Call Interface (OCI) directly from Java, providing the highest compatibility with the different Oracle 7, 8, 8i, and 9i versions. These drivers support all installed Oracle Net adapters, including IPC, named pipes, TCP/IP, and IPX/SPX.

The use of native methods to call C entry points makes the OCI driver dependent on the Oracle platform, requiring an Oracle client installation that includes Oracle Net. Therefore it is not suitable for applets.

"Connect strings" for the OCI driver is of the following form (where tns is an optional TNS alias or full TNS specification):

jdbc:oracle:oci:@<tns>

(For backwards compatibility, "oci7" or "oci8" is still acceptable, instead of "oci".)

Thin driver

The Oracle JDBC Thin driver is a platform-independent, 100% pure Java implementation that uses Java sockets to connect directly to the Oracle server from any Oracle or non-Oracle client. It can be downloaded into a browser simultaneously with the Java applet being run.

The Thin driver supports only TCP/IP protocol and requires a TNS listener to be listening on TCP/IP sockets from the database server. When the Thin driver is used with an applet, the client browser must have the capability to support Java sockets.

Connect strings for the Thin driver are typically of the following form (though there is also a longer form):

jdbc:oracle:thin:@host:port:sid

Server-Side Thin Driver

The Oracle JDBC server-side Thin driver offers the same functionality as the client-side Thin driver, but runs inside Oracle9i and accesses a remote server. This is useful in accessing a remote Oracle server from an Oracle server acting as a middle tier, or, more generally, to access one Oracle server from inside another, such as from any Java stored procedure or Enterprise JavaBeans.

Connect strings for the server-side Thin driver are the same as for the client-side Thin driver.


Note:

In order to leave the originating database when using the server-side Thin driver, the user account must have SocketPermission assigned. See the Oracle9i JDBC Developer's Guide and Reference for more information. See the Oracle9i Java Developer's Guide for general information about SocketPermission and other permissions. 


Server-Side Internal Driver

The Oracle JDBC server-side internal driver provides support for any Java code that runs inside the target Oracle9i instance where the SQL operations are to be performed. The server-side internal driver allows the Oracle JVM to communicate directly with the SQL engine. The server-side internal driver is the default JDBC driver for SQLJ code running as a stored procedure, stored function, trigger, Enterprise JavaBean, or CORBA object in Oracle9i.

Connect strings for the server-side internal driver are of the following form:

jdbc:oracle:kprb:

(If your SQLJ code uses the default connection context, SQLJ will automatically use this driver for code running in the Oracle JVM.)

Driver Selection for Translation

Use SQLJ option settings, either on the command line or in a properties file, to choose the driver manager class and specify a driver for translation.

Use the SQLJ -driver option to choose any driver manager class other than OracleDriver, which is the default.

Specify the particular JDBC driver to choose (such as Thin or OCI for Oracle) as part of the connection URL you specify in the SQLJ -url option.

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

You will typically, but not necessarily, use the same driver that you use in your source code for the runtime connection.


Note:

Remember that the -driver option does not choose a particular driver. It registers a driver class with the driver manager. One driver class might be used for multiple driver protocols (such as OracleDriver, which is used for all of the Oracle JDBC protocols). 


Driver Selection and Registration for Runtime

To connect to the database at runtime, you must register one or more drivers that will understand the URLs you specify for any of your connection instances, whether they are instances of the sqlj.runtime.ref.DefaultContext class or of any connection context classes that you declare.

If you are using an Oracle JDBC driver and create a default connection using the standard Oracle.connect() method (discussed below, under "Single Connection or Multiple Connections Using DefaultContext"), then SQLJ handles this automatically--Oracle.connect() registers the oracle.jdbc.OracleDriver class.

If you are using an Oracle JDBC driver, but do not use Oracle.connect(), then you must manually register the OracleDriver class, as follows:

DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

If you are not using an Oracle JDBC driver, then you must register some appropriate driver class, as follows:

DriverManager.registerDriver(new mydriver.jdbc.driver.MyDriver());

In any case, you must also set your connection URL, user name, and password. This is described in "Single Connection or Multiple Connections Using DefaultContext". That section also further discusses the Oracle.connect() method.


Note:

As an alternative to using the JDBC driver manager in establishing JDBC connections, you can use data sources. You can specify a data source in a with clause, as described in "Declaration WITH Clause". For general information about data sources, see the Oracle9i JDBC Developer's Guide and Reference


Connection Considerations

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

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 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 example that follows, the properties file connect.properties is used.

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

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


Note:

The connect.properties file is searched for relative to the specified class. In the example, if MyClass is located in my-package, then connect.properties must be found in the same package location, my-package, as MyClass.class


If you use connect.properties, you must edit it 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 (Oracle session ID) of the database to connect to on that machine.

Either of these examples creates a special static instance of the DefaultContext class and installs it as your default connection. It is not necessary to do anything with that 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, and the database must have a listener running at the specified port. In using the OCI driver, you can specify an SID, or no SID if you intend to use the client's default account. Alternatively, you can use name-value pairs (see the Oracle9i 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:oci:@orcl
jdbc:oracle:oci:@


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 to 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. The getClass() method is used in some of the SQLJ demo applications.

  • You can access the static DefaultContext instance, which corresponds to your default connection, as follows:

    DefaultContext.getDefaultContext();
    
 

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 };

This is actually an understood shortcut for the following:

#sql [DefaultContext.getDefaultContext()] { 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 connection back and forth, for example.

The following statements establish multiple connections to the same schema (in case you want to use multiple Oracle 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 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 SID orcl1 on the machine localhost1, while the ctx2 object connects to bill/lion on 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:

or:

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) of a try 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 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 block in case a SQL exception is encountered):

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

This situation is somewhat advance, however. 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 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 and returns it. You can use the returned instance as desired.

Both methods register the Oracle JDBC driver manager automatically if the oracle.jdbc.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:

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.

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

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

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 (default behavior):

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:

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. However, if a data source object has been bound under "jdbc/defaultDataSource" in JNDI, then the client will use this data source object as its default connection. (For information about Oracle SQLJ support for data sources and JNDI, see "Data Source Support".)

In the server, getDefaultContext() 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:

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

  • Connection context classes that you declare generally have the same constructor signatures as the DefaultContext class. However, if you declare a connection context class to be associated with a data source, a different set of constructors is provided. (See "Data Source Support" for more information.)

  • 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 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 (default behavior):

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:

For information about the Oracle customizer optcols option (for standard code generation), see "Oracle Customizer Column Definition Option (optcols)". (For Oracle-specific code generation, the SQLJ translator has an -optcols option with the same functionality.)

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

Null-Handling

Java primitive types (such as int, double, or float) cannot have null values, which you must consider in choosing your result expression and host expression types.

Wrapper Classes for Null-Handling

SQLJ consistently enforces retrieving SQL nulls as Java nulls, in contrast to JDBC, which retrieves nulls as 0 or false for certain datatypes. Therefore, do not use Java primitive types in SQLJ for output variables in situations where a SQL null may be received, because Java primitive types cannot take null values.

This pertains to result expressions, output or input-output host expressions, and iterator column types. If the receiving Java type is primitive and an attempt is made to retrieve a SQL null, then a sqlj.runtime.SQLNullException is thrown and no assignment is made.

To avoid the possibility of null values being assigned to Java primitives, use the following wrapper classes instead of primitive types:

In case you must convert back to a primitive value, each of these wrapper classes has an xxxValue() method. For example, intValue() returns an int value from an Integer object and floatValue() returns a float value from a Float object. Do this as in the following example, presuming intobj is an Integer object:

int j = intobj.intValue();


Notes:

  • SQLNullException is a subclass of the standard java.sql.SQLException class. See "Using SQLException Subclasses".

  • Because Java objects can have null values, there is no need in SQLJ for indicator variables such as those used in other host languages (C, C++, and COBOL for example).

 

Examples of Null-Handling

The following examples show the use of the java.lang wrapper classes to handle null data.

Example: Null Input Host Variable

In the following example, a Float object is used to pass a null value to the database. You cannot use the Java primitive type float to accomplish this.

Example:

int empno = 7499; 
Float commission = null;

#sql { UPDATE emp SET comm = :commission WHERE empno = :empno };

Example: Null Iterator Rows

In the following example, a Double column type is used in an iterator to allow for the possibility of null data.

For each employee in the EMP table whose salary is at least $50,000, the employee name (ENAME) and commission (COMM) are selected into the iterator. Then each row is tested to determine if the COMM field is, in fact, null. If so, it is processed accordingly.

Presume the following declaration:

#sql iterator EmployeeIter (String ename, Double comm);

Example:

EmployeeIter ei;
#sql ei = { SELECT ename, comm FROM emp WHERE sal >= 50000 };

while (ei.next())
{
   if (ei.comm() == null) 
      System.out.println(ei.ename() + " is not on commission.");
}
ei.close();
...


Note:

To execute a WHERE-clause comparison against null values, use the following SQL syntax:

...WHERE :x IS NULL
 

Exception-Handling Basics

This section covers the basics of handling exceptions in your SQLJ application, including requirements for error-checking.

SQLJ and JDBC Exception-Handling Requirements

Because SQLJ executable statements result in JDBC calls through sqlj.runtime, and JDBC requires SQL exceptions to be caught or thrown, SQLJ also requires SQL exceptions to be caught or thrown in any block containing SQLJ executable statements. Your source code will generate errors during compilation if you do not include appropriate exception-handling.

Handling SQL exceptions requires the SQLException class, which is included in the standard JDBC java.sql.* package.

Example: Exception Handling

This example demonstrates the kind of basic exception-handling required of SQLJ applications, with a main method with a try/catch block, and another method which is called from main and throws exceptions back to main when they are encountered.

/* Import SQLExceptions class.  The SQLException comes from
   JDBC. Executable #sql clauses result in calls to JDBC, so methods
   containing executable #sql clauses must either catch or throw
   SQLException.  
 */
import java.sql.* ;
import oracle.sqlj.runtime.Oracle;

// iterator for the select

#sql iterator MyIter (String ITEM_NAME);

public class TestInstallSQLJ 
{
  //Main method
  public static void main (String args[]) 
  {
    try { 
      /* 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(TestInstallSQLJ.class, "connect.properties");

      TestInstallSQLJ ti = new TestInstallSQLJ();
      ti.runExample(); 
    } catch (SQLException e) { 
      System.err.println("Error running the example: " + e);
    }

  } //End of method main

  //Method that runs the example
  void runExample() throws SQLException
  {
      //Issue SQL command to clear the SALES table
    #sql { DELETE FROM SALES };
    #sql { INSERT INTO SALES(ITEM_NAME) VALUES ('Hello, SQLJ!')};

    MyIter iter;
    #sql iter = { SELECT ITEM_NAME FROM SALES };

    while (iter.next()) {
      System.out.println(iter.ITEM_NAME());
    }
  }
}

Processing Exceptions

This section discusses ways to process and interpret exceptions in your SQLJ application. During runtime, exceptions may come from any of the following:

Errors originating in the SQLJ runtime are listed in "Runtime Messages".

Errors originating in the Oracle JDBC driver are listed in the Oracle9i JDBC Developer's Guide and Reference. Errors originating in the Oracle RDBMS are listed in the Oracle9i Database Error Messages reference.

Printing Error Text

The example in the previous section showed how to catch SQL exceptions and output the error messages, which is repeated again here:

...
try {
...
} catch (SQLException e) { 
      System.err.println("Error running the example: " + e); 
}
...

This will print the error text from the SQLException object.

You can also retrieve error information using the SQLException class getMessage(), getErrorCode(), and getSQLState() methods, as described in the next section.

Printing the error text as in this example prints the error message with some additional text, such as "SQLException".

Retrieving SQL States and Error Codes

The java.sql.SQLException class and subclasses include the getMessage(), getErrorCode(), and getSQLState() methods. Depending on where the exception originated and how error exceptions are implemented there, these methods provide additional information as follows:

The following example prints the error message as in the preceding example, but also checks the SQL state.

...
try {
...
} catch (SQLException e) { 
      System.err.println("Error running the example: " + e); 
      String sqlState = e.getSQLState();
      System.err.println("SQL state = " + sqlState); 
}
...

Using SQLException Subclasses

For more specific error-checking, use any available and appropriate subclasses of the java.sql.SQLException class.

SQLJ provides one such subclass, the sqlj.runtime.NullException class, which you can catch in situations where a null value might be returned into a Java primitive variable. (Java primitives cannot handle nulls.)

For batch-enabled environments, there is also the standard java.sql.BatchUpdateException subclass. See "Error Conditions During Batch Execution" for further discussion.

When you use a SQLException subclass, catch the subclass exception first, before catching a SQLException, as in the following example:

...
try {
...
} catch (SQLNullException ne) {
     System.err.println("Null value encountered: " + ne); }
  catch (SQLException e) { 
     System.err.println("Error running the example: " + e); }
...

This is because a subclass exception can also be caught as a SQLException. If you catch SQLException first, then execution would not drop through for any special processing you want to use for the subclass exception.

Basic Transaction Control

This section discusses how to manage data updates.

For information about SQLJ support for more advanced transaction control functions--access mode and isolation level--see "Advanced Transaction Control".

Overview of Transactions

A transaction is a sequence of SQL operations that Oracle treats as a single unit. A transaction begins with the first executable SQL statement after any of the following:

A transaction ends with a COMMIT or ROLLBACK operation.


Note:

In Oracle9i, all DDL commands (such as CREATE and ALTER) include an implicit COMMIT. This will commit not only the DDL command, but any preceding DML commands (INSERT, DELETE, UPDATE) that had not yet been committed or rolled back. 


Automatic Commits versus Manual Commits

In using SQLJ or JDBC, you can either have your data updates automatically committed, or commit them manually. In either case, each COMMIT operation starts a new transaction. You can specify that changes be committed automatically by enabling the auto-commit flag, either when you define a SQLJ connection, or by using the setAutoCommit() method of the underlying JDBC connection object of an existing connection. You can use manual control by disabling the auto-commit flag and using SQLJ COMMIT and ROLLBACK statements.

Enabling auto-commit may be more convenient, but gives you less control. You have no option to roll back changes, for example. In addition, some SQLJ or JDBC features are incompatible with auto-commit mode. For example, you must disable the auto-commit flag for update batching or SELECT FOR UPDATE syntax to work properly.

Specifying Auto-Commit as You Define a Connection

When you use the Oracle.connect() or Oracle.getConnection() method to create a DefaultContext instance and define a connection, the auto-commit flag is set to false by default. There are signatures of these methods, however, that allow you to set this flag explicitly. The auto-commit flag is always the last parameter.

The following is an example of instantiating DefaultContext and using the default false setting for auto-commit mode:

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

Or you can specify a true setting:

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

For the complete list of signatures for Oracle.connect() and Oracle.getConnection(), see "More About the Oracle Class".

If you use a constructor to create a connection context instance, either of DefaultContext or of a declared connection context class, you must specify the auto-commit setting. Again, it is the last parameter, as in the following example:

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

For the complete list of signatures for DefaultContext constructors, see "More About the DefaultContext Class".

If you have reason to create a JDBC Connection instance directly, then the auto-commit flag is set to true by default if your program runs on a client, or false by default if it runs in the server. (You cannot specify an auto-commit setting when you create a JDBC Connection instance directly, but you can use the setAutoCommit() method to alter the setting, as described in "Modifying Auto-Commit in an Existing Connection" below.)


Note:

Auto-commit functionality is not supported by the JDBC server-side internal driver. 


Modifying Auto-Commit in an Existing Connection

There is typically no reason to change the auto-commit flag setting for an existing connection, but you can if desired. You can do this by using the setAutoCommit() method of the underlying JDBC connection object.

You can retrieve the underlying JDBC connection object by using the getConnection() method of any SQLJ connection context instance (whether it is an instance of the DefaultContext class or of a connection context class you have declared).

You can accomplish these two steps at once, as follows. In these examples, ctx is a SQLJ connection context instance:

ctx.getConnection().setAutoCommit(false);

or:

ctx.getConnection().setAutoCommit(true);


Important:

Do not alter the auto-commit setting in the middle of a transaction. 


Using Manual COMMIT and ROLLBACK

If you disable the auto-commit flag, then you must manually commit any data updates.

To commit any changes (such as updates, inserts, or deletes) that have been executed since the last COMMIT operation, use the SQLJ COMMIT statement, as follows:

#sql { COMMIT };

To roll back (cancel) any changes that have been executed since the last COMMIT operation, use the SQLJ ROLLBACK statement, as follows:

#sql { ROLLBACK };

Do not use the COMMIT or ROLLBACK commands when auto-commit is enabled. This will result in unspecified behavior (or perhaps SQL exceptions).


Notes:

  • All DDL statements in Oracle SQL include an implicit COMMIT operation. There is no special SQLJ functionality in this regard; such statements follow standard Oracle SQL rules.

  • If auto-commit mode is off and you close a connection context instance from a client application, then any changes since your last COMMIT will be committed (unless you close the connection context instance with KEEP_CONNECTION, which is explained in "Closing Shared Connections").

 

Effect of Commits and Rollbacks on Iterators and Result Sets

COMMIT operations (either automatic or manual) and ROLLBACK operations do not affect open result sets and iterators. The result sets and iterators will still be open, and usually all that is relevant to their content is the state of the database at the time of execution of the SELECT statements that populated them.


Note:

An exception to this is if you declared an iterator class with sensitivity=SENSITIVE. In this case, changes to the underlying result set may be seen whenever the iterator is scrolled outside of its window size. For more information about scrollable iterators, see "Scrollable Iterators". For more information about the underlying scrollable result sets, see the Oracle9i JDBC Developer's Guide and Reference  


This also applies to UPDATE, INSERT, and DELETE statements that are executed after the SELECT statements--execution of these statements does not affect the contents of open result sets and iterators.

Consider a situation where you SELECT, then UPDATE, then COMMIT. A non-sensitive result set or iterator populated by the SELECT statement will be unaffected by the UPDATE and COMMIT.

As a further example, consider a situation where you UPDATE, then SELECT, then ROLLBACK. A non-sensitive result set or iterator populated by the SELECT will still contain the updated data, regardless of the subsequent ROLLBACK.

Summary: First Steps in SQLJ Code

The best way to summarize the SQLJ executable statement features and functionality discussed to this point is by examining short but complete programs. This section presents two such examples.

The first example, presented one step at a time and then again in its entirety, uses a SELECT INTO statement to perform a single-row query of two columns from a table of employees. If you want to run the example, make sure to change the parameters in the connect.properties file to settings that will let you connect to an appropriate database.

The second example, slightly more complicated, will make use of a SQLJ iterator for a multi-row query.

Import Required Classes

Import any JDBC or SQLJ packages you will need.

You will need at least some of the classes in the java.sql package:

import java.sql.*;

You may not need all the java.sql package, however. Key classes there are java.sql.SQLException and any classes that you refer to explicitly (for example, java.sql.Date, java.sql.ResultSet).

You will need the following package for the Oracle class, which you typically use to instantiate DefaultContext objects and establish your default connection:

import oracle.sqlj.runtime.*;

If you will be using any SQLJ runtime classes directly in your code, import the following packages:

import sqlj.runtime.*;
import sqlj.runtime.ref.*;

If your code does not use any SQLJ runtime classes directly, however, it will be sufficient to have them in your classpath as described in "Set the Path and Classpath".

(Key runtime classes include ResultSetIterator and ExecutionContext in the sqlj.runtime package, and DefaultContext in the sqlj.runtime.ref package.)

Register JDBC Drivers and Set Default Connection

Declare the SimpleExample class with a constructor that uses the static Oracle.connect() method to set the default connection. This also registers the Oracle JDBC drivers. If you are using a non-Oracle JDBC driver, you must add code to register it (as mentioned in the code comments below).

This uses a signature of connect() that takes the URL, user name, and password from the connect.properties file. An example of this file is in the directory [Oracle Home]/sqlj/demo and also in "Set Up the Runtime Connection".

public class SimpleExample {

  public SimpleExample() throws SQLException {
    /* If you are using a non-Oracle JDBC driver, add a call here to 
       DriverManager.registerDriver() to register your driver.  */
    // Set default connection (as defined in connect.properties).
    Oracle.connect(getClass(), "connect.properties");
  }

The main() method is defined in "Set Up Exception Handling" below.

Set Up Exception Handling

Create a main() that calls the SimpleExample constructor and then sets up a try/catch block to handle any SQL exceptions thrown by the runExample() method (which performs the real work of this application):

...
public static void main (String [] args) {
    
   try {
      SimpleExample o1 = new SimpleExample();
      o1.runExample();
   }
   catch (SQLException ex) {
      System.err.println("Error running the example: " + ex);
   }
}
...

The runExample() method is defined in "Set Up Host Variables, Execute SQLJ Clause, Process Results" below.

You can also use a try/catch block inside a finally clause when you close the connection (presuming the finally clause is not already inside a try/catch block in case of SQL exceptions):

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

Set Up Host Variables, Execute SQLJ Clause, Process Results

Create a runExample() method that performs the following:

  1. Throws any SQL exceptions to the main() method for processing.

  2. Declares Java host variables.

  3. Executes a SQLJ clause that binds the Java host variables into an embedded SELECT statement and selects the data into the host variables.

  4. Prints the results.

Here is the code:

void runExample() throws SQLException {
     
     System.out.println( "Running the example--" );
     
     // Declare two Java host variables--
     Float salary;
     String empname;

     // Use SELECT INTO statement to execute query and retrieve values.
      #sql { SELECT ename, sal INTO :empname, :salary FROM emp
             WHERE empno = 7499 };
     
     // Print the results--
     System.out.println("Name is " + empname + ", and Salary is " + salary);
  }
}    // Closing brace of SimpleExample class

This example declares salary and ename as Java host variables. The SQLJ clause then selects data from the ENAME and SAL columns of the EMP table and places the data into the host variables. Finally, the values of salary and empname are printed out.

Note that this SELECT statement could select only one row of the EMP table, because the EMPNO column in the WHERE clause is the primary key of the table.

Example of Single-Row Query using SELECT INTO

This section presents the entire SimpleExample class from the previous step-by-step sections. Because this is a single-row query, no iterator is required.

// Import SQLJ classes:
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
import oracle.sqlj.runtime.*;

// Import standard java.sql package:
import java.sql.*;

public class SimpleExample {

  public SimpleExample() throws SQLException {
    /* If you are using a non-Oracle JDBC driver, add a call here to 
       DriverManager.registerDriver() to register your driver.  */
    // Set default connection (as defined in connect.properties).
    Oracle.connect(getClass(), "connect.properties");
  }

  public static void main (String [] args) throws SQLException {
    
    try {
      SimpleExample o1 = new SimpleExample();
      o1.runExample();
    }
    catch (SQLException ex) {
      System.err.println("Error running the example: " + ex);
    }
  }

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

  void runExample() throws SQLException {
     
     System.out.println( "Running the example--" );
     
     // Declare two Java host variables--
     Float salary;
     String empname;

     // Use SELECT INTO statement to execute query and retrieve values.
        #sql { SELECT ename, sal INTO :empname, :salary FROM emp
              WHERE empno = 7499 };
     
     // Print the results--
     System.out.println("Name is " + empname + ", and Salary is " + salary);
  }
}

Set Up a Named Iterator

The next example will build on the previous example by adding a named iterator and using it for a multiple-row query.

First, declare the iterator class. Use object types Integer and Float, instead of primitive types int and float, wherever there is the possibility of null values.

#sql iterator EmpRecs(
      int empno,       // This column cannot be null, so int is OK.
                       // (If null is possible, use Integer.)
      String ename,
      String job,
      Integer mgr,
      Date hiredate,
      Float sal,
      Float comm,
      int deptno);

Later, instantiate the EmpRecs class and populate it with query results.

EmpRecs employees;

#sql employees = { SELECT empno, ename, job, mgr, hiredate,
                   sal, comm, deptno FROM emp };

Then use the next() method of the iterator to print the results.

    while (employees.next())  {
      System.out.println( "Name:       " + employees.ename() );
      System.out.println( "EMPNO:      " + employees.empno() );
      System.out.println( "Job:        " + employees.job() );
      System.out.println( "Manager:    " + employees.mgr() );
      System.out.println( "Date hired: " + employees.hiredate() );
      System.out.println( "Salary:     " + employees.sal() );
      System.out.println( "Commission: " + employees.comm() );
      System.out.println( "Department: " + employees.deptno() );
      System.out.println();
    }

Finally, close the iterator when you are done.

employees.close();

Example of Multiple-Row Query Using Named Iterator

This example uses a named iterator for a multiple-row query that selects several columns of data from a table of employees.

Aside from use of the named iterator, this example is conceptually similar to the previous single-row query example.

// Import SQLJ classes:
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
import oracle.sqlj.runtime.*;

// Import standard java.sql package:
import java.sql.*;

// Declare a SQLJ iterator.
// Use object types (Integer, Float) for mgr, sal, And comm rather
// than primitive types to allow for possible null selection.

#sql iterator EmpRecs(
      int empno,       // This column cannot be null, so int is OK.
                       // (If null is possible, Integer is required.)
      String ename,
      String job,
      Integer mgr,
      Date hiredate,
      Float sal,
      Float comm,
      int deptno);

// This is the application class.  
public class EmpDemo1App {

   public EmpDemo1App() throws SQLException {
      /* If you are using a non-Oracle JDBC driver, add a call here to 
         DriverManager.registerDriver() to register your driver.  */
      // Set default connection (as defined in connect.properties).
      Oracle.connect(getClass(), "connect.properties");
   }

  public static void main(String[] args) {

    try {
      EmpDemo1App app = new EmpDemo1App();
      app.runExample();
    }
    catch( SQLException exception ) {
      System.err.println( "Error running the example: " + exception );
    }
  }

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

  void runExample() throws SQLException  {
    System.out.println("\nRunning the example.\n" );

    // The query creates a new instance of the iterator and stores it in
    // the variable 'employees' of type 'EmpRecs'.  SQLJ translator has
    // automatically declared the iterator so that it has methods for
    // accessing the rows and columns of the result set.

    EmpRecs employees;

    #sql employees = { SELECT empno, ename, job, mgr, hiredate,
                       sal, comm, deptno FROM emp };

    // Print the result using the iterator.

    // Note how the next row is accessed using method 'next()', and how
    // the columns can be accessed with methods that are named after the
    // actual database column names.

    while (employees.next())  {
      System.out.println( "Name:       " + employees.ename() );
      System.out.println( "EMPNO:      " + employees.empno() );
      System.out.println( "Job:        " + employees.job() );
      System.out.println( "Manager:    " + employees.mgr() );
      System.out.println( "Date hired: " + employees.hiredate() );
      System.out.println( "Salary:     " + employees.sal() );
      System.out.println( "Commission: " + employees.comm() );
      System.out.println( "Department: " + employees.deptno() );
      System.out.println();
    }

    // You must close the iterator when it's no longer needed.
    employees.close() ;
  }
}

Other Programming Considerations

This section covers miscellaneous programming considerations not covered previously in this chapter:

Naming Requirements and Restrictions

There are four areas to consider in discussing naming requirements, naming restrictions, and reserved words:

Java Namespace--Local Variable and Class Naming Restrictions

The Java namespace applies to all your standard Java statements and declarations, including the naming of Java classes and local variables. All standard Java naming restrictions apply, and you should avoid use of Java reserved words.

In addition, SQLJ places minor restrictions on the naming of local variables and classes.


Note:

Naming restrictions particular to host variables are discussed in "Restrictions on Host Expressions"


Local Variable Naming Restrictions

Some of the functionality of the SQLJ translator results in minor restrictions in naming local variables.

The SQLJ translator replaces each SQLJ executable statement with a statement block, where the SQLJ executable statement is of the standard syntax:

#sql { SQL operation };  

SQLJ may use temporary variable declarations within a generated statement block. The name of any such temporary variables will include the following prefix:

 __sJT_

(There are two underscores at the beginning and one at the end.)

The following declarations are examples of those that might occur in a SQLJ-generated statement block:

int __sJT_index;
Object __sJT_key;
java.sql.PreparedStatement __sJT_stmt;

The string __sJT_ is a reserved prefix for SQLJ-generated variable names. SQLJ programmers must not use this string as a prefix for the following:

Class Naming Restrictions

Be aware of the following minor restrictions in naming classes in SQLJ applications:

SQLJ Namespace

The SQLJ namespace refers to #sql class declarations and the portion of #sql executable statements outside the curly braces.


Note:

Restrictions particular to the naming of iterator columns are discussed in "Using Named Iterators"


Avoid using the following SQLJ reserved words as class names for declared connection context classes or iterator classes, in with or implements clauses, or in iterator column type declaration lists:

For example, do not have an iterator class or instance called iterator or a connection context class or instance called context.

Note, however, that it is permissible to have a stored function return variable whose name is any of these words.

SQL Namespace

The SQL namespace refers to the portion of a SQLJ executable statement inside the curly braces. Normal SQL naming restrictions apply here. See the Oracle9i SQL Reference for more information.

Note, however, that host expressions follow rules of the Java namespace, not the SQL namespace. This applies to the name of a host variable and to everything between the outer parentheses of a host expression.

File Name Requirements and Restrictions

SQLJ source files have the .sqlj file name extension. If the source file declares a public class (maximum of one), then the base name of the file must match the name of this class (case-sensitive). If the source file does not declare a public class, then the file name must still be a legal Java identifier, and it is recommended that the file name match the name of the first defined class.

For example, if you define the public class MySource in your source file, then your file name must be:

MySource.sqlj


Note:

These file naming requirements follow the Java Language Specification and are not SQLJ-specific. These requirements do not directly apply in Oracle9i, but it is still advisable to adhere to them. 


Statement Caching Methods

To alter the statement cache size or disable statement caching when generating Oracle-specific code, you must use method calls in your code instead of using the customizer stmtcache option (because profile customization is not applicable with Oracle-specific code generation). The sqlj.runtime.ref.DefaultContext class, as well as any connection context class you declare, now has the following static methods:

and the following instance methods:

By default, statement caching is enabled.

You might also want to use these methods with standard code generation, to disable or alter implicit JDBC statement caching behavior. They defer to methods of the same name in the underlying JDBC connection object.

See "Connection Context Methods for Statement Cache Size" for more information. (This is a subsection under "Statement Caching", which provides an overview of statement caching.)

For information about Oracle-specific code generation, see "Oracle-Specific Code Generation (No Profiles)".


Go to previous page Go to next page
Oracle
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Solution Area
Go To Table Of Contents
Contents
Go To Index
Index