SQL to C data conversion in CLI

SQL to C data conversion in CLI allows you to convert character SQL data, graphic SQL data, numeric SQL data, XML SQL data, date SQL data, binary SQL data, time SQL data and timestamp SQL data.
For a given SQL data type:
  • the first column of the table lists the legal input values of the fCType argument in SQLBindCol() and SQLGetData().
  • the second column lists the outcomes of a test, often using the cbValueMax argument specified in SQLBindCol() or SQLGetData(), which the driver performs to determine if it can convert the data.
  • the third and fourth columns list the values (for each outcome) of the rgbValue and pcbValue arguments specified in the SQLBindCol() or SQLGetData() after the driver has attempted to convert the data.
  • the last column lists the SQLSTATE returned for each outcome by SQLFetch(), SQLExtendedFetch(), SQLGetData() or SQLGetSubString().

The tables list the conversions defined by ODBC to be valid for a given SQL data type.

If the fCType argument in SQLBindCol() or SQLGetData() contains a value not shown in the table for a given SQL data type, SQLFetch(), or SQLGetData() returns the SQLSTATE 07006 (Restricted data type attribute violation).

If the fCType argument contains a value shown in the table but which specifies a conversion not supported by the driver, SQLFetch(), or SQLGetData() returns SQLSTATE HYC00 (Driver not capable).

Though it is not shown in the tables, the pcbValue argument contains SQL_NULL_DATA when the SQL data value is NULL. For an explanation of the use of pcbValue when multiple calls are made to retrieve data, see SQLGetData().

When SQL data is converted 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 HY009 (Invalid argument value).

In the following tables:
Length of data
the total length of the data after it has been converted to the specified C data type (excluding the null termination byte if the data was converted to a string). This is true even if data is truncated before it is returned to the application.
Significant digits
the minus sign (if needed) and the digits to the left of the decimal point.
Display size
the total number of bytes needed to display data in the character format.

Converting character SQL data to C data

The character SQL data types are:
  • SQL_CHAR
  • SQL_VARCHAR
  • SQL_LONGVARCHAR
  • SQL_CLOB
Table 1. Converting character SQL data to C data
fCType Test rgbValue pcbValue SQLSTATE
SQL_C_CHAR Length of data < cbValueMax Data Length of data 00000
Length of data >= cbValueMax Truncated data Length of data 01004
SQL_C_BINARY Length of data <= cbValueMax Data Length of data 00000
Length of data > cbValueMax Truncated data Length of data 01004
SQL_C_SHORT
SQL_C_LONG
SQL_C_FLOAT
SQL_C_FLOAT
SQL_C_TINYINT
SQL_C_BIT
SQL_C_UBIGINT
SQL_C_SBIGINT
SQL_C_NUMERIC c
 Data converted without truncation a Data Size of the C data type
00000
Data converted with truncation, but without loss of significant digits a Data Size of the C data type 01004
Conversion of data would result in loss of significant digitsa Untouched Size of the C data type 22003
Data is not a number a Untouched Size of the C data type 22005
SQL_C_TYPE_DATE Data value is a valid date a Data 6 b 00000
Data value is not a valid date a Untouched 6 b 22007
SQL_C_TYPE_TIME Data value is a valid time a Data 6 b 00000
Data value is not a valid time a Untouched 6 b 22007
SQL_C_TYPE_TIMESTAMP Data value is a valid timestamp a Data 16 b 00000
Data value is not a valid timestamp a Untouched 16 b 22007
SQL_C_TIMESTAMP_EXT Data value is a valid timestamp a Data 20 00000
Data value is not a valid timestamp a Untouched 20 22007
Note:
a
The value of cbValueMax is ignored for this conversion. The driver assumes that the size of rgbValue is the size of the C data type.
b
This is the size of the corresponding C data type.
c
SQL_C_NUMERIC is only supported on Windows platforms.

