SQLJ User Guide and Reference

Contents

This document accompanies the SQLJ Reference Implementation Release 1.0.1.1 .
A SQLJ program is a Java program containing embedded SQL statements that comply with the SQLJ Language Reference syntax.

Copyright Information

This document Copyright © 1997-1998 by Oracle Corporation. All rights reserved.
Legal disclaimer for the associated software,

SQLJ Reference Implementation Release 1.0.1.1:
Oracle expressly places this Software in the public domain. However, Oracle grants no other rights, whether express or implied, to users of this Software, and Oracle provides this Software on an "AS-IS" basis, exclusive of any warranty including, without limitation, all warranties of merchantability or fitness for a particular purpose, noninfringement or any other warranty, whether expressed or implied. You use this Software at your own risk and agree that Oracle shall not be liable for any damages, including but not limited to, direct, indirect, incidental, special, cover, reliance, or consequential damages, arising from your use of the Software.

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

SQLJ is designed 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:
Most SQL in applications is static. SQLJ provides more concise and less error prone static SQL constructs than dynamic SQL does.
SQLJ can use a database connection at translate-time to check embedded SQL to make sure it is syntactically and semantically correct.

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:

Getting Started

Requirements for Using SQLJ

To use SQLJ you need
sqlj.runtime
sqlj.runtime.ref
sqlj.runtime.profile
sqlj.runtime.profile.ref

sqlj.runtime.error.

You also need a database system accessible via the JDBC driver.
Note: SQLJ works with generic drivers as well as platform-specific drivers.

How to Install SQLJ

In the following HOME refers to the path to the directory containing the sqlj directory.

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:
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, or by running the TestInstallCreateTable.java program.  Note that the TestInstallCreateTableJava program also uses the class defined in ConnectionManager.java, so you'll have to edit it first, as described above.

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!

Notes on Using SQLJ

Local Variable Names
The SQLJ translator replaces each occurrence of an executable SQL statement whose syntax is #sql { SQL operation } ; with a statement block. A number of temporary variable declarations may be used within the generated statement block. The name of any such temporary will include the prefix __sJT . The following declarations are examples of those which might occur in a SQLJ-generated statement block.
int __sJTindex;
Object __sJTkey;
java.sql.PreparedStatement __sJTstmt;

The string "__sJT" is a reserved prefix for SQLJ-generated variable names. SQLJ programmers may not use this string as a prefix for

Class and Resource File Names
For each file translated by SQLJ, a number of internal classes and resource files may be generated as part of the translation. The name of every such class and resource file has a prefix composed of the name of the original input file followed by the string "_SJ" .
Internal Classes
SQLJ internal classes are classes created for internal use by generated code. SQLJ internal classes are not directly referenced by the SQLJ programmer, as opposed to SQLJ client-declared classes which are explicitly named and declared by the client using the SQLJ class declaration constructs # sql context and # sql iterator.
As described above, the name of a SQLJ internal class includes a prefix that is the name of the original input file followed by the string "_SJ" . All generated classes will appear in the same package as is declared in the original input file. For example, suppose the input file was named Bar.sqlj and declares that it uses package COM.foo . Examples of fully qualified names of internal classes that might be declared as a result of translating Bar.sqlj are COM.foo.Bar_SJInternalClass, COM.foo.Bar_SJProfileKeys , and COM.foo.Bar_SJInternalClass$Inner . Note that declared classes may themselves declare inner classes.
SQLJ internal classes may appear at the end of the translated input file, or may appear in a new Java file created during translation. In the case of newly created Java files, the filename will be the same as the short name of the generated internal class, and will have the java extension. Examples of filenames associated with the aforementioned fully qualified class names are BAR_SJInternalClass.java and Bar_SJProfileKeys.java . Note that the inner class COM.foo.Bar_SJInternalClass$Inner would appear as an inner class within the file BAR_SJInternalClass.java . Generated files for internal classes are created in the same directory as that of the translated input file.
SQLJ programmers may not declare classes with names that may conflict with SQLJ internal classes. In particular, a client-declared top-level class may not use a name of the form a_SJb (where a and b are legal Java identifiers) if a is the name of an existing class included in the SQLJ application. Moreover, filenames included in the application may not conflict with names of files that may be generated by SQLJ. As with Java source files, SQLJ source files are named for the class declared within that source file. Accordingly, restrictions for naming files are similar to those of naming classes.
Resource Files and Profiles
In addition to generating internal classes, a SQLJ translator may also generate a number of resource files. Resource files store information used by SQLJ generated code that is not conveniently represented as a Java class. As with internal classes, generated resource files are not meant to be used directly by the SQLJ programmer.
A profile is a specific kind of resource file used by SQLJ to store information about the SQL operations found in the input file. A profile is generated for each connection context used to perform SQL operations in the input file, and describes those operations performed on the context. For example, if the input file performs SQL operations on two different connection contexts, then two different profiles will be generated as resource files.
Profile names are generated using the same rules as those for generated internal class names; profile names are fully qualified with the package name followed by the input file name followed by the string "_SJ" . Profile names always end with the string "Profile" followed by a number which uniquely identifies the profile. Using again the example of the input file Bar.sqlj, some possible generated profile names are COM.foo.Bar_SJProfile0 and COM.foo.Bar_SJProfile1 . Profile names associated with a particular input file are always numbered consecutively starting at 0.
Physically, a profile exists as a Java serialized object contained within a resource file. Resource files containing profiles are named for the base name of the profile and use the ser extension. Resource files for the two previously mentioned profiles would be named Bar_SJProfile0.ser and Bar_SJProfile1.ser . In general, resource file names follow the same naming conventions as those of generated internal classes; they start with the name of the original input file followed by the string "_SJ" and are completed by some identifying suffix. Resource files are always created in the same directory as that of the translated input file.

The utility profp comes with the SQLJ binary distribution. Use it to print out the contents of a profile file:

profp myFile_JDProfile0.ser

The programming restrictions described for avoiding conflicts with generated internal classes apply for avoiding conflicts with generated resource files as well. No further restrictions are needed.

Overview of SQLJ Command Line and Properties Files

This section discusses general command-line syntax for the script sqlj and lists all of the options available. It then discusses SQLJ properties files, which can be used instead of the command line to set most options. For detailed information about settings for the basic options, see the "Basic SQLJ Options" section later in this chapter. For information about more advanced options, see the "Advanced SQLJ Options" section.
The sqlj script invokes a Java VM, which acts as the SQLJ front end, and passes it the class name of the SQLJ translator ( sqlj.tools.Sqlj ). After that, the VM, or SQLJ front end, invokes the translator and handles things like parsing the command line and properties files. For simplicity, running the script is referred to as "running SQLJ" and its command line is referred to as the "SQLJ command line."
Following is the general syntax for the command line:
sqlj optionlist filelist

The option list is a list of SQLJ option settings separated by spaces. There are also prefixes to mark options to be passed to other executables.

The file list is the list of files, separated by spaces, to be processed by the SQLJ translator (can be .sqlj , .java , .ser , or .jar , as explained in the "Command-Line Operations" section below). The * wildcard entry can be used in filenames. For example, foo*.sqlj would find foo1.sqlj , foo2.sqlj, and foobar.sqlj .

Do NOT include .class files in the file list. But DO be sure that your CLASSPATH is set so that the SQLJ translator can find any .class files it will need for type resolution of variables in your SQLJ source files.

SQLJ Options, Flags, and Prefixes

The table below lists the available options for the SQLJ translator. Boolean options are referred to as flags . Also listed are prefixes , used to pass options to the Java VM, which the script invokes, and to the Java compiler and SQLJ profile customizer, which the Java VM invokes.
Use an equals sign (=), to specify option and flag settings, although for simplicity you do not have to specify =true to turn on a flag--simply typing the flag name in the command line will suffice. You do, however, have to specify =false to turn a flag off--a flag would not toggle from its previous value. For example:
-linemap=true or just - linemap to enable line-mapping
-linemap=false to disable line-mapping


 
Notes
  • The names of command-line options, including options passed elsewhere, are case-sensitive and usually all lowercase. Option values are usually case-sensitive as well. 
  • Most SQLJ options can also be set in a properties file . For more information see the "Properties Files for Option Settings" section later in this chapter. For information about priority of command-line options as opposed to properties file options, see the "Order of Precedence of Option Settings" section.
  • If the same option appears more than once on the command line (or in the properties file), the last value is used.
  • In this document, boolean flags are typically discussed as being true or false , but they can also be enabled/disabled by setting them to yes / no , on / off , 1 / 0 .

Basic flags and options are discussed in the "Basic SQLJ Command-Line Options" section later in this chapter. Advanced flags and options, as well as prefixes, are discussed in the "Advanced SQLJ Command-Line Options" section.

