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 question mark (?) as 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;