Back to Contents
Summary:
See also: Transactions, Positioned
Updates, Static SQL, Result Sets, SQL
Errors, Declaring a cursor (DECLARE).
BDL includes basic SQL instructions in the language syntax (see Static
SQL), but only a limited number of SQL instructions are supported this way. Dynamic
SQL Management allows you to execute any kind of SQL statement, hard coded
or created at runtime, with or without SQL parameters, returning or not
returning a result set.
In order to execute an SQL statement with Dynamic SQL, you must first prepare
the SQL statement to initialize a statement handle, then you execute the
prepared statement one or more times:
When you no longer need the prepared statement, you can free the
statement handle to release allocated resources:
When using insert cursors
or SQL statements that produce a result set (like SELECT),
you must declare a cursor with a prepared statement
handle.
Prepared SQL statements can contain SQL parameters by using ? placeholders
in the SQL text. In this case, the EXECUTE or OPEN
instruction supplies input values in the USING clause.
To increase performance efficiency, you can use the PREPARE
instruction, together with an EXECUTE instruction in a loop, to eliminate overhead
caused by redundant parsing and optimizing. For example, an UPDATE
statement located within a WHILE loop is parsed each time the loop
runs. If you prepare the UPDATE statement outside the loop, the
statement is parsed only once, eliminating overhead and speeding statement
execution.
Purpose:
This instruction prepares an SQL statement for execution in
the current database connection.
Syntax:
PREPARE sid FROM sqltext
Notes:
- This instruction sends the SQL text to the database server for parsing,
validation and to generate the execution plan.
- sid is an identifier to handle the prepared SQL statement.
- A statement identifier can represent only one SQL statement at a time.
You can execute a new PREPARE instruction with an existing statement
identifier if you wish to assign the text of a different SQL statement to
the statement identifier.
- sqltext is a string
expression containing the SQL statement to be prepared.
- The SQL statement can have parameter placeholders, identified by the
question mark (?) character.
- Prepared SQL statements can be executed with the EXECUTE
instruction.
- Prepared SQL statements can be used to declare cursors
with the DECLARE instruction.
- The scope of reference of the sid statement identifier
is local to the module where it is declared.
- Resources allocated by PREPARE can be released later by
the FREE instruction.
Warnings:
- You cannot directly reference a variable
in the text of a prepared SQL statement; you must use
question mark (?) placeholders instead.
- The number of prepared statements in a single program is limited by the
database server and the available memory. Make sure that you free
the resources when you no longer need the prepared statement.
- The identifier of a statement that was prepared in one
module cannot be referenced from another module.
- You cannot use question mark (?) placeholders for SQL
identifiers such as a table name or a column name; you must specify these
identifiers in the statement text when you prepare it.
- Some database servers like Informix support multiple SQL statement
preparation in a unique PREPARE instruction, but most database
servers avoid multiple statements.
Example:
01 MAIN
02 DATABASE stores
03 PREPARE s1 FROM "UPDATE tab SET col=? WHERE key=?"
04 END MAIN
Purpose:
This instruction runs an SQL statement previously prepared in
the same database connection.
Syntax:
EXECUTE sid [ USING pvar [,...]
] [ INTO fvar [,...] ]
Notes:
- Once prepared, an SQL statement can be executed as often as needed.
- sid is an identifier to handle the prepared
SQL statement.
- If the SQL statement has (?) parameter placeholders, you must
specify the USING clause to provide a list of variables as parameter buffers.
Parameter values are assigned by position.
- pvar is a variable containing an input value for an SQL parameter.
- If the SQL statement returns a result set with one row, you can
specify the INTO clause to provide a list of variables to receive the
result set column values. Fetched values are assigned by position.
- fvar is a variable used as fetch buffer, when the prepared
statement returns a single database row.
- If the SQL statement returns a result set with more than one row, the
instruction raises an exception.
Warnings:
- You cannot execute a prepared SQL statement based on database tables
if the table structure has changed (ALTER TABLE) since the PREPARE
instruction; you must re-prepare the SQL statement.
Example:
01 MAIN
02 DEFINE var1 CHAR(20)
03 DEFINE var2 INTEGER
04
05 DATABASE stores
06
07 PREPARE s1 FROM "UPDATE tab SET col=? WHERE key=?"
08 LET var1 = "aaaa"
09 LET var2 = 345
10 EXECUTE s1 USING var1, var2
11
12 PREPARE s2 FROM "SELECT col FROM tab WHERE key=?"
13 LET var2 = 564
14 EXECUTE s2 USING var2 INTO var1
15
16 END MAIN
Purpose:
This instruction releases the resources allocated to a prepared
statement.
Syntax:
FREE sid
Notes:
- sid is the identifier of the prepared SQL statement.
Warnings:
- After resources are released, the statement identifier cannot be
referenced by a cursor, or by the EXECUTE
statement, until you prepare the statement again.
Example:
01 FUNCTION update_customer_name( key, name )
02 DEFINE key INTEGER
03 DEFINE name CHAR(10)
04 PREPARE s1 FROM "UPDATE customer SET name=? WHERE customer_num=?"
05 EXECUTE s1 USING name, key
06 FREE s1
07 END FUNCTION
Purpose:
This instruction passes an SQL statement to the database
server for execution in
the current database connection.
Syntax:
EXECUTE IMMEDIATE sqltext
Notes:
- sqltext is a string
expression containing the SQL statement to be executed.
- The SQL statement must be a single statement without parameters,
returning no result set.
- This instruction performs the functions of PREPARE,
EXECUTE and FREE in one
step.
Warnings:
- The SQL statement cannot contain SQL parameters.
- The SQL statement must not produce a result set.
Example:
01 MAIN
02 DATABASE stores
03 EXECUTE IMMEDIATE "UPDATE tab SET col='aaa' WHERE key=345"
04 END MAIN