The Informix driver supports returning parameter metadata for Select statements that contain parameters in ANSI SQL 92 entry-level predicates, for example, such as COMPARISON, BETWEEN, IN, LIKE, and EXISTS predicate constructs. Refer to the ANSI SQL reference for detailed syntax.

Parameter metadata can be returned for a Select statement if one of the following conditions is true:

  • The statement contains a predicate value expression that can be targeted against the source tables in the associated FROM clause. For example:

    SELECT * FROM foo WHERE bar > ?

    In this case, the value expression "bar" can be targeted against the table "foo" to determine the appropriate metadata for the parameter.

  • The statement contains a predicate value expression part that is a nested query. The nested query's metadata must describe a single column. For example:

    SELECT * FROM foo WHERE (SELECT x FROM y WHERE z = 1) < ?

The following Select statements show further examples for which parameter metadata can be returned:

SELECT col1, col2 FROM foo WHERE col1 = ? and col2 > ?
SELECT ... WHERE colname = (SELECT col2 FROM t2 WHERE col3 = ?)
SELECT ... WHERE colname LIKE ?
SELECT ... WHERE colname BETWEEN ? and ?
SELECT ... WHERE colname IN (?, ?, ?)
SELECT ... WHERE EXISTS(SELECT ... FROM T2 WHERE col1 < ?)

ANSI SQL 92 entry-level predicates in a WHERE clause containing GROUP BY, HAVING, or ORDER BY statements are supported. For example:

SELECT * FROM t1 WHERE col = ? ORDER BY 1

Joins are supported. For example: 

SELECT * FROM t1,t2 WHERE t1.col1 = ?

Fully qualified names and aliases are supported. For example: 

SELECT a, b, c, d FROM T1 AS A, T2 AS B WHERE A.a = ? and B.b = ?"

When parameter metadata is requested for a column defined as an approximate numeric data type, the driver returns a scale of 255, which indicates the column has an approximate numeric data type and has no scale. For example, suppose we create a table where col2 is an approximate numeric data type with a precision of 20:

CREATE table fooTest(col1 int, col2 decimal(20))

The driver returns parameter metadata that indicates that col2 has a data type of decimal, a precision of 20, and a scale of 255.