2
Getting Started

This chapter guides you through the basics of testing your Oracle SQLJ installation and configuration and running a simple application.

Note that if you are using an Oracle database and Oracle JDBC driver, you should also verify your JDBC installation according to the Oracle9i JDBC Developer's Guide and Reference.

This chapter discusses the following topics:

• Assumptions and Requirements
• Checking the Installation and Configuration
• Testing the Setup

Assumptions and Requirements

This section discusses basic assumptions about your environment and requirements of your system so that you can run Oracle SQLJ, covering the following topics:

• Assumptions About Your Environment
• Requirements for Using Oracle SQLJ
• Oracle SQLJ Environment: Key Scenarios and Guidelines
• Environment Issues and Limitations
• Oracle SQLJ Backward Compatibility

Assumptions About Your Environment

The following assumptions are made about the system on which you will be running Oracle SQLJ.

• You have a standard Java environment that is operational on your system. This would typically be using a Sun Microsystems JDK, but other implementations of Java will work. Make sure you can run Java (typically java) and your Java compiler (typically javac).

  To translate and run Oracle SQLJ applications on a Sun JDK, you must use a JDK 1.2.x (or higher) or JDK 1.1.x version, with an appropriate JDBC driver. There are Oracle JDBC Thin and OCI driver versions that work with any of these JDK scenarios.

  For more information, see "Oracle SQLJ Environment: Key Scenarios and Guidelines".

  Note: A Java runtime environment (JRE), such as the one installed with Oracle9i, is not by itself sufficient for translating SQLJ programs. A JRE is sufficient, however, for running SQLJ programs that have already been translated and compiled.

• You can already run JDBC applications in your environment.

  If you are using an Oracle database and Oracle JDBC driver, then you should complete the steps in Chapter 2, "Getting Started", of the Oracle9i JDBC Developer's Guide and Reference.You can also refer to Chapter 1, "Overview", of that document for information about the Oracle JDBC drivers and how to decide which is appropriate for your situation.

  Notes: If you are using a non-Oracle JDBC driver, you must do the following: Modify connect.properties, as discussed in "Set Up the Runtime Connection". Modify the demo applications, as discussed in "Driver Selection and Registration for Runtime", so that your driver is registered before the call to the Oracle.connect() method.

Requirements for Using Oracle SQLJ

The following are required to use Oracle SQLJ:

• a JDBC driver implementing the standard java.sql JDBC interfaces from Sun Microsystems

  Oracle SQLJ works with any standard JDBC driver.

• a database system that is accessible using your JDBC driver
• class files for the SQLJ translator

  Translator-related classes are available in the file:

  [Oracle_Home]/sqlj/lib/translator.jar (or .zip)

• class files for the SQLJ runtime

  Several SQLJ runtime versions are available. You must select a runtime version that is compatible with your Java environment and JDBC driver (these are all in [Oracle_Home]/sqlj/lib).

  • runtime12.jar (or .zip)--for use with Oracle9i JDBC drivers under JDK 1.2.x or higher, providing full ISO SQLJ functionality
  • runtime12ee.jar (or .zip)--for use with Oracle9i JDBC drivers in a J2EE environment (using JDK 1.2.x or higher), providing full ISO SQLJ functionality
  • runtime11.jar (or .zip)--for use with Oracle9i JDBC drivers under JDK 1.1.x
  • runtime.jar (or .zip)--for use with older Oracle JDBC drivers and any JDK environment (intended for Oracle JDBC release 8.1.7 and prior)
  • runtime-nonoracle.jar (or .zip)--for use with non-Oracle JDBC drivers and any JDK environment

Oracle SQLJ Environment: Key Scenarios and Guidelines

To ensure you have a fully working environment, you must consider several aspects of your environment--SQLJ and its code generation mode, JDBC, and the JDK. This section first discusses the two main Oracle scenarios of supported combinations, and then discusses some important general guidelines.

Also see "Environment Issues and Limitations" for related information.

Scenario 1: Oracle-Specific Code

This section documents a typical environment setup for Oracle-specific code generation. Note that in this case, the SQLJ generic runtime library is not an option.

• SQLJ code generation: -codegen=oracle (default)
• SQLJ library during translation:
  • Oracle SQLJ runtime11 library with JDK 1.1

  or:

  • Oracle SQLJ runtime12 or runtime12ee library with JDK 1.2 or higher
