Prepares an SQL statement dynamically generated by a program for
execution, and assigns a name to that statement.
Dynamic SQL lets programs accept or generate SQL statements
at run time, in contrast to 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.
The PREPARE . . . INTO statement stores in the SQLDA the number
and data types of any select list items of a prepared statement.
The SQLDA provides information about dynamic SQL statements to
the program and information about memory allocated by the program
to SQL.
The Oracle Rdb SQL Reference Manual describes in more detail
the specific fields of the SQLDA, and how programs use it to
communicate about select list items in prepared statements.
1 – Environment
You can use the PREPARE statement:
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
2 – Format
PREPARE -+-> <statement-name> --------+--------------+
+-> <statement-id-parameter> + |
+-------------------------------------------+
++----------------------------------------+-+
+-> SELECT LIST INTO <descriptor-name> + |
+-------------------------------------------+
+-> FROM --+-> ' -> <statement-string> -> ' -+->
+-> <parameter> ------------------+
3 – Arguments
3.1 – descriptor-name
Specifies the name of a structure declared in the host program
as an SQLDA to which SQL writes information about select list
items. Precompiled programs can use the embedded SQL statement
INCLUDE SQLDA to automatically insert a declaration of an SQLDA
structure, called SQLDA, in the program when it precompiles
the program. Programs that use the SQL module language must
explicitly declare an SQLDA. Either precompiled or SQL module
language programs can explicitly declare additional SQLDAs, but
must declare them with unique names.
3.2 – FROM
Syntax options:
FROM statement-string
FROM parameter
Specifies the SQL statement to be prepared for dynamic execution.
You either specify the statement string directly enclosed in
single quotation marks, or in a parameter (a host language
variable in a precompiled PREPARE statement or a formal parameter
in a PREPARE statement that is part of an SQL module language
procedure) that contains the statement string.
Whether specified directly or by a parameter, the statement
string must be a character string that is a dynamically
executable SQL statement. If you specify the statement string
directly, the maximum length is 1,024 characters. If you specify
the statement string as a parameter, the maximum length of the
statement string is 65,535 characters.
The form for the statement is the same as for embedded SQL
statements, except that:
o You must not begin the string with EXEC SQL.
o In places where SQL allows host language variables in an
embedded statement, you must specify parameter markers
instead.
If you try to prepare an invalid statement, you will find a value
in the SQLCODE, the SQLCODE field of the SQLCA, or the SQLSTATE
status parameter indicating an error.
The values returned to the SQLCODE field are described in the
Oracle Rdb SQL Reference Manual. Check the message vector to
see which error message was returned. If necessary, refer to the
error message explanations and user actions located by default in
the SQL HELP ERRORS.
Parameter markers are question marks (?) that denote parameters
in the statement string of a PREPARE statement. Parameter markers
are replaced by values in parameters or dynamic memory when the
prepared statement is executed by an EXECUTE or OPEN statement.
3.3 – SELECT_LIST_INTO
Specifies that SQL writes information about the number and data
type of select list items in the statement string to the SQLDA.
The SELECT LIST keywords clarify the effect of the INTO clause
and are optional.
Using the SELECT LIST clause in a PREPARE statement is an
alternative to issuing a separate DESCRIBE . . . INPUT statement.
See the DESCRIBE statement for more information.
The SELECT LIST clause in a PREPARE statement is deprecated
syntax. For more information about deprecated syntax, see the
Oracle Rdb SQL Reference Manual.
NOTE
The PREPARE statement LIST keyword is not related to the
LIST data type or list cursors.
3.4 – statement-name
Identifies the prepared version of the SQL statement specified in
the FROM clause. Depending on the type of SQL statement prepared,
DESCRIBE, EXECUTE, and dynamic DECLARE CURSOR statements can
refer to the statement name assigned in a PREPARE 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.
A single set of dynamic SQL statements (PREPARE, DESCRIBE,
EXECUTE, Extended Dynamic DECLARE CURSOR) can handle any
number of dynamically executed statements. If you decide to
use parameters, statements that refer to the prepared statement
(DESCRIBE, EXECUTE, extended dynamic DECLARE CURSOR) must also
use a parameter instead of the explicit statement name.
Refer to the DECLARE Dynamic_CURSOR statement for an example
demonstrating the PREPARE statement used with a dynamic DECLARE
CURSOR statement.
3.5 – statement-id-parameter
Identifies the prepared version of the SQL statement specified in
the FROM clause. Depending on the type of SQL statement prepared,
DESCRIBE, EXECUTE, and dynamic DECLARE CURSOR statements can
refer to the statement name assigned in a PREPARE 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.
A single set of dynamic SQL statements (PREPARE, DESCRIBE,
EXECUTE, Extended Dynamic DECLARE CURSOR) can handle any
number of dynamically executed statements. If you decide to
use parameters, statements that refer to the prepared statement
(DESCRIBE, EXECUTE, extended dynamic DECLARE CURSOR) must also
use a parameter instead of the explicit statement name.
See the DECLARE Dynamic_CURSOR statement for an example
demonstrating the PREPARE statement used with a dynamic DECLARE
CURSOR statement.
4 – Examples
Example 1: Preparing an INSERT statement with parameter markers
This PL/I program illustrates using a PREPARE statement to
prepare an INSERT statement for dynamic execution. Because the
statement string stored in COMMAND_STRING has parameter markers,
the program needs to assign values to host language variables
that will be substituted for the parameter markers during dynamic
execution.
In this case, a DESCRIBE statement writes information about
the parameter markers to the SQLDA and the program writes the
addresses of the variables to the SQLDA. The program stores
values in the variables and an EXECUTE statement substitutes the
values for the parameter markers in the INSERT statement using
the addresses in the SQLDA.
To shorten the example, this program is simplified:
o The program includes the INSERT statement as part of the
program source code. A program with such coded SQL statements
does not need to use dynamic SQL at all, but can simply embed
the INSERT statement directly in the program. A program that
must process SQL statements generated as it executes is the
only type of program that requires dynamic SQL.
o The program declares host language variables for the
parameter markers without first checking the SQLDA for their
description. Typically, an application needs to look in the
SQLDA to determine the number and data type of parameter
markers in the statement string before allocating memory for
them.
PREP_INTO: procedure options(main);
/*
* Illustrate a dynamic INSERT statement
* with parameter markers:
*/
declare FILESPEC char(20),
EMP_ID CHAR(5),
FNAME CHAR(10),
LNAME CHAR(14),
CITY CHAR(20),
COMMAND_STRING char(256);
/* Declare communication area (SQLCA)
* and descriptor area (SQLDA): */
EXEC SQL INCLUDE SQLDA;
EXEC SQL INCLUDE SQLCA;
/* Declare the database: */
EXEC SQL DECLARE SCHEMA RUNTIME FILENAME :FILESPEC;
/*
*
* procedure division
*
*/
/*
* Assign values to FILESPEC and COMMAND_STRING,
* and allocate memory for the SQLDA:
*/
FILESPEC = 'SQL$DATABASE';
COMMAND_STRING =
'INSERT INTO EMPLOYEES
(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, CITY)
VALUES (?,?,?,?)';
SQLSIZE = 10;
ALLOCATE SQLDA SET (SQLDAPTR);
SQLN = 10;
/*
* Prepare the statement assigned to COMMAND_STRING:
*/
EXEC SQL PREPARE STMT3
FROM COMMAND_STRING;
/* Use a DESCRIBE statement to write information
* about the parameter markers in the statement string
* to the SQLDA:
*/
EXEC SQL DESCRIBE STMT3 MARKERS INTO SQLDA;
/* Assign values to the variables: */
EMP_ID = '99999';
FNAME = 'Bob';
LNAME = 'Addams';
CITY = 'Francestown';
/*
* Assign the addresses of the variables to the SQLDATA field
* of the SQLDA:
*/
SQLDATA(1) = ADDR(EMP_ID);
SQLDATA(2) = ADDR(FNAME);
SQLDATA(3) = ADDR(LNAME);
SQLDATA(4) = ADDR(CITY);
/* Execute STMT3:*/
EXEC SQL EXECUTE STMT3 USING DESCRIPTOR SQLDA;
/*
* Display the contents of table S to make sure
* it has the proper contents and clean it up:
*/
CALL DUMP_S;
EXEC SQL DELETE FROM EMPLOYEES WHERE EMPLOYEE_ID = "99999";
EXEC SQL COMMIT WORK;
RETURN;
DUMP_S: PROC;
EXEC SQL DECLARE X CURSOR FOR SELECT
EMPLOYEE_ID, FIRST_NAME, LAST_NAME, CITY
FROM EMPLOYEES WHERE EMPLOYEE_ID = "99999";
/*
* Declare a structure to hold values of rows from the table:
*/
DCL 1 S,
2 EMP_ID CHAR(5),
2 FNAME CHAR(10),
2 LNAME CHAR(14),
2 CITY CHAR(20);
/* Declare indicator vector for the preceding structure: */
DCL S_IND (4) FIXED(15) BIN;
PUT EDIT ('Dump the contents of S') (SKIP, SKIP, A);
EXEC SQL OPEN X;
EXEC SQL FETCH X INTO :S:S_IND;
DO WHILE (SQLCODE = 0);
PUT EDIT (S_IND(1), ' ', S.EMP_ID, ' ') (SKIP, F(6), A, A, A);
PUT EDIT (S_IND(2), ' ', S.FNAME, ' ') (F(6), A, A, A);
PUT EDIT (S_IND(3), ' ', S.LNAME, ' ') (F(6), A, A, A);
PUT EDIT (S_IND(4), ' ', S.CITY) (F(6), A, A);
EXEC SQL FETCH X INTO :S:S_IND;
END;
EXEC SQL CLOSE X;
RETURN;
END DUMP_S;
END PREP_INTO;
Example 2: Showing the effect of the SQLCA support.
#include <stdio.h>
#include <sql_rdb_headers.h>
exec sql
declare alias filename 'db$:mf_personnel';
exec sql
include SQLCA;
char * s1 = "begin insert into work_status values (?, ?, ?);\
select count(*) into ? from work_status; end";
void main ()
{
int i;
SQLCA.SQLERRD[2] = SQLCA.SQLERRD[3] = 1;
exec sql
prepare stmt from :s1;
if (SQLCA.SQLCODE != 0) sql_signal ();
printf( "SQLCA:\n SQLCODE: %9d\n", SQLCA.SQLCODE);
for (i = 0; i < 6; i++)
printf( " SQLERRD[%d]: %9d\n", i, SQLCA.SQLERRD[i]);
}
The results below show that there are three input arguments and
one output argument.
SQLCA:
SQLCODE: 0
SQLERRD[0]: 0
SQLERRD[1]: 0
SQLERRD[2]: 1
SQLERRD[3]: 3
SQLERRD[4]: 0
SQLERRD[5]: 0