- statement-name
- Names the prepared statement. If the name identifies an existing prepared
statement, that prepared statement is destroyed if:
- it was prepared in the same instance of the same program, or
- CLOSQLCSR(*ENDJOB), CLOSQLCSR(*ENDACTGRP), or CLOSQLCSR(*ENDSQL) are specified
on the CRTSQLxxx commands associated with both prepared statements.
The name must not identify a prepared statement that is the SELECT statement
of an open cursor of this instance of the program.
- USING SQL DESCRIPTOR SQL-descriptor-name
- Identifies an SQL descriptor. If USING is specified, and
the PREPARE statement is successfully executed, information about the prepared
statement is placed in the SQL descriptor specified by the SQL-descriptor-name. Thus, the PREPARE statement:
EXEC SQL PREPARE S1 USING SQL DESCRIPTOR :sqldescriptor FROM :V1;
is equivalent to:
EXEC SQL PREPARE S1 FROM :V1;
EXEC SQL DESCRIBE S1 USING SQL DESCRIPTOR :sqldescriptor;
- LOCAL
- Specifies the scope of the name of the descriptor to be local to program
invocation.
- GLOBAL
- Specifies the scope of the name of the descriptor to be global to the
SQL session.
- SQL-descriptor-name
- Names the SQL descriptor. The name must identify a descriptor that already
exists with the specified scope.
See GET DESCRIPTOR for an explanation of the information
that is placed in the SQLDA.
- INTO
- If INTO is used, and the PREPARE statement is successfully executed,
information about the prepared statement is placed in the SQLDA specified
by the descriptor-name. Thus, the PREPARE statement:
EXEC SQL PREPARE S1 INTO :SQLDA FROM :V1;
is equivalent to:
EXEC SQL PREPARE S1 FROM :V1;
EXEC SQL DESCRIBE S1 INTO :SQLDA;
- descriptor-name
- Identifies an SQL descriptor area (SQLDA), which is described in Appendix D. SQLDA (SQL descriptor area). Before the PREPARE statement is executed, the
following variable in the SQLDA must be set (The rules for REXX are different.
For more information, see the Embedded SQL Programming book.) :
- SQLN
- Indicates the number of variables represented by SQLVAR. (SQLN provides
the dimension of the SQLVAR array.) SQLN must be set to a value greater than
or equal to zero before the PREPARE statement is executed. For information
on techniques to determine the number of occurrences required, see Determining how many SQLVAR occurrences are needed.
See DESCRIBE for an explanation of the information
that is placed in the SQLDA.
- USING
- Specifies what value to assign to each SQLNAME variable in
the SQLDA. If the requested value does not exist or a name is longer than
30, SQLNAME is set to length 0.
- NAMES
- Assigns the name of the column. This is the default. For a prepared
statement where the names are explicitly specified in the select-list, the
name specified is returned.
- SYSTEM NAMES
- Assigns the system column name of the column.
- LABELS
- Assigns the label of the column. (Column labels are defined by the LABEL
statement.) Only the first 20 bytes of the label are returned.
- ANY
- Assigns the column label. If the column has no label, the label is the
column name.
- BOTH
- Assigns both the label and name of the column. In this case, two or
three occurrences of SQLVAR per column, depending on whether the result set
contains distinct types, are needed to accommodate the additional information.
To specify this expansion of the SQLVAR array, set SQLN to 2*n or 3*n(where n is the
number of columns in the table or view). The first n occurrences
of SQLVAR contain the column names. Either the second or third n occurrences contain the column labels. If there are no distinct types,
the labels are returned in the second set of SQLVAR entries. Otherwise, the
labels are returned in the third set of SQLVAR entries.
If the same SQLDA
is used on a subsequent FETCH statement, set SQLN to n after the PREPARE is complete.
- ALL
- Assigns the label, column name, and system column name. In
this case three or four occurrences of SQLVAR per column, depending on whether
the result set contains distinct types, are needed to accommodate the additional
information. To specify this expansion of the SQLVAR array, set SQLN to 3*n or 4*n (where n is the number of columns in the result table). The first n occurrences of SQLVAR contain the system column names. The second or
third n occurrences contain the column labels. The
third or fourth n occurrences contain the column names
if they are different from the system column name. If there are no distinct
types, the labels are returned in the second set of SQLVAR entries and the
column names are returned in the third set of SQLVAR entries. Otherwise, the
labels are returned in the third set of SQLVAR entries and the column names
are returned in the fourth set of SQLVAR entries.
If the same SQLDA is used
on a subsequent FETCH statement, set SQLN to n after
the PREPARE is complete.
- ATTRIBUTES attr-variable
- Specifies the attributes for this cursor that are in effect if a corresponding
attribute has not been specified as part of the associated SELECT statement.
If attributes are specified in the SELECT statement, they are used instead
of the corresponding attributes specified on the PREPARE statement. In turn,
if attributes are specified in the PREPARE statement, they are used instead
of the corresponding attributes specified on a DECLARE CURSOR statement.
attr-variable must identify a character-string or Unicode
graphic variable that is declared in the program in accordance with the rules
for declaring string variables. attr-variable must be
a string variable (either fixed-length or varying-length) that has a length
attribute that does not exceed the maximum length of a VARCHAR. Leading and
trailing blanks are removed from the value of the variable. The variable must
contain a valid attribute-string.
An indicator variable can
be used to indicate whether or not attributes are actually provided on the
PREPARE statement. Thus, applications can use the same PREPARE statement regardless
of whether attributes need to be specified or not. The options that can be
specified as part of the attribute-string are as follows:
- ASENSITIVE, SENSITIVE, or INSENSITIVE
- Specifies whether the cursor is asensitive, sensitive, or insensitive
to changes. For more information, see DECLARE CURSOR.
If SENSITIVE
is specified, then a fetch-first-clause must not be specified. If
INSENSITIVE is specified, then an update-clause must not be specified.
- NO SCROLL or SCROLL
- Specifies whether the cursor is scrollable or not scrollable. For more
information, see DECLARE CURSOR.
- WITHOUT HOLD or WITH HOLD
- Specifies whether the cursor should be prevented from being closed as
a consequence of a commit operation. For more information, see DECLARE CURSOR.
- WITHOUT RETURN or WITH RETURN
- Specifies whether the result table of the cursor is intended to be used
as a result set that will be returned from a procedure. For more information,
see DECLARE CURSOR.
- fetch-first-clause
- Specifies that a maximum number of rows should be retrieved. For more
information, see fetch-first-clause.
If a fetch-first-clause is specified, then an update-clause must not be specified.
- read-only-clause or
update-clause
- Specifies whether the result table is read-only or updatable. The update-clause clause must be specified without column names (FOR UPDATE).
For more information, see read-only-clause and update-clause.
- optimize-clause
- Specifies that the database manager should assume that the program does
not intend to retrieve more than integer rows from the result table.
For more information, see optimize-clause.
- isolation-clause
- Specifies an isolation level at which the select statement is executed.
For more information, see isolation-clause.
- FROM
- Introduces the statement string. The statement string is the value of
the specified string-expression or the identified variable.
- string-expression
- A string-expression is any PL/I string-expression that
yields a character string. SQL expressions that yield a character string are
not allowed. A string-expression is only allowed in PL/I.
- variable
- Identifies a variable that is declared
in the program in accordance with the rules for declaring character-string
or Unicode graphic variables. An indicator variable must not be specified.
The statement string must be one of the following SQL statements:
ALTER |
HOLD LOCATOR |
select-statement |
CALL |
INSERT |
SET CURRENT DEBUG MODE |
COMMENT |
LABEL |
SET CURRENT DEGREE |
COMMIT |
LOCK TABLE |
SET ENCRYPTION PASSWORD |
CREATE |
REFRESH TABLE |
SET PATH |
DECLARE GLOBAL TEMPORARY TABLE |
RELEASE SAVEPOINT |
SET SCHEMA |
DELETE |
RENAME |
SET SESSION AUTHORIZATION |
DROP |
REVOKE |
SET TRANSACTION |
FREE LOCATOR |
ROLLBACK |
UPDATE |
GRANT |
SAVEPOINT |
VALUES INTO |
The statement string must not:
- Begin with EXEC SQL and end with END-EXEC or a semicolon (;).
- Include references to variables.
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.