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.
A comment 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.
- HOLD_CURSORS_OVER_COMMIT sets the result set
holdablity to 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 = ? ;