• JDBC and JDK: Oracle JDBC version 9.0.1 or higher with JDK 1.1 or higher

  If you might be running against either the 9.0.1 or 9.2 JDBC driver, translate against 9.0.1. In general, compile against the oldest driver you might use.

• SQLJ library during runtime: same library as for translation

Scenario 2: ISO Standard Code

This section documents a typical environment setup for ISO standard code generation.

1. SQLJ code generation: -codegen=iso
2. SQLJ library during translation:
   • SQLJ runtime11 library with JDK 1.1

   or:

   • SQLJ runtime12 or runtime12ee library with JDK 1.2 or higher

   or, for Oracle JDBC versions 8.1.7 and prior:

   • SQLJ generic runtime library with any JDK 1.1 or higher
3. JDBC and JDK: any Oracle JDBC version with any JDK 1.1 or higher
4. SQLJ library during runtime: same library as for translation

Environment Scenarios: Key Guidelines

Regarding your environment for running Oracle SQLJ, be aware of the following important guidelines and considerations:

• In general, use the same versions of the SQLJ library, JDBC library, and JDK in translating and compiling all components of your application.
• Always be aware of the following cross-compatibility considerations:
  • If you want to be able to run the same compiled code in either a JDK 1.1 environment or a JDK 1.2 or higher environment, then translate against the runtime11 library under JDK 1.1. This allows the option of using the runtime12 or runtime12ee library under JDK 1.2 at runtime. If you translate against JDK 1.2, several JDBC 2.0 APIs that are not supported under JDK 1.1 are compiled into your class files.
  • If you want to be able to run against either a version 9.0.1 or a version 9.2 Oracle JDBC driver, then translate against a 9.0.1 driver. This allows you to use either driver version at runtime.

    Generated code is optimized toward the JDBC driver in the classpath during translation.

  • For maximal cross-compatibility, avoid using declared connection context classes. If you use JPublisher, use the default settings for the -compatible and -context options. See the Oracle9i JPublisher User's Guide for information about these options.

  You can also consider using the runtime library for cross-compatibility, but this library has disadvantages (such as not supporting the Oracle9i oracle.jdbc.OracleXXX interfaces, which causes problems in the middle tier).

Environment Issues and Limitations

This section discusses a key environmental issue--JDK migration regarding type maps--and lists resulting limitations and some additional environment issues and limitations. These are among the considerations in planning the typical scenarios outlined in "Oracle SQLJ Environment: Key Scenarios and Guidelines".

JDK Migration Issues Regarding Type Maps

The type for JDBC type maps changed between JDK 1.1.x and JDK 1.2.x, from java.util.Dictionary to java.util.Map.

The getTypeMap() method of all SQLJ connection context classes returns a type map instance. The Java type system requires that an implemented method must return exactly the type specified in an underlying interface (in this case, the JDBC Connection interface). Consider the following method signature:

java.util.Hashtable getTypeMap() { ... }

This would seem to implement both the JDK 1.1 and JDK 1.2 specifications, because Hashtable extends Dictionary and implements Map; however, it is not acceptable to the Java type system.

This incompatibility between the JDK 1.1 and JDK 1.2 interfaces has the following consequences if your SQLJ code declares connection context types:

• If you compile under JDK 1.1.x, including when you translate against the SQLJ runtime11 library, or when you compile under any JDK when translating against the SQLJ generic runtime library, the generated code will return Dictionary instances. The code should run under both JDK 1.1.x and JDK 1.2.x or higher. (Under JDK 1.2, the SQLJ runtime uses Java Reflection as necessary.)
• If you compile under JDK 1.2.x or higher, including when you translate against the SQLJ runtime12 or runtime12ee library, you will not be able to run under JDK 1.1.x (because java.util.Map, among other things, was not defined in JDK 1.1.x).
• If you translate .sqlj files under JDK 1.1.x, the generated .java files will not compile under JDK 1.2.x or higher. (This is relevant if you run SQLJ with the -compile=false setting, in order to translate and compile separately.) Likewise, if you translate under JDK 1.2.x or higher, the generated .java files will not compile under JDK 1.1.x.

  Note: If you use JPublisher, the default setting is to use DefaultContext instances for connections, in which case the preceding issues do not arise. But with JPublisher nondefault -context option settings, or with a -compatible option setting of both8i or 8i, JPublisher-generated .sqlj source code declares connection context types. See the Oracle9i JPublisher User's Guide for more information.