For an example and discussion of command-line syntax and operations, see the "Command-Line Syntax and Operations" section below.


 
SQLJ Command-Line Options
Option
Description
Default
Usage Level
-C
Prefix that marks options to be passed to Java compiler
n/a
Advanced
-cache
Flag for whether to cache online semantics checking results (to reduce trips to database)
false
Advanced
-compile
Flag to run the Java compilation step (for .java files generated during current SQLJ run, or previously generated .java files specified in the command line)
true
Advanced
-compiler-executable
Option to specify the Java compiler to use
javac
Advanced
-compiler-encoding-flag
Flag to tell SQLJ whether or not to pass the -encoding setting (if that option is set) to the Java compiler
true
Advanced
-compiler-output-file
Option to specify a file to which the Java compiler output should be written
(* There is no default value, but if this option is not set, SQLJ assumes that compiler output goes to standard output.)
none
Advanced
-compiler-pipe-output-flag Option to tell the Java compiler whether to write its message output to STDOUT or STDERR false Advanced
-d
Option to set output directory for .ser files generated by SQLJ and .class files generated by compiler
empty (use directory of .sqlj input file)
Basic
-default-customizer
Option to specify the profile customizer to use; specify class name 
none (no customizaion)
Advanced
-default-url-prefix Option to set the default URL Prefix none Basic
-dir
Option to set output directory for SQLJ-generated .java files.
empty (use directory of .sqlj input file)
Basic
-driver
Option to specify JDBC drivers that will be registered; specify a class name or comma-separated list of class names ( OracleDriver is in package oracle.jdbc.driver )
OracleDriver
Basic
-encoding
Option to specify NLS encoding to use.  Can be abbreviated to 'e'
VM file.encoding setting
Basic
-help
Flag to display a brief summary of the most commonly-used SQLJ options.  can be abbreviated to 'h'
not enabled
Basic
-help-alias Flag to display command-line aliases or abbreviations not enabled Basic
-help-long Flag to display all SQLJ option names, descriptions, and current values not enabled Basic
-J
Prefix that marks options to be passed to the Java VM 
n/a
Advanced
-linemap
Flag to enable mapping of line numbers between generated Java source code and original SQLJ code
false
Basic
-offline
Option to specify offline checker to use for semantics checking; specify a class name 
sqlj.semantics.OfflineChecker
Advanced
-online
Option to specify online checker to use for semantics checking; specify a class name 
sqlj.sqemantics.JdbcChecker
Advanced
-P
Prefix that marks options to be passed to SQLJ profile customizer
n/a
Advanced
-password
Option to set user password for database connection for online semantics checking
none
Basic
-profile
Flag to run the profile customization step (for .ser files generated during current SQLJ run; does not apply to previously generated .ser files specified on the command line)
true
Advanced
-props
Option to specify properties file (alternative to command line for setting options)
sqlj.properties
Basic
-ser2class
Flag to specify that SQLJ should translate generated .ser files to .class files
false
Advanced
-status
Flag requesting SQLJ to display status messages as it runs.  Can be abbreviated to 'v'.
false
Basic
-url
Option to set database URL for connection for online semantics checking
jdbc:oracle:oci8:@
Basic
-user
Option to enable online semantics-checking and set user ID (and optionally schema) for database connection.  Can be abbreviated to 'u'
none (no online semantics-checking)
Basic
-version
Flag to display SQLJ version number
not enabled
Basic
-vm
Option to specify Java VM to use
java
Advanced
-warn
Comma-separated list of flags to enable or disable various SQLJ warnings--individual flags are precision / noprecision , nulls / nonulls , portable / noportable , strict / nostrict , and verbose / noverbose ; global flag is all / none
precision

nulls 
portable 
strict 
noverbose

Basic

Command-Line Syntax and Operations

The general sequence of events triggered by running the script sqlj was already discussed earlier in this chapter in the "Steps and Process in Translating a SQLJ Application" section. Now some operational details can be added to that discussion as part of this overview of the command line.
Use of Command Line Arguments
When the sqlj script invokes a Java VM it passes all of its command-line arguments to the VM, which later passes them elsewhere (such as to the Java compiler or profile customizer) as appropriate.
Arguments from the option list
Option list arguments are used as follows.
There are also two SQLJ options which have the same name as Java compiler options and, if specified, are automatically passed to the Java compiler as well as being used by SQLJ:
Therefore do not use the -C prefix to specify the -d or -encoding compiler options. Note that this also means that SQLJ and the compiler use the same settings for -d and -encoding .
Any profile customizaton other than what SQLJ performs automatically is considered an advanced feature and is covered in the "User Profiles and Customization" section.
Arguments from the file list
The SQLJ front end parses the file list, processes wildcard characters, and expands file names. By default, files will then be processed as follows:
Note that you may specify .sqlj files together with .java files on the command line, or you may specify .ser files together with .jar files, but do not mix the two categories.
If you have .sqlj files and .java files with interdependencies (each requires access to code in the others), they must all be entered on the command-line for a single execution of SQLJ. (They cannot be processed in separate executions of SQLJ, because then SQLJ would be unable to resolve all of the types.)
(See the "Profile Customization" section later in this chapter for information about how .jar files are handled.)
Command Line Example and Results
Following is a sample command line:
sqlj -J-Duser.language=ja -warn=none -J-prof -encoding=SJIS *bar.sqlj foo*.java
The sqlj script will invoke a Java VM, pass it the class name of the SQLJ translator, and pass it all of the command-line arguments. Options for the Java VM, as designated by -J , are passed to the VM ahead of the translator class file name (just as you would type Java options prior to typing the class file name if you were invoking Java by hand).

After these steps are completed, the results are equivalent to the user having typed the following (presuming sushibar.sqlj , bar.sqlj , fooBar.java , and fooBaz.java were all in the current directory):

java -Duser.language.ja -prof sqlj.tools.Sqlj -warn=none -encoding=SJIS sushibar.sqlj bar.sqlj fooBar.java fooBaz.java

(The above is all one line; it just wraps around.)

For more information about how Java VM options are handled, see the "Options to Pass to the Java VM (-J)" section.

Properties Files for Option Settings

Properties files can be used instead of the command line to supply options to the SQLJ translator, Java compiler, or SQLJ profile customizer.
Properties files can also be used to supply options to the Java VM via the Java compiler, where those VM options would only apply to the running of the compiler. Such options are passed to the VM at the time the compiler is run (after the SQLJ translation step).
Properties files cannot be used to supply options directly to the Java VM, since the properties files are read after the VM is invoked. This applies to the -J prefix and the - vm option.
Properties File Syntax
Option settings in a properties file are placed one per line. Lines with SQLJ options, compiler options, and customizer options can be interspersed. (They are parsed by the SQLJ front end and handled appropriately.)
Syntax for the various kinds of options is as follows:
sqlj.warn=none
compile.verbose
(The Java compiler verbose option outputs status messages during the compile.)
profile.backup
(The profile customizer backup option saves a copy of the previous profile.)
Any profile customizaton other than what SQLJ performs automatically is considered an advanced feature and is covered in the "User Profiles and Customization" section.
# Comment line.
Following are sample properties file entries:
# Set user and JDBC driver
sqlj.user=scott

sqlj.driver=oracle.jdbc.driver.OracleDriver

# Turn on the compiler verbose option

compile.verbose

These entries would be equivalent to having the following on the SQLJ command line:

sqlj -user=scott -driver=oracle.jdbc.driver.OracleDriver -C-verbose


 
Notes
  • SQLJ processes the options in a properties file in order from first to last. A later entry overrides an earlier entry.
  • For information about priority of properties file option settings as opposed to command-line option settings, see the "Order of Precedence of Option Settings" section below.

Default Properties Files
If no properties file is specified in the SQLJ command line, the SQLJ front end looks for files named sqlj.properties to use instead. It looks for them in three places in the following order:
Java home directory, if it exists
  1. user home directory, if it exists
  2. current directory
It processes each such file it finds, overriding previously set options as it encounters new ones. Thus, options set in the sqlj.properties file in the current directory override those set in the sqlj.properties file in the user home directory or Java home directory.

Options, Flags, and Prefixes that Cannot Be Used in a Properties File

The following options, flags, and prefixes cannot be used in a properties file; they must be set on the SQLJ command line:

Order of Precedence of Option Settings

SQLJ takes option settings in the following order. At each step, it overrides any previous settings for any given option.
Sets options to default settings (where applicable).
  1. Looks for a sqlj.properties file in the Java home directory; if one is found, options are set as specified there.
  2. Looks for a sqlj.properties file in the user home directory; if one is found, options are set as specified there.
  3. Looks for a sqlj.properties file in the current directory; if one is found, options are set as specified there.
  4. Looks for option settings on the command-line; options are set as specified there. In the process of this, it looks in any file specified by the -props option, and sets options as specified there.
 
Notes
  • In sqlj.properties files, SQLJ reads option settings from top to bottom, with later entries taking precedence over earlier entries.
  • If there is a properties file specified by the -props option in the command line, SQLJ reads the file from top to bottom, and essentially inserts the file's option settings into the position where the -props option was specified, with the topmost option in the file being the leftmost of these options in the command line.
  • Options in the command line, with options from a -props file inserted, are read in order from left to right. Any later (righthand) setting takes precedence over earlier (lefthand) settings.
Example
Presume SQLJ is run as follows:
sqlj -user=scott -props=myprops.properties -dir=/home/java
And presume the file myprops.properties is in the current directory and contains the following entries:
sqlj.user=tony

sqlj.dir=/home/myjava

These entries are essentially inserted into the command line where the -props option was specified, and then SQLJ reads the option settings from left to right. Therefore the tony entry takes precedence over the scott entry for the user option, but the /home/java entry takes precedence over the /home/myjava entry for the dir option.

Basic SQLJ Options

This section documents the syntax and functionality of the basic flags and options you can specify on the SQLJ command line. These options will allow you to run in a fairly standard mode of operation. For options that can also be specified in a properties file (such as sqlj.properties ), that syntax is noted as well (see the "Properties Files for Option Settings" section for more information).
More advanced command-line flags and options are discussed in the "Advanced SQLJ Command-Line Options" section.

Options for Command Line Only

The following options can only be specified on the SQLJ command line, not in properties files, and are described in this section:
Input Properties File (-props)
The -props option specifies a properties file from which SQLJ can read option settings (an alternative to specifying option settings on the command line).
See the "Properties Files for Option Settings" section earlier in this chapter for information about the format of these files, the details of how they are used in relation to command-line options, and where SQLJ looks for default properties files.
Command-line syntax
-props= filename
Command-line example
-props=myprops.properties
Properties file syntax
n/a
Properties file example
n/a
Default value
sqlj.properties
SQLJ Option Information (-help)
The -help flag instructs SQLJ to display a a brief list of the most commonly-used commands.  Unlike other flags (but like the -version flag), use this flag by just typing -help on the command line, as opposed to setting it to true or false .
No translation is done when you use the -help option, even if you include file names and other options on the command line. It is assumed that you either want to run the translator or you want help, but not both.
 
