This section describes Embedded SQL for Java (SQLJ) programming. The specific topics discussed are:
SQLJ is embedded SQL for Java, and DB2 SQLJ support facilitates the creation, building and running of SQLJ programs against DB2 databases.
SQLJ consists of a set of programming extensions that define interaction between SQL and Java. It comprises a set of clauses that extend Java programs to include static SQL constructs. An SQLJ translator is a utility that transforms those SQLJ clauses into standard Java code that accesses the database through a call interface. The output of an SQLJ translator is a generated Java source program that can then be compiled by any Java compiler. Java programs containing embedded SQL can be subjected to static analysis of SQL statments for the purposes of syntax checking, type checking and schema validation.
SQLJ supports only static SQL constructs. The counterpart to static SQL is dynamic SQL, a call interface for passing strings to a database as SQL commands. No analysis or checking of those strings is done until the database receives them at execution time. A dynamic SQL API for Java has been specified by JavaSoft, called JDBC. For detailed information about DB2 JDBC support, please see "Programming in JDBC".
SQLJ relies upon JDBC for support of dynamic SQL, and does not attempt to replicate the features of JDBC. Rather, SQLJ contains mechanisms that enable a Java programmer to easily move between the two environments and share state information (for example, connection contexts).
DB2 SQLJ support is provided by the DB2 Software Developer's Kit (DB2 SDK). Along with DB2 JDBC support provided by the DB2 Client Application Enabler (DB2 CAE), DB2 SQLJ support allows you to create, build, and run embedded SQL for Java applications, applets, stored procedures and user-defined functions (UDFs). These contain static SQL and use embedded SQL statements that are bound to a DB2 database.
The SQLJ support provided by the DB2 SDK includes:
sqlj.runtime sqlj.runtime.ref sqlj.runtime.profile sqlj.runtime.profile.ref sqlj.runtime.profile.util sqlj.runtime.error
For more information on the SQLJ translator, see "SQLJ Translator Reference". For more information on the db2profc and db2profp commands, see the Command Reference. For more information on the SQLJ runtime classes, visit the DB2 Java web page at:
http://www.software.ibm.com/data/db2/java
DB2 Trace Facilities
SQLJ programs access DB2 using the DB2 JDBC driver, which in turn uses the DB2 CLI/ODBC driver. Therefore, both the CLI/ODBC/JDBC trace facility and the DB2 trace facility, db2trc, can be used to diagnose problems. Details on how to take the above traces are explained in the Troubleshooting Guide.
The SQLJ runtime function includes a utility to install runtime call tracing capability into SQLJ programs. The utility operates on the profiles associated with a program. Suppose a program uses a profile called App_SJProfile0. Then, debugging would be installed into the program with the command:
profdb App_SJProfile0.ser
The profdb script uses the Java Virtual Machine to run the main() method of class sqlj.runtime.profile.util.AuditorInstaller. For more details on usage and options for the AuditorInstaller class, visit the DB2 Java web page at:
http://www.software.ibm.com/data/db2/java
DB2 SQLJ Usage Notes
sqlj.url=jdbc:db2:sample sqlj.driver=COM.ibm.db2.jdbc.app.DB2Driver sqlj.online=sqlj.semantics.JdbcChecker sqlj.offline=sqlj.semantics.OfflineCheckerIf 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.OfflineCheckerwhere dbname is the name of the database.
These options can also be set on the command line.
sqlj pgmname.sqlj
javac pgmname.java
db2profc -user=user-name -password=user-password -url=jdbc:db2:dbname
-prepoptions="bindfile using pgmname0.bnd package using pgmname0"
pgmname_SJProfile0.ser
db2profc -user=user-name -password=user-password -url=jdbc:db2:dbname
-prepoptions="bindfile using pgmname1.bnd package using pgmname1"
pgmname_SJProfile1.ser
...
java pgmname
profp pgmname_SJProfile0.ser
profp pgmname_SJProfile1.ser
...
db2profp -user=user-name -password=user-password -url=jdbc:db2:dbname
pgmname_SJProfile0.ser
db2profp -user=user-name -password=user-password -url=jdbc:db2:dbname
pgmname_SJProfile1.ser
...
where dbname is the name of the database.
NOLINEMACRO OPTLEVEL OUTPUT SQLCA TARGET WCHARTYPE CONNECT DISCONNECT SYNCPOINT SQLRULES SQLFLAG
The DRDA precompile/bind option DYNAMICRULES BIND can be specified to indicate that the authorization identifier used for the execution of positioned UPDATE/DELETE SQL statement is the cursor package owner. This DRDA precompile/bind option is not supported by DB2 Universal Database. Also, the positioned UPDATE/DELETE SQL statement is not a valid sub-statement in a Compound SQL statement.
#sql {begin compound atomic static
select count(*) into :OUT count1 from employee;
end compound}
i = { VALUES ( F(:x) ) };
and will be translated by the SQLJ translator and stored as
? = VALUES (F (?))in the generated profile.
DB2 will customize the VALUE statement into:
VALUES(F(?)) INTO ?
when connecting to a DB2 Universal Database database but into:
SELECT F(?) INTO ? FROM SYSIBM.SYSDUMMY1
when connecting to a DB2 for OS/390 database.
If we run the DB2 SQLJ profile customizer, db2profc, against a DB2 Universal Database database and generate a bindfile, we cannot use that bindfile to bind up to a DB2 for OS/390 database when there is a VALUES clause in the bindfile. This also applies to generating a bindfile 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 on Windows and OS/2, see Appendix D. "Building Applications for Windows and OS/2 Environments Updates". For detailed information on building and running SQLJ programs on UNIX platforms, see Building Applications for UNIX Environments.
Dynamic SQL provides greater flexibility than static SQL since a calling program has the ability to construct and process SQL strings that are dynamically created at runtime. This capability comes at a greater programming cost due to the increased complexity of code necessary to support flexible, dynamic operations. However, many applications do not require this level of complexity because the SQL commands they use are predetermined. Embedded SQL is better suited for these applications as it enables early error checking, allows for precompilation of SQL for faster execution at runtime, and significantly reduces program size and complexity.
Here are some major differences between the two:
prepStmt.prepareCall("{call fun(...)}"); //for stored procedures
prepStmt.prepareCall("{? = call fun(...)}"); //for stored functions
whereas SQLJ provides simplified notations:
#sql {call fun(...) }; //Stored procedure
// Declare x
...
#sql x = {VALUES(fun(...)) }; // Stored function
// where VALUES is the SQL construct
Programming languages containing Embedded SQL are called host languages. Java differs from the traditional host languages C, COBOL, and FORTRAN, in ways that significantly affect its embedding of SQL:
The following kinds of SQL constructs may appear in SQLJ programs:
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 are prefixed by a colon, :. A host variable contains an optional parameter mode identifier: IN, OUT, or INOUT, followed by a Java host variable that is a Java identifier for a parameter, variable, or field. 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 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
Static SQL statements in SQLJ appear in SQLJ clauses. SQLJ clauses represent the mechanism by which SQL statements in Java programs are communicated to the database.
Each SQLJ clause begins with the token #sql, which is not a legal Java identifier, and is terminated by a semicolon, and as such makes the clause and its SQL contents recognizable to an SQLJ translator.
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 and its purpose is to delete all of the 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 quotes), 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 written uppercase, and Java tokens are lowercase or mixed case. Throughout this document, the lowercase null is used to represent the Java "null" value, and the uppercase NULL to represent the SQL null value.
The following example SQLJ application uses static SQL to retrieve and update data from the EMPLOYEE table of the DB2 sample database. The program declares two cursors to retrieve data. After connecting to the database, cursor1 selects data from the EMPLOYEE table, which is printed out. After this, the table is updated, and then cursor2 extracts data from the updated table, which is also printed out. Finally, the changes are rolled back before the program ends.
import java.sql.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
#sql iterator App_Cursor1 (String empno, String firstnme) ;
#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 };
// display the result set
// cursor1.next() returns false when there are no more rows
System.out.println("Received results:");
while (cursor1.next())
{
str1 = cursor1.empno();
str2 = cursor1.firstnme();
System.out.print (" empno= " + str1);
System.out.print (" firstname= " + str2);
System.out.print ("\n");
}
cursor1.close();
// retrieve number of employee from the database
System.out.println("\nRetrieve the number of rows in employee table...");
#sql { SELECT count(*) into :count1 from employee };
if (1 == count1)
System.out.println ("There is " + count1 + " row in employee table.");
else
System.out.println ("There are " + count1 + " rows in employee table.");
// update the database
System.out.println("\n\nUpdate the database... ");
#sql { UPDATE employee set firstnme = 'SHILI' where empno = '000010' };
// retrieve the updated data from the database
System.out.println("\nRetrieve the updated data from the database...");
str1 = "000010";
#sql cursor2 = { SELECT firstnme from employee where empno = :str1 };
// 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 };
if (cursor2.endFetch()) break;
System.out.print (" empno= " + str1);
System.out.print (" firstname= " + str2);
System.out.print ("\n");
}
cursor2.close();
// rollback the update
System.out.println("\n\nRollback the update...");
#sql { ROLLBACK work };
System.out.println("Rollback done.");
}
catch( Exception e )
{
e.printStackTrace();
}
}
}
Each SQLJ executable clause requires, either explicitly or implicitly, a connection context object that designates the database connection at which the SQL operation specified in that clause will be executed. The connection context object is an optional expression, delimited by brackets, that immediately follows the token #sql. For example, in the following SQLJ clause, the connection context is the value of the Java variable myconn:
#sql [ myconn ] { UPDATE TAB2 SET COL1 = :w
WHERE :v < COL2 };
The connection context object designates a database at which the SQL statements will be executed, and the session and transaction in which they are executed. A connection context is an object of a connection context class, which is defined by means of an SQLJ connection clause. A connection context class has methods for opening a connection to a database, given a URL or other connection string, a user name, and password. At run time, an SQLJ program must call those methods to establish a database connection before any SQLJ clauses are executed. The following illustrates an SQLJ connection clause that defines a connection context class named Inventory:
#sql context Inventory;
| Note: | The connection context object implicitly specifies a database and associated schemas as per a JDBC Connection instance. If an SQLJ executable clause specifies an SQL Session management statement (e.g., SET SCHEMA), then that clause will not affect any other SQLJ executable clause. Session management directives are only in conjunction with a connection context object and these objects are initialized only at translation, installation, and customization times in a vendor specific manner. |
When an SQLJ clause contains an expression designating the connection context object on which it will be executed, then that clause is said to use an explicit connection. When the connection context object is omitted from a clause, then that clause is said to use the default connection. Portable applications should always use explicit connection contexts.
As an example, if an invocation of an SQLJ translator indicates that the default connection context class is class Green, then all SQLJ clauses that use the default connection will be translated as if they used the explicit connection context object Green.getDefaultContext().
Programs may install a connection context object as the default connection by calling setDefaultContext. For example:
Green.setDefaultContext(new Green(argv[0], autoCommit));
| Note: | argv[0] is assumed to contain a url naming a database, user ID, and password. autoCommit is a boolean flag that is true if auto commit mode is on, and false otherwise. |
The default connection context object for a program is stored in a static variable of the default connection context class. To avoid using static variables with some SQLJ programs, such as applets, reentrant libraries, and some multi-threaded programs, you can use SQLJ clauses with explicit connection contexts objects.
When an SQLJ program is executing inside a database as a stored procedure, or is otherwise executing in an environment that automatically provides a connection context, calls to method ConnectionContext.getDefaultContext always returns an object representing the schema in which the program is executing. An SQLJ program can detect whether it is executing in an environment that implicitly supplies a connection context by calling ConnectionContext.getDefaultContext before it calls ConnectionContext.setDefaultContext to install a connection context object. An execution environment that automatically supplies a connection context will return a non-null connection context object.
Schema checking using exemplar schemas. At translation time, a connection context class plays a different role. It symbolizes the "type" of database schema to which the SQLJ program will connect at run time. The notion of the "type of a database schema" is informal. It includes the names, and privileges associated with tables and views, the "shapes" of their rows, stored programs, and so forth. The type of a schema is symbolized by an exemplar schema, which is simply a database schema that contains the tables, views, programs, and privileges that would be required in order for the SQL operations in SQLJ clauses to execute successfully. An exemplar schema may be the actual runtime schema, or may be another schema that is a "typical" schema, in ways relevant to the SQLJ program being translated.
The invoker of an SQLJ translator must provide a mapping of connection context classes to exemplar schemas. An SQLJ translator connects to the exemplar schema in order to provide syntax checking, type checking and schema checking for all SQLJ clauses that will be executed in the connection context of the class "exemplified" by that schema. In that way, the exemplar schema represents the database schema to which the application will connect at runtime. It is the responsibility of the application developer to pick an exemplar schema that represents the run time schemas in relevant ways, e.g., having tables, views, stored functions, and stored procedures with the same names and types, and having privileges set appropriately.
If no appropriate exemplar schema is available, or if it is inconvenient to connect to a database during SQLJ program development, then the programmer may omit the exemplar schema for a connection type. SQLJ clauses to be executed on connections of that type will not then be schema checked at translation time, and will instead be checked later at installation or customization time.
The mapping of connection context classes to exemplar schemas is provided to an SQLJ translator in an implementation-dependent way, typically by pairing connection context class names with connect strings and passwords. For example, a client side SQLJ translator may require such mapping on the command line in an invocation of the translator. Those connect strings and passwords are then used as arguments of the connection context class constructors that establish a database connection to the exemplar schema.
Since the connection context is optional in an SQLJ clause, when the connection context is absent from an SQLJ clause there is a default connection context class specified. The clause is then checked against the exemplar schema corresponding to the class of the default connection context object for the program.
Here is a sample program demonstrating connection contexts:
import java.sql.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
import COM.ibm.db2.jdbc.app.*;
import COM.ibm.db2.app.*;
public class connect
{
/*********************
** Register Driver **
*********************/
static
{
try
{
Class.forName ("COM.ibm.db2.jdbc.app.DB2Driver").newInstance ();
}
catch (Exception e)
{
System.out.println ("\n Error loading DB2 Driver...\n");
e.printStackTrace ();
}
}
/*******************
** Main **
*******************/
public static void main (String args[]) throws SQLException
{
// URLs used in Connect to DB
String url = "jdbc:db2:sample";
String userid = "userid"; // update with your user ID
String password = "password"; // update with your password
// Get default connection context if it exists
DefaultContext ctx = DefaultContext.getDefaultContext();
// If no default connection context, connect to database
if (ctx == null)
{
try
{
Connection con = DriverManager.getConnection(url,userid,password);
// AutoCommit can be set to "true" or "false"
con.setAutoCommit(true);
ctx = new DefaultContext(con);
}
catch (SQLException e)
{
System.out.println("*** Error connecting to database.\n");
System.err.println(e) ;
System.exit(1);
}
// Set default context which will be used whenever connection context
// is not specified
DefaultContext.setDefaultContext(ctx);
System.out.println("*** Connected as ADM ID successfully.\n");
}
int SqlCode=0; // Variable to hold SQLCODE
int SqlState=0; // Variable to hold SQLSTATE
try
{
#sql {..... your SQL statement here ......};
}
/* Here's how you can check for SQLCODEs and SQLSTATE */
catch (SQLException e)
{
SqlCode = e.getErrorCode() // Get SQLCODE
SqlState = e.getSQLState() // Get SQLSTATE
if (SqlCode = -1234)
{
// Your code here to handle -1234 SQLCODE
}
else
{
// Your code here to handle other errors
}
System.err.println(e) ; // Print the exception
System.exit(1); // Exit
}
// More SQL statements ....
/************************************************************
* You can drop the connection context when it is no longer
* needed. This is similar to disconnecting from the database
* in a C program. If you do not drop the connection context,
* it will be dropped automatically when the program ends
*************************************************************/
if (ctx != null) ctx.close();
} // End main
} // End connect
A capability central to SQL is the ability to execute queries that retrieve a "result set" of rows from the database. An SQLJ clause may evaluate a query and return a result set iterator object containing the result set selected by that query. Depending on the type of the iterator object, it may be used with the FETCH..INTO idiom of Embedded SQL to extract data into host variables, or it may retrieve, through accessor methods, column data consisting of the names and types of columns returned by the query.
An SQLJ result set iterator is a Java object from which the data returned by an SQL query can be retrieved. In that role, it corresponds to the cursor of Embedded SQL, from which data are fetched. Unlike the cursor, however, an iterator is a first class object. An iterator can be passed as a parameter to a method, and can be used outside the SQLJ translation unit that creates it, without losing its static type for the purposes of type-checking of component interfaces.
An iterator has one or more columns with associated Java types. Names that are Java identifiers can optionally be provided for the iterator columns. The columns of an iterator (which have Java types) are conceptually distinct from the columns of a query (which have SQL types). SQLJ supports two mechanisms for matching iterator columns to query columns. They are bind by position and bind by name.
Bind by position means that the left to right order of declaration of the iterator columns places them in correspondence with the expressions selected in an SQL query. Traditional FETCH..INTO syntax is used to retrieve data from the iterator object into Java variables. Bind by name means that the name of each iterator column is matched to the name of a column returned by the SQL query, independent of the order in which that column appeared in the query. Named accessor methods are generated by the SQLJ translator for each column of the iterator. The name of an accessor method matches the name of a column returned by a query and its return type is the Java type of the iterator column. The FETCH..INTO syntax may not be used with an iterator of this type, as the accessor methods provide the mechanism for transferring the data.
An iterator declaration clause designates whether objects of that iterator type use bind by position or bind by name. The two styles of accessing result set data are mutually exclusive: an iterator class supports either bind by position or bind by name, but not both. Program development tools may prefer to generate SQLJ programs using bind by position, since these tools can generate SQLJ code that is "correct by construction". People writing SQLJ programs "by hand" may prefer to use bind by name, to make their applications resilient against changes to the program or database schema.
Positional binding to columns. The following is an example of an iterator class declaration that binds by position. It declares an iterator class called ByPos, with two columns of types String and int:
#sql public iterator ByPos (String, int);
Assume a table PEOPLE with columns FULLNAME and BIRTHYEAR:
TABLE PEOPLE ( FULLNAME VARCHAR(50),
BIRTHYEAR NUMERIC(4,0) )
An iterator object of type ByPos is used in conjunction with a FETCH..INTO statement to retrieve data from table PEOPLE as illustrated in the following example:
{
ByPos positer; // declare iterator object
String name = null;
int year = 0;
// populate it
#sql positer = { SELECT FULLNAME,
BIRTHYEAR FROM PEOPLE };
#sql { FETCH :positer INTO :name, :year };
while ( !positer.endFetch() )
{
System.out.println(name + " was born in " +
year);
#sql { FETCH :positer INTO :name, :year };
}
}
The predicate method endFetch() of the iterator object returns true when no more rows are available from the iterator (specifically, it becomes true following the first FETCH that returns no data).
The first SQLJ clause in the block above effectively executes its query and constructs an iterator object containing the result set returned by the query, and assigns it to variable positer. The type of the iterator object is derived from the assignment target, which is of type ByPos.
The second SQLJ clause in that block contains a FETCH..INTO statement. The SQLJ translator checks that the types of host variables in the INTO clause match the types of the iterator columns that correspond by position. The types of the SQL columns in the query are convertible to the types of the positionally corresponding iterator columns, according to the SQL to Java type mapping of SQLJ. Those conversions are statically checked at translation time if a database connection to an exemplar schema is provided to the translator.
Named binding to columns. The following is an example of an iterator class declaration that binds by name. It declares an iterator class called ByName, with columns named FULLNAME and BIRTHYEAR:
#sql public iterator ByName
(String fullNAME, int birthYEAR);
That iterator class can then be used as follows:
{
ByName namiter; // define iterator object
#sql namiter = { SELECT FULLNAME, BIRTHYEAR
FROM PEOPLE };
String s; int i;
// advances to next row
while ( namiter.next() )
{
i = namiter.birthYEAR();
// returns column named BIRTHYEAR
s = namiter.fullNAME();
// returns column named FULLNAME
System.out.println(s + " was born in " + i);
}
}
In this example, the first SQLJ clause constructs an iterator object of type ByName, as that is the type of the assignment target in that clause. That iterator has generated accessor methods birthYEAR() and fullNAME() that return the data from the result set columns with those names. The names of the generated accessor methods are an exact case sensitive match with their definitions on the iterator declaration clause. Matching a specific accessor method to a specific column name in the SELECT list expressions is performed using a case insensitive match. Two column names that differ only in case sensitivity must use the SQL AS clause to alias one of the column names in order to avoid ambiguity.
Method next() advances the iterator object to successive rows of the result set. It returns true when a next row is available, and false after it fails to retrieve a next row because the iterator contains no more rows.
A Java compiler will detect type mismatch errors in the uses of column accessor methods. Additionally, if a connection to an exemplar schema is provided at translation time, then the SQLJ translator will statically check the validity of the types and names of the iterator columns against the SQL queries associated with it.
Providing names for columns of queries. When the expressions selected by a query are unnamed, or have SQL names that are not legal Java identifiers, then SQL column aliases may be used to name them. Consider a table named "Trouble!" with a column called "Not a legal Java identifier":
CREATE TABLE "Trouble!"
(
"Not a legal Java identifier" VARCHAR(10),
col2 FLOAT
)
The following line generates an iterator class called xY. The iterator declaration clause may appear wherever a Java class definition may appear:
#sql iterator xY (String x, double Y);
The SQLJ clause in the following block uses column aliases to associate that column's name with an expression in the query:
{
xY it;
#sql it = { SELECT "Not a legal Java identifier" AS "x",
COL2 * COL2 AS Y
FROM "Trouble!" };
while (it.next()) { System.out.println(it.x() + it.Y()); }
}
The first line declares a local variable of that iterator class. The second line initializes that variable to contain a result set obtained from the specified query. The while() loop calls the column accessor methods of the iterator to obtain and print data from its rows.
Databases may contain stored procedures and stored functions. User-defined procedures and functions 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 the CALL statement. For example:
#sql { CALL SOME_PROC(:INOUT myarg) };
Stored procedures may have IN, OUT, or IN OUT 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 stored function by means of the SQL VALUES construct. For example, assume a stored 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) ) };
}
| Note: | DB2 does not support Java stored procedures and Java user-defined functions (UDFs) accessing DB2 databases on HP-UX and SCO UnixWare servers. |
The sections that follow discuss more advanced programming techniques including multiple connections, dynamic SQL and multi-threading.
SQLJ supports connecting to multiple schemas at the same time. The various schemas used at runtime are modeled as distinct connection context classes in SQLJ programs, which allows type checking using the same schemas at translation time. The following program demonstrates the use of multiple contexts by connecting to two DB2 databases, sample and sample2.
import java.sql.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
import COM.ibm.db2.jdbc.app.*;
import COM.ibm.db2.app.*;
public class multicon
{
/*********************
** Register Driver **
*********************/
static
{
try
{
Class.forName ("COM.ibm.db2.jdbc.app.DB2Driver").newInstance ();
}
catch (Exception e)
{
System.out.println ("\n Error loading DB2 Driver...\n");
e.printStackTrace ();
}
}
/*******************
** Main **
*******************/
public static void main (String args[]) throws SQLException
{
/*********************************
** URLs used to connect to DBs **
*********************************/
String url1 = "jdbc:db2:sample"; // database SAMPLE
String url2 = "jdbc:db2:sample2"; // database SAMPLE2
String userid = "userid"; // change the userid to yours
String password = "password"; // change the password to yours
System.out.println("*** Begin multicon ***\n");
DefaultContext ctx1 = null; // connection context 1
DefaultContext ctx2 = null; // connection context 2
/*********************************
** Connect to SAMPLE database **
*********************************/
try
{
Connection con = DriverManager.getConnection(url1,userid,password);
con.setAutoCommit(true);
ctx1 = new DefaultContext(con);
}
catch (SQLException e)
{
System.out.println("*** Error: could not connect to SAMPLE db.\n");
System.err.println(e) ;
// System.exit(1);
}
/* If you want to set a default connection, uncomment the next line of code
out. If you have a default connection, it will be used whenever a connection
context is not specified */
// DefaultContext.setDefaultContext(ctx1);
System.out.println("*** Got a connection to SAMPLE db successfully.\n");
/**********************************
** Connect to SAMPLE2 database **
***********************************/
try
{
Connection con = DriverManager.getConnection(url2,userid,password);
con.setAutoCommit(true);
ctx2 = new DefaultContext(con);
}
catch (SQLException e)
{
System.out.println("*** Error: could not connect to SAMPLE2 db.\n");
System.err.println(e) ;
// System.exit(1);
}
// You could also set ctx2 as default connect INSTEAD of ctx1 if you
// want to use default connection context
// DefaultContext.setDefaultContext(ctx2);
System.out.println("*** Got a connection to SAMPLE2 db successfully.\n");
/******************************************************************************
** Counting number of tables/views in SAMPLE database using connection ctx1 **
******************************************************************************/
short count=-1;
try
{
#sql [ctx1] {SELECT COUNT(*) INTO :count FROM SYSCAT.TABLES };
}
catch (SQLException e)
{
System.out.println("*** Error Selecting from SAMPLE's SYSCAT.TABLES.\n");
System.err.println(e) ;
// System.exit(1);
}
System.out.println("*** Database SAMPLE has " + count + " tables and views.\n");
/******************************************************************************
** Counting number of tables/views in SAMPLE database using connection ctx2 **
******************************************************************************/
count=-1; // reset count
try
{
#sql [ctx2] {SELECT COUNT(*) INTO :count FROM SYSCAT.TABLES };
}
catch (SQLException e)
{
System.out.println("*** Error Selecting from SAMPLE2's SYSCAT.TABLES.\n");
System.err.println(e) ;
// System.exit(1);
}
System.out.println("*** Database SAMPLE2 has " + count + " tables and views.\n");
if (ctx1 != null) ctx1.close(); // "disconnect" from SAMPLE
if (ctx2 != null) ctx2.close(); // "disconnect" from SAMPLE2
} // End main
} // End multicon
The execution semantics of SQL operations can be queried and modified via the execution context associated with the operation. An execution context exists as an instance of class sqlj.runtime.ExecutionContext.
The following ExecutionContext attributes control the execution environment of SQL operations. The getXXX and setXXX methods read and change the XXX value. Once set, they affect all SQL operations subsequently executed on that execution context.
The following ExecutionContext attributes describe the results of the last SQL operation executed:
An execution context is associated either explicitly or implicitly with each executable SQL operation appearing in an SQLJ program. An execution context may be supplied explicitly as an argument to each SQL operation:
ExecutionContext execCtx = new ExecutionContext();
#sql [execCtx] { DELETE FROM emp WHERE sal > 10000 };
When explicit execution contexts are used, each SQL operation may be executed using a different execution context instance. If an explicit connection context is also being used, both may be passed as arguments to the SQL operation:
#sql [connCtx, execCtx] { DELETE FROM emp WHERE sal > 10000 };
If an execution context is not supplied explicitly as an argument to an SQL operation, a default execution context is used implicitly. The default execution context for a particular SQL operation is obtained via the getExecutionContext() method of the connection context used in the operation. For example:
#sql [connCtx] { DELETE FROM emp WHERE sal > 10000 };
uses the execution context associated with the connection context given by connCtx. When neither a connection context nor an execution context is explicitly supplied, the execution context associated with the default connection context is used.
The following code demonstrates the use of some ExecutionContext methods:
{
ExecutionContext execCtx = new ExecutionContext();
// Wait only 3 seconds for operations to complete
execCtx.setQueryTimeout(3);
try {
// delete using explicit execution context
// if operation takes longer than 3 seconds,
// SQLException is raised
#sql [execCtx] { DELETE FROM emp WHERE sal > 10000 };
System.out.println
("removed " + execCtx.getUpdateCount() + " employees");
}
catch(SQLException e) {
// Assume a timeout occurred
System.out.println("SQLException has occurred with" +
" exception " + e );
}
}
SQLJ can be used to write multi-threaded applications. The SQLJ runtime supports multiple threads sharing the same connection context. However, SQLJ programs are subject to synchronization limitations imposed by the underlying DBMS implementation. If a DBMS implementation mandates explicit synchronization of statements executed in a specific connection, then an SQLJ program using that implementation would require a similar synchronization of SQL operations.
Whereas connection contexts may be safely shared between threads, execution contexts should not be shared. If an execution context is shared, the results of an SQL operation performed by one thread will be visible in the other thread. If both threads are executing SQL operations, a race condition may occur in which the results of an execution in one thread are overwritten by the results of an execution in the next thread before the first thread has processed the original results. Furthermore, if a thread attempts to execute an SQL operation using an execution context that is currently being used to execute an operation in another thread, a runtime exception is raised. To avoid such problems, each thread should use a distinct execution context whenever an SQL operation is executed on a shared connection context.
The SQLJ language provides direct support for static SQL operations that are known at the time the program is written. If some or all of a particular SQL statement cannot be determined until runtime, it is a dynamic operation. To perform dynamic SQL operations from an SQLJ program, use JDBC. A ConnectionContext object contains a JDBC Connection object which can be used to create JDBC Statement objects needed for dynamic SQL operations.
Every SQLJ ConnectionContext class includes a constructor that takes as an argument a JDBC Connection. This constructor is used to create an SQLJ connection context instance that shares its underlying database connection with that of the JDBC connection.
Every SQLJ ConnectionContext instance has a getConnection method that returns a JDBC Connection instance. The JDBC Connection returned shares the underlying database connection with the SQLJ connection context. It may be used to peform dynamic SQL operations as described in the JDBC API.
Session Sharing. The interoperablity methods described above provide a conversion between the connection abstractions used in SQLJ and those used in JDBC. Both abstractions share the same database session (i.e., the underlying database connection). Accordingly, calls to methods that affect session state on one object will also be reflected in the other object, as it is actually the underlying shared session that is being affected.
JDBC defines the default values for session state of newly created connections. In most cases, SQLJ adopts these default values. However, whereas a newly created JDBC connection has auto commit mode on by default, an SQLJ connection context requires the auto commit mode to be specified explicitly upon construction.
Connection Resource Management. Calling the close method of a connection context instance causes the associated JDBC connection instance and the underlying database connection to be closed. Since connection contexts may share the underlying database connection with other connection contexts and/or JDBC connections, it may not be desirable to close the underlying database connection when a connection context is closed. A programmer may wish to release the resources maintained by the connection context (for example, statement handles) without actually closing the underlying database connection. To this end, connection context classes also support a close method which takes a boolean argument indicating whether or not to close the underlying database connection: the constant CLOSE_CONNECTION if the database connection should be closed, and KEEP_CONNECTION if it should be retained. The variant of close that takes no arguments is a shorthand for calling close(CLOSE_CONNECTION).
If a connection context instance is not explicitly closed before it is garbage collected, then close(KEEP_CONNECTION) is called by the finalize method of the connection context. This allows connection related resources to to be reclaimed by the normal garbage collection process while maintaining the underlying database connection for other JDBC and SQLJ objects that may be using it. Note that if no other JDBC or SQLJ objects are using the connection, then the database connection will also be closed and reclaimed by the garbage collection process.
Both SQLJ connection context objects and JDBC connection objects respond to the close method. When writing an SQLJ program, it is sufficient to call the close method on only the connection context object. This is because closing the connection context will also close the JDBC connection associated with it. However, it is not sufficient to close only the JDBC connection returned by the getConnection method of a connection context. This is because the close method of a JDBC connection will not cause the containing connection context to be closed, and therefore resources maintained by the connection context will not be released until it is garbage collected.
The isClosed method of a connection context returns true if any variant of the close method has been called on the connection context instance. If isClosed is true, then calling close has no effect, and calling any other method is undefined.
ANSI/ISO specifies a "standard embedded language", for FORTRAN, PL/1, COBOL, ADA, MUMPS, and C. An embedded program is a mixture of embedded SQL statements and host language statements.
An embedded SQL statement always has an SQL prefix, usually EXEC SQL, and a terminator appropriate to its host language, for example, a semi-colon for C, or a new line character for Fortran.
Elements of Embedded SQL fall into four groups, which are treated differently in SQLJ than they are in other embedded languages:
The following elaborates the differences summarized above between the elements of SQLJ and other embedded languages:
Explicit manipulation of connection objects is supported for Java programs that need to avoid hidden global state (e.g., Java "static variables") that would be used to implement the SQL connection statement. In particular, Java applets and other multi-threaded programs are usually coded to avoid contention of global state. Such programs will store connection objects in local variables and mention them explicitly in SQLJ clauses.
SQLJ allows the possibility that a Java program can manipulate multiple connection objects, connected to different databases. When a program manipulates multiple connections, they are mentioned explicitly in the SQLJ clauses, so they are regular Java objects.
SQLJ does not define a host variable definition section. SQLJ translators can take advantage of the portability and component software available for Java in order to have greater parsing ability than traditional pre-compilers, so that the DECLARE SECTION is not required for the purpose of confining definitions of host variables to a small portion of the host program. Instead, any Java variable, parameter, or field (of an object) may be used as a host variable.
EXEC SQL WHENEVER exception_condition
GOTO program_label;
EXEC SQL WHENEVER exception_condition
CONTINUE;
The Java language does not support the goto statement, therefore the direct transliteration of the above construct into Java is not possible. Instead, Java provides a try..catch statement that associates a handler for certain exceptions in the Java block in which those exceptions might be raised. For example, assume an exception called e:
try { block_that_may_throw_exception_e }
catch (Exception e) { block_that_handles_exception_e }
In addition, Java has well developed rules for declaring and handling exceptions, thus the EXEC SQL WHENEVER statement does not add value. Other object-oriented languages have facilities for declaring and handling exceptions, similar to those in Java.
JDBC has defined an exception, globally named java.sql.SQLException, as the superclass of exceptions that are returned from SQL. SQLJ follows that precedent in order to facilitate interoperability between static SQL and dynamic SQL.
EXEC SQL FETCH cursor1 INTO :var1,:var2,:var3;
SQLJ supports a modified version of the FETCH statement. It also supports access to columns by name, through generated methods with the names and types of the columns.
EXEC SQL OPEN cursor1;
SQLJ does not have an OPEN operation to open or re-open iterator objects. SQLJ does not name a static query, nor treat it as data. Instead, a query returns an iterator object that is manipulated as data. Of course, a programmer may, in effect, name a query by writing it in an SQLJ clause in the body of a method. Methods are called by their names, and can return result set objects as their values.
Use the script sqlj to run the translator as follows:
sqlj [ options ] filelist
Alternatively, you can run the translator by using its class name:
java sqlj.translator.Main [ options ] filelist
The filelist is a list of file names separated by spaces:
file1.sqlj [file2.sqlj] ... [foo1.java] [foo2.java]...
The files with the .java extension are included to resolve type references, but no output files are created for them. The files with the .sqlj extension include SQLJ clauses. The translator creates .java files, as well as .ser (serialized object) files for them.
The following applies to all options:
-user=scott -user=myaccount
SQLJ uses the second value, myaccount.
Property Files
Property files can be used to supply options to the translator. Options in a property file appear one per line. Options have the same syntactic form as those appearing on the command line, except that the token sqlj. replaces the initial hyphen. SQLJ ignores properties without the sqlj. prefix in the property file. This allows several programs to share one properties file. Empty lines are ignored. Lines that start with # are comments. Here is an example of an sqlj.properties file:
sqlj.user=scott sqlj.driver=db2.jdbc.driver.DB2Driver
SQLJ processes the options in a property file in order from first to last. A later entry overrides an earlier entry.
SQLJ looks for files called sqlj.properties to use as property files when it starts to run. It looks for them in three places, in the following order:
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's home or Java home directories.
SQLJ starts by setting all options to their default values, if any. It then reads any default property files it finds, using settings it finds in them to override the original defaults. Finally it looks for options on the command line and uses them to override the settings it has so far. It processes options on the command line from left to right, treating property files specified with the -props option on the command line as if their contents were specified inline.
General Options
In the following, all options are described as if they were given on the command line. However, all options except for -props, -help and -version may also appear in a properties file.
-help
The help option causes SQLJ to list all translator options in effect at that time.
| Note: | The output display contains these lines for each option:
|
-version
Displays the build version.
-dir
The dir option specifies the directory for generated files. For example:
-dir=java/files
If you do not specify dir, SQLJ uses the current directory. The behavior of the dir option is similar to the behavior of the -d option of javac. Suppose we have the files File1.sqlj and File2.sqlj. File1.sqlj has no package declaration. File2.sqlj is in the sqlj.demo package. If sqlj is invoked with the option -dir=/src, then File1.java is created in /src, but File2.java is created in /src/sqlj/demo. If no dir option is specified, then the output file directory is the same as that of the input file.
-warn
You can specify a list of flags for turning warnings on or off with this option. Several values for the warn option must be combined into a single, comma-separated string. SQLJ applies the specified flags in the order in which they appear on the command line. Permitted flags are all, none, verbose, noverbose, null, nonull, precision, noprecision, portable, noportable, strict, nostrict. Default value is verbose. For instance:
-warn=none,null,precision
first turns off all warnings, then turns nullability and precision on. The possible values of the warn option are:
-props
The props option specifies the name of a property file from which to read options. For example:
-props=myapplic.properties
Connection Options
These options specify the database connection for online checking. All of these options (except for driver) may be tagged with a ConnectionContext type:
-option@ConnectionContextType=value
This permits the use of separate exemplar schemas for each of the connection contexts. If you omit the connection context type, when specifying one of these options, the value will be used for any SQL statements that use the default connection context. If no option value is given at a specific ConnectionContextType, then the option value for the default connection context is used.
-user
The user option specifies the username for connecting to a database in order to perform semantic analysis of the SQL expressions embedded in a SQLJ program. It contains the username, for example:
-user=scott
The user command line option may include a connection context type. For example:
-user@Ctx1=scott
Whenever a username is required for the connection to a database context Ctx1, SQLJ uses the user option that was tagged with Ctx1. If it can't find one, SQLJ issues a message and looks for an untagged user option to use instead.
Specifying a user value indicates to SQLJ that online checking is to be performed. If you do not specify the user option, SQLJ does not connect to the database for semantic analysis. There is no default value for the user option.
If you have turned on online checking by default (for example, by specifying -user=scott), then in order to disable online checking for a particular connection context type Ctx2, you have to explicitly give an empty user name:
-user@Ctx2=
-password
The password option specifies a password for the user. The password will be requested interactively if it is not supplied. This option can be tagged with a connection context type. The two forms are:
-password=tiger -password@Ctx1=tiger
-url
This sub-option specifies a JDBC URL for establishing a database connection:
-url=jdbc:db2:sample
Semantic Checking Options
These options specify the characteristics of offline and online SQL checking.
-offline
Offline checking assumes that there is no connection to the database so that only SQL syntax and usage of Java types are checked. The offline option specifies the Java class that implements the SQL checking component of SQLJ for offline checking. This option permits customized checking for diverse databases by means of the checker class, sqlj.semantics.OfflineChecker.
The offline option can be tagged with a connection context:
-offline@myconnect=sqlj.semantics.OfflineChecker
-online
Online checking assumes that the database connection exists. You must have specified a user ID by means of the user option for checking to actually occur. The online checker passes DML statements to the database for syntactic and semantic analysis, in addition to the features of the offline checker. The online checker also checks stored functions and procedures for overloading by means of the checker class, sqlj.semantics.JdbcChecker.
The online option specifies the Java class that implements the SQL checking component of SQLJ via database connections to exemplar schemas. In a similar way to JDBC driver registration, the checker is queried as to whether it is able to perform semantic analysis for the given connection:
-online=sqlj.semantics.JdbcChecker
The JDBC online checker checks the signature of stored function or procedure calls and matches it with the JDBC types. It also determines the ResultSetMetaData for SELECT statements. SELECT statements are executed to determine the ResultSetMetaData. In order to reduce the size of the returned result set to 0, the WHERE clause is added or modified to read WHERE 1=2. This option can also be tagged with a connection context type:
-online@myconnect=sqlj.semantics.JdbcChecker
-cache
This option can be used to turn on caching of the results of the online checking in order to avoid database connections during subsequent precompilation runs. The analysis results are cached in the file, SQLChecker.cache, in the current directory. The cache may be emptied simply by removing this file.
The cache holds a serialized representation of all SQL statements that have been translated without error or warning messages, with the statements' parameters, return type, the translator settings and modes inferred about the parameters.
The cache is cumulative, adding new statements. If you are just fixing bugs in the Java source, you will not have to re-connect to the database. The boolean value for the cache option can be specified as yes, no, true, false, on, off, 1, or 0. Caching of semantic analysis results is turned off by default. Example:
-cache=true
-default-block-mode
Specifies the default mode of host variables occuring in an SQL block. It is used during offline checking when the mode cannot be determined. During online checking, the actual mode must correspond to the setting implied by this flag, unless the setting unknown is used. Possible settings are unknown, inout, in, and out. The default value is in. Example:
-default-block-mode=unknown
-default-function-mode
Specifies the default mode of host variables occuring in arguments of a stored function invocation. It is used during offline checking when the mode cannot be determined. During online checking, the actual mode must correspond to the setting implied by this flag, unless the setting unknown is used. Possible settings are unknown, inout, in, and out. The default value is in. Example:
-default-function-mode=unknown
-default-procedure-mode
Specifies the default mode of host variables occuring in arguments of a stored function invocation. It is used during offline checking when the mode cannot be determined. During online checking, the actual mode must correspond to the setting implied by this flag, unless the setting unknown is used. Possible settings are unknown, inout, in, and out. The default value is in. Example:
-default-procedure-mode=out