When the values to be passed to an SQL statement are not known at compile time, or the same statement is going to be used many times, then prepared statements can be useful.
The statement is prepared using the command PREPARE. For the values that are not known yet, use the placeholder "?":
EXEC SQL PREPARE stmt1 FROM "SELECT oid, datname FROM pg_database WHERE oid = ?" END-EXEC.
If a statement returns a single row, the application can call EXECUTE after PREPARE to execute the statement, supplying the actual values for the placeholders with a USING clause:
EXEC SQL EXECUTE stmt1 INTO :dboid, :dbname USING 1 END-EXEC.
If a statement returns multiple rows, the application can use a cursor declared based on the prepared statement. To bind input parameters, the cursor must be opened with a USING clause:
EXEC SQL PREPARE stmt1 FROM "SELECT oid,datname FROM pg_database WHERE oid > ?" END-EXEC.
EXEC SQL DECLARE foo_bar CURSOR FOR stmt1 END-EXEC.
* when end of result set reached, break out of while loop
EXEC SQL WHENEVER NOT FOUND GOTO FETCH-END END-EXEC.
EXEC SQL OPEN foo_bar USING 100 END-EXEC.
...
PERFORM NO LIMIT
EXEC SQL FETCH NEXT FROM foo_bar INTO :dboid, :dbname END-EXEC
END-PERFORM.
FETCH-END.
EXEC SQL CLOSE foo_bar END-EXEC.When you don't need the prepared statement anymore, you should deallocate it:
EXEC SQL DEALLOCATE PREPARE name END-EXEC.
For more details about PREPARE, see "D.11.10 PREPARE". Also see "D.5 Dynamic SQL"for more details about using placeholders and input parameters.