SQLSTATE 00000 is not returned by SQLGetDiagRec(), rather it is indicated when the function returns SQL_SUCCESS.

Converting graphic SQL data to C data

The graphic SQL data types are:
  • SQL_GRAPHIC
  • SQL_VARGRAPHIC
  • SQL_LONGVARGRAPHIC
  • SQL_DBCLOB
Table 2. Converting GRAPHIC SQL data to C data
fCType Test rgbValue pcbValue SQLSTATE
SQL_C_CHAR Number of double byte characters * 2 <= cbValueMax Data Length of data(octects) 00000
Number of double byte characters * 2 > cbValueMax Truncated data, to the nearest even byte that is less than cbValueMax. Length of data(octects) 01004
SQL_C_DBCHAR Number of double byte characters * 2 < cbValueMax Data Length of data(octects) 00000
Number of double byte characters * 2 >= cbValueMax Truncated data, to the nearest even byte that is less than cbValueMax. Length of data(octects) 01004
Note: SQLSTATE 00000 is not returned by SQLGetDiagRec(), rather it is indicated when the function returns SQL_SUCCESS.

When converting to floating point values, SQLSTATE 22003 will not be returned if non-significant digits of the resulting value are lost.

Converting numeric SQL data to C data

The numeric SQL data types are:
  • SQL_DECIMAL
  • SQL_NUMERIC
  • SQL_SMALLINT
  • SQL_INTEGER
  • SQL_BIGINT
  • SQL_REAL
  • SQL_DECFLOAT
  • SQL_FLOAT
  • SQL_DOUBLE
Table 3. Converting numeric SQL data to C data
fCType Test rgbValue pcbValue SQLSTATE
SQL_C_CHAR
Display size < cbValueMax Data Length of data 00000
Number of significant digits < cbValueMax Truncated data Length of data 01004
Number of significant digits >= cbValueMax Untouched Length of data 22003
SQL_C_DBCHAR
SQL_C_WCHAR
 Display size * 2 < cbValueMax Data Length of data 00000
Number of significant digits * 2 < cbValueMax Truncated Data Length of Data 01004
Number of significant digits * 2 >= cbValueMax Untouched Length of Data 22003
SQL_C_SHORT
SQL_C_LONG
SQL_C_FLOAT
SQL_C_DOUBLE
SQL_C_TINYINT
SQL_C_BIT
SQL_C_UBIGINT
SQL_C_SBIGINT
SQL_C_NUMERIC b
 Data converted without truncation a Data Size of the C data type 00000
Data converted with truncation, but without loss of significant digits a Truncated data Size of the C data type 01004
Conversion of data would result in loss of significant digits a Untouched Size of the C data type 22003
Note:
a
The value of cbValueMax is ignored for this conversion. The driver assumes that the size of rgbValue is the size of the C data type.
b
SQL_C_NUMERIC is only supported on Windows platforms.

SQLSTATE 00000 is not returned by SQLGetDiagRec(), rather it is indicated when the function returns SQL_SUCCESS.

Converting binary SQL data to C data

The binary SQL data types are:
  • SQL_BINARY
  • SQL_VARBINARY
  • SQL_LONGVARBINARY
  • SQL_BLOB
Table 4. Converting binary SQL data to C data
fCType Test rgbValue pcbValue SQLSTATE
SQL_C_CHAR (Length of data) < cbValueMax Data Length of data N/A
(Length of data) >= cbValueMax Truncated data Length of data 01004
SQL_C_BINARY Length of data <= cbValueMax Data Length of data N/A
Length of data > cbValueMax Truncated data Length of data 01004
Note: The SQL_BINARY and SQL_VARBINARY data types are supported for Db2® for IBM® i Version 6 Release 1 servers or later releases.

Converting XML SQL data to C data

The XML SQL data type is:
  • SQL_XML
