Converting Data from SQL to C

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

Converting Data from SQL to C

When you call SQLExtendedFetch, SQLFetch, or SQLGetData, IBM Informix ODBC Driver retrieves data from a data source. If necessary, IBM Informix ODBC Driver converts the data from the source data type to the data type that the TargetType argument in SQLBindCol or the fCType argument in SQLGetData specifies. Finally, IBM Informix ODBC Driver stores the data in the location pointed to by the rgbValue argument in SQLBindCol or SQLGetData.

The tables in the following sections describe how IBM Informix ODBC Driver converts data that it retrieves from a data source. For a given Informix ODBC Driver SQL data type, the first column of the table lists the legal input values of the TargetType argument in SQLBindCol and the fCType argument in SQLGetData. The second column lists the outcomes of a test, often using the cbValueMax argument specified in SQLBindCol or SQLGetData, which IBM Informix ODBC Driver performs to determine whether it can convert the data. For each outcome, the third and fourth columns list the values of the rgbValue and pcbValue arguments specified in SQLBindCol or SQLGetData after IBM Informix ODBC Driver tries to convert the data.

The last column lists the SQLSTATE returned for each outcome by SQLExtendedFetch, SQLFetch, or SQLGetData.

If the TargetType argument in SQLBindCol or the fCType argument in SQLGetData contains a value for an Informix ODBC Driver C data type that is not shown in the table for a given Informix ODBC Driver SQL data type, SQLExtendedFetch, SQLFetch, or SQLGetData returns SQLSTATE 07006 (Restricted data type attribute violation). If the fCType argument or the TargetType argument contains a value that specifies a conversion from a driver-specific SQL data type to an Informix ODBC Driver C data type and IBM Informix ODBC Driver does not support this conversion, then SQLExtendedFetch, SQLFetch, or SQLGetData returns SQLSTATE S1C00 (Driver not capable).

Although the tables in this chapter do not show it, the pcbValue argument contains SQL_NULL_DATA when the SQL data value is null. When IBM Informix ODBC Driver converts SQL data to character C data, the character count returned in pcbValue does not include the null-termination byte. If rgbValue is a null pointer, SQLBindCol or SQLGetData returns SQLSTATE S1009 (Invalid argument value).

The following terms and conventions are used in the tables:

Length of data is the number of bytes of C data that are available to return in rgbValue, regardless of whether or not the data is truncated before it returns to the application. For string data, this does not include the null-termination byte.
Display size is the total number of bytes that are needed to display the data in character format.
Words in italics represent function arguments or elements of the Informix ODBC Driver SQL grammar.

Default C Data Types

If you specify SQL_C_DEFAULT for the TargetType argument in SQLBindCol, the fCType argument in SQLGetData, or the ValueType argument in SQLBindParameter, IBM Informix ODBC Driver uses the C data type of the output or input buffer for the SQL data type of the column or parameter to which the buffer is bound.

Standard Default C Data Types

For each Informix ODBC Driver SQL data type, the following table shows the default C data type.

Informix ODBC Driver SQL Data Type (fSqlType): Default Informix ODBC Driver
C Data Type (fCType)
SQL_BIGINT (IDS): SQL_C_CHAR
SQL_BIT (IDS): SQL_C_BITS
SQL_CHAR: SQL_C_CHAR
SQL_DATE: SQL_C_DATE
SQL_DECIMAL: SQL_C_CHAR
SQL_DOUBLE: SQL_C_DOUBLE
SQL_INTEGER: SQL_C_SLONG
SQL_LONGVARBINARY: SQL_C_BINARY
SQL_LONGVARCHAR: SQL_C_CHAR
SQL_NUMERIC: SQL_C_NUMERIC
SQL_REAL: SQL_C_FLOAT
SQL_SMALLINT: SQL_C_SSHORT
SQL_TIMESTAMP: SQL_C_TIMESTAMP
SQL_VARCHAR: SQL_C_CHARS

Additional Default C Data Types for Dynamic Server

For each additional Informix ODBC Driver SQL data type for Dynamic Server, the following table shows the default C data type.

