Distinguish between columns and collection variables

When you use the collection-derived table clause with a collection host variable in an SQL statement (such as SELECT, INSERT, or UPDATE), the statement is not sent to the database server for processing. Instead, Informix® ESQL/C processes the statement. Consequently, some of the syntax checking that the database server performs is not done on SQL statements that include the collection-derived table clause.

In particular, the Informix ESQL/C preprocessor cannot distinguish between column names and host variables. Therefore, when you use the collection-derived table clause with an UPDATE or INSERT statement, you must use valid host-variable syntax in:
  • The SET clause of an UPDATE statement
  • The VALUES clause of an INSERT statement