HP 3000 Manuals HP 3000 Manuals
PREPARE
The PREPARE statement dynamically preprocesses an SQL statement for later
execution.
Scope
ISQL or Application Programs
SQL Syntax
PREPARE [REPEAT] {StatementName }
{[Owner.]ModuleName [(SectionNumber)]}
[IN DBEFileSetName] FROM {'String' }
{:HostVariable}
Parameters
REPEAT specifies the use of semi-permanent sections for
queries. Unlike temporary sections,
semi-permanent sections are retained in memory
until the DBEnvironment session ends, not when
the current transaction ends.
To improve performance, you can set the Authorize
Once per Session flag to ON with the SQLUtil
ALTDBE command when using semi-permanent
sections. However, you must take care to ensure
that a prepared statement is not executed after
authorization has been revoked from the object
that contains that statement.
StatementName This option of the PREPARE statement is used in
an application program; it cannot be used
interactively. Refer to the ALLBASE/SQL
application programming guide for the language
you are using to determine whether this statement
is supported in that language.
StatementName specifies a name for the statement
being preprocessed. You reference StatementName
in an EXECUTE statement later in the current
transaction to execute the dynamically
preprocessed statement. StatementName must
conform to the ALLBASE/SQL rules for a basic name
given in the "Names" chapter. Two PREPARE
statements in an application program cannot
specify the same StatementName.
When necessary, you use the DESCRIBE statement to
determine whether the prepared statement is a
SELECT statement. If so, other information
provided by the DESCRIBE statement helps you
determine how much storage to dynamically
allocate for the query result; then you reference
the StatementName in a DECLARE CURSOR statement
and use the cursor to execute the dynamically
preprocessed SELECT statement.
If it is possible that dynamic parameters are
present in the prepared statement, you must use
the DESCRIBE statement with the INPUT clause. If
dynamic parameters are present, the appropriate
data buffer or host variables must be loaded with
the values of any dynamic parameters before the
statement can be executed.
See related ALLBASE/SQL statements in this manual
and the appropriate ALLBASE/SQL application
programming guide for details of these
programming methods.
[Owner.]ModuleName This option of the PREPARE statement is used
[(SectionNumber)] interactively; it cannot be used in an
application program.
This option specifies an identifier to be
assigned to the statement being preprocessed.
Later, the identifier can be specified in an
EXECUTE statement to execute the dynamically
preprocessed statement.
The section number is an integer to be used in
identifying the dynamically preprocessed
statement. You can group several related
sections under the same module name by giving
each one a different section number. You can
specify any section number from 1 to 32767. If
you do not specify a section number, section
number 1 is assumed.
You must not already have a dynamically
preprocessed statement with the same module name
and section number. You must not already have a
preprocessed application program with the
specified module name.
You can specify an owner name if you have DBA
authority. Non-DBA users can specify the name of
any group of which they are a member. Otherwise,
ALLBASE/SQL assigns your logon name as the owner
name of the module.
You cannot interactively prepare a SELECT
statement.
DBEFileSetName identifies the DBEFileSet used to store the
dynamically prepared statement. If not
specified, the default SECTIONSPACE DBEFileSet is
used. (Refer to syntax for the SET DEFAULT
DBEFILESET statement.)
String is the statement to be preprocessed. The
preprocessor cannot process more than 32,762
characters. If the string contains embedded
strings, delimit the embedded strings with double
single quotation marks as follows:
PREPARE MyStatement FROM 'DELETE FROM PurchDB.Parts
WHERE PartNumber = ''1123-P-01'''
HostVariable specifies a host variable having as its value a
character string which is the statement to be
preprocessed. The preprocessor cannot process
more than 32,762 characters. However, the length
of a string contained in a host variable is
limited by the defined length of the host
variable.
Description
* You cannot use the PREPARE statement to preprocess the following
statements:
ADVANCE BEGIN DECLARE SECTION BEGIN WORK
CLOSE COMMIT WORK CONNECT
DECLARE CURSOR DELETE WHERE CURRENT DESCRIBE
DISCONNECT END DECLARE SECTION EXECUTE
EXTRACT FETCH INCLUDE
OPEN PREPARE RELEASE
ROLLBACK WORK SET CONNECTION SET SESSION
SET TRANSACTION SETOPT START DBE
STOP DBE SQLEXPLAIN UPDATE WHERE CURRENT
TERMINATE USER WHENEVER
* You cannot interactively prepare a SELECT statement.
* A statement to be dynamically preprocessed in an application
program must be terminated with a semicolon.
* You cannot prepare a statement which contains host variables.
Dynamic parameters should be used instead. (Use PREPARE without
the REPEAT option.)
* In an application program, a dynamically preprocessed statement
(PREPARE without the REPEAT option) is automatically deleted from
the system at the end of the transaction in which it was prepared.
It cannot be executed in any other transaction.
* When a PREPARE statement is issued interactively, the dynamically
preprocessed statement is stored in the system catalog until
deleted by a DROP MODULE statement. The statement is not stored,
however, if you specify an owner name of TEMP.
* If the IN DBEFileSetName clause is specified, but the module owner
does not have SECTIONSPACE authority for the specified DBEFileSet,
a warning is issued and the default SECTIONSPACE DBEFileSet is
used instead. (Refer to syntax for the GRANT statement and the
SET DEFAULT DBEFILESET statement.)
Authorization
You do not need authorization to use the PREPARE statement. However, the
authority required to execute the dynamically preprocessed statement
depends on whether the statement is executed programmatically or
interactively. Refer to the EXECUTE statement authorization for details.
To specify a DBEFileSetName for a prepared section, the module owner must
have SECTIONSPACE authority on the referenced DBEFileSet.
Examples
1. Interactive use
PREPARE Statistics(1)
FROM 'UPDATE STATISTICS FOR TABLE PurchDB.Orders'
PREPARE Statistics(2)
FROM'UPDATE STATISTICS FOR TABLE PurchDB.OrderItems'
Two sections for module Statistics are stored in the system
catalog.
EXECUTE Statistics(1)
The statistics for table PurchDB.Orders are updated.
EXECUTE Statistics(2)
The statistics for table PurchDB.OrderItems are updated.
DROP MODULE Statistics
Both sections of the module are deleted.
2. Programmatic use
If you know in advance that the statement to be dynamically
preprocessed is not a SELECT statement and does not contain
dynamic parameters, you can prepare it and execute it in one step,
as follows:
EXECUTE IMMEDIATE :Dynam1
It may be more appropriate to prepare and execute the statement in
separate operations. For example, if you don't know the format of
a statement:
PREPARE Dynamic1 FROM :Dynam1
The statement stored in :Dynam1 is dynamically preprocessed.
DESCRIBE Dynamic1 INTO Sqlda
If Dynamic1 is not a SELECT statement, the Sqld field of the Sqlda
data structure is 0. In this case, if you know there are no
dynamic parameters in the prepared statement, use the EXECUTE
statement to execute the dynamically preprocessed statement.
If it is possible that dynamic parameters are present in the
prepared statement, you must describe the statement for input:
DESCRIBE INPUT Dynamic1 USING SQL DESCRIPTOR SqldaIn
If dynamic parameters are present, the appropriate data buffer or
host variables must be loaded with the values of any dynamic
parameters. Then if the statement is not a query, it can be
executed, as in this example using a data buffer:
EXECUTE Dynamic1 USING SQL DESCRIPTOR SqldaIn
If Dynamic1 is a SELECT statement and the language you are using
supports dynamically defined SELECT statements, use a cursor to
manipulate the rows in the query result:
DECLARE Dynamic1Cursor CURSOR FOR Dynamic1
Place the appropriate values into the SQL descriptor areas. Use
the USING DESCRIPTOR clause of the OPEN statement to identify
where dynamic parameter information is located. Use the USING
DESCRIPTOR clause of the FETCH statement to identify where to
place the rows selected.
OPEN Dynamic1Cursor USING SQL DESCRIPTOR SqldaIn
Load related dynamic parameter data into the input data buffer.
FETCH Dynamic1Cursor USING DESCRIPTOR SqldaOut
:
When all rows have been processed, close the cursor:
CLOSE Dynamic1Cursor