Note
This list is also automatically displayed if you run SQLJ without specifying any files to process.
Abbreviation
-h
Command-line syntax
sqlj -h/-help
Command-line example
sqlj -help
sqlj -h
Properties file syntax
n/a
Properties file example
n/a
Default value
off
SQLJ Option Information (-help-alias)
The -help-alias flag instructs SQLJ to display a list of which commands can be abbreviated using the SQLJ command- line utility.  These abbreviations aren't avaiable to programs which call SQLJ programmatically (via an IDE, for example).  Unlike other flags (but like the -version flag), use this flag by just typing -help-alias on the command line, as opposed to setting it to true or false .
No translation is done when you use the -help-alias option, even if you include file names and other options on the command line. It is assumed that you either want to run the translator or you want help, but not both.
Command-line syntax
sqlj -help-alias
Command-line example
sqlj -help-alias
Properties file syntax
n/a
Properties file example
n/a
Default value
off
SQLJ Option Information (-help-long)
The -help-long flag instructs SQLJ to display a list of command- line option information, including the following for each option:
Unlike other flags (but like the -version flag), use this flag by just typing -help on the command line, as opposed to setting it to true or false .
No translation is done when you use the -help-long option, even if you include file names and other options on the command line. It is assumed that you either want to run the translator or you want help, but not both. It might be useful, however, to include other option settings on the command line with a -help-long option, especially with complex options (like -warn ) or combinations of options, so you can see what option settings resulted from your actions. (Remember that -help-long shows current settings of all options.)
Command-line syntax
sqlj -help-long
Command-line example
sqlj -help-long
Properties file syntax
n/a
Properties file example
n/a
Default value
off
SQLJ Version Number (-version)
Displays the version number of your version of SQLJ.
Unlike other flags (but like the -help flag), use this flag by just typing -version on the command line, as opposed to setting it to true or false . .
sqlj -version

No translation is done when you use the -version option, even if you include file names and other options on the command line. It is assumed that you either want to run the translator or you want version information, but not both. Properties files and anything else you type on the command line are ignored.

Command-line syntax
sqlj -version
Command-line example
sqlj -version
Properties file syntax
n/a
Properties file example
n/a
Default value
off

Options for Output Files and Directories

The following option specifies encoding for SQLJ input and output source files:
The following options specify where SQLJ output files are placed:
All three options are described in this section.

Encoding for Input and Output Source Files (-encoding)

The -encoding option specifies NLS encoding to be applied to .sqlj and .java input files and .java generated files.
When this option is specified it is also passed to the Java compiler (unless the -compiler-encoding-flag is off), which uses it to specify encoding for .java files processed by the compiler. When the option is passed, the equals sign is stripped out and replaced by a space in order for the syntax to be accepted by the Java compiler. For example, consider the following SQLJ option:
-encoding=SJIS

This is passed to the Java compiler as:

-encoding SJIS

Abbreviation
-e
Command-line example
-encoding=SJIS
-e=SJIS
Properties file syntax
sqlj.encoding= Java_character_encoding >
Properties file example
sqlj.encoding=SJIS
sqlj.e=SJIS
Default value
setting in Java VM system property file.encoding
Output Directory for Generated .ser Files (-d)
The -d option specifies the root output directory for .ser files (serialized object files) generated by the SQLJ translator, and is also passed to the Java compiler to specify the root output directory for .class files generated by the compiler.
Whenever a directory is specified, the output files are generated under this directory according to the package name, if applicable. For example, if you have source files in a package a.b.c and specify directory /mydir , output files will be placed in directory /mydir/a/b/c .
If you specify a relative directory path, this would be from your current directory.
A simple example is as follows, which will make /root the root directory for generated .ser files:

-d=/root

If your current directory is /root/home/mydir and you set the -d option to the relative directory path mysubdir/myothersubdir as follows, /root/home/mydir/mysubdir/myothersubdir will be the root directory for generated .ser files:

-d=mysubdir/myothersubdir

You can also use standard syntax such as a period for the current directory or two periods to go up a level (the second example immediately below will go up a level, then back down to a parallel directory called paralleldir ):

-d=.

-d=../paralleldir

If the -d option is not specified, then files are generated under the same directory as the original .sqlj source file (NOT under the current directory).

If you specifically want to set the output directory to be the same as your .sqlj source directory (perhaps overriding other -d settings, such as in properties files), you can use the -d option as follows:

-d=

When the -d option is specified it is also automatically passed to the Java compiler, which uses it to specify the root directory for .class files generated by the compiler. When the option is passed, the equals sign is stripped out and replaced by a space in order for the syntax to be accepted by the compiler. For example, consider the following SQLJ option:

-d=/mydir

This is passed to the compiler as:

-d /mydir


 
Note
Throughout this discussion the forward-slash (/) was used as the file separator. Be aware, however, that in specifying this or similar options you must actually use the file separator of your operating system, as specified in the file.separator system property of your Java VM.

Command-line syntax
-d= directory_path
Command-line example
-d=/topleveldir/mydir
Properties file syntax
sqlj.d= directory_path
Properties file example
sqlj.d=/topleveldir/mydir
Default value
empty (i.e., use directory of .sqlj source file)
Output Directory for Generated .java Files (-dir)
The -dir option specifies the root directory for .java files generated by the SQLJ translator.
Whenever a directory is specified, the output files are generated under this directory according to the package name, if applicable. For example, if you have source files in a package a.b.c and specify directory /mydir , output files will be placed in directory /mydir/a/b/c .
If you specify a relative directory path, this would be from your current directory.
A simple example is as follows, which will make /root the root directory for generated .java files:

-dir=/root

If your current directory is /root/home/mydir and you set the -dir option to the relative directory path mysubdir/myothersubdir as follows, /root/home/mydir/mysubdir/myothersubdir will be the root directory for generated .java files:

-dir=mysubdir/myothersubdir

You can also use standard syntax such as a period for the current directory or two periods to go up a level (the second example immediately below will go up a level, then back down to a parallel directory called paralleldir ):

-dir=.

-dir=../paralleldir

If the -dir option is not specified, then files are generated under the same directory as the original .sqlj source file (NOT under the current directory).

If you specifically want to set the output directory to be the same as your .sqlj source directory (perhaps overriding other -dir settings, such as in properties files), you can use the -dir option as follows:

-dir=


 
Note
Throughout this discussion the forward-slash (/) was used as the file separator. Be aware, however, that in specifying this or similar options you must actually use the file separator of your operating system, as specified in the file.separator system property of your Java VM.

Command-line syntax
-dir= directory_path
Command-line example
-dir=/topleveldir/mydir
Properties file syntax
sqlj.dir= directory_path
Properties file example
sqlj.dir=/topleveldir/mydir
Default value
empty (i.e., use directory of .sqlj source file)

Connection Options (Basic Features)

The following options are used for the database connection for online semantics-checking and are described in this section:
There is no requirement that the SQLJ translator connect to the same database or as the same user as the application itself does at runtime. The connection information in application source code can be independent of the connection information provided in the SQLJ options.
A situation where you would likely want to use a different connection for translation than for runtime is if you are developing in a different environment than the one to which you will deploy.
The subsections that follow discuss basic features of the connection options, and this discussion assumes that only the default connection context is being used. More advanced features, involving use of non-default or multiple connection contexts, are discussed in the "Connection Options (Advanced Features)" section.
Online Semantics-Checking and Username (-user)--Basic Features
The -user option enables online semantics-checking and specifies the username (schema name) for the database connection to be used for the checking.
Note that there is no other flag to enable or disable online semantics-checking--SQLJ enables it or disables it according to the presence or absence of the -user option.
This section discusses basic features of the -user option, and this discussion assumes that only the default connection context is being used. More advanced features, involving use of non-default or multiple connection contexts, are discussed in the "Online Semantics-Checking and Username (-user)--Advanced Features" section.
The most basic usage of the -user option specifies just a username for the default connection context, as follows:

-user=scott

You can also specify the password along with the username, separating the two with a forward-slash:

-user=scott/tiger

(Otherwise the password can be specified interactively or by using the -password option, documented below.)

You can disable online semantics-checking by setting the -user option to an empty value:

-user=

This would be useful, for example, if you have online checking enabled in a properties file but want to override that on the command line, or have it enabled in the default properties file but want to override that in a user-specified properties file (specified using the -props option).

There is also a special username, URL.CONNECT , which does not require a password. It connects to the database specified in the -url option.

-user=URL.CONNECT


 
Notes
It is actually true that if you are using multiple connection contexts, the -user option settings shown above would apply not only to the default connection context, but also to any connection context that does not have a specific setting. See the discussion in the "Advanced Features" chapter.

Command-line abbreviation
-u
Command-line syntax
-user= username < /password >
Basic command-line examples
-user=scott
-user=scott/tiger
-user=
-user=URL.CONNECT
-u=scott
-u=scott/tiger
-u=
-u=URL.CONNECT
Basic properties file syntax
sqlj.user= username < /password >
Basic properties file examples
sqlj.user=scott
sqlj.user=scott/tiger
sqlj.user=
sqlj.user=URL.CONNECT
sqlj.u=scott
sqlj.u=scott/tiger
sqlj.u=
sqlj.u=URL.CONNECT
Default value
none (no online semantics-checking)
User Password for Online Semantics-Checking (-password)--Basic Features
The -password option specifies the user password for the database connection to be used for online semantics-checking. It is used in conjunction with the -user option, which enables online checking and specifies the username for the connection.
This section discusses basic features of the -password option, and this discussion assumes that only the default connection context is being used. More advanced features, involving use of non-default or multiple connection contexts, are discussed in the "User Password for Online Semantics-Checking (-password)--Advanced Features" section)
Specify a password as follows:
-password=tiger

You can also specify the password as part of the -user option instead of using the -password option. (See the "Online Semantics-Checking and Username (-user)--Basic Features" section above for information.)

If you enable online checking without specifying a password, either from the -password option or the -user option, SQLJ will ask for it interactively (unless the username is URL.CONNECT , as discussed in the -user option section above).

If you specifically want to override any other settings of the default context -password option (in a properties file, for example) and be prompted interactively, you can set an empty password:

-password=

If you actually want to use an empty password to log in, specify the following:

