SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
P-26
Considerations—PREPARE

Cannot refer to host variables

Cannot use an INTO clause if it is a SELECT

Cannot be CLOSE, DECLARE CURSOR, DESCRIBE, DESCRIBE INPUT,
EXECUTE, EXECUTE IMMEDIATE, FETCH, OPEN, PREPARE, or RELEASE
Considerations—PREPARE

If a PREPARE statement fails, any subsequent attempt to execute the named
statement fails.
Only the process that executes the PREPARE can execute the associated
prepared statement. The prepared statement is available for execution until the
process (the program or SQLCI session) terminates, executes another PREPARE
statement that uses the same statement name (either successfully or
unsuccessfully), or (programs only) releases the host variable that contains the
statement name with a RELEASE statement.
In programs, the scope of a prepared statement depends partly on the rules of the
host language in which the PREPARE executes. For more information, see the
SQL/MP programming manual for your host language.

Unless a CONTROL QUERY BIND NAMES AT EXECUTION directive is in effect
when the PREPARE executes, a prepared statement uses defaults and DEFINEs
in effect at the time it is prepared, not the time it executes. (For information, see
CONTROL QUERY Directive on page C-74 or Name Resolution on page N-2.)

You can have up to 20 prepared statements in an SQLCI session. (Programs can
have more prepared statements.)
Examples—PREPARE

This example uses PREPARE to compile an SQL statement stored in :INTEXT, a
varying length character variable. The program constructs the SQL statement (not
shown), compiles it (naming it OPERATION1), and then executes it.
...
EXEC SQL
PREPARE OPERATION1 FROM :INTEXT;
...
EXEC SQL
EXECUTE OPERATION1;
...

This SQLCI example prepares a SELECT statement (naming it EMPCOM) and
then enters the DISPLAY STATISTICS command to display the preparation
statistics:
>> PREPARE EMPCOM FROM
+> "SELECT FIRST_NAME, LAST_NAME, DEPTNUM"
+> & "FROM PERSNL.EMPLOYEE WHERE DEPTNUM <> 1500"
+> & "AND SALARY <= (SELECT AVG (SALARY)"