Table 5. Converting XML SQL data to C data
fCType Test rgbValue pcbValue SQLSTATE
SQL_C_CHAR Length of data < cbValueMax Data Length of data 00000
Length of data >= cbValueMax Truncated data Length of data 01004
SQL_C_BINARY Length of data <= cbValueMax Data Length of data 00000
Length of data > cbValueMax Truncated data Length of data 01004
SQL_C_BINARYXML Length of data <= cbValueMax Data Length of data 00000
Length of data > cbValueMax Truncated data Length of data 01004
SQL_C_DBCHAR Number of double-byte characters * 2 < cbValueMax Data Length of data 00000
Number of double-byte characters * 2 >= cbValueMax Truncated data, to the nearest even byte that is less than cbValueMax Length of data 01004
SQL_C_WCHAR Number of double-byte characters * 2 < cbValueMax Data Length of data 00000
Number of double-byte characters * 2 >= cbValueMax Truncated data, to the nearest even byte that is less than cbValueMax Length of data 01004
Note:
  1. SQLSTATE 00000 is not returned by SQLGetDiagRec(), rather it is indicated when the function returns SQL_SUCCESS.
  2. Length of data includes any XML declaration in the target encoding.
  3. The SQL_XML data type is not supported for use with an Informix® data server.

Converting date SQL data to C data

The date SQL data type is:
  • SQL_TYPE_DATE
Table 6. Converting date SQL data to C data
fCType Test rgbValue pcbValue SQLSTATE
SQL_C_CHAR cbValueMax >= 11 Data 10 00000
cbValueMax < 11 Untouched 10 22003
SQL_C_TYPE_DATE None a Data 6 b 00000
SQL_C_TYPE_TIMESTAMP None a Data c 16 b 00000
SQL_C_TIMESTAMP_EXT None a Data c 20 00000
Note:
a
The value of cbValueMax is ignored for this conversion. The driver assumes that the size of rgbValue is the size of the C data type.
b
This is the size of the corresponding C data type.
c
The time fields of the TIMESTAMP_STRUCT or TIMESTAMP_STRUCT_EXT structure are set to zero.

SQLSTATE 00000 is not returned by SQLGetDiagRec(), rather it is indicated when the function returns SQL_SUCCESS.

When the date SQL data type is converted to the character C data type, the resulting string is in the "yyyy-mm-dd” format.

Converting Time SQL Data to C Data

The time SQL data type is:
  • SQL_TYPE_TIME
Table 7. Converting time SQL data to C data
fCType Test rgbValue pcbValue SQLSTATE
SQL_C_CHAR cbValueMax >= 9 Data 8 00000
cbValueMax < 9 Untouched 8 22003
SQL_C_TYPE_TIME None a Data 6 b 00000
SQL_C_TYPE_TIMESTAMP None a Data c 16 b 00000
SQL_C_TIMESTAMP_EXT None a Data c 20 00000
Note:
a
The value of cbValueMax is ignored for this conversion. The driver assumes that the size of rgbValue is the size of the C data type.
b
This is the size of the corresponding C data type.
c
The date fields of the TIMESTAMP_STRUCT or TIMESTAMP_STRUCT_EXT structure are set to the current system date of the machine that the application is running, and the time fraction is set to zero.

SQLSTATE 00000 is not returned by SQLGetDiagRec(), rather it is indicated when the function returns SQL_SUCCESS.

When the time SQL data type is converted to the character C data type, the resulting string is in the "hh:mm:ss” format.

Converting timestamp SQL data to C data

The timestamp SQL data type is:
  • SQL_TYPE_TIMESTAMP