-password=EMPTY.PASSWORD


 
Note
It is actually true that if you are using multiple connection contexts, the -password option settings shown above would apply not only to the default connection context, but also to any connection context that does not have a specific setting. See the discussion in the "Advanced Features" chapter.

Basic command-line syntax
-password= user _ password
Basic command-line example
-password=tiger
Basic properties file syntax
sqlj.password= user _ password
Basic properties file examples
sqlj.password=tiger
Default value
none
Connection URL for Online Semantics-Checking (-url)--Basic Features
The -url option specifies a JDBC URL for establishing a database connection for online semantics-checking.
This section discusses basic features of the -url option, and this discussion assumes that only the default connection context is being used. More advanced features, involving use of non-default or multiple connection contexts, are discussed in the "Connection URL for Online Semantics-Checking (-url)--Advanced Features" section)
As necessary, the URL can include a host name, port number, and Oracle database SID.
Simple URL:

-url=jdbc:oracle:oci8:@

URL with host name, port number, and SID:

-url=jdbc:oracle:thin:@hostname:1521:orcl


 
Note
It is actually true that if you are using multiple connection contexts, the -url option settings shown above would apply not only to the default connection context, but also to any connection context that does not have a specific setting. See the discussion in the "Advanced Features" chapter.

Basic command-line syntax
-url= JDBC_url
Basic command-line examples
-url= jdbc:oracle:oci8:@
-url= jdbc:oracle:thin:@hostname:1521:orcl
Basic properties file syntax
sqlj.url= JDBC_url
Basic properties file examples
sqlj.url= jdbc:oracle:oci8:@
sqlj.url= jdbc:oracle:thin:@hostname:1521:orcl
Default value
jdbc:oracle:oci8:@
JDBC Drivers to Register for Online Semantics-Checking (-default-url-prefix)
Sets the default URL prefix.  This value is prepended to any URL that doesn't start with "jdbc:".  In order to disable this functionality, just set -default-url-prefix to the empty string.
 
Command-line syntax
-default-url-prefix=<new value>
Command-line example
-default-url-prefix=jdbc:oracle:oci8:
Properties file syntax
sqlj.default-url-prefix=<new value>
Properties file example
sqlj.default-url-prefix=jdbc:oracle:oci8:
Default value
jdbc:oracle:thin:
JDBC Drivers to Register for Online Semantics-Checking (-driver)
The -driver option specifies the JDBC drivers that will be registered in order to interpret JDBC connection URLs for online semantics-checking. Specify a driver class or comma-separated list of classes.
The default, OracleDriver , supports the Oracle OCI 8, Thin, and KPRB (server-side) JDBC drivers for use with Oracle databases.
Command-line syntax
-driver= driver1< , driver2 , driver3 ,...>
Command-line examples
-driver=oracle.jdbc.driver.OracleDriver
-driver=oracle.jdbc.driver.OracleDriver,sun.jdbc.odbc.JdbcOdbcDriver
Properties file syntax
sqlj.driver= driver1< , driver2 , driver3 ,...>
Properties file examples
sqlj.driver=oracle.jdbc.driver.OracleDriver
sqlj.driver=oracle.jdbc.driver.OracleDriver,sun.jdbc.odbc.JdbcOdbcDriver
Default value
oracle.jdbc.driver.OracleDriver

Reporting and Line-Mapping Options

The following options instruct SQLJ about what kinds of conditions to monitor and whether or not to generate real-time error and status messages:
The following option enables line-mapping from the .java output file back to the .sqlj source file, so that you can trace compilation and execution errors back to the appropriate location in your original source code.
All three options are described in this section.
Translator Warnings (-warn)
There are various warnings and informational messages that the SQLJ translator can display as dictated by conditions it encounters during the translation. The -warn option consists of a set of flags that specify which of those warnings and messages should be displayed (in other words, which conditions should be monitored and which should be ignored).
All of the flags for this option must be combined into a single, comma-separated string.
The table below documents the conditions that can be tested, what a true flag value means, what the true and false flag values are for each condition, and which value is the default.

 
Tests and Flags for SQLJ Warnings
Tests and Flag Functions
TRUE/FALSE Values
Data precision test--Enabling precision warns if there was a possible loss of precision when moving values from database columns to Java host variables.
precision (default)
noprecision
Conversion loss test for nullable data--Enabling nulls warns if there was possible conversion loss when moving nullable columns or nullable Java types from database columns to Java host variables.
nulls (default)
nonulls
Portability test--Enabling portable checks SQLJ clauses for portability and warns if there are non-portable clauses. (Where non-portable refers to the use of extensions to the SQLJ standard, such as vendor-specific types or features.)
portable (default)
noportable
Strict matching test for named iterators--Enabling strict instructs SQLJ to strictly match named iterators against the columns returned by the database, and issue a warning for any column in the iterator that is not matched by a column in the database cursor. 
strict (default)
nostrict
Translation-time informational messages--Enabling verbose provides additional informational messages about the translation process (such as what database connections were made for online checking).
verbose
noverbose (default)
Enable or disable all warnings.
all
none
The verbose/noverbose flag works a little differently than the others. It does not enable a particular test, but enables general informational messages to be provided about the semantics check.


 
Note
Do not confuse -warn=verbose with the -status flag. The -status flag provides real-time informational messages about all aspects of SQLJ translation--translation, semantics-checking, compilation, and profile customization. The -warn=verbose flag simply results in additional reporting after the translation itself and about the translation phase only.

The global all / none flag takes priority over default settings. You can use it to enable or disable all flags, or to serve as an initialization to make sure all flags are off before you turn selected flags on, or vice versa. Essentially, all is equivalent to precision, nulls, portable, strict, verbose and none is equivalent to noprecision, nonulls, noportable, nostrict, noverbose . There is no default for all / none ; there are only defaults for individual flags.

For example, use the following sequence to make sure only the nulls flag is on:

-warn=none,nulls

And the following sequence would have the same result, since the verbose setting would be overridden:

-warn=verbose,none,nulls

Or use the following to make sure everything except the portability flag is on:

-warn=all,noportable

And the following sequence would have the same result, since the nonulls setting would be overridden:

-warn=nonulls,all,noportable

Other than placement of the all / none flag, the order in which flags appear in a -warn setting is unimportant. (But in the case of conflicting settings, such as -warn=portable,nulls,noportable , the last setting would take precedence.)

Separate settings of the -warn option in properties files and on the command line are NOT cumulative. Only the last setting is processed. In the following example, the -warn=portable setting is ignored; that flag and all other flags besides nulls/nonulls are set according to their defaults:

-warn=portable -warn=nonulls


 
Note
The precision, nullability, and strictness tests are part of online semantics-checking and require a database connection.

Command-line syntax
-warn= comma-separated _list_of_flags
Command-line example
-warn=none,nulls,precision
Properties file syntax
sqlj.warn= comma-separated _list_of_flags
Properties file example
sqlj.warn=none,nulls,precision
Default values
precision,nulls,noportable,strict,noverbose
Real-Time Status Messages (-status)
The -status flag instructs SQLJ to immediately report errors and output additional status messages throughout all aspects of the SQLJ process--translation, semantics-checking, compilation, and customization. Messages are output as each file is processed, and at each stage of SQLJ operation.
Command-line abbreviation
-v
Command-line syntax
-status= true/false
Command-line example
-status=true
Properties file syntax
sqlj.status= true/false
Properties file example
sqlj.status=false
Default values
false
Line-Mapping to SQLJ Source File (-linemap)
The -linemap flag instructs SQLJ to map line numbers from a SQLJ source code file to locations in the corresponding .class file. (This would be the .class file created during compilation of the .java file that was generated by the SQLJ translator from your .sqlj file.) As a result of this, when Java runtime errors occur, the line number reported by the Java VM is the line number in the SQLJ source code, making it much easier to debug.
Normally, the codings in a .class file map to source code lines in the corresponding .java file. This would be of limited use to SQLJ developers, though, as they would still need to map line numbers in the generated .java file to line numbers in their original .sqlj file.
The SQLJ translator modifies the .class file to implement the -linemap option, replacing line numbers and file name from the generated .java file with corresponding line numbers and file name from the original .sqlj file. This process is known as instrumenting the class file .
In order to do this, SQLJ takes the following into account:

 
Notes
  • A current limitationof the -linemap option is that the name of the class being instrumented must be the same as the basename of the .sqlj source file. For example, if your source file is foo.sqlj , it must include a class named foo , which is the class that will be instrumented. If there is no class foo in sqlj.foo , then no instrumentation will be done.
  • If you are processing a .sqlj file and the compilation step is skipped due to error, then no line-mapping can be done either, since no .class file is available for mapping. 
  • You can, however, enable line-mapping while compilation is disabled ( -compile=false ) if there is a previously existing .class file that corresponds to the current version of your source file. This is true both in the case where you are processing a .sqlj file on the command line and in the case where you are processing a .java file on the command line. (In fact, line-mapping is the only operation performed on a .java file if -compile=false .)
  • When the Java compiler is invoked from SQLJ (as is typical), it always reports compilation errors in terms of line numbers of the original .sqlj source file, not of the generated .java file.
  • It is automatically true that the Java compiler, when reporting errors while compiling a .java file that was generated by SQLJ, will report line numbers from the original .sqlj file, not from the generated .java file. No option needs to be set for this mapping.

Command-line syntax
-linemap= true/false
Command-line example
-linemap=true
Properties file syntax
sqlj.linemap= true/false
Properties file example
sqlj.linemap=false
Default value
false

Advanced SQLJ Options

This section documents the syntax and functionality of the advanced flags and options you can specify on the SQLJ command line, as well as prefixes used to pass options to the Java VM, Java compiler, or SQLJ profile customizer. These options will allow you to exercise any of the specialized features of Oracle SQLJ. For options that can also be specified in a properties file (such as sqlj.properties ), that syntax is noted as well (see the "Properties Files for Command Line Options" section in Chapter 3, "Basic Features" for more information).
More basic command line flags and options are discussed in the "Basic SQLJ Command Line Options" section in Chapter 3, "Basic Features."

