Execute

Valid in: ESQL

The Execute statement executes a previously prepared dynamic SQL statement.

Syntax

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 regular or delimited identifier or a host language variable. It must identify a valid prepared statement.

  If the statement identified by statement_name is invalid, the DBMS 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 named statement.) Similarly, if the statement name refers to a cursor update or delete whose associated cursor is no longer open, the DBMS Server issues an error. For more information, see Update and Delete.

• USING variable

  Must be used if question marks (?) are used in the prepared statement as placeholders for parameters to be specified at runtime. If the number and data types of the expressions specified by question marks in the prepared statement are known, use the USING variable_list alternative. The number of the variables listed must correspond to the number of question marks in the prepared statement, and each must be type-compatible with its usage in the prepared statement.

• USING DESCRIPTOR descriptor_name

  Must be used if the number and data types of the parameters in the prepared statement are not known until runtime.

Description

The Execute statement executes the prepared statement specified by statement_name. Execute can be used to carry out any statement that can be prepared, with the exception of the select statement.

Note: To execute a prepared select statement, use the execute immediate statement. For more information, see Execute Immediate.

To use long varchar columns as variables in the USING clause, specify a DATAHANDLER clause in place of the host language variable. For details about data handler routines, see the Embedded SQL Companion Guide.

The syntax for the datahandler clause is as follows:

datahandler(handler_routine ([handler_arg]))[:indicator_var]

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 parameters in the prepared statement are not known until runtime, the USING DESCRIPTOR alternative must be used. 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, as it is in this case, your application program must set the sqlvar array element type, length, and data area for each portion of the prepared statement that is specified by question marks prior to executing the statement. Your application program can use one of the following methods to 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 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 an 4GL 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.

In addition, the program must also correctly set the sqld field in the SQLDA structure.

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, 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 that allows a host structure variable when issued non-dynamically.

This statement must be terminated according to the rules of the host language.

Permissions

All users are permitted to use this statement.

Locking

The locking behavior of the Execute statement depends on the statement that is executed.

Related Statements

Describe

Prepare

Examples: Execute

The following are Execute statement examples:

1. Even though the commit statement can be prepared, once the statement is executed, the prepared statement becomes invalid.

   For example, the following code causes an error on the second execute statement.

   statement_buffer = 'commit';
   
   exec sql prepare s1 from :statement_buffer;
   
   process and update data;
   exec sql execute s1;
   /* Once committed, 's1' is lost */
   
   process and update more data;
   exec sql execute s1;
   /* 's1' is NOT a valid statement name */

2. When leaving an application, each user deletes all their rows from a working table. User rows are identified by their different access codes. One user can have more than one access code.

   read group id from terminal;
   statement_buffer =
   'delete from ' + group_id +
   ' where access_code = ?';
   
   exec sql prepare s2 from :statement_buffer;
   
   read access_code from terminal;
   loop while (access_code <> 0)
   
   exec sql execute s2 using :access_code;
   read access_code from terminal;
   
   end loop;
   exec sql commit;