Other Environment Issues and Limitations

The following list notes additional environmental issues and limitations, mostly related to the type map issues discussed above.

• With the default -codegen=oracle setting, you cannot use the generic runtime library. Use the runtime11 library (for JDK 1.1) or the runtime12 or runtime12ee library (for JDK 1.2 or higher).
• For Oracle JDBC version 8.1.7 or prior, you must use -codegen=iso and the generic runtime library (not runtime11, runtime12, or runtime12ee).
• For any SQLJ application using declared connection context classes, all modules must be translated against JDK 1.1 (using the runtime or runtime11 library) or all modules must be translated against JDK 1.2 or higher (using the runtime12 or runtime12ee library). There cannot be a mixture. This includes situations where you use JPublisher with a nondefault -context option setting.
• You can run against a JDK version that is at least as high as the version you translated against. If you translate under JDK 1.1.x, you can run the application under either JDK 1.1.x or JDK 1.2 or later. This assumes that you do not have any JDBC code that uses the oracle.jdbc2 package, which Oracle SQLJ does not support. Oracle JDBC used this package to support JDBC 2.0 types under JDK 1.1.x. If you translate under JDK 1.2, you can run the application under JDK 1.2 or higher.

  Notes: Oracle JDBC releases 8.1.5 and prior do not support JDK 1.2.x. In Oracle9i, Oracle SQLJ and Oracle JDBC do not support JDK 1.0.2. (Release 8.1.6 was the last Oracle JDBC release to support JDK 1.0.2, while Oracle SQLJ has never supported JDK 1.0.2.) This includes applets running in browsers that use JDK 1.0.2 except where special preparations have been made. (This chapter does not discuss applets. Refer to "Running SQLJ in Applets".)

Oracle SQLJ Backward Compatibility

Be aware of the following regarding Oracle SQLJ backward compatibility:

• Code generated with an earlier release of the SQLJ translator will continue to do the following (subject to cross-compatibility limitations discussed in "Environment Issues and Limitations"):
  • run against current runtime (.jar or .zip) libraries
  • be compilable against current runtime (.jar or .zip) libraries
• Oracle-specific translator output (code generated with the default -codegen=oracle setting) must be created and executed using the runtime11, runtime12, or runtime12ee library. Furthermore:
  • Such code will be executable under future releases of Oracle JDBC and SQLJ.
  • Such code, however, will not be executable under previous releases of Oracle JDBC and the Oracle SQLJ runtime. In these circumstances, you will have to retranslate the code.

  Also remember that Oracle-specific code is not portable.

• ISO standard generated code (-codegen=iso) can be created and executed against an earlier Oracle JDBC release using the current runtime (.jar or .zip) library.

  Note: Regarding Oracle JDBC backward compatibility to prior database releases, any given Oracle JDBC driver release is compatible with any Oracle database release from 7.3.4 up to the release number of the JDBC driver. For more information see the Oracle9i JDBC Developer's Guide and Reference.

Checking the Installation and Configuration

Once you have verified that the above assumptions and requirements are satisfied, you must check your Oracle SQLJ installation.

Check for Installed Directories and Files

Verify that the following directories have been installed and are populated.

Directories for Oracle JDBC

If you are using one of the Oracle JDBC drivers, refer to the Oracle9i JDBC Developer's Guide and Reference for information about JDBC files that should be installed on your system.

Directories for Oracle SQLJ

Installing the Oracle9i Java environment will include, among other things, installing a sqlj directory under your [Oracle_Home] directory. The sqlj directory contains the following subdirectories:

• demo (demo applications, including some referenced in this chapter)
• doc
• lib (.jar or .zip files containing class files for SQLJ)

In addition, directly under [Oracle_Home] is the following directory, containing utilities for all Java product areas:

• bin

Check that all these directories have been created and populated, especially lib and bin.

The structure is similar if you download SQLJ from a Web site, such as the Oracle Technology Network http://otn.oracle.com address. The bin directory, with both SQLJ and JPublisher executable files, is directly under the sqlj directory.

Set the Path and Classpath

Make sure your PATH and CLASSPATH environment variables have the necessary settings for Oracle SQLJ (and Oracle JDBC if applicable).

Path and Classpath for Oracle JDBC

If you are using one of the Oracle JDBC drivers, you will need the Oracle JDBC classes JAR/ZIP file that is appropriate for your environment.

