Home | Previous Page | Next Page   Data Types > SQL Data Types >

Standard SQL Data Types

The following table lists the standard Informix SQL data types and their corresponding Informix ODBC Driver data types.

Informix SQL Data Type Informix ODBC Driver SQL
Data Type (fSqlType)
Description
BOOLEAN
(IDS)
SQL_BIT 't' or 'f'
BYTE SQL_LONGVARBINARY Binary data of variable length
CHAR(n), CHARACTER(n) SQL_CHAR Character string of fixed length n
(1 ≤ n ≤ 32,767)
CHARACTER VARYING(m, r) SQL_VARCHAR Character string of variable length with maximum length m (1 ≤ m ≤ 255) and minimum amount of reserved space r
(0 ≤ r < m)
DATE SQL_DATE Calendar date
DATETIME SQL_TIMESTAMP Calendar date and time of day
DEC(p, s), DECIMAL(p, s) SQL_DECIMAL Signed numeric value with precision p and scale s
(1 ≤ p ≤ 32; 0 ≤ sp)
DOUBLE PRECISION SQL_DOUBLE Signed numeric value with the same characteristics as the standard C double data type
FLOAT SQL_DOUBLE Signed numeric value with the same characteristics as the standard C double data type
INT, INTEGER SQL_INTEGER Signed numeric value with precision 10, scale 0, and range n
(-2,147,483,647 ≤ n ≤ 2,147,483,647)
INT8 (IDS) SQL_BIGINT Signed numeric value with precision 10, scale 0, and range n
(–(263 - 1) ≤ n ≤ 263 – 1)
INTERVAL MONTH(p) SQL_INTERVAL_MONTH Number of months between two dates; p is the interval leading precision.
INTERVAL YEAR(p) SQL_INTERVAL_YEAR Number of years and months between two dates; p is the interval leading precision.
INTERVAL YEAR(p) TO MONTH SQL_INTERVAL_YEAR_TO_MONTH Number of years and months between two dates; p is the interval leading precision.
INTERVAL DAY(p) SQL_INTERVAL_DAY Number of days between two dates; p is the interval leading precision.
INTERVAL HOUR(p) SQL_INTERVAL_HOUR Number of hours between two date times; p is the interval leading precision.
INTERVAL MINUTE(p) SQL_INTERVAL_MINUTE Number of minutes between two date/times; p is the interval leading precision.
INTERVAL SECOND(p,q) SQL_INTERVAL_SECOND Number of seconds between two date/times; p is the interval leading precision and q is the interval seconds precision.
INTERVAL DAY(p) TO HOUR SQL_INTERVAL_DAY_TO_HOUR Number of days/hours between two date/times; p is the interval leading precision.
INTERVAL DAY(p) TO MINUTE SQL_INTERVAL_DAY_TO_MINUTE Number of days/hours/minutes between two date/times; p is the interval leading precision.
INTERVAL DAY(p) TO SECOND(q) SQL_INTERVAL_DAY_TO_SECOND Number of days/hours/minutes/seconds between two date/times; p is the interval leading precision and q is the interval seconds precision.
INTERVAL HOUR (p) TO MINUTE SQL_INTERVAL_HOUR_TO_MINUTE Number of hours/minutes between two date/times; p is the interval leading precision.
INTERVAL HOUR(p) TO SECOND(q) SQL_INTERVAL_HOUR_TO_SECOND Number of hours/minutes/seconds between two date/times; p is the interval leading precision and q is the interval seconds precision.
INTERVAL MINUTE(p) TO SECOND(q) SQL_INTERVAL_MINUTE
_TO_SECOND
Number of minutes/seconds between two date/times; p is the interval leading precision and q is the interval seconds precision.
LVARCHAR (IDS) SQL_VARCHAR Character string of variable length with length l
(255 ≤ l ≤ 32,000). When connecting to IDS 10.0 servers using the ODBC 2.90 driver, the SQLDescribeCol, SQLColAttributes & SQLDescribeParam APIs report the length mentioned during creation of the LVARCHAR column. If no length was mentioned during creation, length defaults to 2048 bytes.
MONEY(p, s) SQL_DECIMAL Signed numeric value with precision p and scale s
(1 ≤ p ≤ 32; 0 ≤ sp)
NUMERIC SQL_NUMERIC Signed, exact, numeric value with precision p and scale s
(1 ≤ p ≤ 15; 0 ≤ sp)
REAL SQL_REAL Signed numeric value with the same characteristics as the standard C float data type
SERIAL SQL_INTEGER Sequential INTEGER
SERIAL8
(IDS)
SQL_BIGINT Sequential INT8
SMALLFLOAT SQL_REAL Signed numeric value with the same characteristics as the standard C float data type
SMALLINT SQL_SMALLINT Signed numeric value with precision 5, scale 0, and range n
(–32,767 ≤ n ≤ 32,767)
TEXT SQL_LONGVARCHAR Character string of variable length
VARCHAR(m, r) SQL_VARCHAR Character string of variable length with maximum length m
(1 ≤ m ≤ 255) and minimum amount of reserved space r
(0 ≤ r < m)

Using Visual Basic Client-side Cursors

When you use Visual Basic client side cursors to perform rowset update related operations using CHAR or LVARCHAR columns with lengths greater than or equal to 16,385, the IBM Informix ODBC Driver might return an error.

Visual Basic sends the SQL data type to SQLBindParameter as SQL_LONGVARCHAR instead of SQL_VARCHAR when the length is greater than or equal to 16,385. IBM Informix ODBC driver maps SQL_LONGVARCHAR to TEXT data type. Therefore, applications might see the error:

[Informix][Informix ODBC Driver]No cast from text to lvarchar

or

[Informix][Informix ODBC Driver]Illegal attempt to use Text/Byte host variable.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]