2 Introduction to SQLJ

SQLJ Translator

This component is a precompiler that you run after creating SQLJ source code.

The translator, which is written in pure Java, supports a programming syntax that enables you to embed SQL statements in SQLJ executable statements. SQLJ executable statements and SQLJ declarations are preceded by the #sql token and can be interspersed with Java statements in a SQLJ source code file. SQLJ source code file names must have the .sqlj extension. The following is a sample SQLJ statement:

#sql { INSERT INTO emp (ename, sal) VALUES ('Joe', 43000) };

The translator produces a .java file.

You can invoke the translator using the sqlj command-line utility. On the command line, specify the files that need to be translated and any desired SQLJ option settings.

SQLJ Run Time

This component is also written in pure Java and is invoked automatically each time you run a SQLJ application.

Oracle JDBC calls are generated directly into the translated code and the SQLJ run time plays a much smaller role.

SQLJ Type Extensions

The Oracle SQLJ implementation supports the following Java types as extensions to the SQLJ standard:

• Instances of oracle.sql.* classes as wrappers for SQL data.

  See Also:

  "Support for JDBC 2.0 LOB Types and Oracle Type Extensions"

• Custom Java classes, typically produced by the JPublisher utility to correspond to SQL objects, object references, and collections. For example, classes that implement the oracle.sql.ORAData interface or the JDBC standard java.sql.SQLdata interface.

  Note:

  The SQLData interface is standard. Classes that implement it are supported by JDBC drivers and databases of other vendors.

  See Also:

  "Custom Java Classes"

• Stream instances: BinaryStream and CharacterStream, the latter of which replaces the deprecated AsciiStream and UnicodeStream, used as output parameters.

  See Also:

  "Support for Streams"

• Iterator and result set instances as input or output parameters. The SQLJ standard specifies them only in result expressions or cast statements.

  See Also:

  "Using Iterators and Result Sets as Host Variables" and "Using Iterators and Result Sets as Stored Function Returns"

• Unicode character types: NString, NCHAR, NCLOB, and NcharCharacterStream, the latter of which replaces the deprecated NcharAsciiStream and NcharUnicodeStream.

  See Also:

  "SQLJ Extended Globalization Support"

Using any of these extensions requires Oracle-specific code generation or Oracle customization during translation, as well as Oracle SQLJ run time and an Oracle JDBC driver when your application runs. Do not use these or other types if you want to use your code in other environments. To ensure that your application is portable, use the SQLJ -warn=portable flag.

SQLJ Functionality Extensions

The Oracle SQLJ implementation also supports the following extended functionality:

• Oracle-specific code generation

  This generates JDBC code directly. Much of the SQLJ run-time functionality is bypassed during program execution.

  See Also:

  "Oracle-Specific Code Generation (No Profiles)"

• Dynamic SQL in SQLJ statements

  See Also:

  "Support for Dynamic SQL"

• Scrollable result set iterators with additional navigation methods, and FETCH syntax from result set iterators and scrollable result set iterators

  See Also:

  "Scrollable Iterators"

• Optimization flags for column and parameter size definitions

  See Also:

  "Column Definitions", "Parameter Size Definitions", and "Options for Code Generation, Optimizations, and CHAR Comparisons"

• Flags for modified translator behavior, such as for binding host expressions by identifier or accounting for blank padding in CHAR comparisons for WHERE clauses

  See Also:

  "Binding Host Expressions by Identifier (-bind-by-identifier)" and "CHAR Comparisons with Blank Padding (-fixedchar)"

• SQLJ statement caching on connection contexts

  See Also:

  "Statement Caching"

SQLJ Translation Steps

After you have written your .sqlj file, you must run SQLJ to process the files. The following example shows SQLJ being run in its simplest form with no command-line options for the Foo.sqlj source file with the public class Foo:

% sqlj Foo.sqlj

This command runs a front-end script or utility depending on the platform. The script or utility reads the command line, invokes a Java virtual machine (JVM), and passes arguments to it. The JVM invokes the SQLJ translator and acts as a front end.

Figure 2-1 Flow of Control

Description of "Figure 2-1 Flow of Control"

