SQLJ User Guide and Reference

SQLJ Beta Release 0.7.1.1 is the Reference Implementation of the SQLJ language. This release of SQLJ supports Oracle database releases 7.3.4 and 8.0.4.

A SQLJ program is a Java program containing embedded SQL statements that comply with the SQLJ Language Reference syntax.

Copyright Information.
Introduction. What SQLJ is, where it fits in, and the conditions under which Oracle provides it.
Using SQLJ. Role and general design philosophy of SQLJ.
Getting Started. Instructions for downloading, installing, and testing SQLJ. What you need to provide.
SQLJ Language Reference. The specification is included as part of this distribution.
Using SQLJ. A SQLJ tutorial based on sample programs.
Changes from the Previous Release.
SQLJ Translator Reference. Options for invoking the SQLJ translator.
SQLJ and National Language Character Sets. What is supported.
Frequently Asked Questions about SQLJ.
Error Messages. Causes of errors and corrective actions.
Using SQLJ with Oracle7 and Oracle8. Code samples for use with Oracle databases.

Copyright Information

Introduction

Purpose of SQLJ

Java is a strongly typed object-oriented programming language.

SQL is a non-procedural language for defining and manipulating data in relational databases. SQLJ is a language that embeds static SQL in Java in a way that is compatible with Java's design philosophy.

The SQLJ translator is run before compilation, and is similar to the Oracle precompilers. The translation process replaces SQLJ statements with Java source statements that invoke the JDBC API.

SQLJ does syntax-checking of the embedded SQL, type-checking to assure that the data exchanged between Java and SQL have compatible types and proper type conversions, and schema-checking to assure congruence between SQL constructs and the database schema.

If you wish to comment on any aspect of SQLJ, please send e-mail to:

sqljsup@us.oracle.com

SQLJ Overview

SQLJ is a way to embed SQL in Java programs. SQLJ looks like standard Java with a small number of localized extensions. The SQLJ translator replaces embedded SQL with calls to JDBC, the standard Java application programming interface to databases. A JDBC driver must be installed to provide connectivity to the database.

Thus, the SQLJ distribution consists of a translator (written in Java) and a set of Java classes that implement SQLJ's runtime support.

This figure shows how the pieces fit together:

SQLJ Design Goals

Oracle designed SQLJ with specific goals in mind. The main goal is to provide simple extensions to Java to allow rapid development and easy maintenance of Java applications that use embedded SQL to interact with databases.

Specific goals in support of the main goal are:

Provide a concise, legible mechanism for database access via static SQL.
Most SQL in applications is static. SQLJ provides more concise and less error prone static SQL constructs than dynamic SQL does.
Check static SQL at translate time.
SQLJ can use a database connection at translate-time to check embedded SQL to make sure it is syntactically and semantically correct.
Keep syntax and semantics locations independent. The syntax and semantics of SQLJ do not depend on the configuration under which the SQLJ is running. This makes it possible to implement SQLJ on the client or database side or in the middle tier.
SQLJ is an effort of a group of vendors and will be supported by all of them.
Binary portability. Executables can be used with all of the vendors' DBMS.

Advantages of SQLJ over JDBC for Static SQL

JDBC provides a complete dynamic SQL interface from Java to relational databases. SQLJ fills a complementary role.

JDBC provides fine-grained control of the execution of dynamic SQL from Java, while SQLJ provides a higher level static binding to SQL operations in a specific database schema. Here are some differences:

SQLJ source programs are smaller than equivalent JDBC programs.
SQLJ uses database connections to type-check static SQL code. JDBC, being a completely dynamic API, does not.
SQLJ programs allow direct embedding of Java bind expressions within SQL statements. JDBC requires a separate call statement for each bind variable and specifies the binding by position number.
SQLJ provides strong typing of query outputs and return parameters and allows type-checking on calls. JDBC passes values to and from SQL without compile-time type checking.

Getting Started

Requirements for using SQLJ

To use SQLJ you need

The SQLJ translator, which translates SQLJ programs (file.sqlj) into Java programs (file.java). The translator class files are available in translator.zip.
A Java compiler and execution environment compatible with JDK version 1.1.
The SQLJ runtime classes (available in runtime.zip), consisting of the following packages

  sqlj.runtime 
  sqlj.runtime.ref 
  sqlj.runtime.profile 
  sqlj.runtime.profile.ref
  sqlj.runtime.error.