Informix ODBC Driver SQL Data Type (fSqlType): Default Informix ODBC Driver
C Data Type (fCType)
SQL_IFMX_UDT_BLOB: SQL_C_BINARY
SQL_IFMX_UDT_CLOB: SQL_C_BINARY
SQL_INFX_UDT_FIXED: This Informix ODBC Driver SQL data type does not have a default Informix ODBC Driver C data type. Because this Informix ODBC Driver SQL data type can contain binary data or character data, you must bind a variable for this Informix ODBC Driver SQL data type before you fetch a corresponding value. The data type of the bound variable specifies the C data type for the value.
SQL_INFX_UDT_VARYING: This Informix ODBC Driver SQL data type does not have a default Informix ODBC Driver C data type. Because this Informix ODBC Driver SQL data type can contain binary data or character data, you must bind a variable for this Informix ODBC Driver SQL data type before you fetch a corresponding value. The data type of the bound variable specifies the C data type for the value.

SQL to C: Binary

The binary Informix ODBC Driver SQL data type is SQL_LONGVARBINARY. The following table shows the Informix ODBC Driver C data types to which binary SQL data can be converted.

fCType	Test	rgbValue	pcbValue	SQLSTATE
SQL_C_BINARY	Length of data ≤ cbValueMax Length of data > cbValueMax	Data Truncated data	Length of data Length of data	N/A 01004
SQL_C_CHAR	(Length of data) * 2 < cbValueMax (Length of data) * 2 ≥ cbValueMax	Data Truncated data	Length of data Length of data	N/A 01004

When IBM Informix ODBC Driver converts binary SQL data to character C data, each byte (8 bits) of source data is represented as two ASCII characters. These characters are the ASCII character representation of the number in its hexadecimal form. For example, IBM Informix ODBC Driver converts binary 00000001 to "01" and binary 11111111 to "FF."

IBM Informix ODBC Driver converts individual bytes to pairs of hexadecimal digits and terminates the character string with a null byte. Because of this conversion, if cbValueMax is even and is less than the length of the converted data, the last byte of the rgbValue buffer is not used. (The converted data requires an even number of bytes, the next-to-last byte is a null byte, and the last byte cannot be used.)

SQL to C: Boolean (IDS)

The boolean Informix ODBC Driver SQL data type is SQL_BIT. The following table shows the Informix ODBC Driver C data types to which boolean SQL data can be converted. When IBM Informix ODBC Driver converts boolean SQL data to character C data, the possible values are 0 and 1.

fCType	Test	rgbValue	pcbValue	SQLSTATE5
SQL_C_BINARY	cbValueMax ≤ 1 cbValueMax < 1	Data Untouched	1 Untouched	N/A 22003
SQL_C_BIT	IBM Informix ODBC Driver ignores the value of cbValueMax for this conversion. IBM Informix ODBC Driver uses the size of rgbValue for the size of the C data type.	Data	1 (This is the size of the corresponding C data type.)	N/A
SQL_C_CHAR	cbValueMax > 1 cbValueMax ≤ 1	Data Untouched	1 Untouched	N/A 22003

fCType

Test

rgbValue

pcbValue

SQLSTATE5

SQL_C_BINARY

cbValueMax ≤ 1

cbValueMax < 1

Data

Untouched

N/A

22003

SQL_C_BIT

IBM Informix ODBC Driver ignores the value of cbValueMax for this conversion. IBM Informix ODBC Driver uses the size of rgbValue for the size of the C data type.

Data

1
(This is the size of the corresponding C data type.)

N/A

SQL_C_CHAR

cbValueMax > 1

cbValueMax ≤ 1

Data

Untouched

N/A

22003

SQL to C: Character

The character Informix ODBC Driver SQL data types are:

SQL_CHAR
SQL_LONGVARCHAR
SQL_VARCHAR

The following table shows the Informix ODBC Driver C data types to which character SQL data can be converted. When IBM Informix ODBC Driver converts character SQL data to numeric, date, or time stamp C data, it ignores leading and trailing spaces.