Prefixes that Pass Option Settings to Other Executables

The following flags mark options to be passed to the Java interpreter, Java compiler, and SQLJ profile customizer and are described in this section (this is also discussed in the "Overview of SQLJ Command Line Syntax" section):
Options to Pass to Java VM (-J)
The -J prefix marks options to be passed to the Java VM from which SQLJ was invoked. This prefix immediately precedes a Java VM option, with no spaces in between. After stripping off the -J prefix, the sqlj script passes the Java option to the VM.
For example:
-J-Duser.language=ja

After stripping the -J prefix, the sqlj script passes the -Duser.language argument as is to the VM. In the JDK, the flag -Duser.language.ja sets the system property user.language to the value ja (Japanese). But specific flags are dependent on the actual Java executable you are using and are not interpreted or acted upon by the sqlj script in any way.

You cannot pass options to the Java VM from a properties file, since properties files are read after the VM is invoked.


 
Note
While there is no way to use a properties file to pass options directly to the Java VM in which the SQLJ translator runs, it is possible to use a properties file to pass options to the VM in which the Java compiler runs, in situations where the compiler is running in a separate VM. See the "Options to Pass to Java Compiler (-C)" section below for information.

Command-line syntax
-J- Java_option
Command-line example
-J-Duser.language=ja
Properties file syntax
n/a
Properties file example
n/a
Default value
n/a
Options to Pass to Java Compiler (-C)
The -C prefix marks options to be passed to the Java compiler that is invoked from the sqlj script. This prefix immediately precedes a Java compiler option, with no spaces in between. After stripping off the -C prefix, the sqlj script passes the compiler option to the Java compiler (typically but not necessarily javac ).
For example:
-C-nowarn

After stripping the -C prefix, the sqlj script passes the -nowarn argument as is to the compiler. The -nowarn flag is a javac option to suppress warning messages during compilation.

There is one Java compiler option, CLASSPATH, that must be slightly modified when it is passed to the compiler. All other compiler options are passed without change.

Specify CLASSPATH to the Java compiler using the following syntax:

-C-classpath= path

For example:

-C-classpath=/user/jdk/bin

The equals sign is required for SQLJ parsing, but must be replaced with a space when the option is passed to the Java compiler. So after the -C is stripped off and the equals sign is replaced, the option will be passed to the compiler as follows:

-classpath /user/jdk/bin

If the Java compiler runs in its own Java VM, you can pass options to that VM via the compiler. This can be done by prefixing the VM option with -C-J with no spaces between this prefix combination and the VM option.

For example:

-C-J-Duser.language=de


 
Notes
  • In the above -classpath discussion the forward-slash (/) was used as the file separator. Be aware, however, that in specifying this or similar options you must actually use the file separator of your operating system, as specified in the file.separator system property of your Java VM.
  • Do not use -C-d to specify an output directory for .class files. Instead you should use the SQLJ -d option, which specifies the output directory for generated .ser files and is also passed to the Java compiler. This ensures that .class files and .ser files will be in the same directory.
  • Do not use -C-encoding to specify encoding of .class files generated by the Java compiler. Instead you should use the SQLJ -encoding option, which specifies encoding of .java and .ser files generated by SQLJ and is also passed to the compiler. This ensures that .class files and .ser files receive the same encoding.
  • If you specify compiler options but also turn off compilation for .sqlj files (and have no .java files on the command line), the compiler options will be silently ignored.

Command-line syntax
-C- Java_compiler_option >
Command-line example
-C-nowarn
Properties file syntax
compile. Java_compiler_option
Properties file example
compile.nowarn
Default value
n/a
Options to Pass to Profile Customizer (-P)
The -P prefix marks options to be passed to the SQLJ profile customizer that is invoked from the sqlj script. This prefix immediately precedes a customizer option, with no spaces in between. After stripping off the -P prefix, the sqlj script passes the customizer option to the profile customizer.
Examples:
-P-backup

-P-driver=oracle.jdbc.driver.OracleDriver

After stripping the -P prefix, the sqlj script passes arguments as is to the customizer. The -backup flag is an option of the standard customizer (OraCustomizer) to backup the previous customization before generating a new one. The -driver option is used to register JDBC drivers for the customizer's connection to the database.

For information about available options of the standard profile customizer, see the "Profile Customizaton" section later in this chapter.


 
Note
If you specify customizer options but also turn off customization for .sqlj files (and have no .ser files on the command line), the customizer options will be silently ignored.

Command-line syntax
-P- profile_customizer_option >
Command-line example
-P-driver=oracle.jdbc.driver.OracleDriver
Properties file syntax
profile. profile_customizer_option
Properties file example
profile.driver=oracle.jdbc.driver.OracleDriver
Default value
n/a

Flags for Special Processing

As mentioned above, .sqlj files are typically processed by the SQLJ translator, Java compiler, and SQLJ profile customizer. The following flags limit this processing, directing the SQLJ startup script to skip the indicated process (this is also discussed in the "Overview of SQLJ Command Line Syntax" section):
In addition, the following flag directs special processing by SQLJ after profile customization:
All three of these flags are discussed in this section.
Compilation Flag (-compile)
The -compile flag enables or disables processing of .java files by the compiler. This applies both to .java files generated by the SQLJ translator from .sqlj files specified on the command line, and to previously generated .java files specified on the command line. This flag would be useful, for example, if you wanted to compile .java files later using a compiler other than javac . The flag is true by default; setting it to false disables compilation.
(It is sensible to allow -compile to be set to false even when .java files are specified on the command line because there may be other operatons besides compilation, such as line mapping, that you want to perform on the .java files. This would imply, though, that .class files already existed from previous compilation, since that is where the mapping information goes.)
When you process a .sqlj file with -compile=false , you are responsible for compiling and customizing it later as necessary.

 
Note
Setting -compile=false also implicitly sets -profile=false . In other words, whenever -compile is false both compilation and customization are skipped. If you set -compile=false and -profile=true , your -profile setting will be ignored.
Command-line syntax
-compile= true/false
Command-line example
-compile=false
Properties file syntax
sqlj.compile= true/false
Properties file example
sqlj.compile=false
Default value
true (enabled)
Profile Customization Flag (-profile)
The -profile flag enables or disables processing of generated .ser files by the SQLJ profile customizer. However, this only applies to .ser files generated by the SQLJ translator from .sqlj files specified on the current command line; it does NOT apply to previously generated .ser files (or to .jar files) specified on the command line. The flag is true by default; setting it to false disables customization.
(This option behaves differently than the -compile option for files specified on the command line, in that .ser and .jar files specified on the command line will still be customized if -profile=false , but .java files specified on the command line will NOT be compiled if -compile=false . The reason for this is that there are other operations, such as line mapping, that you might want to perform on a .java file. There are, however, no other operations that could be performed on a .ser or .jar file specified on the command line, so it makes no sense to not customize them.)
When you process a .sqlj file with -profile=false , you are responsible for customizing it later as necessary.

 
Note
Setting -compile=false also implicitly sets -profile=false . In other words, whenever -compile is false both compilation and customization are skipped. If you set -compile=false and -profile=true , your -profile setting will be ignored.
Command-line syntax
-profile= true/false
Command-line example
-profile=false
Properties file syntax
sqlj.profile= true/false
Properties file example
sqlj.profile=false
Default value
true (enabled)
Conversion of .ser File to .class File (-ser2class)
The -ser2class flag instructs SQLJ to convert the generated .ser file to a .class file. This is necessary if you are using SQLJ to create an applet that will be run from a browser that does not support resource filenames with the .ser suffix. (This is true of Netscape Navigator 4.x, for example.)
The translation is done after profile customization so that it includes your customizations.

 
Note
The original .ser file is NOT saved.

Command-line syntax
-ser2class= true/false
Command-line example
-ser2class=true
Properties file syntax
sqlj. ser2class = true/false
Properties file example
sqlj.ser2class=false
Default value
false

Connection Options (Advanced Features)

The following options are used for the database connection for online semantics-checking and are described in this section:
There is no requirement that the SQLJ translator connect to the same database or as the same user as the application itself does at runtime. The connection information in application source code can be independent of the connection information provided in the SQLJ options.
The subsections that follow discuss advanced features, where non-default or multiple connection contexts are being used. More basic discussion of these options, where only the default connection context is being used, are discussed in the "Connection Options (Basic Features)" section of Chapter 3, "Basic Features.")
Online Semantics-Checking and Username (-user)--Advanced Features
The -user option enables online semantics-checking and specifies usernames (schema names) for database connections to be used for the checking.
This section discusses advanced features of the -user option, where multiple or non-default connection contexts are being used. More basic discussion of this option, where only the default connection context is being used, is located in the "Online Semantics-Checking and Username (-user)--Basic Features" section of Chapter 3, "Basic Features."
To specify a username for a particular connection context ( CtxClass ):
-user@CtxClass=scott

This will result in online checking against the scott schema for any of your SQLJ executable statements that specify a connection object that is an instance of CtxClass .

The CtxClass connection context class must be declared in your source code or previously compiled into a .class file. (See the "Connection Contexts" section earlier in this chapter for more information.)

You must use the -user option separately for each connection context username you want to specify, and these settings have no influence on each other:

-user@CtxClass1=user1 -user@CtxClass2=user2 -user@CtxClass3=user3

To specify a username for the default connection context and any other connection contexts that you do not specify a username for:

-user=foo

Essentially, any connection context that does not have a username setting specifically assigned to it will use the username setting of the default connection context, presuming a username has been set for the default context.

For example, if you had declared CtxClass 1, CtxClass2 , CtxClass3 , and CtxClass4 in your application, and you use both of the preceding -user commands on your command line, the username would be foo for CtxClass4 and the default connection context.

You can also specify the password along with the username for a particular connection context:

-user@CtxClass=scott/tiger

This sets the username to scott and the password to tiger for the CtxClass connection context class.

