Errors after a PREPARE statement

When the database server returns an error for a PREPARE statement, this error is usually because of a syntax error in the prepared text. When this occurs, the database server returns the following information:
  • The SQLCODE variable indicates the cause of the error.
  • The sqlca.sqlerrd[4] field contains the offset into the prepared statement text at which the error occurs. Your program can use the value in sqlca.sqlerrd[4] to indicate where the syntax of the dynamically prepared text is incorrect.
If you prepare multiple statements with a single PREPARE statement, the database server returns an error status on the first error in the text, even if it encounters several errors.
Important: The sqlerrd[4] field, which is the offset of the error into the SQL statement, might not always be correct because the preprocessor converts the embedded SQL statements into host-language format. In so doing, the preprocessor might change the relative positions of the elements within the embedded statement.
For example, consider the following statement, which contains an invalid WHERE clause: 
EXEC SQL INSERT INTO tab VALUES (:x, :y, :z) 
   WHERE i = 2;
The preprocessor converts this statement to a string like the following string: 
" insert into tab values ( ? , ? , ? ) where i = 2 "

This string does not have the EXEC SQL keywords. Also, the characters ?, ?, ? have replaced :x, :y, :z (five characters instead of eight). The preprocessor has also dropped a newline character between the left parenthesis (“)”) and the WHERE keyword. Thus, the offset of error in the SQL statement that the database server sees is different from the offset of the error in the embedded SQL statement.

The sqlca.sqlerrd[4] field also reports statement-offset values for errors in the EXECUTE IMMEDIATE and DECLARE statements.