Dynamically executes a previously prepared statement.
The EXECUTE statement is a dynamic SQL statement. Dynamic SQL
lets programs accept or generate SQL statements at run time,
in contrast to SQL statements that are part of the source code
for precompiled programs or SQL module language procedures.
Unlike precompiled SQL or SQL module language statements, such
dynamically executed SQL statements are not necessarily part of a
program's source code, but can be generated while the program is
running. Dynamic SQL is useful when you cannot predict the type
of SQL statement your program will need to process.
If a program needs to dynamically execute a statement more than
once, the statement should be prepared first with the PREPARE
statement and executed each time with the EXECUTE statement.
SQL does not parse and compile prepared statements every time it
dynamically executes them with the EXECUTE statement.
1 – Environment
You can use the EXECUTE statement:
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
2 – Format
EXECUTE --+-> <statement-name> --------+-+
+-> <statement-id-parameter> + |
+----------------------------------------+
+-+-------------------------------------------------+-+
+-> INTO --+-> DESCRIPTOR <desc-name> ----------+-+ |
+-+-+-> <parameter> ------------+-+-+ |
| +-> <qualified-parameter> --+ | |
| +-> <variable> ------------+ | |
+-------------- , <--------------+ |
+--------------------------------<--------------------+
+-+-------------------------------------------------+--->
+-> USING -+-> DESCRIPTOR <desc-name> ----------+-+
+-+-+-> <parameter> ------------+-+-+
| +-> <qualified-parameter> --+ |
| +-> <variable> ------------+ |
+-------------- , <--------------+
3 – Arguments
3.1 – INTO_DESCRIPTOR
Specifies an SQLDA descriptor that contains addresses and data
types that specify output parameters or variables.
The descriptor must be a structure declared in the host language
program as an SQLDA. If the program is precompiled and uses the
embedded SQL statement INCLUDE SQLDA, the name of the structure
is simply SQLDA. Programs can use multiple SQLDAs, but must
explicitly declare them with names other than SQLDA.
Programs can always use the INTO DESCRIPTOR clause of the EXECUTE
statement whether or not the statement string contains output
parameter markers, as long as the value of the SQLD field in
the SQLDA corresponds to the number of output parameter markers.
SQL updates the SQLD field with the correct number of output
parameter markers when it processes the DESCRIBE statement for
the statement string.
3.2 – INTO clause
Syntax options:
INTO parameter | INTO qualified-parameter | INTO variable
Specifies output parameters or variables whose values are
returned by a successful EXECUTE statement.
When you specify a list of parameters or variables, the number
of parameters in the list must be the same as the number of
output parameter markers in the statement string of the prepared
statement. If SQL determines that a statement string had no
output parameter markers, the INTO clause is not allowed.
3.3 – statement-name
Specifies the name of a prepared statement. You can supply either
a parameter or a compile-time statement name. Specifying a
parameter lets SQL supply identifiers to programs at run time.
Use an integer parameter to contain the statement identifier
returned by SQL or a character string parameter to contain the
name of the statement that you pass to SQL.
If the PREPARE statement for the dynamically executed statement
specifies a parameter, use that same parameter in the EXECUTE
statement instead of an explicit statement name.
3.4 – statement-id-parameter
Specifies the name of a prepared statement. You can supply either
a parameter or a compile-time statement name. Specifying a
parameter lets SQL supply identifiers to programs at run time.
Use an integer parameter to contain the statement identifier
returned by SQL or a character string parameter to contain the
name of the statement that you pass to SQL.
If the PREPARE statement for the dynamically executed statement
specifies the same parameter, use that parameter in the EXECUTE
statement instead of an explicit statement name. If an SQLCA is
used to capture completion conditions and row count information,
an SQLCA must be used with the PREPARE statement. See the PREPARE
statement for more information.
3.5 – USING_DESCRIPTOR
Specifies an SQLDA descriptor that contains addresses and data
types of input parameters or variables.
The descriptor must be a structure declared in the host language
program as an SQLDA. If the program is precompiled and uses the
embedded SQL statement INCLUDE SQLDA, the name of the structure
is simply SQLDA. Programs can use multiple SQLDAs, but must
explicitly declare them with names other than SQLDA.
Programs can always use the USING DESCRIPTOR clause of the
EXECUTE statement whether or not the statement string contains
input parameter markers, as long as the value of the SQLD
field in the SQLDA corresponds to the number of input parameter
markers. SQL updates the SQLD field with the correct number of
input parameter markers when it processes the DESCRIBE statement
for the statement string.
3.6 – USING clause
Syntax options:
USING parameter | USING qualified-parameter | USING variable
Specifies input parameters or variables whose values SQL uses to
replace parameter markers in the prepared statement string.
When you specify a list of parameters or variables, the number
of parameters in the list must be the same as the number of
input parameter markers in the statement string of the prepared
statement. If SQL determines that a statement string had no input
parameter markers, the USING clause is not allowed.
4 – Example
Example 1: Executing an INSERT statement with parameter markers
These fragments from the online sample C program sql_dynamic
illustrate using an EXECUTE statement in an SQL module procedure
to execute a dynamically generated SQL statement.
The program accepts input of any valid SQL statement from the
terminal and calls the subunit shown in the following program
excerpt:
.
.
.
/*
**----------------------------------------------------------------------------
** Begin Main routine
**----------------------------------------------------------------------------
*/
int sql_dynamic (psql_stmt, input_sqlda, output_sqlda, stmt_id, is_select)
char *psql_stmt;
sqlda *input_sqlda;
sqlda *output_sqlda;
long *stmt_id;
int *is_select;
{
sqlda sqlda_in, sqlda_out; /* Declare the SQLDA structures. */
int rowcount, status;
int param;
/* Declare arrays for storage of original data types and allocate memory. */
mem_ptr output_save;
mem_ptr input_save;
/* * If a NULL SQLDA is passed, then a new statement is being prepared. */
if ((*input_sqlda == NULL) && (*output_sqlda == NULL))
{
new_statement = TRUE;
/*
* Allocate separate SQLDAs for input parameter markers (SQLDA_IN)
* and output list items (SQLDA_OUT). Assign the value of the constant
* MAXPARMS to the SQLN field of both SQLDA structures. SQLN specifies to
* SQL the maximum size of the SQLDA.
*/
if ((sqlda_in = (sqlda) calloc (1, sizeof (sqlda_rec))) == 0)
{
printf ("\n\n*** Error allocating memory for sqlda_in: Abort");
return (-1);
}
else /* set # of possible parameters */
sqlda_in->sqln = MAXPARAMS;
if ((sqlda_out = (sqlda) calloc (1, sizeof (sqlda_rec))) == 0)
{
printf ("\n\n*** Error allocating memory for sqlda_out: Abort");
return (-1);
}
}
else
/* Set # of possible select list items. */
sqlda_out->sqln = MAXPARAMS;
/* copy name SQLDA2 to identify the SQLDA */
strncpy(&sqlda_in->sqldaid[0],"SQLDA2 ",8);
strncpy(&sqlda_out->sqldaid[0],"SQLDA2 ",8);
/*
* Call an SQL module language procedure, prepare_stmt and
* describe_stmt that contains a PREPARE and DESCRIBE...OUTPUT
* statement to prepare the dynamic statement and write information
* about any select list items in it to SQLDA_OUT.
*/
*stmt_id = 0; /* If <> 0 the BADPREPARE error results in the PREPARE.*/
PREPARE_STMT (&SQLCA, stmt_id, psql_stmt);
if (SQLCA.SQLCODE != sql_success)
{
printf ("\n\nDSQL-E-PREPARE, Error %d encountered in PREPARE",
SQLCA.SQLCODE);
display_error_message();
return (-1);
}
DESCRIBE_SELECT (&SQLCA, stmt_id, sqlda_out);
if (SQLCA.SQLCODE != sql_success)
{
printf ("\n\nDSQL-E-PREPARE, Error %d encountered in PREPARE",
SQLCA.SQLCODE);
display_error_message();
return (-1);
}
/*
* Call an SQL module language procedure, describe_parm, that contains a
* DESCRIBE...INPUT statement to write information about any parameter
* markers in the dynamic statement to sqlda_in.
*/
DESCRIBE_PARM (&SQLCA, stmt_id, sqlda_in);
if (SQLCA.SQLCODE != sql_success)
{
printf ("\n\n*** Error %d returned from describe_parm: Abort",
SQLCA.SQLCODE);
display_error_message();
return (-1);
}
/* Save the value of the SQLCA.SQLERRD[1] field so that program can
* determine if the statement is a SELECT statement or not.
* If the value is 1, the statement is a SELECT statement.*/
*is_select = SQLCA.SQLERRD[1];
.
.
.
/*
* Check to see if the prepared dynamic statement contains any parameter
* markers by looking at the SQLD field of sqlda_in. SQLD contains the
* number of parameter markers in the prepared statement. If SQLD is
* positive, the prepared statement contains parameter markers. The program
* executes a local procedure, get_in_params, that prompts the user for
* values, allocates storage for those values, and updates the SQLDATA field
* of sqlda_in:
*/
if (sqlda_in->sqld > 0)
if ((status = get_in_params(sqlda_in,input_save)) != 0)
{
printf ("\nError returned from GET_IN_PARAMS. Abort");
return (-1);
}
/* Check to see if the prepared dynamic statement is a SELECT by looking
* at the value in is_select, which stores the value of the
* SQLCA.SQLERRD[1] field. If that value is equal to 1, the prepared
* statement is a SELECT statement. The program allocates storage for
* rows for SQL module language procedures to open and fetch from a cursor,
* and displays the rows on the terminal:
*/
if (*is_select)
{
if (new_statement == TRUE) /* Allocate buffers for output. */
{
/* assign a unique name for the cursor */
sprintf(cursor_name,"%2d",++cursor_counter);
if ((status = allocate_buffers(sqlda_out)) != 0)
.
.
.
/*
* If the SQLCA.SQLERRD[1] field is not 1, then the prepared statement is not a
* SELECT statement and only needs to be executed. Call an SQL module language
* procedure to execute the statement, using information about parameter
* markers stored in sqlda_in by the local procedure get_in_params:
*/
{
EXECUTE_STMT (&SQLCA, stmt_id, sqlda_in);
if (SQLCA.SQLCODE != sql_success)
.
.
.
The SQL module language procedures called by the preceding
fragment:
.
.
.
-------------------------------------------------------------------------------
-- Procedure Section
-------------------------------------------------------------------------------
-- This procedure prepares a statement for dynamic execution from the string
-- passed to it. It also writes information about the number and data type of
-- any select list items in the statement to an SQLDA2 (specifically,
-- the sqlda_out SQLDA2 passed to the procedure by the calling program).
--
PROCEDURE PREPARE_STMT
SQLCA
:DYN_STMT_ID INTEGER
:STMT CHAR(1024);
PREPARE :DYN_STMT_ID FROM :STMT;
-- This procedure writes information to an SQLDA (specifically,
-- the sqlda_in SQLDA passed to the procedure by the calling program)
-- about the number and data type of any parameter markers in the
-- prepared dynamic statement. Note that SELECT statements may also
-- have parameter markers.
PROCEDURE DESCRIBE_SELECT
SQLCA
:DYN_STMT_ID INTEGER
SQLDA;
DESCRIBE :DYN_STMT_ID OUTPUT INTO SQLDA;
PROCEDURE DESCRIBE_PARM
SQLCA
:DYN_STMT_ID INTEGER
SQLDA;
DESCRIBE :DYN_STMT_ID INPUT INTO SQLDA;
-- This procedure dynamically executes a non-SELECT statement.
-- SELECT statements are processed by DECLARE CURSOR, OPEN CURSOR,
-- and FETCH statements.
--
-- The EXECUTE statement specifies an SQLDA2 (specifically,
-- the sqlda_in SQLDA2 passed to the procedure by the calling program)
-- as the source of addresses for any parameter markers in the dynamic
-- statement.
--
-- The EXECUTE statement with the USING DESCRIPTOR clause
-- also handles statement strings that contain no parameter markers.
-- If a statement string contains no parameter markers, SQL sets
-- the SQLD field of the SQLDA2 to zero.
PROCEDURE EXECUTE_STMT
SQLCA
:DYN_STMT_ID INTEGER
SQLDA;
EXECUTE :DYN_STMT_ID USING DESCRIPTOR SQLDA;
.
.
.