EXECUTE
Valid in: ESQL, OpenAPI, ODBC, JDBC, .NET
The EXECUTE statement executes a previously prepared dynamic OpenSQL statement.
The EXECUTE statement has the following format:
exec sql EXECUTE statement_name
[USING variable {, variable} | USING DESCRIPTOR descriptor_name];
statement_name
Identifies a valid object name specified using a a string literal or a host language variable. It must identify a valid prepared statement. If the statement identified by statement_name is invalid, the Enterprise Access product or server issues an error and aborts the execute statement. (A prepared statement is invalid if a transaction was committed or rolled back after the statement was prepared or if an error occurred while preparing the statement.)
The EXECUTE statement executes the prepared statement specified by statement_name. EXECUTE can be used to execute any statement that can be prepared, except the SELECT statement.
If the prepared statement refers to a cursor update or delete and the associated cursor is not open, the Enterprise Access product or server issues an error
. For more information, see
UPDATE and
DELETE.
If the prepared statement uses a question mark (?) to specify one or more constant expressions, the USING clause must be specified in the statement. If you know the number and data types of the expressions specified by question marks in the prepared statement, use the using variable {, variable} option. The number of the variables listed must correspond to the number of question marks in the prepared statement, and each variable's data type must be compatible with its usage in the prepared statement.
The following example prepares a statement containing one question mark from a buffer and executes it using a host language variable:
statement_buffer =
'delete from ' + table_name + ' where code = ?';
exec sql prepare del_stmt from :statement_buffer;
...
exec sql execute del_stmt using :code;
The value in the variable, code, replaces the ? in the where clause of the prepared delete statement.
If the number and data types of the prepared statement parameters are not known until runtime, use the using descriptor option. In this alternative, the descriptor_name identifies an SQLDA, a host language structure that must be allocated prior to its use. The SQLDA includes the sqlvar array. Each element of sqlvar is used to describe and point to a host language variable. The execute statement uses the values placed in the variables pointed to by the sqlvar elements to execute the prepared statement.
When the SQLDA is used for input, the program must set the sqlvar array element type, length, and data area for each portion of the prepared statement specified by question marks, prior to executing the statement.
Here are some of the ways the program can supply that information:
• When preparing the statement, the program can request all type and length information from the interactive user.
• Before preparing the statement, the program can scan the statement string, and build a select statement out of the clauses that include parameters. The program can then prepare and describe this select statement to collect data type information to be used on input.
• If another application development tool is being used to build the dynamic statements (such as a Vision frame or a VIFRED form), the data type information included in those objects can be used to build the descriptor. An example of this method is shown in the Examples section.
In addition, the program must correctly set the sqld field in the SQLDA structure. For a complete description of the structure of the SQLDA and how to use it, see the chapter “Dynamic OpenSQL.”
The variables used by the USING clause can be associated with indicator variables if indicator variables are permitted with the same statement in the non‑dynamic case. For example, because indicator variables are permitted in the INSERT statement VALUES clause, then the following dynamically defined INSERT statement can include indicator variables (name_ind and age_ind) in the EXECUTE statement:
statement_buffer = 'insert into employee (name, age) values (?, ?)';
exec sql prepare s1 from :statement_buffer;
exec sql execute s1 using :name:name_ind,
:age:age_ind;
However, a host structure variable cannot be used in the USING clause, even if the named statement refers to a statement which allows a host structure variable when issued non‑dynamically.
This statement must be terminated according to the rules of the host language.
Last modified date: 01/30/2023