EXECUTE (SQL statement)
This instruction runs an SQL statement previously prepared.
Syntax
EXECUTE sid
[
USING pvar {
IN|
OUT|
INOUT}
[
,...]
]
[
INTO fvar [
,...]
]
- sid is an identifier to handle the prepared SQL statement.
- pvar is a variable containing an input value for an SQL parameter.
- fvar is a variable used as fetch buffer.
Usage
The EXECUTE
instruction performs the execution of an SQL statement initiated
by a PREPARE
instruction.
Once prepared, an SQL statement can be executed several times with different SQL parameters.
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.
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. If the SQL statement returns a result
set with more than one row, the instruction raises an exception.
The IN
, OUT
or INOUT
options can only be
used for simple variables, you cannot specify those options for a complete record with the
record.*
notation.
The IN
, OUT
or INOUT
options can be used
to call stored procedures having input / output parameters. Use the IN
,
OUT
or INOUT
options to indicate if a parameter is respectively
for input, output or both.
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
MAIN
DEFINE var1 CHAR(20)
DEFINE var2 INTEGER
DATABASE stores
PREPARE s1 FROM "UPDATE tab SET col=? WHERE key=?"
LET var1 = "aaaa"
LET var2 = 345
EXECUTE s1 USING var1, var2
PREPARE s2 FROM "SELECT col FROM tab WHERE key=?"
LET var2 = 564
EXECUTE s2 USING var2 INTO var1
PREPARE s3 FROM "CALL myproc(?,?)"
LET var1 = 'abc'
EXECUTE s3 USING var1 IN, var2 OUT
END MAIN