Table 8. Converting timestamp SQL data to C data
fCType Test rgbValue pcbValue SQLSTATE
SQL_C_CHAR Display size < cbValueMax Data Length of data 00000
19 <= cbValueMax <= Display size Truncated Data b Length of data 01004
cbValueMax < 19 Untouched Length of data 22003
SQL_C_TYPE_DATE None a Truncated data c 6 e 01004
SQL_C_TYPE_TIME None a Truncated data d 6 e 01004
SQL_C_TYPE_TIMESTAMP None a Data 16 e 00000
SQL_C_TIMESTAMP_EXT None a Data 20 00000
Note:
a
The value of cbValueMax is ignored for this conversion. The driver assumes that the size of rgbValue is the size of the C data type.
b
The fractional seconds of the timestamp are truncated.
c
The time portion of the timestamp is deleted.
d
The date portion of the timestamp is deleted.
e
This is the size of the corresponding C data type.

SQLSTATE 00000 is not returned by SQLGetDiagRec(), rather it is indicated when the function returns SQL_SUCCESS.

When the timestamp SQL data type is converted to the character C data type, the resulting string is in the "yyyy-mm-dd hh:mm:ss.ffffffffffff” format where fractional second digits range from 0 to 12 (regardless of the precision of the timestamp SQL data type). If an application requires the ISO format, set the CLI/ODBC configuration keyword PATCH2=33.

Converting timestamp(p) with timezone SQL data to C data

The timestamp SQL data type is:
  • SQL_TYPE_TIMESTAMP_WITH_TIMEZONE
Table 9. Converting timestamp(p) with timezone SQL data to C data
fCType SQL Type Test/Result SQLSTATE
SQL_C_TYPE_TIMESTAMP_ EXT_TZ SQL_CHAR Data value is a valid timestamp with timezone N/A
Data value is not a valid timestamp with timezone 22007
SQL_C_TYPE_DATE SQL_TYPE_TIMESTAMP_ WITH_TIMEZONE Truncated data 01S07
SQL_C_TYPE_TIME SQL_TYPE_TIMESTAMP_ WITH_TIMEZONE Truncated data 01S07
SQL_C_TYPE_TIMESTAMP SQL_TYPE_TIMESTAMP_ WITH_TIMEZONE Truncated data 01S07

SQL to C data conversion examples

Table 10. SQL to C data conversion examples
SQL data type SQL data value C data type cbValue max rgbValue SQL STATE
SQL_CHAR abcdef SQL_C_CHAR 7 abcdef\0 a 00000
SQL_CHAR abcdef SQL_C_CHAR 6 abcde\0 a 01004
SQL_DECIMAL 1234.56 SQL_C_CHAR 8 1234.56\0 a 00000
SQL_DECIMAL 1234.56 SQL_C_CHAR 5 1234\0 a 01004
SQL_DECIMAL 1234.56 SQL_C_CHAR 4 --- 22003
SQL_DECIMAL 1234.56 SQL_C_FLOAT ignored 1234.56 00000
SQL_DECIMAL 1234.56 SQL_C_SHORT ignored 1234 01004
SQL_TYPE_DATE 1992-12-31 SQL_C_CHAR 11 1992-12-31\0 a 00000
SQL_TYPE_DATE 1992-12-31 SQL_C_CHAR 10 --- 22003
SQL_TYPE_DATE 1992-12-31 SQL_C_TYPE_ TIMESTAMP ignored 1992,12,31, 0,0,0,0 b 00000
SQL_TYPE_ TIMESTAMP 1992-12-31 23:45:55.12 SQL_C_CHAR 23 1992-12-31 23:45:55.12\0 a 00000
SQL_TYPE_ TIMESTAMP 1992-12-31 23:45:55.12 SQL_C_CHAR 22 1992-12-31 23:45:55.1\0 a 01004
SQL_TYPE_ TIMESTAMP 1992-12-31 23:45:55.12 SQL_C_CHAR 18 --- 22003
Note:
a
"\0" represents a null termination character.
b
The numbers in this list are the numbers stored in the fields of the TIMESTAMP_STRUCT or TIMESTAMP_STRUCT_EXT structure.

SQLSTATE 00000 is not returned by SQLGetDiagRec(), rather it is indicated when the function returns SQL_SUCCESS.