ONJava.com    
 Published on ONJava.com (http://www.onjava.com/)
 See this if you're having trouble printing code examples


Learning SQLJ

Setting Up Your Environment to Develop SQLJ Programs

by Jason Price, author of Java Programming with Oracle SQLJ
12/05/2001

SQLJ is a technology that enables a Java program to access a database using embedded Structured Query Language (SQL) statements. You can embed SQLJ statements in many types of Java programs, including stand-alone applications, servlets, JavaServer Pages, and Enterprise JavaBeans. This first column in my Learning SQLJ series introduces SQLJ, describes the software requirements for using it, and shows you how to configure your computer to develop SQLJ programs. I'll also demonstrate how to use SQLJ to develop a version of the classic "Hello World" program that will access a database.

I'm the author of O'Reilly's Java Programming with Oracle SQLJ, which shows you how to get the most out of this powerful technology. These columns will reference numerous SQL scripts, source code, and other files, which are all available for download from the book's O'Reilly Web site. All of the files are contained in a single ZIP file, named SQLJ_examples.zip, which you should download and then open using WinZip. To extract the ZIP file, you must use the Extract function from the Actions menu, which will create three subdirectories: programs (contains the program source files), sql (contains the SQL*Plus scripts to create the database schemas), and sample_files (contains two sample files).

Requirements for Using SQLJ

SQLJ consists of two components: the translator and the runtime libraries. The translator reads a Java source file containing embedded SQL statements, which are then translated into calls to the SQLJ runtime libraries. The SQLJ translator also compiles the Java source file into byte codes. The calls to the runtime libraries in the compiled code perform the actual database operations. SQLJ is an ISO and ANSI standard that was developed by many large corporations including Oracle, Sun Microsystems, IBM, Compaq, Informix, and Sybase. You can view the latest developments on the SQLJ standard at the SQLJ.org Web site.

There are various software requirements for using SQLJ. Before you can write and execute a SQLJ 8i (or above) program, you need to have the following software installed:

The SQLJ translator, JDBC drivers, and the JDK need to be installed on the machine that you are using for development. The Oracle8i database and Net8 can reside anywhere on your network, so long as you have access to them. You can download trial versions of all the Oracle software from Oracle's Technet Web site, and you can download the JDK from Sun's Java Web site. If you are installing the Oracle database onto your development machine, you can also choose to install the SQLJ translator and JDBC drivers at the same time. You don't have to install them separately.

The specific version numbers for the above components are important. A given release of SQLJ will only work with specific versions of the JDK and the other Oracle software components. Thus, you not only need to have the right components, but you need to have the right combination of versions. SQLJ versions 8.1.6 and 8.1.7 are compatible with JDK versions 1.1.x and 1.2.x; SQLJ 9i is also compatible with versions of the JDK higher than 1.2.x. JDK version 1.2.2 was used for this article. You should consult the Oracle SQLJ Release Notes for details on which version of the JDK and the other Oracle software components you need if you are using a different version of SQLJ.

All of the examples in this article were developed using Oracle SQLJ version 8.1.6, along with version 8.1.6 of the Oracle JDBC drivers.

In addition to the necessary components listed here, the Oracle JDeveloper tool is very useful when developing Java programs. JDeveloper runs on Windows NT and is a tool that features an integrated development environment. However, JDeveloper is not required in order for you to use SQLJ, because you can use the Oracle Java command-line tools to perform your development tasks. The various Oracle Java command-line utilities use the same syntax and options, regardless of the operating system on which they run.

Configuring Your Environment

This section describes how to configure your the computing environment to develop and run SQLJ programs. Three environment variables must be set: ORACLE_HOME, PATH, and CLASSPATH. The ORACLE_HOME environment variable specifies the directory in which the Oracle software is installed. The PATH environment variable specifies a list of directories that contain the command line utilities. The CLASSPATH environment variable specifies a list of Java class libraries and directories that are used by the various Java utilities.

The ORACLE_HOME Environment Variable

You should add a system environment variable named ORACLE_HOME using the System Properties dialog. The ORACLE_HOME environment variable should be set to the directory where the Oracle software is installed. Many Oracle applications and utilities depend on the ORACLE_HOME environment variable to point the way to the Oracle software directory.

To set your ORACLE_HOME environment variable using Windows NT 4.0, you should use the System Properties dialog in the Control Panel. Figure 1 shows an example of the System Properties dialog, along with an ORACLE_HOME environment variable (Windows 2000 has a similar dialog).

Screen shot.
Figure 1. Setting the ORACLE_HOME environment variable in Windows NT 4.0

To set your ORACLE_HOME environment variable using the Bash shell with Linux, you should add lines to your .bash_profile file that are similar to the following:

ORACLE_HOME=installation_directory
export ORACLE_HOME

Where installation_directory is the directory where the Oracle software is installed.


The PATH Environment Variable

Related Reading

Java Programming with Oracle SQLJJava Programming with Oracle SQLJ
By Jason Price
Table of Contents
Index
Sample Chapter
Full Description
Read Online -- Safari

The following two directories should be added to your PATH environment variable:

To set your PATH environment variable using Windows NT 4.0, you should use the System Properties dialog in the Control Panel. To set your PATH environment variable using the Bash shell with Linux, you should add lines similar to those in the following example to your .bash_profile file:

PATH=$PATH:/usr/local/jdk1.2.2/bin:$ORACLE_HOME/bin
export PATH

The CLASSPATH Environment Variable

When the Oracle software is installed, a directory named jdbc is created under the ORACLE_HOME directory. This directory contains example programs, documentation, and the libraries for Oracle JDBC. The JDBC libraries are contained in a ZIP file, and this ZIP file must be included in your CLASSPATH environment variable.

If you are using JDK 1.2.x or higher, then ORACLE_HOME\jdbc\lib\classes12.zip should be added to your CLASSPATH environment variable. If you are using JDK 1.1.x, then ORACLE_HOME\jdbc\lib\classes111.zip should be added to your CLASSPATH.

In addition to the jdbc directory, a directory named sqlj is also created under the ORACLE_HOME directory. This directory contains example programs, documentation, and the libraries for Oracle SQLJ. In order to support SQLJ, the following file should be added to your CLASSPATH environment variable:

ORACLE_HOME\sqlj\lib\translator.zip

In addition, you should also add one of the SQLJ runtime files to your CLASSPATH environment variable, depending on which version of the JDK you are using. If you are using JDK 1.1.x, you should add ORACLE_HOME\sqlj\lib\runtime11.zip. If you are using JDK 1.2.x, you should add ORACLE_HOME\sqlj\lib\runtime12.zip. If you are using JDK 1.2.x Enterprise Edition, you should add ORACLE_HOME\sqlj\lib\runtime12ee.zip. You can add ORACLE_HOME\sqlj\lib\runtime.zip, which is compatible with all JDK versions, but doesn't comply with the ISO standard when using JDBC 2.0 types.

You should also add the current directory, specified using the dot (.) character, to your CLASSPATH.

If you are using Windows NT 4.0, you should set your CLASSPATH using the System Properties dialog. To set your CLASSPATH environment variable using the Bash shell with Linux, you should add lines to your .bash_profile file that are similar to those in the following example:

CLASSPATH=$CLASSPATH:.:$ORACLE_HOME/sqlj/lib/translator.zip:
$ORACLE_HOME/sqlj/lib/runtime.zip:$ORACLE_HOME/jdbc/lib/classes12.zip
export CLASSPATH

A "Hello World" Program for SQLJ

Many programming books introduce new languages using a variation of the "Hello World" program. The classic "Hello World" program has a simple objective: to display the words "Hello World" on the screen. My version of the "Hello World" program uses SQLJ statements to connect to the database and retrieve the current date, which is then displayed for you to see. First, I'll show you the code required for this "Hello World" program. Then I'll show you how to compile and run it. By convention, SQLJ programs use the file extension .sqlj, and so the "Hello World" program will be named HelloWorld.sqlj.

The HelloWorld.sqlj program, shown in Example 1-1, performs the following tasks:

  1. Connects to the database.
  2. Retrieves the current date from the database.
  3. Displays a message containing the current date obtained in the previous step.
  4. Disconnects from the database.

Example 1-1 lists the HelloWorld.sqlj program.


Example 1-1. HelloWorld.sqlj

/*
   The program HelloWorld.sqlj illustrates how to 
   connect to a database, and display the words 
   "Hello World" along with the current date.
*/

// import required packages
import java.sql.Date;
import java.sql.SQLException;
import oracle.sqlj.runtime.Oracle;

public class HelloWorld {

  public static void main(String [] args) {

    java.sql.Date current_date;

    try {

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

      // get the current date from the database
      #sql { SELECT sysdate INTO :current_date FROM dual };

      // display message
      System.out.println("Hello World! The current date is " +
        current_date);

    } catch ( SQLException e ) {

      System.err.println("SQLException " + e);

    } finally {
      try {

        // disconnect from the database
        Oracle.close();

      } catch ( SQLException e ) {
        System.err.println("SQLException " + e);
      }
    }

  } // end of main()

}


Compiling and Running HelloWorld.sqlj

The HelloWorld.sqlj file contains embedded SQL statements that are contained within SQLJ program statements. Having written the program, your next step is to translate the SQLJ statements contained in the HelloWorld.sqlj file into calls to the SQLJ runtime libraries. The SQLJ runtime libraries then use JDBC to actually perform the database operations. This translation is performed using the sqlj command-line utility. The sqlj command-line utility translates the .sqlj file into a .java file, and then compiles the .java file into a .class file using the Java compiler.

The following command invokes sqlj to translate and compile the HelloWorld.sqlj file:

sqlj HelloWorld.sqlj

The sqlj command-line utility translates HelloWorld.sqlj into HelloWorld.java, and then calls the Java compiler to compile HelloWorld.java into HelloWorld.class. The HelloWorld.class file may then be executed using the java command-line utility:

java HelloWorld

The program should display the following line (your date will be different):

Hello World! The current date is 2001-11-10

That's it! You have successfully compiled and run your first SQLJ program. If your program is unable to connect to the database, check with your database administrator to determine if the database URL is correct for your environment.

I hope you've enjoyed reading this first column on learning SQLJ. Next month, I'll go over how to connect to a database and embed SQL statements in your Java programs using SQLJ.

Jason Price is currently employed as a product manager with Oracle Corporation. He has contributed to many products at Oracle, including the database server, the application server, and several CRM applications.


Read more Learning SQLJ columns.

Return to ONJava.com.

Copyright © 2009 O'Reilly Media, Inc.