Setting Up Your Environment to Develop SQLJ Programs
The PATH Environment Variable
The following two directories should be added to your PATH environment variable:
JDK_HOME\bin.
JDK_HOME is the directory where the Sun JDK is installed. This directory contains the Java command-line utilities.
ORACLE_HOME\bin.
ORACLE_HOME is the directory is where the Oracle software is installed. This directory contains the Oracle command-line utilities.
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:
- Connects to the database.
- Retrieves the current date from the database.
- Displays a message containing the current date obtained in the previous step.
- 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.