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.