Statement Text

Statement text can be specified in the PREPARE statement
  • as a quoted string
  • or as text that is stored in an ESQL/C program variable
  • or (in SPL routines) as a character expression.
The following restrictions apply to the statement text:
  • The text can contain only SQL statements. It cannot contain statements or comments from the host programming language.
  • The text can contain comments preceded by a double hyphen (--), or that are enclosed in braces ( { } ) or in C-style slash and asterisk ( /* */ ) delimiters.

    These symbols introduce or enclose SQL comments. For more information on SQL comment symbols, see How to Enter SQL Comments.

  • The text can contain either a single SQL statement or (in ESQL/C routines) a series of statements that are separated by semicolon ( ; ) symbols.

    For a list of SQL statements that cannot be prepared, see Restricted Statements in Single-Statement Prepares. For more information on how to prepare multiple SQL statements, see Preparing Multiple SQL Statements.

  • The text cannot include an embedded SQL statement prefix or terminator, such as a dollar sign ( $ ) or the words EXEC SQL.
  • Host-language variables are not recognized as such in prepared text.

    Therefore, you cannot prepare a SELECT (or EXECUTE FUNCTION or EXECUTE PROCEDURE) statement that includes an INTO clause, because the INTO clause requires a host-language variable.

  • The only identifiers that you can use are names that are defined in the database, such as names of tables and columns. For more information on how to use identifiers in statement text, see Preparing Statements with SQL Identifiers.
  • Use a question mark ( ? ) as a placeholder to indicate where data is supplied when the statement executes, as in this example:
    EXEC SQL prepare new_cust from
           'insert into customer(fname,lname) values(?,?)';

For more information on how to use question marks as placeholders, see Preparing Statements That Receive Parameters.

If the prepared statement contains the Collection-Derived Table segment or the collection variable, some additional limitations exist on how you can assemble the text for the PREPARE statement. For information about dynamic SQL, see the HCL OneDB™ ESQL/C Programmer's Manual. SPL routines cannot use dynamic SQL statements to process prepared statements that contain the Collection-Derived Table segment.