fCType	Test	rgbValue	pcbValue	SQLSTATE
SQL_C_BINARY	Length of data ≤ cbValueMax. Length of data > cbValueMax.	Data Truncated data	Length of data Length of data	N/A 01004
SQL_C_BIT (IDS)	Data is 0 or 1. Data is greater than 0, less than 2, and not equal to 1. Data is less than 0 or greater than or equal to 2. Data is not a numeric-literal.	Data Truncated data Untouched Untouched	1 1 Untouched Untouched (The size of the corresponding C data type is 1.)	N/A 01004 22003 22005
SQL_C_CHAR	Length of data < cbValueMax. Length of data ≥ cbValueMax.	Data Truncated data	Length of data Length of data	N/A 01004
SQL_C_DATE	Data value is a valid date-value. Data value is a valid timestamp-value; time portion is zero. Data value is a valid timestamp-value; time portion is non-zero. (IBM Informix ODBC Driver ignores the date portion of timestamp-value.) Data value is not a valid date-value or timestamp-value. (For all these conversions, IBM Informix ODBC Driver ignores the value of cbValueMax. IBM Informix ODBC Driver uses the size of rgbValue for the size of the C data type.)	Data Data Truncated data Untouched	6 6 6 Untouched (The size of the corresponding C data type is 6.)	N/A N/A 01004 22008
SQL_C_DOUBLE SQL_C_FLOAT	Data is within the range of the data type to which the number is being converted. Data is outside the range of the data type to which the number is being converted. Data is not a numeric-literal. (For all these conversions, IBM Informix ODBC Driver ignores the value of cbValueMax. IBM Informix ODBC Driver uses the size of rgbValue for the size of the C data type.)	Data Untouched Untouched	Size of the C data type Untouched Untouched	N/A 22003 22005
SQL_C_LONG SQL_C_SHORT SQL_C_SLONG SQL_C_SSHORT SQL_C_STINYINT SQL_C_TINYINT SQL_C_ULONG SQL_C_USHORT SQL_C_UTINYINT	Data converted without truncation. Data converted with truncation of fractional digits. Conversion of data would result in loss of whole (as opposed to fractional) digits. Data is not a numeric-literal. (For all these conversions, IBM Informix ODBC Driver ignores the value of cbValueMax. IBM Informix ODBC Driver uses the size of rgbValue for the size of the C data type.)	Data Truncated data Untouched Untouched	Size of the C data type Size of the C data type Untouched Untouched	N/A 01004 22003 22005
SQL_C_TIMESTAMP	Data value is a valid timestamp-value; fractional seconds portion not truncated. Data value is a valid timestamp-value; fractional seconds portion truncated. Data value is a valid date-value. Data value is a valid time-value. Data value is not a valid date-value, time-value, or timestamp-value. (For all these conversions, IBM Informix ODBC Driver ignores the value of cbValueMax. IBM Informix ODBC Driver uses the size of rgbValue for the size of the C data type.)	Data Truncated data Data (IBM Informix ODBC Driver sets the time fields of the time stamp structure to zero.) Data (IBM Informix ODBC Driver sets the date fields of the time stamp structure to the current date.) Untouched	16 16 16 16 Untouched (The size of the corresponding C data type is 16.)	N/A N/A N/A N/A 22008

SQL to C: Date

The date IBM Informix ODBC Driver SQL data type is SQL_DATE. The following table shows the IBM Informix ODBC Driver C data types to which date SQL data can be converted. When IBM Informix ODBC Driver converts date SQL data to character C data, the resulting string is in the "yyyy-mm-dd" format.

fCType	Test	rgbValue	pcbValue	SQLSTATE
SQL_C_BINARY	Length of data ≤ cbValueMax Length of data > cbValueMax	Data Untouched	Length of data Untouched	N/A 22003
SQL_C_CHAR	cbValueMax ≥ 11 cbValueMax < 11	Data Untouched	10 Untouched	N/A 22003
SQL_C_DATE	IBM Informix ODBC Driver ignores the value of cbValueMax for this conversion. IBM Informix ODBC Driver uses the size of rgbValue for the size of the C data type.	Data	6 (This is the size of the corresponding C data type.)	N/A
SQL_C_TIMESTAMP	IBM Informix ODBC Driver ignores the value of cbValueMax for this conversion. IBM Informix ODBC Driver uses the size of rgbValue for the size of the C data type.	Data (IBM Informix ODBC Driver sets the time fields of the time stamp structure to zero.)	16 (This is the size of the corresponding C data type.)	N/A

