Prepared Statements

Prepared statements can be used when the value passed to an SQL statement is unknown at compile time or the same statement will be used multiple times.

• Statements are prepared using the PREPARE command. For the values that are not known yet, use the placeholder (?).

  EXEC SQL PREPARE stmt1 FROM "SELECT oid, datname FROM pg_database WHERE oid = ?";

• 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;

• 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 > ?";
  EXEC SQL DECLARE foo_bar CURSOR FOR stmt1;
  /* When the end of the result set is reached, exit the while loop. */
  EXEC SQL WHENEVER NOT FOUND DO BREAK;
  EXEC SQL OPEN foo_bar USING 100;
  ...
  while (1)
  {
      EXEC SQL FETCH NEXT FROM foo_bar INTO :dboid, :dbname;
      ...
  }
  EXEC SQL CLOSE foo_bar;

• When a prepared statement is no longer needed, it should be deallocated.

  EXEC SQL DEALLOCATE PREPARE name;