In my first article I described what SQLJ is, compared to PL/SQL and JDBC, and examined SQLJ's benefits. In this article, I explore the fundamentals of the SQLJ programming language so you can get ready to put some actual SQLJ to use.

A SQLJ program is a regular Java program with embedded SQL statements that begin with a #sql token and end with a semicolon. There are two categories of SQLJ statements: declarations and executable statements.

Declarations declare connection contexts and iterators. Connection contexts are used to establish the database connection, while iterators are used to store result sets returned by SQL queries; more on those later. Executable statements execute embedded SQL statements and PL/SQL blocks. Because the SQLJ program will be translated and run via JDBC, any SQLJ statement supported by a JDBC driver (see below) may be embedded within a SQLJ executable statement. Executable statements may also contain host expressions, which are used to exchange information between the Java program and the database via Java variables.

Oracle JDBC Drivers
Oracle provides the following JDBC drivers:

• Client-side Thin driver is a 100% Java driver for client-side use without an Oracle installation. Oracle recommends using it with applets. It can be downloaded to the browser while the Java applet runs.
  
  
• OCI drivers (OCI8 and OCI7) are for client-side use with an Oracle client installation. The Oracle JDBC OCI drivers access the database by calling the Oracle Call Interface (OCI) directly from Java, providing the highest compatibility with the different Oracle 7, 8, and 8i versions. These drivers require an Oracle client installation with Net8.
  
  
• Server-side Thin driver offers the same functionality as the client-side Thin driver but runs inside an Oracle database and accesses a remote database. 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 EJB.
  
  
• Server-side Internal driver, called KPRB (Kernel Program Bundled), provides support for any Java code that runs inside the target Oracle database where the SQL operations are to be performed. The server-side Internal driver allows the JServer JVM to communicate directly with the SQL engine. This is a default JDBC driver for SQLJ code running as a stored procedure, stored function, trigger, EJB, or CORBA object in the Oracle 8i/9i server.
  
  The KPRB JDBC driver is very light and efficient, and it is specifically optimized for running inside Oracle JServer. That's the driver you will be using to write your SQLJ stored procedure.

Let's examine the described SQLJ elements, one by one: connection contexts, iterators, executable statements, and host expressions.

Connection Contexts
For a single connection, you could use one instance of the DefaultContext class and specify the database URL, username, and password when you construct the DefaultContext object. The easiest way is to use the connect() method of the oracle.sqlj.runtime.Oracle class provided by Oracle Corp. In this example, we are going to use the JDBC Thin driver to connect user "scott" with password "tiger" to a database on server MYSERVER through port 1521, where ORCL is the SID of the database to connect to:

Oracle.connect("jdbc:oracle:thin@MYSERVER:1521:ORCL", "scott", "tiger");

It creates an instance of the DefaultContext class and installs it as your default connection. It is not necessary to do anything with the instance of DefaultContext directly.

For multiple connections, you can create and use additional instances of the DefaultContext class by using the Oracle.getConnection() method. In this example, you will use the Oracle OCI8 driver with MYSERVER_ORCL as an Oracle service name, created in the TNSNames.ora file for an ORCL instance:

DefaultContext myContext1 = Oracle.getConnection
("jdbc:oracle:oci8@MYSERVER_ORCL", "scott", "tiger");
DefaultContext myContext2 = Oracle.getConnection
("jdbc:oracle:oci8@MYSERVER_ORCL ", "tom", "bear");

This code creates two connection context instances that both use the same Oracle OCI8 driver, but different schemas. You can perform SQL operations in both schemas by specifying the connection for each statement:

#sql [myContext1] { SQL statement };
...
#sql [myContext2] { SQL statement };

At the end of the program, we need to commit or roll back any pending changes and close the connection(s) in a FINALLY clause and TRY/CATCH block:

finally
{
   #sql [myContext1] { commit };
   myContext1.close();

   #sql [myContext2] { commit };
   myContext2.close();
}
...

Iterators
In a SQLJ program, the result set returned by a SQL query can be represented as an iterator object that is used to hold the data. An iterator object is an instance of an iterator class, and conceptually is similar to a PL/SQL cursor.

There are five steps you must perform to use an iterator to process rows returned by a SQL query:

1. Declare the iterator class.
   
2. Declare an iterator object from the iterator class.
   
3. Populate the iterator object using a SELECT statement.
   
4. Read the rows from the iterator object.
   
5. Close the iterator object.

There are two types of iterator classes:

• Named iterators, where both the Java variable type and iterator column name have to be specified.
  
  
• Positional iterators, where only Java variable type for column retrieved from the database has to be specified.

Named Iterators: A named iterator declaration specifies both column accessor names and their Java types.

Let's demonstrate all five steps using the example, where you want to retrieve the Ename, Job, and HireDate columns of the Emp table for employees with salaries greater than 1,500.

 Declare the iterator class:

#sql iterator EmpIteratorClass(String Ename, String Job, Timestamp HireDate);