SQL to C: Numeric

The numeric Informix ODBC Driver SQL data types are:

SQL_DECIMAL
SQL_DOUBLE
SQL_INTEGER
SQL_REAL
SQL_SMALLINT

The following table shows the Informix ODBC Driver C data types to which numeric SQL data can be converted.

fCType	Test	rgbValue	pcbValue	SQLSTATE
SQL_C_BINARY	Length of data ≤ cbValueMax. Length of data > cbValueMax.	Data Untouched	Length of data Untouched	N/A 22003
SQL_C_BIT (IDS)	Data is 0 or 1. Data is greater than 0, less than 2, and not equal to 1. Data is less than 0 or greater than or equal to 2. Data is not a numeric-literal.	Data Truncated data Untouched Untouched	1 1 Untouched Untouched (The size of the corresponding C data type is 1.)	N/A 01004 22003 22005
SQL_C_CHAR	Display size < cbValueMax. Number of whole (as opposed to fractional) digits < cbValueMax. Number of whole (as opposed to fractional) digits ≥ cbValueMax.	Data Truncated data Untouched	Length of data Length of data Untouched	N/A 01004 22003
SQL_C_DOUBLE SQL_C_FLOAT	Data is within the range of the data type to which the number is being converted. Data is outside the range of the data type to which the number is being converted. (IBM Informix ODBC Driver ignores the value of cbValueMax for this conversion. IBM Informix ODBC Driver uses the size of rgbValue for the size of the C data type.)	Data Untouched	Size of the C data type Untouched	N/A 22003
SQL_C_LONG SQL_C_SHORT SQL_C_SLONG SQL_C_SSHORT SQL_C_STINYINT SQL_C_TINYINT SQL_C_ULONG SQL_C_USHORT SQL_C_UTINYINT	Data converted without truncation. Data converted with truncation of fractional digits. Conversion of data would result in loss of whole (as opposed to fractional) digits. (IBM Informix ODBC Driver ignores the value of cbValueMax for this conversion. IBM Informix ODBC Driver uses the size of rgbValue for the size of the C data type.)	Data Truncated data Untouched	Size of the C data type Size of the C data type Untouched	N/A 01004 22003

SQL to C: Time Stamp

The time-stamp Informix ODBC Driver SQL data type is SQL_TIMESTAMP. The following table shows the Informix ODBC Driver C data types to which time-stamp SQL data can be converted.

fCType	Test	rgbValue	pcbValue	SQLSTATE
SQL_C_BINARY	Length of data ≤ cbValueMax. Length of data > cbValueMax.	Data Untouched	Length of data Untouched	N/A 22003
SQL_C_CHAR	cbValueMax > Display size. 20 ≤ cbValueMax ≤ Display size. cbValueMax < 20.	Data Truncated data (IBM Informix ODBC Driver truncates the fractional seconds portion of the time stamp.) Untouched	Length of data Length of data Untouched	N/A 01004 22003
SQL_C_DATE	Time portion of time stamp is zero. Time portion of time stamp is nonzero. (IBM Informix ODBC Driver ignores the value of cbValueMax for this conversion. IBM Informix ODBC Driver uses the size of rgbValue for the size of the C data type.)	Data Truncated data (IBM Informix ODBC Driver truncates the time portion of the time stamp.)	6 6 (The size of the corresponding C data type is 6.)	N/A 01004
SQL_C_TIMESTAMP	Fractional seconds portion of time stamp is not truncated. Fractional seconds portion of time stamp is truncated. (IBM Informix ODBC Driver ignores the value of cbValueMax for this conversion. IBM Informix ODBC Driver uses the size of rgbValue for the size of the C data type.)	Data Truncated data (IBM Informix ODBC Driver truncates the fractional seconds portion of the time stamp.)	16 16 (The size of the corresponding C data type is 16.)	N/A 01004

