Call Level Interface Guide and Reference

Converting Data from SQL to C Data Types

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 198. 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 digits^a Untouched Size of the C data type 22003
Data is not a number ^a Untouched Size of the C data type 22005
SQL_C_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_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_TIMESTAMP Data value is a valid timestamp ^a Data 16 ^b 00000
Data value is not a valid timestamp ^a Untouched 16 ^b 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 32-bit Windows platforms.

SQLSTATE 00000 is not returned by SQLError(), 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 199. 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 SQLError(), 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_FLOAT

SQL_DOUBLE

Table 200. 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_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 32-bit Windows platforms.

SQLSTATE 00000 is not returned by SQLError(), 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 201. 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

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

Converting Date SQL Data to C Data

The date SQL data type is:

SQL_DATE

Table 202. 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_DATE None ^a Data 6 ^b 00000
SQL_C_TIMESTAMP None ^a Data ^c 16 ^b 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 structure are set to zero.

SQLSTATE 00000 is not returned by SQLError(), 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_TIME

Table 203. 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_TIME None ^a Data 6 ^b 00000
SQL_C_TIMESTAMP None ^a Data ^c 16 ^b 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 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 SQLError(), 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_TIMESTAMP

Table 204. 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_DATE None ^a Truncated data ^c 6 ^e 01004
SQL_C_TIME None ^a Truncated data ^d 6 ^e 01004
SQL_C_TIMESTAMP None ^a Data 16 ^e 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 SQLError(), 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[.fff[fff]]" format (regardless of the precision of the timestamp SQL data type).

SQL to C Data Conversion Examples

Table 205. 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_DATE 1992-12-31 SQL_C_CHAR 11 1992-12-31\0 ^a 00000
SQL_DATE 1992-12-31 SQL_C_CHAR 10 --- 22003
SQL_DATE 1992-12-31 SQL_C_ TIMESTAMP ignored 1992,12,31, 0,0,0,0 ^b 00000
SQL_TIMESTAMP 1992-12-31 23:45:55.12 SQL_C_CHAR 23 1992-12-31 23:45:55.12\0 ^a 00000
SQL_TIMESTAMP 1992-12-31 23:45:55.12 SQL_C_CHAR 22 1992-12-31 23:45:55.1\0 ^a 01004
SQL_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 structure.

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

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]