A JDBC driver implementing package java.sql.

You also need a database system accessible via the JDBC driver.

Note: SQLJ works with generic drivers as well as platform-specific drivers.

Download SQLJ

Obtain the SQLJ distribution from the download area of Oracle's web site:

www.oracle.com

How to Install SQLJ

Uncompress the downloaded file. This creates a hierarchy with a directory called sqlj at the top. Specify the directory you want to contain the sqlj directory as the target for unpacking.
In the following HOME refers to the path to the directory containing the sqlj directory.
Add HOME/sqlj/bin to PATH and HOME/sqlj/lib/translator.zip to CLASSPATH

How to Test SQLJ

Installing creates a number of subdirectories, including one called HOME/sqlj/demo, which contains a set of sample applications to test the installation.

Edit the Files

The applications refer to tables on a database account with user name scott and password tiger. Most Oracle installations have this account. You can substitute other values for scott and tiger in the demo programs.

Each program uses a class called ConnectionManager to initiate a JDBC connection. The ConnectionManager class uses a JDBC driver oracle.jdbc.driver.OracleDriver, and URL jdbc:oracle:oci8:@, specific to Oracle OCI8 JDBC installations. You may need to change these to reflect your database and JDBC driver settings.

In summary, the modifiable ConnectionManager data members defined in the ConnectionManager class are the following:

UID: User name of the account.
PWD: Password of the account.
DRIVER: Name of the JDBC driver.
DBURL: URL of the database.

Edit the file HOME/sqlj/demo/ConnectionManager.java to contain specific values to your JDBC driver and database connection. The driver provider supplies the URL and driver name. Your database administrator supplies your username and password. All of the files described below can be found in HOME/sqlj/demo. The commands assume that HOME/sqlj/demo is the current directory, and that the current directory is in your CLASSPATH.

Create Tables

These tests assume a table called SALES. Create it by executing the command:

CREATE TABLE SALES (
      ITEM_NUMBER NUMBER,
      ITEM_NAME CHAR(30),
      SALES_DATE DATE, 
      COST NUMBER, 
      SALES_REP_NUMBER NUMBER,
      SALES_REP_NAME CHAR(20))

in your database's command line processor.

If your JDBC connection already works, you can compile and execute the Java program TestInstallCreateTable.java to create the table. Edit the ConnectionManager variables as described above before compiling.

Test the JDBC Driver

After creating the table, run TestInstallJDBC.java to verify that the JDBC driver is working. Edit the ConnectionManager variables as described above, then use the following commands to compile and run the program:

javac TestInstallJDBC.java
java  TestInstallJDBC

If your JDBC driver is working correctly, the program prints:

Hello, Database

Test the SQLJ Installation

Now run TestInstallSQLJ.sqlj, a SQLJ program that behaves like TestInstallJDBC. Use the following commands to compile and run the program:

sqlj  TestInstallSQLJ.sqlj
javac TestInstallSQLJ.java
java  TestInstallSQLJ

If the test works correctly, it prints:

Hello, SQLJ!

Test SQLJ Translator Connection to the Database

If the translator is able to connect to a database it can provide translate-time checking of your SQL operations. The SQLJ translator is written in Java and gets information it needs from the database using JDBC. You provide the connection parameters to SQLJ via a property file, sqlj.properties, or via command line options.

Copy the files sqlj.properties and TestInstallSQLJChecker.sqlj into your workspace. Edit the following property file lines to reflect your database connection information, as with the ConnectionManager class.

sqlj.url=jdbc:oracle:oci8:@
sqlj.driver=oracle.jdbc.driver.OracleDriver
sqlj.password=tiger

Translate-time checking is enabled by specifying a username for the database connection. The username is specified either by uncommenting the sqlj.user line in the properties files, or by the -user command-line option. Test translate-time checking by translating the file TestInstallSQLJChecker.sqlj as follows (substituting your username in place of scott):

sqlj -user=scott TestInstallSQLJChecker.sqlj

This produces a number of informational messages, as well as the following error message when you use the Oracle-specific JDBC driver:

TestInstallSQLJChecker.sqlj:41: Unable to check SQL query. Error returned by 
database is: ORA-00904: invalid column name

Edit the file TestInstallSQLJChecker.sqlj to fix the error on line 41. The column name should be ITEM_NAME instead of ITEM_NAMAE. Once fixed, the file can be retranslated without error, and run using the following commands:

sqlj -user=scott TestInstallSQLJChecker.sqlj
javac TestInstallSQLJChecker.java
java  TestInstallSQLJChecker

If everything works, this prints:

Hello, SQLJ Checker!

Using SQLJ

This section presents increasingly complex annotated examples. They illustrate the essentials of using SQLJ.

Before beginning, connect to the database following the procedures described in "Getting Started". Then execute the following SQL statement:

CREATE TABLE SALES (
      ITEM_NUMBER NUMBER,
      ITEM_NAME CHAR(30),
      SALES_DATE DATE, 
      COST NUMBER, 
      SALES_REP_NUMBER NUMBER,
      SALES_REP_NAME CHAR(20))

IterDemo1.sqlj

The first example (found in the directory demo) illustrates the use of named iterators.

An iterator is a Java object that contains a result set of rows returned by a query. There are two kinds: named iterators and positional iterators. See the SQLJ Language Reference for a complete discussion of iterators.

The example also illustrates a standard pattern for SQLJ programs.

// -------------------- Begin of file IterDemo1.sqlj -----------------------
//
// Invoke the SQLJ translator with the following command:
//    sqlj IterDemo1.sqlj


/* Import useful classes.
**
** Note that java.sql.Date (and not java.util.Date) is being used.
*/

import java.sql.Date;
import java.sql.SQLException;


/* Declare an iterator.
**
** This is an example of an iterator-class declaration (SalesRec) that binds by
** name.
** Column item_number has a corresponing accessor-method (item_number) in
** the object SalesRec, which has a return value of int.
*/

#sql iterator SalesRecs(
      int item_number,
      String item_name,
      Date sales_date,
      double cost,
      Integer sales_rep_number,
      String sales_rep_name );


class IterDemo1
{

  public static void main( String args[] )
  {
    IterDemo1 app = new IterDemo1();

    try
    {
      app.runExample();
    } 
    catch( SQLException exception )
    {
      System.err.println( "Error running the example: " + exception );
    }
  }


  /* Initialize database connection.
  **
  ** Before any #sql blocks can be executed, a connection to a database
  ** must be established.  The constructor of the application class is a
  ** convenient place to do this, since it is executed only once in this
  ** application.
  */

  IterDemo1()
  {
    ConnectionManager.initContext();
  }


  void runExample() throws SQLException
  {
    System.out.println();
    System.out.println( "Running the example." );
    System.out.println();


    /* Reset the database for the demo application.
    */

    #sql { DELETE FROM SALES };


    /* Insert a row into the cleared table.
    */

    #sql
    {
      INSERT INTO SALES VALUES(
            101,'Relativistic redshift recorder',
            TO_DATE('22-OCT-1997','dd-mon-yyyy'),
            10999.95,
            1,'John Smith')
    };


    /* Insert another row in the table using bind variables.
    */

    int     itemID = 1001;
    String  itemName = "Left-handed hammer";
    double  totalCost = 79.99;

    Integer salesRepID = new Integer(358);
    String  salesRepName = "Jouni Seppanen";
    Date    dateSold = new Date(97,11,6);

    #sql { INSERT INTO SALES VALUES( :itemID,:itemName,:dateSold,:totalCost,
          :salesRepID,:salesRepName) };


    /* Instantiate and initialize the iterator.
    **
    ** The iterator object is initialized using the result of a query.
    ** The query creates a new instance of the iterator and stores it in
    ** the variable 'sales' of type 'SalesRecs'.  SQLJ translator has
    ** automatically declared the iterator so that it has methods for
    ** accessing the rows and columns of the result set.
    */

    SalesRecs sales;

    #sql sales = { SELECT item_number,item_name,sales_date,cost,
          sales_rep_number,sales_rep_name FROM sales };


    /* Print the result using the iterator.
    **
    ** Note how the next row is accessed using method 'next()', and how
    ** the columns can be accessed with methods that are named after the
    ** actual database column names.
    */

    while( sales.next() )
    {
      System.out.println( "ITEM ID: " + sales.item_number() );
      System.out.println( "ITEM NAME: " + sales.item_name() );
      System.out.println( "COST: " + sales.cost() );
      System.out.println( "SALES DATE: " + sales.sales_date() );
      System.out.println( "SALES REP ID: " + sales.sales_rep_number() );
      System.out.println( "SALES REP NAME: " + sales.sales_rep_name() );
      System.out.println();
    }


    /* Close the iterator.
    **
    ** Iterators should be closed when you no longer need them.
    */

    sales.close() ;
  }

}