And, similarly to preceding discussion, you can set a username and password for the default connection context and any other connection contexts that you did not set specifically, as follows:

-user=scott/tiger

Also, once you have enabled online checking by setting the -user option, you can disable online checking for a particular connection context by setting the -user option again with an empty username for that connection context. For example, after semantics checking has been enabled, the following will disable it for connection context CtxClass2 :

-user@CtxClass2=

This disables online sematics-checking for any SQLJ executable statements that specify a connection object that is an instance of CtxClass2 .

The CtxClass2 connection context class must be declared in your source code or previously compiled into a .class file.

To disable online semantics-checking for the default connection context and any other connection contexts that you do not specify a username for:

-user=

For example, if you had declared CtxClass and CtxClass2 in your application and you set a username for CtxClass in a properties file or on the command line, and then you use the above -user= command, online checking would be disabled for CtxClass2 and the default connection context, but would still be enabled for CtxClass using the username you specified.

There is also a special username, URL.CONNECT , which does not require a password. It connects to the database specified in the -url option for the particular context class:

-user@CtxClass=URL.CONNECT

Advanced command-line syntax
-user<@ conn context class >= username < /password >
Advanced command-line examples
-user@Context=scott
-user@Context=scott/tiger
-user@Context=

-user@Context=URL.CONNECT

Advanced properties file syntax
sqlj.user<@ conn context class >= username < /password >
Advanced properties file examples
sqlj.user@Context=scott
sqlj.user@Context=scott/tiger
sqlj.user@Context=

sqlj.user@Context=URL.CONNECT

Default value
none (no online semantics-checking)
User Password for Online Semantics-Checking (-password)--Advanced Features
The -password option specifies user passwords for database connections to be used for online semantics-checking. It is used in conjunction with the -user option, which enables online checking and specifies usernames for the connections.
This section discusses advanced features of the -password option, where multiple or non-default connection contexts are being used. More basic discussion of this option, where only the default connection context is being used, is located in the "User Password for Online Semantics-Checking (-password)--Basic Features" section of Chapter 3, "Basic Features.")
To set the password for a specific connection context ( CtxClass ):
-password@CtxClass=tiger

The CtxClass connection context class must be declared in your source code or previously compiled into a .class file. (See the "Connection Contexts" section earlier in this chapter for more information.)

To specify a password for the default connection context and any other connection contexts that you do not specify a password for:

-password=tiger

Essentially, any connection context that does not have a password specifically set for it will use the password of the default connection context, presuming a password has been set for the default context.

For example, if you had declared CtxClass and CtxClass2 in your application, and you specified some other password for CtxClass , the above password would apply to CtxClass2 and the default connection context. Of course if this results in a password being assigned to a connection context that does have have a username assigned to it (presumably due to user error), no online checking would be done for that connection context and the password would be ignored.

You will be prompted interactively by SQLJ if there are any connection contexts for which there is a username but no password (unless the username is URL.CONNECT , as discussed in the -user option section above).

If you specifically want to be prompted interactively for the password for a particular connection context, you can set an empty password:

-password@CtxClass=

If you set an empty password without specifying a connection context, then you would be prompted interactively for passwords for all connection contexts that you do not specifically set a password for. For example, if you declared CtxClass and CtxClass2 in your application, and you run SQLJ with the following options:

-password=

-password@CtxClass2=foo

Then you would be prompted interactively for passwords for CtxClass and the default connection context (presuming usernames other than URL.CONNECT were set for those). (This is actually equivalent to not using any password setting at all for CtxClass or the default context, since you would also be prompted interactively in that case, except that the empty password setting shown above would override any previous password setting for the default context.)

If you actually want to use an empty password to log in, specify the following:

-password=EMPTY.PASSWORD

Advanced command-line syntax
-password<@ conn context class >= user _ password
Advanced command-line example
-password@Context=tiger
Advanced properties file syntax
sqlj.password<@ conn context class >= user _ password
Advanced properties file example
sqlj.password@Context=tiger
Default value
none
Connection URL for Online Semantics-Checking (-url)--Advanced Features
The -url option specifies JDBC URLs for establishing database connections for online semantics-checking.
This section discusses advanced features of the -url option, where multiple or non-default connection contexts are being used. More basic discussion of this option, where only the default connection context is being used, is located in the "Connection URL for Online Semantics-Checking (-url)--Basic Features" section of Chapter 3, "Basic Features.")
To specify a URL for a particular connection context ( CtxClass ):
-url@CtxClass=jdbc:oracle:oci8:@

The CtxClass connection context class must be declared in your source code or previously compiled into a .class file. (See the "Connection Contexts" section earlier in this chapter for more information.)

To specify a URL for the default connection context and any other connection contexts that you do not specify a URL for:

-url=jdbc:oracle:oci8:@

Essentially, any connection context that does not have a URL specifically set for it will use the URL of the default connection context, presuming a URL has been set for the default context.

For example, if you had declared CtxClass and CtxClass2 in your application, and you specified some other URL for CtxClass , the above URL would apply to CtxClass2 and the default connection context.


 
Note
Remember that any connection context with a URL must also have a username in order for online checking to occur.

Advanced command-line syntax
-url<@ conn context class >= JDBC_url
Advanced command-line examples
-url@Context= jdbc:oracle:oci8:@
-url@Context= jdbc:oracle:thin:@hostname:1521:orcl
Advanced properties file syntax
sqlj.url<@ conn context class >= JDBC_url
Advanced properties file examples
sqlj.url@Context= jdbc:oracle:oci8:@
sqlj.url@Context= jdbc:oracle:thin:@hostname:1521:orcl
Default value
jdbc:oracle:oci8:@

Semantics-Checking Options

The following options specify characteristics of online and offline semantics-checking and are described in this section:

 
Note
The -default-function-mode , -default-procedure-mode , and -default-block-mode options have been discontinued, as the SQLJ standard specifies their values.

Offline Semantics-Checker (-offline)
The -offline option specifies a Java class that implements the semantics-checking component of SQLJ for offline checking. With offline checking, there is no connection to the database; only SQL syntax and usage of Java types is checked. (For information about what offline checkers do and how they function, see the "Semantics Checking" and "Translator Operations" sections in Chapter 3, "Basic Features.")
Note that offline checking is not enabled by the -offline option. Offline checking only runs when online checking does not (either because online checking was not enabled or because it could not complete due to error).
You can specify different offline checkers for different connection contexts, with a limit of one checker per context (do not list multiple offline checkers for one connection context).
This option permits customized checking for different databases, as specified in the table below.


 
Types Checked by Offline Semantics-Checkers
Semantics-Checker Class
Usage
sqlj.semantics.OfflineChecker
For generic types or non-Oracle databases

To specify the offline checker for a particular connection context ( CtxClass ):
-offline@CtxClass=oracle.sqlj.checker.Oracle8OfflineChecker
This will result in Oracle8OfflineChecker being used for offline checking for any of your SQLJ executable statements that specify a connection object that is an instance of CtxClass .

The CtxClass connection context class must be declared in your source code or previously compiled into a .class file. (See the "Connection Contexts" section earlier in this chapter for more information.)

Use the -offline option separately for each connection context offline checker you want to specify, and these settings have no influence on each other:

-offline@CtxClass2=oracle.sqlj.checker.Oracle8OfflineChecker

-offline@CtxClass3=sqlj.semantics.OfflineChecker

To specify the offline checker for the default connection context and any other connection contexts that you do not specify an offline checker for:

-offline=oracle.sqlj.checker.Oracle8OfflineChecker

Essentially, any connection context that does not have an offline checker setting specifically assigned to it will use the offline checker setting of the default connection context, presuming an offline checker has been set for the default context.

Command-line syntax
-offline<@ conn context class >= checker_class
Command-line examples
-offline=oracle.sqlj.checker.Oracle8OfflineChecker
-offline@ Context =oracle.sqlj.checker.Oracle8OfflineChecker
Properties file syntax
sqlj.offline<@ conn context class >= checker_class
Properties file examples
sqlj.offline=oracle.sqlj.checker.Oracle8OfflineChecker
sqlj.offline@ Context =oracle.sqlj.checker.Oracle8OfflineChecker
Default value
sqlj.semantics.OfflineChecker
Online Semantics-Checker (-online)
The -online option specifies a Java class or list of classes that implement the online semantics-checking component of SQLJ. This involves connection to a database.
Remember that online checking is not enabled by the -online option. You must enable it through the -user option, and the -password , -url , and -driver options must be set appropriately as well. (For information about what online checkers do and how they function, see the "Semantics Checking" and "Translator Operations" sections in Chapter 3, "Basic Features.")
You can specify different online checkers for different connection contexts, and you can list more than one checker for any given context. In cases where multiple checkers are listed for a single context, SQLJ will use the first checker (reading from left to right in the list) that accepts the database connection that was established for online checking. (At analysis time, a connection is passed to each online checker, and the checker reports whether or not the database is recognized.)
This option permits customized checking for different databases, as specified in the table below.


 
Types Checked by Online Semantics-Checkers
Semantics-Checker Class
Types Checked
sqlj.semantics.JdbcChecker
For generic types or non-Oracle databases

The default online checker, Oracle8JdbcChecker , requires installation of the Oracle JDBC OCI 8 driver. Also, the driver class must be in your CLASSPATH.

Oracle also provides a generic checker, sqlj.semantics.JdbcChecker , that is used with non-Oracle databases.

To specify a list of drivers and allow the proper class to be selected depending on what kind of database is being accessed:

-online=oracle.sqlj.checker.Oracle8JdbcChecker,sqlj.semantics.JdbcChecker

With this specification, if connection is made to an Oracle database the Oracle8JdbcChecker is used. If connection is made to any other kind of database the JdbcChecker is used.

To specify the online checker for a particular connection context ( CtxClass ):

-online@CtxClass=oracle.sqlj.checker.Oracle8JdbcChecker

This will result in Oracle8JdbcChecker being used for online checking for any of your SQLJ executable statements that specify a connection object that is an instance of CtxClass , presuming online checking has been enabled for CtxClass .

