It began sometime in late '96 or early '97 ­ JDK 1.0 still ruled and Tandem was still called Tandem, not Digital or Compaq ­ when people from IBM, Tandem and Oracle met and started to muse.

"Wouldn't it be nice to have SQL embedded in Java just as it's embedded in other host languages? However, we don't just want to copy previous efforts but to do justice to the Java language." Of course, the embedding would have to permit the use of compiled SQL statements and be just as portable as Java code. It would also need to provide the easiest, most robust way to write SQL code in Java. Sun, Sybase and Informix soon joined the fray....and the "JSQL" effort was born.

"Gotcha," you're thinking. "You meant to say SQLJ, didn't you?" Well, yes and no ­ hold off just a second. The JSQL enterprise was all about Java programs that call SQL. When the same informal intercompany working group also embarked on an effort to describe the implementation of SQL stored procedures and functions in Java (christened SQLJ, since it kind of goes in the opposite direction ­ SQL procedures that "call" Java in their body), this became known as SQLJ part 1. And when they started yet another project to describe how an SQL database could store Java objects in table columns and also publish them as SQL types, this was called SQLJ part 2. When the time came to submit JSQL to the ANSI standards committee, it turned out that the name was already a registered trademark of Caribou Lake Software for their JDBC drivers. So what did that bewildered bunch of computer scientists do when they realized they'd goofed? The same thing they've done since the dawn of the computer age: they started counting from zero! Thus SQLJ part 0 was born.

On the other hand, the ANSI people don't refer to it as part 0; in fact, they don't even refer to it as SQLJ: to them it's SQL Part 10: Object Language Bindings. ANSI put its imprimatur on SQLJ part 0 around the end of 1998. Since then, SQLJ has been winding its merry way through the International Standards Organization, picking up a bunch of JDBC 2.0 features along the ride.

But enough history; let's get back to serious business. In this article I'm going to cover the following ground:

• A reprise of SQLJ iterators: all about positional iterators
• Calling stored procedures and functions in the database
• Reflections on bridging the gap between SQL types and Java types

Get Into Position!
SQLJ provides two flavors of iterators. Last month we looked at named iterators, in which you specify both the Java column types and the column name. Remember that the name also appears as the accessor function with which you retrieve the column value. This kind of iterator is most "Java-ish," and JDBC programmers immediately feel familiar with it.

Today I'll be taking a closer look at positional iterators. They're characterized by the order and by the Java types of their columns. Positional iterators require neither the next() method nor the accessors of the named iterator. They use a FETCH statement to advance to the next row and retrieve the column values into a list of variables all at once. Each variable in the INTO clause corresponds to exactly one column in the SELECT list in the same order. This will look familiar if you're used to other languages with embedded SQL.

#sql { FETCH :p INTO :name, :salary };

Declarations for positional iterator types are even simpler than for the named variety.

#sql iterator PosIter (String, Double);

In the processing loop for a positional iterator, you issue FETCH statements to retrieve the next row of data into host variables. After a FETCH, the endFetch() call returns true if the FETCH was successful and false if there was no row left that could be fetched. Also remember to call close() on any iterator ­ named or positioned ­ once you're done using it or you'll find yourself running out of database resources. The following example uses a positional iterator:

String name = null;
Double salary = null;
PosIter p;
#sql p = { SELECT ename, sal FROM emp };

while (true) {
#sql { FETCH :p INTO :name, :salary };
if (p.endFetch()) break;
System.out.println(name + " would like to make " + (salary * 2));
}
p.close();

Tip: Even though it might look somewhat unusual, you should always employ the following template when using positional iterators:

Let's Get Results ­ Functions First
We've already seen how results can be received from an SQL statement when we used the SELECT-INTO statement. More often, results from an SQL operation are returned through an SQLJ assignment statement. Let's look at a call to an SQL function SYSDATE():

java.sql.Date today;
#sql today = { VALUES( SYSDATE() ) };
System.out.println("The database thinks that today is "+today);

The VALUES( ... ) syntax is SQLJ-specific syntax for calling a stored function. Such functions might also take arguments, as in the following code snippet in which Next_Paycheck is an SQL stored function that returns the date of the next paycheck on or after a given date:

String moreMoney;
#sql moreMoney = { VALUES( Next_Paycheck(:today) ) };

(Note that we can receive an SQL DATE value in different formats in Java ­ in our examples, as a java.sql.Date and as a java.lang.String.)

