Format of the SQL script file used by the GeneratePureQueryXml utility

An SQL script file contains SQL statements and optional cursor attribute information used when running an SQL statement. You can create a pureQueryXML file from the SQL script file using the GeneratePureQueryXml utility or the workbench.
In the SQL script file, The following are defaults for comment lines and SQL statement terminators that can be changed when using the GeneratePureQueryXml utility:
  • Two dashes (--) start a comment line.
  • A semicolon terminates an SQL statement.
Comments that start with -commentStart indicator can contain key value pairs that specify cursor attributes for the first SQL statement that follows the comments.

Comment can also be enclosed in /* and */. Cursor attributes specified in comment text enclosed by /* and */ are ignored.

Cursor attribute parameters

The following are the supported cursor attribute parameters and values that can be used in an SQL script file:

resultSetHoldability
Optional. Specifies the ResultSet holdability. You can specify one of the following values for resultSetHoldability:
  • CLOSE_CURSORS_AT_COMMIT sets the result set holdablity to close at commit. This value sets the JDBC value java.sql.ResultSet.CLOSE_CURSORS_AT_COMMIT.
  • HOLD_CURSORS_OVER_COMMIT sets the result set holdablity to hold cursors over commit. This value sets the JDBC value java.sql.ResultSet.HOLD_CURSORS_OVER_COMMIT.

The default value is HOLD_CURSORS_OVER_COMMIT.

resultSetConcurrency
Optional. Specifies the ResultSet concurrency. You can specify one of the following values for resultSetConcurrency
  • CONCUR_READ_ONLY sets the cursor concurrency to be read only.
  • CONCUR_UPDATABLE sets the cursor concurrency to be updatable.

The default value is CONCUR_READ_ONLY.

resultSetType
Optional. Specifies the ResultSet types. You can specify one of the following values for resultSetType:
  • TYPE_FORWARD_ONLY sets the result set type to be forward only.
  • TYPE_SCROLL_SENSITIVE sets the result set type to be scroll sensitive.
  • TYPE_SCROLL_INSENSITIVE sets the result set type to be scroll insensitive.

The default value is TYPE_FORWARD_ONLY.

Examples

The following example sets the attrubutes resultSetHoldability, resultSetConcurrency, and resultSetType for the SQL select statement:
-- resultSetHoldability= CLOSE_CURSORS_AT_COMMIT, resultSetConcurrency=CONCUR_UPDATABLE, resultSetType=TYPE_SCROLL_SENSITIVE
SELECT ID, NAME FROM EMP WHERE REGION=?;
The following example sets the attributes for the SQL select statement using three comment lines:
-- resultSetHoldability= CLOSE_CURSORS_AT_COMMIT
-- resultSetConcurrency=CONCUR_UPDATABLE
-- resultSetType=TYPE_SCROLL_SENSITIVE
SELECT * FROM PRODUCTS WHERE  PRICE > ? ;
In the following example, the attributes enclosed between the comment markers /* and */ are ignored:
/*
   resultSetHoldability= CLOSE_CURSORS_AT_COMMIT
   resultSetConcurrency=CONCUR_UPDATABLE
   resultSetType=TYPE_SCROLL_SENSITIVE
*/
SELECT * FROM PRODUCTS WHERE  PRICE > ? ;
In the following example, the attributes apply to the first SELECT statement. The second SELECT statement uses the default values:
-- resultSetHoldability= CLOSE_CURSORS_AT_COMMIT
-- resultSetConcurrency=CONCUR_UPDATABLE
-- resultSetType=TYPE_SCROLL_SENSITIVE

SELECT * FROM PRODUCTS WHERE PRICE > ? 
  AND COLOR = ? ;

SELECT PRICE FROM PRODUCTS WHERE  PRODID = ? ;

Feedback