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 [(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.

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

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