OPEN is an executable command that can only be embedded in a host language. OPEN cannot be dynamically prepared.
The privileges required to execute an OPEN statement correspond to those privileges required for declaring the cursor being opened. See the authorization description under the DECLARE CURSOR command.
OPEN cursor-name
[USING {host-variable[,...]
| DESCRIPTOR descriptor-name }]
| cursor-name | Identifies a previously declared cursor. |
| host-variable | Identifies a variable described in the application program in accordance with the rules for declaring host variables. |
| descriptor-name | Preceded by the keyword DESCRIPTOR, identifies an SQLDA containing a valid description of application program host variables, which contains certain field values that must be set in order prior to executing an OPEN statement. |
Before the OPEN statement is executed, each target parameter marker in the cursor query is assigned the value of its corresponding host variable. The USING clause introduces a list of host variables whose values are substituted for the parameter markers (question mark characters) appearing in a prepared SELECT statement. The number of host variables must equal the number of parameter markers. The USING clause must be specified if the DECLARE CURSOR statement names a prepared statement containing parameter markers. If the prepared statement has no parameter markers, the XDB Server ignores the USING clause.
The USING clause is intended primarily for cursors declared with prepared select statements containing parameter markers. When the prepared select statement of the cursor is evaluated, each parameter marker is replaced by its corresponding host variable listed in the USING clause. The USING clause also functions in those cases when the DECLARE CURSOR statement contains the actual select statement, which typically includes host variables rather than parameter markings. In these cases, the OPEN statement is executed as if each host variable in the cursor select statement is a parameter marker. The values of the cursor select statement host variables are replaced by the values of the host variables specified in the USING clause.
If an SQLDA is specified (preceded by the keyword DESCRIPTOR), the user must have already set the value of certain SQLDA fields. The SQLN field must contain a value indicating the number of values in the SQLVAR array variable. Additionally, the SQLDABC field must indicate the bytes of storage allocated for the SQLDA, while the SQLD field value must denote the total number of variables used in the SQLDA when processing the OPEN statement. Lastly, each entry in the SQLVAR array must describe the attributes of an individual variable. If LOBs or distinct types are present in the results, there must be additional SQLVAR entries for each input host variable.
The value stored in SQLDABC must be calculated according to the formula SQLN*44+16, in order for the SQLDA to contain enough storage for all SQLVAR array values. Additionally, the SQLD field must be set to a value greater than or equal to zero, and less than or equal to the value of the SQLN field. This field value must equal the number of parameter markers in the prepared statement.
FETCH statements retrieve rows from the result table of a cursor when the cursor is open. The only way to open a closed cursor is by executing an OPEN statement. All cursors in an application process are in a closed state when the application process is started or when a new unit of work for the application process is commenced (unless WITH HOLD is specified in the DECLARE CURSOR statement). Additionally, performing a CONNECT closes any open cursors in an application.
The parameter host-variables is a list of host variables defined in the Working-Storage Section.
Assume that CUSTBL is a cursor opened on a command that was previously prepared. This prepared command will have one substitution parameter. Note that HOSTVAR1 must be defined in the Working-Storage Section of your COBOL program.
EXEC SQL
OPEN custbl
END-EXEC
EXEC SQL
OPEN custbl USING :hostvar1
END-EXEC
Description
The cursor must be in the closed state in order to execute an OPEN command. The OPEN command has two basic configurations. The first syntax format is the simple OPEN command, consisting of the keyword OPEN followed by the name of a previously declared cursor name. The second syntax format is used in dynamic SQL applications, and includes a USING clause. The USING clause introduces either a list of host variables (previously defined in the application) or an SQL Descriptor Area (SQLDA) also containing host variable information (including SELECT statement column information) previously described into the SQLDA. (See the DESCRIBE command.)
The cursor select statement can either be a SELECT statement specified in the DECLARE CURSOR statement or a previously prepared (see PREPARE command) SELECT statement identified by a statement name, which can also be specified in the DECLARE CURSOR statement. The OPEN command evaluates the cursor select statement and determines a result table for that cursor. This result table includes current values of any special registers specified in the cursor select statement, as well as the current values of any host variables specified in either the cursor select statement or the USING clause of the OPEN statement.
Result table rows can be determined either during the execution of the OPEN statement (in which case a temporary table will be created to hold them) or these rows can be determined during the execution of subsequent FETCH statements performed on the opened cursor. In either case, the cursor is opened, and the cursor pointer is positioned before the first row of the result table. If the table happens to be empty, the cursor pointer is considered to be positioned after the last (nonexistent) row. The XDB Server does not alert users to an empty result table when the OPEN statement is executed, but does indicate an empty table by returning an SQLCODE value of +100 upon execution of the first FETCH statement on that result table.