JDK 1.1-compatible classes are in classes111.jar or .zip; JDK 1.2 (or higher) compatible classes are in classes12.jar or .zip; JDK 1.4-compatible classes are in ojdbc14.jar or .zip. Presuming you use a Sun Microsystems JDK, make sure the appropriate JAR/ZIP file name is in your classpath setting. There may also be alternative JDBC driver libraries available, such as classes12_g.jar, which permits driver debugging information to be printed.

For more information about libraries and required path and classpath settings for Oracle JDBC, refer to the Oracle9i JDBC Developer's Guide and Reference.

Path and Classpath for Oracle SQLJ

Set your PATH and CLASSPATH variables as follows for Oracle SQLJ.

Path Setting

To be able to run the sqlj script (which invokes the SQLJ translator) without having to fully specify its path, verify that your PATH environment variable has been updated to include the following:

[Oracle_Home]/bin

Use backward slashes for Windows. Replace [Oracle_Home] with your actual Oracle home directory.

Classpath Setting

Update your CLASSPATH environment variable to include the current directory as well as the following (either .jar or .zip):

[Oracle_Home]/sqlj/lib/translator.jar

Use backward slashes for Windows. Replace [Oracle_Home] with your actual 
Oracle home directory.

In addition, you must include one of the following runtime libraries in your classpath (either .jar or .zip):

[Oracle_Home]/sqlj/lib/runtime12.jar
[Oracle_Home]/sqlj/lib/runtime12ee.jar
[Oracle_Home]/sqlj/lib/runtime11.jar
[Oracle_Home]/sqlj/lib/runtime.jar
[Oracle_Home]/sqlj/lib/runtime-nonoracle.jar

See "Requirements for Using Oracle SQLJ" regarding which runtime library to use for your JDBC driver and Java environment.

sqlj -version-long

Verify Installation of sqljutl Package

The package sqljutl is required for online checking of stored procedures and functions in an Oracle database. For Oracle release 8.1.5 and later, it should have been installed automatically under the SYS schema during installation of your database server-side JVM. To verify the installation of sqljutl, issue the following SQL command (from SQL*Plus, for example):

describe sys.sqljutl

This should result in a brief description of the package. If you get a message indicating that the package cannot be found, then you must install it manually. To do so, use SQL*Plus to run the sqljutl.sql script, which is located as follows:

[Oracle_Home]/sqlj/lib/sqljutl.sql

(The sqljutl package is installed in the SYS schema.)

Consult your installation instructions if necessary.

Testing the Setup

You can test your database, JDBC, and SQLJ setup using demo applications defined in the following source files:

• TestInstallCreateTable.java
• TestInstallJDBC.java
• TestInstallSQLJ.sqlj
• TestInstallSQLJChecker.sqlj

There is also a Java properties file, connect.properties, that helps you set up your database connection. You must edit this file to set appropriate user, password, and URL values.

These demo applications are provided with your SQLJ installation in the demo directory:

[Oracle_Home]/sqlj/demo

You must edit some of the source files as necessary and translate/compile them as appropriate (as explained in the following subsections).

The demo applications provided with the Oracle SQLJ installation refer to tables on an Oracle account with user name scott and password tiger. Most Oracle installations have this account. You can substitute other values for scott and tiger if desired.

Set Up the Runtime Connection

This section describes how to update the connect.properties file to configure your Oracle connection for runtime. The file is in the demo directory and looks something like the following:

# Users should uncomment one of the following URLs or add their own.
# (If using Thin, edit as appropriate.)
#sqlj.url=jdbc:oracle:thin:@localhost:1521:ORCL
#sqlj.url=jdbc:oracle:oci:@
#
# User name and password here
sqlj.user=scott
sqlj.password=tiger

(User scott and password tiger are used for the demo applications.)

Connecting with an Oracle JDBC Driver

With Oracle9i, use "oci" in the connect string for the Oracle JDBC OCI driver in any new code. For backward compatibility, however, "oci8" is still accepted, so you do not have to change existing code. (Also, "oci7" is accepted for Oracle JDBC release 7.3.4.)

If you are using the JDBC Thin driver, then uncomment the thin URL line in connect.properties and edit it as appropriate for your Oracle connection. Use the same URL that was specified when your JDBC driver was set up.

Connecting with a non-Oracle JDBC Driver

If you are using a non-Oracle JDBC driver, then add a line to connect.properties to set the appropriate URL, as follows:

sqlj.url=your_URL_here

Use the same URL that was specified when your JDBC driver was set up.

You must also register the driver explicitly in your code. This is performed automatically in the demo and test programs if you use an Oracle JDBC driver. See "Driver Selection and Registration for Runtime".

In addition, in the SQLJ demo programs, you must replace the following code:

Oracle.connect(url, user, password);

with the following:

DriverManager.registerDriver(new yourdriver());
Connection conn = DriverManager.getConnection(url, user, password);
conn.setAutoCommit(false);
DefaultContext.setDefaultContext(new DefaultContext(conn));

Create a Table to Verify the Database

The following tests assume a table called SALES. If you compile and run TestInstallCreateTable as follows, it will create the table for you if the database and your JDBC driver are working and your connection is set up properly in the connect.properties file:

javac TestInstallCreateTable.java
java TestInstallCreateTable

If you do not want to use TestInstallCreateTable, you can instead create the SALES table using the following command in a command-line processor (such as SQL*Plus):

CREATE TABLE SALES (
      ITEM_NUMBER NUMBER,
      ITEM_NAME CHAR(30),
      SALES_DATE DATE, 
      COST NUMBER, 
      SALES_REP_NUMBER NUMBER,
      SALES_REP_NAME CHAR(20));

Verify the JDBC Driver

If you want to further test the Oracle JDBC driver, use the TestInstallJDBC demo.

Verify that your connection is set up properly in connect.properties as described above, then compile and run TestInstallJDBC:

javac TestInstallJDBC.java
java  TestInstallJDBC 

The program should print:

Hello, JDBC! 

Verify the SQLJ Translator and Runtime

Now translate and run the TestInstallSQLJ demo, a SQLJ application that has similar functionality to TestInstallJDBC. Use the following command to translate the source:

sqlj  TestInstallSQLJ.sqlj

After a brief wait you should get your system prompt back with no error output. Note that this command also compiles the application and customizes it to use an Oracle database.

In a UNIX environment, the sqlj script is in [Oracle_Home]/bin, which should already be in your path as described above. (On Windows, use the sqlj.exe executable in the bin directory.) The SQLJ translator JAR/ZIP file has the class files for the SQLJ translator and runtime. It is located in [Oracle_Home]/sqlj/lib and should already be in your classpath as described above.

Now run the application:

java  TestInstallSQLJ

The program should print:

Hello, SQLJ!

Verify the SQLJ Translator Connection to the Database

If the SQLJ translator is able to connect to a database, then it can provide online semantics-checking of your SQL operations during translation. The SQLJ translator is written in Java and uses JDBC to get information it needs from a database connection that you specify. You provide the connection parameters for online semantics-checking using the sqlj script command line or using a SQLJ properties file (called sqlj.properties by default).

While still in the demo directory, edit the file sqlj.properties and update, comment, or uncomment the sqlj.password, sqlj.url, and sqlj.driver lines, as appropriate, to reflect your database connection information, as you did in the connect.properties file. For some assistance, see the comments in the sqlj.properties file.

Following is an example of what the appropriate driver, URL, and password settings might be if you are using the Oracle JDBC OCI driver. The user name will be discussed next.

sqlj.url=jdbc:oracle:oci:@
sqlj.driver=oracle.jdbc.OracleDriver
sqlj.password=tiger

Online semantics-checking is enabled as soon as you specify a user name for the translation-time connection. You can specify the user name either by uncommenting the sqlj.user line in the sqlj.properties file or by using the -user command-line option. The user, password, URL, and driver options all can be set either on the command line or in the properties file. This is explained in "Connection Options".

You can test online semantics-checking by translating the file TestInstallSQLJChecker.sqlj, located in the demo directory, as follows (or using another user name if appropriate):

sqlj -user=scott TestInstallSQLJChecker.sqlj

This should produce the following error message if you are using one of the Oracle JDBC drivers:

TestInstallSQLJChecker.sqlj:41: Warning: Unable to check SQL query. Error 
returned by database is: ORA-00904: invalid column name

Edit TestInstallSQLJChecker.sqlj to fix the error on line 41. The column name should be ITEM_NAME instead of ITEM_NAMAE. Once you make this change, you can translate and run the application without error using the following commands:

sqlj -user=scott TestInstallSQLJChecker.sqlj
java  TestInstallSQLJChecker

If everything works, this prints:

Hello, SQLJ Checker!