The CtxClass connection context class must be declared in your source code or previously compiled into a .class file. (See the "Connection Contexts" section earlier in this chapter for more information.)

Use the -online option separately for each connection context online checker you want to specify, and these settings have no influence on each other:

-online@CtxClass2=oracle.sqlj.checker.Oracle8JdbcChecker

-online@CtxClass3=sqlj.semantics.JdbcChecker

To specify the online checker for the default connection context and any other connection contexts that you do not specify an online checker for:

-online=oracle.sqlj.checker.Oracle8JdbcChecker

Essentially, any connection context that does not have an online checker setting specifically assigned to it will use the online checker setting of the default connection context, presuming an online checker has been set for the default context.

Command-line syntax
-online<@ conn context class >= checker_class(list)
Command-line examples
-online=oracle.sqlj.checker.Oracle8JdbcChecker
-online=oracle.sqlj.checker.Oracle8JdbcChecker,sqlj.semantics.JdbcChecker
-online@ Context =oracle.sqlj.checker.Oracle8JdbcChecker
Properties file syntax
sqlj.online<@ conn context class >= checker_class(list)
Properties file examples
sqlj.online=oracle.sqlj.checker.Oracle8JdbcChecker
sqlj.online=oracle.sqlj.checker.Oracle8JdbcChecker,sqlj.semantics.JdbcChecker
sqlj.online@ Context =oracle.sqlj.checker.Oracle8JdbcChecker
Default value
sqlj.semantics.JdbcChecker
Caching of Online Semantics-Checker Results (-cache)
The -cache option can be used to enable caching of the results generated by the online checker. This avoids additional database connections during subsequent SQLJ translation runs. The analysis results are cached in a file SQLChecker.cache that is placed in your current directory.
The cache holds serialized representations of all SQL statements that have been successfully translated (translated without error or warning messages), with all statement parameters, return types, translator settings, and modes inferred.
The cache is cumulative and continues to grow through successive invocations of the SQLJ translator. Remove the SQLChecker.cache file to empty the cache.
Command-line syntax
-cache= true/false
Command-line example
-cache=true
Properties file syntax
sqlj.cache= true/false
Properties file example
sqlj.cache=false
Default value
false

Java, Compiler, and Customizer Options

The following options relate to the operation of the Java VM, Java compiler, and SQLJ profile customizer and are described in this section:
Name of Java VM (-vm)
Use the -vm option if you want to specify a particular Java VM for SQLJ to use. Otherwise SQLJ will use java .
This command must be specified on the command line; it cannot be specified in a properties file (since properties files are read after the VM is invoked).
Command-line syntax
-vm= Java_VM_path+name
Command-line example
-vm=/myjavadir/myjavavm
Properties file syntax
n/a
Properties file example
n/a
Default value
java
Name of Java Compiler (-compiler-executable)
Use the -compiler-executable option if you want to specify a particular Java compiler for SQLJ to use. Otherwise SQLJ will use javac .
If you do not specify a directory path along with the name of the compiler executable file, SQLJ will look for the executable according to the PATH setting of your operating system.
Command-line syntax
-compiler-executable= Java_compiler_<path+>name
Command-line example
-compiler-executable=/myjavadir/myjavac
Properties file syntax
sqlj.compiler-executable= Java_compiler_<path+>name
Properties file example
sqlj.compiler-executable=myjavac
Default value
javac
Compiler Encoding Support (-compiler-encoding-flag)
As mentioned in Chapter 3, "Basic Features," it is typical that whenever you use the -encoding option to specify an encoding character set for SQLJ to use, SQLJ will pass this to the Java compiler for it to use as well. Set the -compiler-encoding-flag to false if you do NOT want SQLJ to pass the character-encoding to the compiler (for example, if you are using a compiler other than javac and it does not support an -encoding option by that name).
(See the "Encoding for Generated .java and .ser Files (-encoding)" section in Chapter 3, "Basic Features," for information about the -encoding option.)
Command-line syntax
-compiler-encoding-flag= true/false
Command-line example
-compiler-encoding-flag=false
Properties file syntax
sqlj.compiler-encoding-flag= true/false
Properties file example
sqlj.compiler-encoding-flag=false
Default value
true
Java Compiler Output File (-compiler-output-file)
If the Java compiler has been instructed to output its results to a file, use the -compiler-output-file option to make SQLJ aware of the filename. Otherwise SQLJ assumes that the compiler outputs to the standard output device (such as STDOUT on a UNIX system).
Command-line syntax
-compiler-output-file= output_file_path+name
Command-line example
-compiler-output-file=/myjavadir/mycmploutput
Properties file syntax
sqlj.compiler-output-file= output_file_path+name
Properties file example
sqlj.compiler-output-file=/myjavadir/mycmploutput
Default value
none (standard output)
Java Compiler Output File (-compiler-pipe-output-flag)
If this option is set to true (which is the default behavior) the following flag will be passed to the Java compiler:
             -J-Djavac.pipe.output=true
      By default, the JDK 1.1 Java compilers write messages to stderr rather than stdout. If the javac.pipe.output system property is set to
      true, output is written to stdout instead. SQLJ expects the compiler output to be written to stdout, so that it can be captured reliably.
      If your Java compiler is not based on JDK 1.1.X or JDK 1.2.X, then it probably will not understand the flag above, and you should set the SQLJ
      option:
          -compiler-pipe-output-flag=false
      This setting disables the flag above from being passed to your Java compiler. ava
Command-line syntax
-compiler-pipe-output-flag=true/false
Command-line example
-compiler-pipe-output-flag=false
Properties file syntax
sqlj.compiler-pipe-output-flag=true/false
Properties file example
sqlj.compiler-pipe-output-flag=false
Default value
true
Default Profile Customizer (-default-customizer)
Use the -default-customizer option to instruct SQLJ to use a profile customizer by default
In particular, you would use this option if expect to have to customize SQLJ profiles.
This option takes a Java class name as its argument.
Command-line syntax
-default-customizer= customizer_classname
Command-line example
-default-customizer=sqlj.myutil.MyCustomizer
Properties file syntax
sqlj.default-customizer= customizer_classname
Properties file example
sqlj.default-customizer=sqlj.myutil.MyCustomizer
Default value
none (i.e. no customization will be performed)

SQLJ and National Language Character Sets

This section describes the SQLJ support for native character encoding:

Property file.encoding

SQLJ uses the Java property, file.encoding. If this property is non-existent, empty, or invalid, SQLJ behaves as if this property is defined as the string 8859_1 (The Latin-1 character set).
To process, for example, files with SJIS encoding, you have the following options:
sqlj -Dfile.encoding=SJIS file .sqlj
Reporting Errors
Any errors, warnings, etc., are reported in terms of their position in the Java character stream which is obtained after:
This loses the precise knowledge about which input characters were given in a native character set and which ones were escaped with Unicode escape sequences.
If file.encoding is set to 8859_1, errors and warnings are reported with respect to the actual location in the input file, rather than the Unicode character stream location.
The .java source files that SQLJ produces contain only ASCII characters with values ranging from 0 to 127. All other characters are represented with escape sequences.

Frequently Asked Questions about SQLJ

General

Is SQLJ really database neutral?
We make minimal assumptions about the SQL dialect.
We assume, for example, that you can have
#sql positer = { SELECT ...} ;

if the SQL statement begins with SELECT, but not if it begins with INSERT.

The SQL in such constructs is simply passed to the JDBC driver. If your JDBC driver and database understand the SQL dialect you embed, SQLJ doesn't complain.

When semantic analysis is done on a SELECT statement in a #sql construct we do make assumptions that your SELECT statement syntax is SQL92. There may be extensions to SELECT statements in some SQL dialect that the SQLJ parser doesn't understand, in which case the implicit cursor declaration or semantic checking of the SQL statement may not work.

Nothing about the SQLJ language is JDBC-specific. It can in principle be implemented with interfaces other than JDBC. This particular implementation happens to be based on JDBC

Is it possible to run a SQLJ program under Java version 1.0.2?

No. Both, the SQLJ translator and generated SQLJ programs require Java 1.1.1 or later.

What are the SQLJ plans for the future?

We want to establish a conformance QA suite for SQLJ implementations in general.

Does this SQLJ release implement the full SQLJ specification?

This release implements the full SQLJ specification as submitted to ANSI.

Writing SQLJ Programs

In the example programs, I see the following import clauses at the beginning of the file:
import java.sql.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;

What are these packages? Do I need to use the same import statements in my own sqlj files?

These packages belong to the JDBC API (java.sql.*) and to the SQLJ runtime API. In order to simplify the exposition in the examples, we import entire packages. However, you may just import those classes that you refer to in your sqlj program:

Does SQLJ support Unicode? What about my native character set?
SQLJ fully supports Java's Unicode escape sequences. See See SQLJ and National Language Character Sets and See Property file.encoding for more information.
Naturally, you can also use Unicode characters inside of any SQL code fragment. The SQLJ translator does not check the usage of such characters. Please consult your JDBC driver and database server documentation to determine legal usage.

Translating SQLJ Programs

When I translate my SQLJ program, I notice a number of " .ser" files that include the name "_SJProfile" are also created. What are these files and how should I use them?
When a SQLJ file is translated into a Java file, information about the SQL operations it contains is stored in SQLJ-generated resource files called profiles. These profiles are used by the SQLJ runtime, and allow for vendor-specific static SQL compilation and optimization. Profiles are identified by the suffix "_SJProfileN" (where N is a number) following the name of the original input file. A SQLJ programmer does not need to reference the profiles directly, but should package them in the same location in the CLASSPATH as the rest of the classes that make up the SQLJ application.
SQLJ also generates a file SQLChecker.cache - what is this for?

Compiling a "sqlj" file with online checking turned on takes a long time. Is there a way to speed this up?

If you use the cache option is set as: -cache=true, then SQLJ remembers the result of online checking and stores it in the file SQLChecker.cache. This can obviate the need to connect to the database for every #sql statement in your program. Note, however, that only those SQL statements that do not result in error or warning messages are being cached.