Are We Outmoded Yet? ­ Getting Into Procedures
In the foregoing discussion we glossed over the fact that host variables or expressions are used in different modes:

• IN: The value of the expression is sent to the database.
• OUT: The expression denotes a location and receives a value from the database.
• INOUT: All of the above.

Host expressions, by default, have the mode IN ­ with the exception of host expressions in INTO-lists and the return value of a stored function call, which have the mode OUT. In all other cases you have to explicitly prefix the host expression with the mode. SQL stored procedures can have parameters with all three modes. The SQLJ syntax for calling a stored procedure is illustrated in the following code fragment:

int x = 10;
int y;
int z = 20;
#sql { CALL Toutes_Les_Modes( :x, :OUT y, :INOUT z ) };

Tip: You must add OUT or INOUT modes to all host expressions in procedure arguments that don't have the mode IN. Otherwise you won't see any values returned from the database in these positions. A good way to ensure that you've specified all the required modes is to run the SQLJ translator with online checking.

What Type Are You?
So far, we've used a bunch of Java types in our SQLJ programs without having a clue which types are permitted and how they're used. SQLJ includes all of the JDBC types with some additional twists. Following is a list of JDBC-supported Java types and how they're used in SQLJ. Please see the sidebar ("Coming Soon: Scrollable Iterators") for JDBC 2.0-specific type support.

• Numeric types: This includes: int, Integer, long, Long, short, Short, byte, Byte, boolean, Boolean, double, Double, float, Float and ­ just to prove I don't stutter ­ java.math.BigDecimal. So what's the deal with supporting both the primitive type (such as int, or double) and the corresponding Java object type (such as Integer, or Double)? In SQLJ the SQL NULL value always maps to Java null ­ and vice versa. Thus, if you retrieve an SQL NULL value into an Integer, you receive a Java null, but if you try to read it into an int, you'll only get an sqlj.runtime.SQLNullException, which is a subclass of SQLException.

• Character types: The Java type String represents these very well, thank you. Note that the Java char and Character types aren't supported by SQLJ or by JDBC (besides, they could only hold a single character, anyway). Also useful are the character streams sqlj.runtime.AsciiStream and sqlj.runtime.UnicodeStream. One peculiarity about SQL is that columns defined as SQL CHAR type are automatically blank-padded. If you don't get the same string back that you inserted into the database, or if SQL comparisons with a given Java string fail mysteriously, you should check the SQL type used in your table.
• Date and time types: These include java.sql.Time, java.sql.Timestamp and java.sql.Date. Yes, that's java.sql.Date and not java.util.Date ­ don't confuse the two!
• Raw types: Raw data can be represented as byte[], aka "byte-array," or ­ in stream form ­ as sqlj.runtime.BinaryStream, discussed next.
• Stream types: SQLJ provides new stream types sqlj.runtime.BinaryStream, sqlj.runtime.AsciiStream and sqlj.runtime.UnicodeStream for wrapping a LONG (or LONG RAW) column in the database. These three stream types implement a java.io.InputStream. When you retrieve a LONG column, data in the same row prior to that column may be lost by some JDBC drivers (such as Oracle's). This imposes limitations on positional iterators (at most, one stream column is permitted, and it must also be the last column of the iterator) and it requires extra care when using named iterators (columns must be accessed in SELECT sequence). You could use byte arrays or Strings to circumvent these problems.

Exercise: Investigate conversions between Java types and SQL types:

1. Take a positional iterator that contains a String column and an int column. What happens if you flip the corresponding host variables in the FETCH statement?
2. What happens if you flip the corresponding columns in the SELECT statement?

SET :x = 2 + 2

Against an Oracle database, however, this would have to be written as follows.

BEGIN :OUT x := 2 + 2; END;

The Oracle customizer (see Figure 1) takes an existing serialized profile and adds Oracle-specific information to it ­ in this case the new SQL text. At runtime the actual database connection is used to determine which vendor's customization/runtime pair becomes activated. If no customization exists for a given connection, SQLJ reverts to using the standard JDBC API.

Okay, let's call it a day. Next time we'll cover (almost) everything else there is to know about SQLJ. I'll teach you some neat translator tricks for the command line. You'll also be initiated into the mysteries of execution contexts and connection contexts. Finally, we'll examine how JDBC and SQLJ can live in blissful harmony happily ever after. In the meantime, keep your feedback, your answers to exercises and your questions coming!