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.

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. The Oracle JDBC Thin and OCI drivers work with any of these JDK versions.

  For more information, see "Supported JDK Versions".

  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 sample 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 JDBC driver that complies with standards.

• a database system that is accessible using your JDBC driver

• class files for the SQLJ translator and SQLJ profile customizer

  Translator-related classes are available in the file:

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

• 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.zip (or .jar)--for use with Oracle9i JDBC drivers under JDK 1.2.x or higher, providing full SQLJ ISO functionality

  • runtime12ee.zip (or .jar)--for use with Oracle9i JDBC drivers in a J2EE environment (including JDK 1.2.x or higher), providing full SQLJ ISO functionality

  • runtime11.zip (or .jar)--for use with Oracle9i JDBC drivers under JDK 1.1.x

  • runtime.zip (or .jar)--for use with older Oracle JDBC drivers and any JDK environment (intended for Oracle JDBC release 8.1.7 and prior)

  • runtime-nonoracle.zip (or .jar)--for use with non-Oracle JDBC drivers and any JDK environment

Special Notes Regarding the SQLJ Libraries

Be aware of the following:

• For SQLJ ISO-compliant support for JDBC 2.0 types such as java.sql.Ref, Clob, Blob, Struct, and SQLData, use the runtime12 or runtime12ee library with JDK1.2 or J2EE and an Oracle9i (or 8.1.7) JDBC driver.

• For Oracle-specific code generation, use the runtime11, runtime12, or runtime12ee library.

• Certain features, such as Oracle-specific code generation, are not supported by the generic runtime ZIP/JAR file, which is intended mainly for backwards compatibility, or by the runtime-nonoracle ZIP/JAR file.

• The runtime-nonoracle library provides the highest portability across different Java and JDBC environments, but does not support Oracle-specific functionality.

• The runtime library provides the highest flexibility across different Java and Oracle JDBC environments, but does not support all SQLJ ISO functionality.

• In SQLJ release 8.1.6 and earlier, there was only one runtime library, and the runtime ZIP/JAR file was a subset of the translator ZIP/JAR file. In Oracle9i this is no longer the case. You must now specify both a runtime file and the translator file in your classpath.

• If you will be running only SQLJ applications that have already been translated, compiled, and customized, you will not need the translator ZIP/JAR file.

• The translator ZIP file and all runtime ZIP files are uncompressed for maximum portability. JAR files are compressed, however.

Supported JDK Versions

Oracle9i SQLJ works in any JDK 1.1.x or higher Java environment. There is only one SQLJ translator file, translator.zip or .jar, for use in any JDK environment and with any JDBC driver. There are several choices for the SQLJ runtime file (runtime12, runtime12ee, runtime11, runtime, or runtime-nonoracle, all .zip or .jar). Choose one according to your JDBC driver and Java environment, as discussed above in "Requirements for Using Oracle SQLJ".

General JDK MIgration Notes

Note the following regarding migration of SQLJ source code:

• If you translate under JDK 1.1.x, Oracle supports running 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, Oracle supports running the application under JDK 1.2 or higher.

  Notes: If an application compiles under both JDK 1.1.x and JDK 1.2, and if JDK 1.2 was used to compile it, then the application will likely not run under JDK 1.1.x. You should use JDK 1.1.x to compile an application that will run under 1.1.x. Oracle JDBC releases 8.1.5 and prior do not support JDK 1.2.x.

Be sure to use an appropriate version of the JDBC driver. See "Path and Classpath for Oracle JDBC".

Note that in Oracle9i, neither Oracle SQLJ nor Oracle JDBC 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".)

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. Unfortunately, there is a limitation in the Java type system when you implement an interface, such that an implemented method must return exactly the type specified in the interface. Consider the following method signature:

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

This would seem to be an ideal solution to the migration issue, because Hashtable extends Dictionary and implements Map, but it is not acceptable to the Java type system.

This has the following consequences if your SQLJ code declares connection context types:

• If you compile under JDK 1.1.x, the generated code will return Dictionary instances. The code should run under both JDK 1.1.x and JDK 1.2.x or higher. (Modifications were made to the SQLJ runtime so that if the interface method is not found, Java reflection is used.)

• If you compile under JDK 1.2.x or higher, you will not be able to run under JDK 1.1.x (java.util.Map just 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: With default settings, JPublisher-generated .sqlj source code declares connection context types. See the Oracle9i JPublisher User's Guide for more information, particularly information about the JPublisher -context option.

Oracle SQLJ Backwards Compatibility

Be aware of the following regarding Oracle SQLJ backwards compatibility:

• Code generated with an earlier release of the SQLJ translator will:
  • continue to run against the current runtime (.zip or .jar) library

  • continue to be compilable against the current runtime (.zip or .jar) library

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

• Oracle-specific translator output (code generated with -codegen=oracle) 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.

  • If such code was translated and compiled under JDK 1.2.x, it will not run under JDK 1.1.x. Re-translate it under JDK 1.1.x.

  Also remember that Oracle-specific generated code is not portable.

  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.

Oracle JVM Configuration

This manual presumes that system configuration issues are outside the duties of most SQLJ developers. Therefore, configuration of the Oracle Java virtual machine (JVM) is not covered here. For information about setting Java-related configuration parameters (such as JAVA_POOL_SIZE), see the Oracle9i Java Developer's Guide.

If you need information about configuring the multi-threaded server, dispatcher, or listener (which may be particularly relevant if you are coding Enterprise JavaBeans or CORBA objects), see the Oracle9i Net Services Administrator's Guide.

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 (.zip/.jar 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://technet.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 ZIP/JAR file that is appropriate for your environment.

JDK 1.1.x-compatible classes are in classes111.zip or .jar; JDK 1.2.x (or higher) compatible classes are in classes12.zip or .jar. Presuming you use a Sun Microsystems JDK, make sure the appropriate ZIP/JAR file name is in your classpath setting.

For more information about 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 .zip or .jar):

[Oracle Home]/sqlj/lib/translator.zip

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 .zip or .jar):

[Oracle Home]/sqlj/lib/runtime12.zip
[Oracle Home]/sqlj/lib/runtime12ee.zip
[Oracle Home]/sqlj/lib/runtime11.zip
[Oracle Home]/sqlj/lib/runtime.zip
[Oracle Home]/sqlj/lib/runtime-nonoracle.zip

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's 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 and/or 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.)

There is also a listing of connect.properties in "Runtime Connection Properties File".

Connecting with an Oracle JDBC Driver

With the Oracle9i release, use "oci" in the connect string for the Oracle JDBC OCI driver in any new code. For backwards compatibility, however, "oci8" or "oci7" are still accepted, so you do not have to change existing code.

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.

On Solaris, 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 ZIP/JAR 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=java.sql.Connection
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!