IterDemo2.sqlj

The following example program from directory demo illustrates positional iterators (see discussion in the SQLJ Language Reference).

// ---------------------- Begin of file IterDemo2.sqlj ---------------------
//
// Invoke the SQLJ translator as follows:
//
// sqlj IterDemo2.sqlj
//

import java.sql.* ;

/* Declare a ConnectionContext class named DemoCtx. Instances of this 
   class can be used to specify where SQL operations should execute. */
#sql context DemoCtx;

/* Declare a positional iterator class named FetchSalesIter.*/
#sql iterator FetchSalesIter (int, String, Date, double);

class IterDemo2 {

  private DemoCtx ctx = null;  // holds the database connection info

  /* The constructor sets up a database connection. */
  public IterDemo2() {
    try 
    {  
      ctx = new DemoCtx(ConnectionManager.newConnection());
    } 
    catch (Exception exception) 
    {	System.err.println (
	"Error setting up database connection: " + exception);
    }
   }

  //Main method
  public static void main (String args[])
  {
    IterDemo2 iterDemo2 = new IterDemo2();

    try 
    { 
      //Run the example
      iterDemo2.runExample() ; 

      //Close the connection
      iterDemo2.ctx.close() ;
    } 
    catch (SQLException exception)
    { System.err.println (
      "Error running the example: " + exception ) ;
    }
  } //End of method main


  //Method that runs the example
  void runExample() throws SQLException
  {

    /* Reset the database for the demo application.    */
    #sql [ctx] { DELETE FROM SALES };

    insertSalesRecord
    ( 250, "widget1", new Date(97, 9, 9), 12.00, 
      new Integer(218), "John Doe"
    ) ;
    
    insertSalesRecord
    ( 267, "thing1", new Date(97, 9, 10), 700.00, 
      new Integer(218), "John Doe"
    ) ;
   
    insertSalesRecord
    ( 270, "widget2", new Date(97, 9, 10), 13.00, 
      null, "Jane Doe" // Note: Java null is same as SQL null
    ) ;
    
    System.out.println("Sales records before delete") ;
    printRecords(fetchSales()) ;

    // Now delete some sales records
    Date delete_date;
    #sql [ctx] { SELECT MAX(sales_date) INTO :delete_date
			 FROM SALES };

    #sql [ctx] { DELETE FROM SALES WHERE sales_date = :delete_date };

    System.out.println("Sales records after delete") ;
    printRecords(fetchSales()) ;
	   
  } //End of method runExample


  //Method to select all records from SALES through a positional iterator
  FetchSalesIter fetchSales() throws SQLException {
    FetchSalesIter f;

    #sql [ctx]  f = { SELECT item_number, item_name, sales_date, cost
		      FROM sales };
    return f;
  }

  //Method to print rows using a FetchSalesIter
  void printRecords(FetchSalesIter salesIter) throws SQLException
  {
    int item_number = 0; 
    String item_name = null;
    Date sales_date = null;
    double cost = 0.0;

    while (true) 
    {
      #sql { FETCH :salesIter 
             INTO :item_number, :item_name, :sales_date, :cost 
           };
      if (salesIter.endFetch()) break;

      System.out.println("ITEM NUMBER: " + item_number) ;
      System.out.println("ITEM NAME: " + item_name) ;
      System.out.println("SALES DATE: " + sales_date) ;
      System.out.println("COST: " + cost) ;
      System.out.println() ;
    }
    
    //Close the iterator since we are done with it.
    salesIter.close() ;

  } //End of method runExample


  //Method to insert one row into the database
  void insertSalesRecord(
    int item_number,
    String item_name,
    Date sales_date,
    double cost,
    Integer sales_rep_number,
    String sales_rep_name)

  throws SQLException
  {
    #sql [ctx] {INSERT INTO SALES VALUES 
                (:item_number, :item_name, :sales_date, :cost,
                 :sales_rep_number, :sales_rep_name
                )
    } ;
  } //End of method insertSalesRecord

} //End of class IterDemo2

//End of file IterDemo2.sqlj

Advanced SQLJ Features

The tutorial thus far illustrates the features of the SQLJ language and runtime package needed to write simple SQLJ programs. The sections that follow discuss more advanced programming techniques including multiple connections, dynamic SQL and multi-threading.