Preparing Multiple SQL Statements

In ESQL/C, you can execute several SQL statements as one action if you include them in the same PREPARE statement. Multistatement text is processed as a unit; actions are not treated sequentially. Therefore, multistatement text cannot include statements that depend on actions that occur in a previous statement in the text. For example, you cannot create a table and insert values into that table in the same prepared statement block.

If a statement in a multistatement prepare returns an error, the whole prepared statement stops executing. The database server does not execute any remaining statements. In most situations, compiled products return error-status information on the error, but do not indicate which statement in the text causes an error. You can use the sqlca.sqlerrd[4] field in the sqlca to find the offset of the errors.

In a multistatement prepare, if no rows are returned from a WHERE clause in the following statements, the database server returns SQLNOTFOUND (100):

UPDATE … WHERE …
SELECT INTO TEMP … WHERE …
INSERT INTO … WHERE …
DELETE FROM … WHERE …

In the next example, four SQL statements are prepared into a single Informix® ESQL/C string called query. Individual statements are delimited with semicolons.

A single PREPARE statement can prepare the four statements for execution, and a single EXECUTE statement can execute the statements that are associated with the qid statement identifier:

sprintf (query,  "%s %s %s %s %s %s %s",
   "update account set balance = balance + ? ",
      "where acct_number = ?;",
   "update teller set balance = balance + ? ",
      "where teller_number = ?;",
   "update branch set balance = balance + ? ",
      "where branch_number = ?;",
   "insert into history values (?, ?);";
EXEC SQL prepare qid from :query;

EXEC SQL begin work;
EXEC SQL execute qid using
      :delta, :acct_number, :delta, :teller_number,
      :delta, :branch_number, :timestamp, :values;
EXEC SQL commit work;

Here the semicolons ( ; ) are required as SQL statement-terminator symbols between each SQL statement in the text that query holds.