The following sequence of events occurs, presuming each step completes without error:

1. The JVM invokes the SQLJ translator.

2. The translator parses the SQLJ and Java code in the .sqlj file, checking for proper SQLJ syntax and looking for type mismatches between the declared SQL data types and corresponding Java host variables. Host variables are Java local variables that are used as input or output parameters in SQL operations.

   See Also:

   "Java Host, Context, and Result Expressions"

3. Depending on the SQLJ option settings, the translator invokes the online semantics-checker, the offline parser, neither, or both. This is to verify syntax of embedded SQL and PL/SQL statements and to check the use of database elements in the code against an appropriate database schema, for online checking. Even when neither is specified, some basic level of checking is performed.

   When online checking is specified, SQLJ will connect to a specified database schema to verify that the database supports all the database tables, stored procedures, and SQL syntax that the application uses. It also verifies that the host variable types in the SQLJ application are compatible with data types of corresponding database columns.

4. For Oracle-specific SQLJ code generation (-codegen=oracle, which is default), SQL operations are converted directly into Oracle JDBC calls.

   See Also:

   "Oracle-Specific Code Generation (No Profiles)"

   Generated Java code is put into a .java output file containing the following:

   • Any class definitions and Java code from the .sqlj source file

   • Class definitions created as a result of the SQLJ iterator and connection context declarations

     See Also:

     "Overview of SQLJ Declarations"

   • Calls to Oracle JDBC drivers to implement the actions of the embedded SQL operations

5. The JVM invokes the Java compiler, which is usually, but not necessarily, the standard javac provided with the Sun Microsystems JDK.

6. The compiler compiles the Java source file generated in Step 4 and produces Java .class files as appropriate. This will include a .class file for each class that is defined, each of the SQLJ declarations.

General SQLJ Notes

Consider the following when translating and running SQLJ applications:

• It is also possible to specify existing .java files on the command line to be compiled and to be available for type resolution as well.

  See Also:

  "Translator Command Line and Properties Files"

• Your application requires an Oracle JDBC driver when it runs, even if your code does not use Oracle-specific features.

Summary of Translator Input and Output

This section summarizes what the SQLJ translator takes as input, what it produces as output, and where it places its output. This section covers the following topics:

• Translator Input

• Translator Output

• Output File Locations

MyClass.sqlj

SQLJ Run-Time Processing

This section discusses run-time processing during program execution.

When you translate with the default -codegen=oracle setting, your program performs the following at run time:

• Executes Oracle-specific application programming interfaces (APIs) that ensure batching support and proper creation and closing of Oracle JDBC statements

• Directly calls Oracle JDBC APIs for registering, passing, and retrieving parameters and result sets

SQLJ Version of the Sample Code

The SQLJ version of the sample code that defines methods to retrieve an employee's address from the database, update the address, and return it to the database is as follows:

import java.sql.*;

/**
  This is what you have to do in SQLJ
  **/
public class SimpleDemoSQLJ                                  // line 6
{
  //TO DO: make a main that calls this

  public Address getEmployeeAddress(int empno)              // line 10
    throws SQLException
  {
    Address addr;                                           // line 13
    #sql { SELECT office_addr INTO :addr FROM employees
           WHERE empnumber = :empno };
    return addr;
  }
                                                            // line 18
  public Address updateAddress(Address addr)
    throws SQLException
  {
    #sql addr = { VALUES(UPDATE_ADDRESS(:addr)) };          // line 22
    return addr;
  }
}

Line 10

The getEmployeeAddress() method does not require an explicit Connection object. SQLJ can use a default connection context instance, which should be initialized somewhere earlier in the application.

Lines 13-15

The getEmployeeAddress() method retrieves an employee address according to the employee number. Use standard SQLJ SELECT INTO syntax to select an employee's address from the employee table if the employee number matches the one (empno) passed in to getEmployeeAddress(). This requires a declaration of the Address object (addr) that will receive the data. The empno and addr variables are used as input host variables.

Line 16

The getEmployeeAddress() method returns the addr object.

Line 19

The updateAddress() method also uses the default connection context instance.

Lines 19-22

