Nonstop Database, HiRDB Version 9 UAP Development Guide
Because SQLJ has no DECLARE CURSOR, OPEN, or CLOSE statements, cursor declaration, opening, and closing must be coded as Java instructions. During this step, an iterator object is used in place of a cursor name. Because the iterator object is declared as a reference variable to an object, the same naming rule and valid range as in the Java rules apply here.
Depending on the iterator object type used, the retrieval result can be obtained using or not using a FETCH statement. A FETCH statement uses an object in the iterator type with a position specification and cannot use an object in the iterator type with a column name specification.
The method for describing a retrieval using a FETCH statement is explained as follows.
For the standard interface edition, define a class for an iterator with a position specification and declare an iterator object. class-name indicates a Java identifier. data-type-N indicates the data type of a Java variable that stores the N-th retrieval item in the FETCH statement.
#sql modifier iterator class-name (data-type-1,data-type-2,...) ; modifier class-name iterator-object ; |
#sql modifier iterator class-name [ implements JP.co.Hitachi.soft.HiRDB.pdjpp.runtime.ForUpdate ] [ with keyword=setting-value,...] (data-type-1,data-type-2,...) ; modifier class-name iterator-object ; |
If an iterator is used in an UPDATE or DELETE statement, the JP.co.Hitachi.soft.HiRDB.pdjpp.runtime.ForUpdate interface is inherited.
keyword in the WITH clause indicates the function of the iterator. Only a constant can be specified. The following table shows the combinations of keyword in the WITH clause and the setting values.
Table 19-7 Combinations of keyword in the WITH clause and setting values
Keyword in the WITH clause | Function | Setting |
---|---|---|
holdability | Indicates a holdable cursor. | TRUE |
updateColumns | Indicates the column to be updated. | "column-name,column-name,..." |
Substitute the result set from the SELECT statement into the declared iterator object.
#sql [context] iterator-object = { SELECT-statement } ; |
Specify an iterator object instead of a cursor name and execute a FETCH statement. The iterator object must be preceded by a colon.
#sql [context] { FETCH :iterator-object INTO :variable-1,:variable-2,...} ; |
Invoke the endFetch method for the iterator object and determine whether the result is NOT FOUND. If there is no row to be retrieved, true is returned. If the next row is found, false is returned. If the endFetch method is invoked after the cursor is closed, true is returned.
while(! iterator-object.endFetch()) { processing-on-the-extracted-row } |
To close the cursor, invoke the close method.
iterator-object.close() ; |
An example of a retrieval using a FETCH statement follows:
#sql public iterator ByPos(String, int); : { ByPos positer; String name = null; int code = 0; #sql positer = { SELECT PNAME,PCODE FROM STOCK }; #sql { FETCH :positer INTO :name,:code }; while( !positer.endFetch() ){ System.out.println(name + ":" + code); #sql { FETCH :positer INTO :name,:pcode }; } positer.close(); } |
Using the fields in the iterator with a column name specification, read out each column of the retrieval result.
Define the same name (not case sensitive) as the retrieval item as the class field. For the data type, specify the data type of the Java variable that receives the retrieval result. This class cannot be used for the native interface.
If the retrieval item is a value expression, a column name that includes a character that cannot be used in Java, for example, use an AS clause to define an alias for the retrieval item, and use that alias.
#sql modifier iterator class-name (data-type-1 column-name-1, data-type-2 column-name-2,...) ; modifier class-name iterator-object; |
Substitute the result set from the SELECT statement into the declared iterator object.
#sql [context] iterator-object = { SELECT-statement } ; |
Invoke the next method for the iterator object and determine whether the result is NOT FOUND. If the result is NOT FOUND, TRUE is returned. If a row is found, FALSE is returned. After the cursor is opened, it is not positioned on the first line of the retrieval result until the first next method is executed.
while(iterator-object.next()){ processing-on-the-extracted-row } |
Read data out from each field of the iterator object. If the result is NOT FOUND or if data is read out after the cursor is closed, the result is undetermined. If data is read out when the data type of the field is the Java basic data type and the retrieval result is a null value, the SQLNullException object occurs.
Data substituted into a field is not reflected in the database.
variable-1 = iterator-object.column-name-1 ; variable-2 = iterator-object.column-name-2 ; ... |
To close the cursor, invoke the close method for the iterator object.
iterator-object.close() ; |
An example of retrieval without using a FETCH statement follows:
Example:
#sql public iterator ByName(String pname, int pcode); : { ByName nameiter; String s; int i; #sql nameiter = { SELECT PNAME, PCODE FROM STOCK }; while( nameiter.next() ){ s = nameiter.pname(); i = nameiter.pcode(); System.out.println(s + ":" + i); } nameiter.close(); } |
For the native interface, a cursor can be used to update data.
To use an UPDATE or DELETE statement to manipulate the row on which the cursor is positioned, specify an iterator instead of a cursor name. Note that when the class for the iterator is being defined, it must inherit the ForUpdate interface.
#sql [context] { DELETE-statement WHERE CURRENT OF :iterator-object } ; #sql [context] { UPDATE-statement WHERE CURRENT OF :iterator-object } ; |
An example of an update that uses an iterator follows:
#sql public iterator ByPos implements JP.co.Hitachi.soft.HiRDB.pdjpp.runtime.ForUpdate (String, int); : { ByPos positer; String name = null; int year = 0; int newyear; #sql positer = { SELECT FULLNAME, BIRTHYEAR FROM PEOPLE }; #sql { FETCH :positer INTO :name,:year }; while( !positer.endFetch() ){ newyear=year+10; #sql { UPDATE PEOPLE SET YEAR=:newyear WHERE CURRENT OF :positer; }; } positer.close(); } |
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.