Static SQL statements in SQLJ appear in SQLJ clauses. SQLJ clauses are the mechanism by which SQL statements in Java programs are communicated to the database.
The SQLJ translator recognizes SQLJ clauses and SQL statements because of their structure, as follows:
The simplest SQLJ clauses are executable clauses and consist of the token #sql followed by an SQL statement enclosed in braces. For example, the following SQLJ clause may appear wherever a Java statement may legally appear. Its purpose is to delete all rows in the table named TAB:
#sql { DELETE FROM TAB };
In an SQLJ executable clause, the tokens that appear inside the braces are SQL tokens, except for the host variables. All host variables are distinguished by the colon character so the translator can identify them. SQL tokens never occur outside the braces of an SQLJ executable clause. For example, the following Java method inserts its arguments into an SQL table. The method body consists of an SQLJ executable clause containing 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) };
}
In general, SQL tokens are case insensitive (except for identifiers delimited by double quotation marks), and can be written in upper, lower, or mixed case. Java tokens, however, are case sensitive. For clarity in examples, case insensitive SQL tokens are uppercase, and Java tokens are lowercase or mixed case. Throughout this chapter, the lowercase null is used to represent the Java "null" value, and the uppercase NULL to represent the SQL null value.
Unlike SQL statements that retrieve data from a table, applications that perform positioned UPDATE and DELETE operations, or that use iterators with holdability or returnability attributes, require two Java source files. Declare the iterator as public in one source file, appending the with and implements clause as appropriate.
To set the value of the holdability or returnability attribute, you must declare the iterator using the with clause for the corresponding attribute. The following example sets the holdability attribute to true for the iterator WithHoldCurs:
#sql public iterator WithHoldCurs with (holdability=true) (String EmpNo);
Iterators that perform positioned updates require an implements clause that implements the sqlj.runtime.ForUpdate interface. For example, suppose that you declare iterator DelByName like this in file1.sqlj:
#sql public iterator DelByName implements sqlj.runtime.ForUpdate(String EmpNo);
You can then use the translated and compiled iterator in a different source file. To use the iterator:
To use DelByName for a positioned DELETE in file2.sqlj, execute statements like those in Deleting Rows Using a Positioned Iterator.
{
DelByName deliter; // Declare object of DelByName class
String enum;
(1) #sql deliter = { SELECT EMPNO FROM EMP WHERE WORKDEPT='D11'};
while (deliter.next())
{
(2) enum = deliter.EmpNo(); // Get value from result table
(3) #sql { DELETE WHERE CURRENT OF :deliter };
// Delete row where cursor is positioned
}
}
Notes:
The following example SQLJ application, App.sqlj , uses static SQL to retrieve and update data from the EMPLOYEE table of the DB2 sample database.
import java.sql.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
#sql iterator App_Cursor1 (String empno, String firstnme) ; (1)
#sql iterator App_Cursor2 (String) ;
class App
{
/**********************
** Register Driver **
**********************/
static
{
try
{
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").newInstance();
}
catch (Exception e)
{
e.printStackTrace();
}
}
/********************
** Main **
********************/
public static void main(String argv[])
{
try
{
App_Cursor1 cursor1;
App_Cursor2 cursor2;
String str1 = null;
String str2 = null;
long count1;
// URL is jdbc:db2:dbname
String url = "jdbc:db2:sample";
DefaultContext ctx = DefaultContext.getDefaultContext();
if (ctx == null)
{
try
{
// connect with default id/password
Connection con = DriverManager.getConnection(url);
con.setAutoCommit(false);
ctx = new DefaultContext(con);
}
catch (SQLException e)
{
System.out.println("Error: could not get a default context");
System.err.println(e) ;
System.exit(1);
}
DefaultContext.setDefaultContext(ctx);
}
// retrieve data from the database
System.out.println("Retrieve some data from the database.");
#sql cursor1 = {SELECT empno, firstnme FROM employee}; (2)
// display the result set
// cursor1.next() returns false when there are no more rows
System.out.println("Received results:");
while (cursor1.next()) (3)
{
str1 = cursor1.empno(); (4)
str2 = cursor1.firstnme();
System.out.print (" empno= " + str1);
System.out.print (" firstname= " + str2);
System.out.print ("");
}
cursor1.close(); (9)
// retrieve number of employee from the database
#sql { SELECT count(*) into :count1 FROM employee }; (5)
if (1 == count1)
System.out.println ("There is 1 row in employee table");
else
System.out.println ("There are " + count1
+ " rows in employee table");
// update the database
System.out.println("Update the database. ");
#sql { UPDATE employee SET firstnme = 'SHILI' WHERE empno = '000010' };
// retrieve the updated data from the database
System.out.println("Retrieve the updated data from the database.");
str1 = "000010";
#sql cursor2 = {SELECT firstnme FROM employee WHERE empno = :str1}; (6)
// display the result set
// cursor2.next() returns false when there are no more rows
System.out.println("Received results:");
while (true)
{
#sql { FETCH :cursor2 INTO :str2 }; (7)
if (cursor2.endFetch()) break; (8)
System.out.print (" empno= " + str1);
System.out.print (" firstname= " + str2);
System.out.print ("");
}
cursor2.close(); (9)
// rollback the update
System.out.println("Rollback the update.");
#sql { ROLLBACK work };
System.out.println("Rollback done.");
}
catch( Exception e )
{
e.printStackTrace();
}
}
}
Arguments to embedded SQL statements are passed through host variables, which are variables of the host language that appear in the SQL statement. Host variables have up to three parts:
The evaluation of a Java identifier does not have side effects in a Java program, so it may appear multiple times in the Java code 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 containing the query:
SELECT COL1, COL2 FROM TABLE1 WHERE :x > COL3
All host variables specified in compound SQL are input host variables by default. You have to specify the parameter mode identifier OUT or INOUT before the host variable in order to mark it as an output host variable. For example:
#sql {begin compound atomic static
select count(*) into :OUT count1 from employee;
end compound}
Databases may contain stored procedures, user-defined functions, and user-defined methods. Stored procedures, user-defined functions, and user-defined methods are named schema objects that execute in the database. An SQLJ executable clause appearing as a Java statement may call a stored procedure by means of a CALL statement like the following:
#sql { CALL SOME_PROC(:INOUT myarg) };
Stored procedures may have IN, OUT, or INOUT parameters. In the above case, the value of host variable myarg is changed by the execution of that clause. An SQLJ executable clause may call a function by means of the SQL VALUES construct. For example, assume a function F that returns an integer. The following example illustrates a call to that function that then assigns its result to Java local variable x:
{
int x;
#sql x = { VALUES( F(34) ) };
}
To run an SQLJ program with program name MyClass, do the following:
sqlj MyClass.sqlj
When you use the SQLJ translator without specifying an sqlj.properties file, the translator uses the following values:
sqlj.url=jdbc:db2:sample sqlj.driver=COM.ibm.db2.jdbc.app.DB2Driver sqlj.online=sqlj.semantics.JdbcChecker sqlj.offline=sqlj.semantics.OfflineChecker
If you do specify an sqlj.properties file, make sure the following options are set:
sqlj.url=jdbc:db2:dbname sqlj.driver=COM.ibm.db2.jdbc.app.DB2Driver sqlj.online=sqlj.semantics.JdbcChecker sqlj.offline=sqlj.semantics.OfflineChecker
where dbname is the name of the database. You can also specify these options on the command line. For example, to specify the database mydata when translating MyClass, you can issue the following command:
sqlj -url=jdbc:db2:mydata MyClass.sqlj
Note that the SQLJ translator automatically compiles the translated source code into class files, unless you explicitly turn off the compile option with the -compile=false clause.
db2profc -user=user-name -password=user-password -url=jdbc:db2:dbname
-prepoptions="bindfile using MyClass0.bnd package using MyClass0"
MyClass_SJProfile0.ser
db2profc -user=user-name -password=user-password -url=jdbc:db2:dbname
-prepoptions="bindfile using MyClass1.bnd package using MyClass1"
MyClass_SJProfile1.ser
...
java MyClass
The translator generates the SQL syntax for the database for which the SQLJ profile is customized. For example,
i = { VALUES ( F(:x) ) };
is translated by the SQLJ translator and stored as
? = VALUES (F (?))
in the generated profile. When connecting to a DB2 Universal Database database, DB2 will customize the VALUE statement into:
VALUES(F(?)) INTO ?
but when connecting to a DB2 Universal Database for OS/390 database, DB2 customizes the VALUE statement into:
SELECT F(?) INTO ? FROM SYSIBM.SYSDUMMY1
If you run the DB2 SQLJ profile customizer, db2profc, against a DB2 Universal Database database and generate a bind file, you cannot use that bind file to bind up to a DB2 for OS/390 database when there is a VALUES clause in the bind file. This also applies to generating a bind file against a DB2 for OS/390 database and trying to bind with it to a DB2 Universal Database database.
For detailed information on building and running DB2 SQLJ programs, refer to the Application Building Guide.
The SQLJ translator supports the same precompile options as the DB2 PRECOMPILE command, with the following exceptions:
CONNECT DISCONNECT DYNAMICRULES NOLINEMACRO OPTLEVEL OUTPUT SQLCA SQLFLAG SQLRULES SYNCPOINT TARGET WCHARTYPE
To print the content of the profiles generated by the SQLJ translator in plain text, use the profp utility as follows:
profp MyClass_SJProfile0.ser
profp MyClass_SJProfile1.ser
...
To print the content of the DB2 customized version of the profile in plain text, use the db2profp utility as follows, where dbname is the name of the database:
db2profp -user=user-name -password=user-password -url=jdbc:db2:dbname
MyClass_SJProfile0.ser
db2profp -user=user-name -password=user-password -url=jdbc:db2:dbname
MyClass_SJProfile1.ser
...