Each SQL statement must be enclosed between the SQL leading character string (#sql) and the SQL trailing character (;). The SQL statement itself must further be enclosed between curly brackets. Connection class and cursor declarations must also be enclosed between the SQL leading character string and the SQL trailing character.

Table 18-2 shows the SQL statement coding formats.

Table 18-2 SQL statement coding formats

#sql [context] { SQL-statement } ;

       (data-type,...) ;

#sql modifier context class-name ;

#sql iterator-object = { SELECT-statement } ;

#sql [context] iterator-object
     = {CAST :JDBC-result-set} ;

Notes

modifier

Combination of private, public, protected, final, abstract, protected, static, native, synchronized, transient, and volatile.

context

{connection-context|connection-context,execution-context|execution-context}

keyword

holdability or updateColumns

value

true, false, or "column-name-1,column-name-2,..."

data-type

Java data type

column-name

Retrieval item

When you use the multi-connection facility, insert the connection context surrounded by square brackets between the SQL leading character string and the SQL statement, to explicitly specify the connection to be used. An example follows:

 #sql [connCtx] { DELETE FROM EMP WHERE SAL > 1000};

If no connection context is explicitly specified, the default connection context is assumed.

In SQLJ, a user can explicitly specify an execution environment instead of using the default one. To specify an execution environment, insert the execution connection context surrounded by square brackets between the SQL leading character string and the SQL statement.

If SQL statements are simultaneously being executed in multiple threads for a single connection, using separate multiple execution environments can prevent an execution result from being overwritten by another SQL statement. An example follows:

 ExecutionContext execCtx = new ExecutionContext();
 try {
  #sql [execCtx] { DELETE FROM STOCK WHERE PCODE > 1000 };
  System.out.println
   ("removed " + execCtx.getUpdateCount() + "goods");
 }
 catch(SQLException e){
  System.out.println("SQLException has occurred with "+ " exception " + e);
 }

If no execution connection context is explicitly specified, the default execution environment is used.

The values described in the following table are maintained in the execution environments. These values are set using the set<name> method and determined using the get<name> method.

If multi-connection is also specified, the connection context and execution connection context must be specified in that order, delimited by a comma. An example follows:

 #sql [connCtX, execCtx] { DELETE FROM STOCK WHERE PCODE > 1000 };

In SQLJ, BEGIN DECLARE SECTION for declaring embedded variables is not used.

Any variables, parameters, and object fields can be used as embedded variables. In an SQL statement, a variable is described as ":variable-name" with a colon at the front. The colon can be separated by blank spaces from the variable name.

The IN, OUT, or INOUT parameter of a CALL statement is described as ":{IN|OUT|INOUT}variable-name".

Additionally, in SQLJ, you can use ":(expression)" as an embedded variable. The expression must be enclosed by parentheses. This is a Java method and not an SQL method. An example follows:

#sql { SELECT COL1, COL2 FROM TABLE1 WHERE :(x[--i]) > COL3 };

SQLJ has no indicator variable. Therefore, to set a null value for an embedded variable, use the Wrapper type defined in the sql.lang package instead of the basic data type. If a null value is received by a Java variable of the basic data type, the SQLNullException exception occurs.

SQLJ cannot handle exceptions from an embedded SQL WHENEVER statement. Therefore, Java exception handling (try...catch) is used instead of WHENEVER. An example follows:

 try{
    #sql { DELETE FROM STOCK WHERE PCODE > 1000 };
 }
 catch(SQLException e){
    System.out.println("SQLCODE:" + e.getErrorCode() +
                       "\nERRMSG:" + e.getMessage() );
 }

If an error occurs during SQL execution, the JDBC exception object (java.sql.SQLException) is issued.

SQLCODE, SQLSTATE, and error messages are stored in exception objects, and their values can be obtained using the getErrorCode, getSQLState, and getMessage methods.

In SQLJ, only static SQL statements can be described. Dynamic SQL statements cannot be described.

To use a dynamic SQL statement, use the JDBC API.

You can use a CAST statement to convert and read out the result set of a dynamic cursor created using the JDBC API as the result set of an SQLJ cursor. An example follows:

 
 #sql iterator Employees(String ename, double sal);
 Statement stmt=conn.createStatement();
 String Query="SELECT pname, pcode FROM stock WHERE pcode > 1000";
 ResultSet rs=stmt.executeQuery(query);
 Employees emps;
 #sql emps ={CAST :rs };
 

The CAST statement cannot be used with the native interface version. If the statement is used, a translation error results.

The CONNECT and DISCONNECT statements can be used in the native interface version but not in the standard interface version. For both the standard interface and native interface versions, Java instructions can be used to connect to or disconnect from a HiRDB server.

In HiRDB embedded SQL statements, an alarm is issued in the following cases. In contrast, exceptions occur in SQLJ.

• In a single-row SELECT statement, the number of search items does not match the number of variables specified in an INTO clause.
• In a single-row SELECT statement, the retrieval result has zero rows.
• In a single-row SELECT statement, the retrieval result has multiple rows.
• In a FETCH statement, the number of search items does not match the number of variables specified in an INTO clause.
• The number of columns defined by an iterator with a position specification does not match the number of retrieval items.
• The number of columns defined by an iterator with a column name specification is greater than the number of retrieval items.

Comments (/*-*/) described between the SQL leading character string and the SQL trailing character are deleted. However, in cursor declaration and SQL statement execution, the SQL optimization specification (/*>>-<<*/) described between curly brackets is not deleted and handled as an SQL statement. All other SQL optimization specifications (/*>>-<<*/) are treated as comments. For details on comments and SQL optimization specifications inside SQL statements, see the manual HiRDB Version 8 SQL Reference.