SQL support / Dynamic SQL management |
This instruction runs an SQL statement previously prepared.
EXECUTE sid [ USING pvar {IN|OUT|INOUT} [,...] ] [ INTO fvar [,...] ]
The EXECUTE instruction performs the execution of a prepared SQL statement. Once prepared, an SQL statement can be executed as often as needed.
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.
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