Nonstop Database, HiRDB Version 9 UAP Development Guide
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.
The following table shows the SQL statement coding formats.
Table 19-2 SQL statement coding formats
Function | Format | Purpose |
---|---|---|
SQL execution | #sql [context] { SQL-statement } ; |
Executes an SQL statement. The SQL statements that can be used differ for the standard interface edition and the native interface edition. For details, see 19.3.3 SQL statements that can be used in SQLJ. |
Declaration of an iterator class with column specification |
|
Declares the class to be used for cursor declaration. Cannot be used in a FETCH statement. |
Declaration of an iterator class with a position specification |
(data-type,...) ; |
Declares the class to be used in the cursor declaration. This function is used in a FETCH statement. |
Declaration of a connection class | #sql modifier context class-name ; |
Declares the class to be used for connection. |
Declaration of a cursor | #sql iterator-object = { SELECT-statement } ; |
Defines and opens a cursor. |
Conversion of a result set |
#sql [context] iterator-object = {CAST :JDBC-result-set} ;
|
Converts a JDBC result set into one that can be used by SQLJ. |
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.
Name | Details |
---|---|
MaxRows | Maximum number of rows to be returned from a search. |
MaxFieldSize | Maximum size of data in units of bytes to be returned in columns and OUTPUT variable value. |
QueryTimeout | Maximum wait time until SQL execution is completed. This is invalid in HiRDB. |
UpdateCount | Number of updated, inserted, or deleted rows (reference only). |
SQLWarnings | Correspond to SQLWARN0-SQLWARNF (reference only). |
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 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 edition. If the statement is used, a translation error results.
The CONNECT and DISCONNECT statements can be used in the native interface edition but not in the standard interface edition. For both the standard interface and native interface editions, 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.
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 9 SQL Reference.
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.