The Java String class is used to represent the Ename and Job columns because it is compatible with the Oracle VARCHAR2 database type. The java.sql.Timestamp type is used for the HireDate column (DATE Oracle type) because the java.sql.Date type can hold only year, date, and time information, not hours, minutes, and seconds like java.sql.Timestamp.

 Declare an iterator object from the iterator class:

EmpIteratorClass empIterator;

 Populate the iterator object using a SQL SELECT statement. The following SQLJ statement populates the empIterator object with the contents of the Ename, Job, and HireDate columns from the Emp table:

int salary = 1500;
#sql empIterator = {
   select Ename, Job, HireDate
      from Emp
      where Sal > :salary
};

We also declared the host variable salary, which is used in the WHERE clause to identify the rows to be retrieved from the Emp table. Keep in mind that the names of the database columns returned by the SQL query have to correspond to the iterator column names defined in Step 1.

 Read the rows from the iterator object. Because the iterator object may contain multiple rows, we need to use a loop to access each row in turn—the same way we read rows from the PL/SQL cursor. The named iterator implements a method called next() that allows you to move through rows of the iterator object. In addition, SQLJ provides accessor methods to retrieve values of iterator columns.

The following code prints the name, job, and hire date in a loop:

while (empIterator.next()) {
   System.out.println("Name: "     + empIterator.Ename());
   System.out.println("Job: "      + empIterator.Job());
   System.out.println("Hire Date:" +
   empIterator.HireDate().toString());
}

 Close the iterator object:

empIterator.close();

Listing 1 combines Steps 2–5 and shows the method listEmployees() with named iterator and empSalary parameter.

Positional Iterators: In contrast to named iterators, positional iterators specify only the number and types of columns, not their names. Column data can be accessed by position only, through the traditional (to PL/SQL) FETCH...INTO syntax. In addition to using the FETCH statement, you also need to use the positional iterator method endFetch() to detect the termination condition and get out of the loop. This condition must always be checked before fetched data is accessed.

Here are the same five steps now using the example of the positional iterator:

 Declare the iterator class:

#sql iterator EmpIteratorClass(String, String, Timestamp);

 Declare an iterator object from the iterator class. Also declare all host variables necessary for data fetching from the iterator object:

EmpIteratorClass empIterator;
String name = null;
String job = null;
Timestamp hireDate = null;

 Populate the iterator object using a SQL SELECT statement:

int salary = 1500;
#sql empIterator = {
   select Ename, Job, HireDate
      from Emp
      where Sal > :salary
};

 Read the rows from the iterator object into host variables:

while (true) {
   #sql { FETCH :empIterator INTO :name, :job, :hireDate };
   if (empIterator.endFetch()) {
      break;
   }

   System.out.println("Name: "     + name);
   System.out.println("Job: "      + job);
   System.out.println("Hire Date:" + hireDate().toString());
}

 Close the iterator object:

empIterator.close();

Listing 2 combines Steps 2–5 and shows the method listEmployees() with positional iterator and empSalary parameter.

As you can see, you navigate through the positional iterator object using syntax very similar to the PL/SQL cursor syntax. Both named and positional iterators perform the same basic function: they hold the results of a SQL query that may return multiple rows. Which type of iterator to use is a matter or personal preference; from the performance point of view, they produce identical results.

Executable Statements
Executable SQLJ statements contain a static SQL operation within curly braces. There are two possible types of executable statements, determined by whether SQL returns a value or not.

Here is an example of an embedded SQL statement that does not return a value; it creates a composite index on the Ename and Sal columns in the Emp table:

#sql { create index EMP_ENAME_SAL on Emp(Ename, Sal) };

If an embedded SQL statement returns a value, you need to use a host variable to specify where the result should be placed. In this example you call the PL/SQL function getSalary, which returns the salary value of employee with Empno = 7900. You can use either the VALUES or SET operator to call a function; that is,

int salary;
int empNo = 7900;
#sql salary = { VALUES getSalary(:empNo} };

or

#sql { SET :salary = getSalary(:empNo} };

Host Expressions
Host variables, as you can see in the previous examples, allow a SQLJ program to exchange information between the database and the Java program. They are any Java variables that are declared in the Java program and referenced inside SQLJ statements. Host variables are embedded into the SQLJ statements with a colon prefix and called host expressions. These bind host variables to the SQLJ executable statement, and they may also include Java array elements, object attributes or Java functions. SQLJ takes care of moving data back and forth between the SQL and Java environments.

All standard JDBC types—such as Boolean, byte, short, int, String, byte[], double, float, java.sql.Date, etc.—are valid host expression types in SQLJ. In addition, Oracle's SQLJ translator supports the use of Oracle types such as ROWID, CLOB, BLOB, as well as Object and REF types.

In this article I talked about all main SQLJ types of objects that are necessary to start writing actual SQLJ code: connection contexts, named and positional iterators, executable statements and host expressions. In the next article, I will write a server-side SQLJ program, compile it, deploy it into Oracle JServer, and compare the performance with a PL/SQL counterpart.

Boris Milrud has 10 years of experience as a software developer. He is a Senior Database Engineer with Callidus Software, Inc. located in San Jose CA. He is specializing in all aspects of Oracle database software development including database design, programming, optimization and tuning. Reach him at milrud@hotmail.com.