Whenever SQLChecker.cache has become too large, or you want to start with a clean cache, you can just remove SQLChecker.cache before running SQLJ.

I have turned on online checking by giving the option -user=scott. How can I turn off online checking for the connection context Ctx?

Specify an empty user name for Ctx with the additional option, -user@Ctx=

What are these Java classes that are specified in the -offline and -online options, and where do I get them from?

The classes specified in -offline and -online options provide syntactic and semantic checking of the SQL statements that occur in SQLJ programs. They permit vendor- and database-specific customization. If you want to build a customized checker, look at the sqlj.framework.checker package, and obtain the additional documentation on SQL checking.

The Reference Implementation distribution comes with the following implementations of SQL checking:

For more details, see .
SQLJ complains about the following code, which javac compiles just fine:
char c = '\u000a' ;
Are you going to fix this bug?

In this case, javac does not entirely follow the Java language specification: unicode escape sequences are processed before lexing takes place. In this example, \u000a is replaced with a linefeed, resulting in illegal Java code. You can rewrite the declaration correctly as:

char c = '\n' ;

When I try to run SQLJ, I get the error "Unable to initialize threads: ...".

When I try to run SQLJ, I get the error "An application error has occurred... Exception:access violation ...". I am trying to run Sun's JDK 1.1.X on Windows NT/Windows 95.

If you have installed the Java Development Kit from Sun to run SQLJ, you may be experiencing problems from conflicts with previously installed Java environments.

The easiest way to avoid such problems is to customize your PATH and CLASSPATH setting to only include those binaries and libraries that are needed by the JDK, the JDBC driver, and SQLJ.

Please note the exact location of the following directories:

jdkdir the directory where you installed Sun's JDK 1.1.X

jdbcdir the directory into which you unzipped Oracle's thin JDBC driver.

sqljdir the directory into which you unzipped SQLJ

Open a DOS Window and enter the following commands. (You can also put these commands into a .BAT file; this will make it easier to edit and replay them.)

SET PATH=j dkdir \bin

We cut the path as short as possible. It is important that you do not include your previous PATH!

SET CLASSPATH=.; jdkdir \lib\classes.zip

This puts only the JDK class libraries into your CLASSPATH.

At this point you should be able to check the version of the JDK that you are using:

java -version

Next, we add to the CLASSPATH with the classes required by the JDBC driver.

SET CLASSPATH=%CLASSPATH%; jdbcdir \lib\classes111.zip

If you use a driver other than Oracle's thin JDBC driver, you must expand the CLASSPATH following that driver's installation instructions.

For some drivers (such as Oracle's thick JDBC driver) you must also expand the PATH following that driver's installation instructions.

Finally we also include the classes that comprise the SQLJ translator.

SET CLASSPATH=%CLASSPATH%; sqljdir \lib\translator.zip

SET PATH=%PATH%; sqljdir \bin

If you do omit the PATH expansion above, you will have to invoke SQLJ explicitly with:

java sqlj.translator.Main options

You should now be able to run SQLJ.

If you want to determine, what caused your problem in the first place, augment the new paths step by step to include your original settings, until the problem reappears.

The SQL syntax of my non-Oracle database requires the characters '?' and ':'. But the JDBC code generated by SQLJ also contains these characters as place-holders for the bind variables. How do I tell the translator that some are not to be interpreted, but should be left unchanged?

Write those occurrences of ' ? ' and ' : ' as "?" and ":" . Implement a profile customizer for your database that will replace all occurrences of "?" and ":" with ? and : respectively.

Compiling Generated Files

Is it possible to compile the generated "java" files under Java version 1.0.2?
No. You need a Java compiler that compiles Java version 1.1.1 or later.
I have written a SQLJ application. The SQLJ translator produces "java" files, but the javac compiler complains that it cannot find a number of classes.
SQLJ produces "java" files that require several SQLJ runtime classes in order to compile and run. You may want to add the entire set of classes that make up the SQLJ translator to the CLASSPATH of your Java compiler. If you want to distribute your application, you also have to include all the class files that live in sqlj.runtime.* and its subtrees (These files are available in the SQLJ distribution as runtime.zip). The same set of classes are also required by a Java compiler to compile "java" files generated by SQLJ.

I am trying to declare a block-level iterator class, which I later populate and use in the same block. SQLJ appears to translate the file just fine, but javac gives a compilation error. Here is the offending method, followed by the error reported by javac.

void method() throws SQLException

{

#sql iterator BlockIter (int col1, String col2);

BlockIter i;

#sql i = { SELECT col1, col2 FROM tab };

while (i.next()) { ... }

}

Error: Class BlockIter not found in type declaration.

The error location is somewhere within the code generated for the select operation. Is this due to a bug in the generated code?

This problem results from a known bug in the javac compiler, version 1.1.4. The problem is that block-level classes can only be referenced in the same block as they were declared, but not from within blocks within the declaring block. SQLJ generates code for the above select operation as a statement block, and thus cannot instantiate a BlockIter instance within the generated block. A work-around is to declare the iterator at the class-level rather than the block-level.

Can you make the javac compiler report any errors in terms of the original .sqlj file that was translated by SQLJ?

By default, the sqlj command line too invokes the Java compiler (javac) and reports errors produced by it in terms of the original .sqlj source file.

Running SQLJ Programs

I wrote a SQLJ program called ProfProb.sqlj. When I try to run the program I get the following runtime exception.
java.sql.SQLException: profile ProfProb_SJProfile0 not found:
java.lang.ClassNotFoundException: ProfProb_SJProfile0

What does this mean?

The SQLJ runtime is unable to find the profile generated for your program (see See Translating SQLJ Programs ). If the generated *.ser files are located in a different directory tree than your class files, you may need to copy the file ProfProb_SJProfile0.ser from the source tree to the class tree. You can use the -d option to control the placement of generated *.ser files. Note that when SQLJ invokes the Java compiler, it will pass on the same value to the compiler, thus ensuring that both, class files and serialized profiles are placed in the same location.

If this was not the problem, you may need to regenerate the profiles for your program by translating the input file again.

Why do I get a java.lang.ClassCastException at runtime when I attempt to access an iterator column whose type is java.math.BigDecimal? I have also noticed the problem when I SELECT...INTO or FETCH...INTO a BigDecimal target, or when I get a BigDecimal as an out-parameter from a stored procedure.

Due to a restriction in the JDBC API given by package java.sql, we are unable to perform non-default conversions from database types to java.math.BigDecimal. If the default mapping from database type to Java type for your driver is not BigDecimal, SQLJ will not be able to properly convert the database type at runtime, resulting in a ClassCastException. Note that database types which map to BigDecimal by default will not cause this problem. Consult your JDBC driver documentation for specifics on type mapping from database to Java types.

I'm trying to debug one of my SQLJ programs. It looks like there's a problem that may by due to an error in the JDBC driver I'm using, but I'm not sure since I'm unfamiliar with the SQLJ runtime library. Is there any way to determine what JDBC calls my SQLJ program is making at runtime?

The JDBC calls cannot be directly traced. However, the SQLJ runtime includes a utility to install tracing capability into your program. The calls traced are the calls made to the SQLJ runtime just before the calls to the JDBC driver are made. They have names similar to the those in JDBC. The utility operates on the profiles associated with your program. Suppose your program uses a profile called MyApp_SJProfile0 . Add debugging to your program with the command:

% sqlj -P-debug MyApp_SJProfile0.ser

This uses the Java VM to run the main method of class sqlj.runtime.profile.util.AuditorInstaller . See the documentation of the AuditorInstaller class for more details on usage and options.

Applets

The examples shown here are all applications. Does SQLJ allow you to write Applets?
SQLJ imposes no applet restrictions that your JDBC driver doesn't impose.
Our sample programs were applications because applications are simpler to read. This allows the examples to focus on SQLJ constructs.
I'm having some problems loading applets that use SQLJ. I have an applet called MyApp.class, and I have packaged with it the profile generated during translation, MyApp_Profile0.ser. When I try to load the applet in some browsers, I get the following error message:

java.lang.ClassNotFoundException: MyApp_Profile0

It appears as if the browser is trying to load the profile as a class rather than a serialized object. Why is this, and what can I do to get my applet to run?

Some browsers -notably Netscape 4.0- do not yet have support for loading a serialized object from a resource file associated with the applet. As a work-around, there is an additional "utility step"provided with the SQLJ translator to a serialized profile into a profile stored in Java class format. Your profile can be converted using the following command:

% sqlj -ser2class MyApp_SJProfile0.ser

As a result, the file MyApp_SJProfile0.ser is replaced with the class MyApp_SJProfile0.class. You can also use this flag during the SQLJ translation step, or you may even want to store it in your sqlj.properties file. Once you have replaced all profiles in .ser format used by the applet with profiles in .class format, the problem should go away. If you want to invoke this utility directly, it is available in a Java class called sqlj.runtime.profile.util.SerProfileToClass. It takes a serialized profile resource file as input and produces a Java class containing the profile as output.

Error Messages

Compile time messages

[To be completed]

Runtime Error Messages

java.lang.ClassNotFoundException: unable to instantiate profile profileName
java.lang.ClassNotFoundException: unable to instantiate serialized profile profileName
java.lang.ClassNotFoundException: profileName is not a valid profile
java.sql.SQLException: cannot perform concurrent executes using same execution context
java.sql.SQLException: expected expectedNumCols columns in select list but found foundNumCols
java.sql.SQLException: expected instance of ForUpdate iterator at parameter paramNum, found class className
java.sql.SQLException: expected statement { sqlOperation } to be executed via executeUpdate
java.sql.SQLException: expected statement { sqlOperation } to be prepared using prepareStatement
java.sql.SQLException: expected statement { sqlOperation } to use no parameters
java.sql.SQLException: found null connection context
java.sql.SQLException: found null execution context
java.sql.SQLException: multiple rows found for select into statement
java.sql.SQLException: no rows found for select into statement
java.sql.SQLException: profile profileName not found: profileException