SQLJ statement syntax

SQLJ statements can be intermixed with Java statements in an SQLJ file.

 

Embedded SQL statements in Java

Static SQL statements in SQLJ appear in SQLJ clauses. The SQLJ translator recognizes SQLJ clauses because they begin with the token #sql and end with a semicolon.

The simplest SQLJ clauses are executable clauses that consist of the token #sql followed by an SQL statement enclosed in braces. For example, the following SQLJ clause can appear wherever a Java statement can legally appear. The purpose of the SQLJ clause is to delete all rows in the table MYTABLE:

   #sql { DELETE FROM MYTABLE };

 

Host variables in Java

Arguments for embedded SQL statements are passed through host variables, which are Java variables that appear in the SQL statement. Host variables have up to three parts:

• A colon prefix, :

• An optional parameter mode identifier: IN, OUT, or INOUT

• A Java host variable that is a Java identifier for a parameter, variable, or field

A Java identifier can appear multiple times in the Java code that is generated to replace an SQLJ clause.

The following query contains the host variable, :x, which is the Java variable, field, or parameter x visible in the scope that contains the query:

   SELECT  COL1,  COL2  FROM  TABLE1  WHERE  COL3  <  :x

All host variables that are specified in compound SQL are input host variables by default. You must specify the parameter mode identifier OUT or INOUT before the host variable to mark it as an output host variable. For example:

   #sql {begin compound atomic static 
        select count(*) into :OUT count1 from employee;
        end compound}

 

SQL tokens

In an SQLJ executable clause, the tokens that appear inside the braces are SQL tokens, except for the host variables, which correspond to Java variables. Host variables must be preceded by the colon character (:). SQL tokens never occur outside the braces of an SQLJ executable clause.

For example, the following Java method inserts the values of its arguments into an SQL table. The method body consists of an SQLJ executable clause that contains the host variables x, y, and z:

   void m (int x, String y, float z) throws SQLException 
   {
       #sql { INSERT INTO TAB1 VALUES (:x, :y, :z) };
   }

Do not initialize static SQL statements in a loop. One statement must exist for each static SQL statement. For example, the following code snippet is invalid:

   for( int i=0; i<2; i++ ){
      #sql [ctx] itr[i] = { SELECT id, name FROM staff };
   }

Replace the code snippet above with the following statements:

   int i=0;
   #sql [ctx] itr[i] = { SELECT id, name FROM staff };
   i=1;
   #sql [ctx] itr[i] = { SELECT id, name FROM staff };

In general, SQL tokens are case insensitive (except for identifiers that are delimited by double quotation marks), and can be written in uppercase, lowercase, or mixed case. However, Java tokens, are case sensitive. For clarity in examples in the SQLJ Help topics, case insensitive SQL tokens are uppercase, and Java tokens are lowercase or mixed case. The lowercase null is used to represent the Java null value, and the uppercase NULL to represent the SQL null value.

 

Parent topic

Developing SQLJ applications

 

Related tasks

Creating an SQLJ file
Adding SQLJ support to Java projects
Removing SQLJ support from Java projects