The address is passed to the updateAddress() method, which passes it to the database. The database updates the address and passes it back. The actual updating of the address is performed by the UPDATE_ADDRESS() stored function. Use standard SQLJ function-call syntax to receive the addr address object returned by UPDATE_ADDRESS().

Line 23

The updateAddress() method returns the addr object.

Specific Features of the SQLJ Version of the Code

Note the following features of the SQLJ version of the sample code:

• An explicit connection is not required. SQLJ can use a default connection context that has been initialized previously in the application.

• No data type casting is required.

• SQLJ does not require knowledge of _SQL_TYPECODE, _SQL_NAME, or factories.

• NULL value data is processed implicitly.

• No explicit code for resource management (for example, closing statements or results sets) is required.

• SQLJ embeds host variables, in contrast to JDBC, which uses parameter markers.

• String concatenation for long SQL statements is not required.

• You do not have to register output parameters.

• SQLJ syntax is simpler. For example, SELECT INTO statements are supported and ODBC-style escapes are not used.

• You do not have to implement your own statement cache. By default, SQLJ will automatically cache #sql statements. This results in improved performance, for example, if you repeatedly call getEmployeeAddress() and updateAddress().

JDBC Version of the Sample Code

If you are familiar with JDBC, then you can check the following the JDBC version of the sample code, which defines methods to retrieve an employee's address from the database, update the address, and return it to the database.

import java.sql.*;
import oracle.jdbc.*;

/**
  This is what you have to do in JDBC
  **/
public class SimpleDemoJDBC                                  // line 7
{

//TO DO: make a main that calls this

  public Address getEmployeeAddress(int empno, Connection conn)
    throws SQLException                                     // line 13
  {
    Address addr;
    PreparedStatement pstmt =                               // line 16
      conn.prepareStatement("SELECT office_addr FROM employees" + 
       " WHERE empnumber = ?");
    pstmt.setInt(1, empno);
    OracleResultSet rs = (OracleResultSet)pstmt.executeQuery();
    rs.next();                                              // line 21
     //TO DO: what if false (result set contains no data)?
    addr = (Address)rs.getORAData(1, Address.getORADataFactory());
    //TO DO: what if additional rows? 
    rs.close();                                             // line 25
    pstmt.close();
    return addr;                                            // line 27
  }
  public Address updateAddress(Address addr, Connection conn)
    throws SQLException                                     // line 30
                                                           
  {
    OracleCallableStatement cstmt = (OracleCallableStatement)
      conn.prepareCall("{ ? = call UPDATE_ADDRESS(?) }");   //line 34
    cstmt.registerOutParameter(1, Address._SQL_TYPECODE, Address._SQL_NAME);
                                                            // line 36
    if (addr == null) {
      cstmt.setNull(2, Address._SQL_TYPECODE, Address._SQL_NAME);
    } else {
      cstmt.setORAData(2, addr);
    } 
    
    cstmt.executeUpdate();                                  // line 43
    addr = (Address)cstmt.getORAData(1, Address.getORADataFactory());
    cstmt.close();                                          // line 45
    return addr;
  }
}

Running SQLJ in Applets

Because the SQLJ run time is pure Java, you can use SQLJ source code in applets as well as applications. However, there are a few considerations.

This section covers the following topics:

• General Development and Deployment Considerations

• General End User Considerations

• Java Environment and the Java Plug-In

Overview of SQLJ in the Server

In addition to its use in client applications, SQLJ code can run within a target Oracle Database in stored procedures, stored functions, or triggers. Server-side access occurs through an Oracle JDBC driver that runs inside the server itself. Additionally, Oracle Database 11g (and preceding versions) has an embedded SQLJ translator so that SQLJ source files for server-side use can optionally be translated directly in the server.

The two main areas to consider are the following:

