Oninit
Talk to an expert →

Informix Error -485

-485 Number of host variables does not match SELECT list.

This error can occur only if your database is ANSI compliant. The error indicates that the number of host variables in an ESQL statement is not the same as the number of values that the database server returns. In addition, a warning flag is placed in the third element of the sqlwarn structure of sqlca.

The execution of the second statement in the following ESQL/C example returns this error:

$create table mytab (i integer, f float) $select * into :var1 from mytab; --error

If your database is not ANSI compliant, this and similar statements complete without error, and the values of the host variables are set in sequence to their respective returned values. If the number of returned values is smaller than the number of host variables, the remaining host variables are undefined. As with an ANSI-compliant database, a warning flag is placed in the third element of the sqlwarn structure of sqlca.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-485 is specific to ANSI-compliant databases: the number of host variables in a SELECT ... INTO (or similar embedded SQL construct) doesn't match the number of columns the SELECT actually returns. On a non-ANSI-compliant database, this exact mismatch doesn't raise an error at all — values are assigned sequentially, and excess host variables are simply left undefined, with only a warning flag (sqlca.sqlwarn.sqlwarn3) set rather than a hard failure.

  1. A SELECT ... INTO with more or fewer host variables than the query's select list has columns — the direct, only cause.
  2. A query modified to add/remove a column (e.g. SELECT * behavior changing after a schema change) without updating the corresponding host-variable list.
  3. Code developed and tested on a non-ANSI-compliant database, where this same mismatch is silently tolerated, later deployed against an ANSI-compliant database where it's a hard error.
Solutions / Resolution
  1. Match the number of host variables exactly to the number of columns in the select list, per the official guidance, for ANSI-compliant databases.
  2. Avoid SELECT * in embedded SQL with a fixed host-variable list — make the select list explicit so mismatches are caught by inspection rather than relying on runtime behavior.
  3. When porting code from a non-ANSI-compliant database to an ANSI-compliant one, audit every SELECT ... INTO for this exact mismatch, since it was previously silent.
Examples
Matching host variables to the select list
EXEC SQL SELECT order_id, customer_id, status
    INTO :ord_id, :cust_id, :status_val
    FROM orders WHERE order_id = 1001;
The disallowed mismatch (on an ANSI-compliant database)
EXEC SQL SELECT order_id, customer_id, status
    INTO :ord_id, :cust_id
    FROM orders WHERE order_id = 1001;
/* -485: three columns selected, only two host variables given */
Diagnostic Checks
  1. Count the columns in the select list and compare against the number of host variables in the INTO clause.
  2. Confirm whether the database is ANSI-compliant — this exact mismatch is silent (though flagged via sqlwarn3) on non-ANSI-compliant databases.
Related Errors / Related Topics
  • -317 — "The statement failed because you must have the same number of selected columns in each UNION, INTERSECT, or MINUS query." A conceptually similar column-count-matching restriction, in the UNION/INTERSECT/MINUS context instead.
  • -415 — "Data conversion error." A related embedded-SQL value-assignment error, about type conversion rather than column/variable count matching.

This is enforced only on ANSI-compliant databases — code ported from a non-ANSI-compliant one may have this exact mismatch already present, silently tolerated until now.