|
|
The PREPARE statement dynamically preprocesses an SQL statement for
later execution.
ISQL or Application Programs
PREPARE [REPEAT]{StatementName
[Owner.]ModuleName [(SectionNumber)]}
[IN DBEFileSetName]FROM {'String'
:HostVariable}
- 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 [(SectionNumber)]
This option of the PREPARE statement is used 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 login 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.
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.)
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.
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.
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
|