• Creating SQLJ code for use within the server

  Coding a SQLJ application for use within the target Oracle Database is similar to coding for client-side use. The issues that exist are due to general JDBC characteristics, as opposed to SQLJ-specific characteristics. The main differences involve connections:

  • You have only one connection.

  • The connection is to the database in which the code is running.

  • The connection is implicit (does not have to be explicitly initialized, unlike on a client).

  • The connection cannot be closed. Any attempt to close it will be ignored.

  Additionally, the JDBC server-side driver used for connections within the server does not support auto-commit mode.

  Note:

  There is also a server-side Thin driver for connecting to one server from code that runs in another. This case is effectively the same as using a Thin driver from a client and is coded in the same way. Refer "Overview of Oracle JDBC Drivers" for further information.

• Translating and loading SQLJ code for server-side use

  You can translate and compile your code either on a client or in the server. If you do this on a client, then you can load the class and resource files into the server from your client, either by pushing them from the client using the Oracle loadjava utility or pulling them in from the server using SQL commands.

  Alternatively, you can translate and load in one step using the embedded server-side SQLJ translator. If you load a SQLJ source file instead of class or resource files, then translation and compilation are done automatically. In general, loadjava or SQL commands can be used for class and resource files or for source files. From a user perspective, .sqlj files are treated the same as .java files, with translation taking place implicitly.

  See Also:

  "Loading SQLJ Source Code into the Server" for information about using the embedded server-side translator

  Note:

  The server-side translator does not support the SQLJ -codegen option and generates Oracle-specific code. To use ISO SQLJ standard code in the server, you must translate on a client and load the individual components into the server. Also note restrictions on interoperability when running code generated with different settings. For more information, refer to "Translating SQLJ Source on a Client and Loading Components" and "Oracle-Specific Code Generation (No Profiles)".

SQLJ Globalization Support

Support for native languages and character encodings by the Oracle SQLJ implementation is based on Java built-in globalization support capabilities.

The standard user.language and file.encoding properties of the JVM determine appropriate language and encoding for translator and run-time messages. The SQLJ -encoding option determines encoding for interpreting and generating source files during translation.

SQLJ in Oracle JDeveloper 10g and Other IDEs

The Oracle SQLJ implementation includes a programmatic API so that it can be embedded in IDEs, such as Oracle JDeveloper 10g. The IDE takes on a role similar to that of the front-end sqlj script, invoking the translator, semantics-checker, compiler, and customizer (as applicable).

JDeveloper is a Jave-based, cross-platform visual development environment for Java programming. The JDeveloper Suite enables developers to build multitier, scalable Internet applications using Java across the Oracle Internet Platform. The core product of the suite, the JDeveloper IDE, excels in creating, debugging, and deploying component-based applications.

Oracle JDBC OCI and Thin drivers are included with JDeveloper. The compilation functionality of JDeveloper includes an integrated SQLJ translator so that your SQLJ application is translated automatically as it is compiled.

Information about JDeveloper is available at the following URL:

http://www.oracle.com/technology/products/jdev/index.html

Windows Considerations

Note the following if you are using a Microsoft Windows environment instead of a UNIX environment:

• This manual uses UNIX syntax. Use platform-specific file names and directory separators, such as "\" on Microsoft Windows, that are appropriate for your platform, because your JVM expects file names and paths in the platform-specific format. This is true even if you are using a shell, such as ksh, that permits a different file name syntax.

• For UNIX, the Oracle SQLJ implementation provides a front-end script, sqlj, that you use to invoke the SQLJ translator. On Microsoft Windows, Oracle instead provides an executable file, sqlj.exe. Using a script is not feasible on Microsoft Windows because .bat files on these platforms do not support embedded equals signs (=) in arguments, string operations on arguments, or wildcard characters in file name arguments.

• How to set environment variables is specific to the operating system. There may also be OS-specific restrictions. In Windows 95, use the Environment tab in the System control panel. Additionally, because Windows 95 does not support the "=" character in variable settings, SQLJ supports the use of "#" instead of "=" in setting SQLJ_OPTIONS, an environment variable that SQLJ can use for option settings. Consult your operating system documentation regarding settings and syntax for environment variables, and be aware of any size limitations.

• As with any operating system and environment you use, be aware of specific limitations. In particular, the complete, expanded SQLJ command line must not exceed the maximum command-line size, which is 250 characters for Windows 95 and 4000 characters for Windows NT. Consult your operating system documentation.

Refer to the release notes for Windows for additional information.