Expansion of Statement-name

The expanded parameters of statement-name represent a dynamically-prepared statement.
idmscu19
The expanded parameters of statement-name represent a dynamically-prepared statement.
This article describes the following information:
2
2
Syntax
Expansion of statement-name
►►──┬── 
static-statement-name
 ────┬───────────────────────────────────────────►◄     └── 
extended-statement-name
 ──┘
Expansion of static-statement-name
►►── statement-name ──────────────────────────────────────────────────────────►◄
Expansion of extended-statement-name
►►──┬─────────────┬───┬─ 'cursor-name' ────┬──────────────────────────────────►◄     ├─── LOCAL ◄ ─┤   ├─ :host-variable ───┤     └─── GLOBAL ──┘   ├─ 
local-variable
 ───┤                       └─ 
routine-parameter
 ┘
Parameters
  • statement-name
    Specifies the name of the statement as an identifier.
  • '
    statement-name
    '
    Specifies the name of the statement as a literal whose value must conform to the rules for an identifier.
  • :
    host-variable
    Specifies the name of the statement as a host-variable whose value must conform to the rules for an identifier.
  • local-variable
    Specifies the name of the statement as a local-variable whose value must conform to the rules for an identifier.
  • routine-parameter
    Specifies the name of the statement as a routine-parameter whose value must conform to the rules for an identifier.
  • LOCAL/GLOBAL
    Specifies the scope of the associated statement name:
    • LOCAL indicates that the statement can be referenced only from within the program where it is prepared.
    • GLOBAL indicates that the statement can be referenced from any program executing within the same SQL transaction.
    The default is LOCAL.
Usage
Static Versus Extended Statement Names
A static statement name is one coded as a simple identifier. The following PREPARE statement assigns the static name "select1" to the statement being prepared:
PREPARE select1 from :select1-text
An extended statement name is one coded either as a literal, a host-variable, a local-variable, or a routine-parameter.
MOVE 'SELECT1' to statement-nam PREPARE :statement-nam FROM :select1-text
If a statement is prepared using a static name, it must be referenced using a static name; similarly, if it is prepared using an extended name, it must be referenced using an extended name that has the same scope option.
Uniqueness of Statement Names
Static and extended names do not have to be unique with respect to each other. If two statements are assigned the same value for a name, they are considered two separate statements provided that either:
  • One of the names is static while the other is extended.
  • Both of the names are extended, but they have different scopes, as indicated by their LOCAL/GLOBAL parameter.
Example
Static-statement-name
The following PREPARE statement creates a statement using a static statement name of S1. It is referenced within the subsequent DESCRIBE statement:
EXEC SQL   PREPARE S1 FROM :TEXT END-EXEC EXEC SQL   DESCRIBE S1 USING DESCRIPTOR SQLDA END-EXEC
Extended-statement-name
The following PREPARE statement creates a local statement using an extended statement name of S1. It is then referenced in the subsequent DESCRIBE statement:
EXEC SQL   PREPARE 'S1' FROM :TEXT END-EXEC EXEC SQL   DESCRIBE 'S1' USING DESCRIPTOR SQLDA END-EXEC
Even though S1 is used as the statement name in both of the above examples, two separate statements are created: one with a static name of S1 and one with an extended name of S1.
Global Extended-statement-name
The following PREPARE statement creates a global statement using an extended statement name whose value is not known until runtime. In this case, the value 'S2' is moved to the host variable before the statement is executed and will be the name of the statement created:
MOVE 'S2' TO :SNAME EXEC SQL   PREPARE GLOBAL :SNAME FROM :TEXT END-EXEC
Since this is a global statement, it can be referenced in a different program than the one where the PREPARE statement appears. For example, the following DESCRIBE statement might be contained in a different program:
EXEC SQL   DESCRIBE GLOBAL 'S2' END-EXEC
It does not matter that in one case the name of the statement is supplied through a host-variable and in the other it is specified as a literal. They both refer to the global statement S2.