When IBM Informix ODBC Driver converts time stamp SQL data to character C data, the resulting string is in the "yyyy-mm-dd hh:mm:ss[.f...]" format, where up to nine digits can be used for fractional seconds. Except for the decimal point and fractional seconds, the entire format must be used, regardless of the precision of the time stamp SQL data type.

SQL-to-C Data Conversion Examples

The following table illustrates how IBM Informix ODBC Driver converts SQL data to C data. "\0" represents a null-termination byte ( "\0" represents a wide null termination character when the C data type is SQL_C_WCHAR). IBM Informix ODBC Driver always null-terminates SQL_C_CHAR and SQL_C_WCHAR data. For the combination of SQL_DATE and SQL_C_TIMESTAMP, IBM Informix ODBC Driver stores the numbers that are in the rgbValue column in the fields of the TIMESTAMP_STRUCT structure.

SQL Data Type	SQL Data Value	C Data Type	cbValueMax	rgbValue	SQLSTATE
SQL_CHAR	tigers	SQL_C_CHAR	7	tigers\0	N/A
SQL_CHAR	tigers	SQL_C_CHAR	6	tiger\0	01004
SQL_CHAR	tigers	SQL_C_WCHAR	14	tigers\0	N/A
SQL_CHAR	tigers	SQL_C_WCHAR	12	tiger\0	01004
SQL_DECIMAL	1234.56	SQL_C_CHAR	8	1234.56\0	N/A
SQL_DECIMAL	1234.56	SQL_C_CHAR	5	1234\0	01004
SQL_DECIMAL	1234.56	SQL_C_CHAR	4	—	22003
SQL_DECIMAL	1234.56	SQL_C_WCHAR	16	1234.56\0	N/A
SQL_DECIMAL	1234.56	SQL_C_WCHAR	10	1234\0	01004
SQL_DECIMAL	1234.56	SQL_C_WCHAR	8	—	220023
SQL_DECIMAL	1234.56	SQL_C_FLOAT	Ignored	1234.56	N/A
SQL_DECIMAL	1234.56	SQL_C_SSHORT	Ignored	1234	01004
SQL_DECIMAL	1234.56	SQL_C_STINYINT	Ignored	—	22003
SQL_DOUBLE	1.2345678	SQL_C_DOUBLE	Ignored	1.2345678	N/A
SQL_DOUBLE	1.2345678	SQL_C_FLOAT	Ignored	1.234567	N/A
SQL_DOUBLE	1.2345678	SQL_C_STINYINT	Ignored	1	N/A
SQL_DATE	1992-12-31	SQL_C_CHAR	11	1992-12-31\0	N/A
SQL_DATE	1992-12-31	SQL_C_CHAR	10	—	22003
SQL_DATE	1992-12-31	SQL_C_WCHAR	22	1992-12-31\0	N/A
SQL_DATE	1992-12-31	SQL_C_WCHAR	20	—	22003
SQL_DATE	1992-12-31	SQL_C_TIMESTAMP	Ignored	1992,12,31, 0,0,0,0	N/A
SQL_TIMESTAMP	1992-12-31 23:45:55.12	SQL_C_CHAR	23	1992-12-31 23:45:55.12\0	N/A
SQL_TIMESTAMP	1992-12-31 23:45:55.12	SQL_C_CHAR	22	1992-12-31 23:45:55.1\0	01004
SQL_TIMESTAMP	1992-12-31 23:45:55.12	SQL_C_CHAR	18	—	22003
SQL_TIMESTAMP	1992-12-31 23:45:55.12	SQL_C_WCHAR	46	1992-12-31 23:45:55.12\0	N/A
SQL_TIMESTAMP	1992-12-31 23:45:55.12	SQL_C_WCHAR	44	1992-12-31 23:45:55.1\0	01004
SQL_TIMESTAMP	1992-12-31 23:45:55.12	SQL_C_WCHAR	36	—	22003

Important:

The size of a wide character (wchar_t) is platform dependent. The above examples are applicable to Windows where the size of wide characters is two bytes. On most UNIX platforms, wide characters are four bytes. On IBM AIX versions lower than AIX5L, it is 2 bytes.