solidDB Help : solidDB reference : ODBC: Data types : Converting data from SQL to C data types : Data conversion tables from SQL to C
  
Data conversion tables from SQL to C
The tables in the following sections describe how the driver or data source converts data retrieved from the data source; drivers are required to support conversions to all ODBC C data types from the ODBC SQL data types that they support.
Conversion table description (SQL to C)
The following columns are included in the tables:
▪ For a given ODBC SQL data type, the first column of the table lists the legal input values of the TargetType argument in SQLBindCol and SQLGetData.
▪ The second column lists the outcomes of a test, often using the BufferLength argument specified in SQLBindCol or SQLGetData, which the driver performs to determine if it can convert the data.
▪ For each outcome, the third and fourth columns list the values placed in the buffers specified by the TargetValuePtr and StrLen_or_IndPtr arguments specified in SQLBindCol or SQLGetData after the driver has attempted to convert the data. (The StrLen_or_IndPtr argument corresponds to the SQL_DESC_OCTET_LENGTH_PTR field of the ARD.)
▪ The last column lists the SQLSTATE returned for each outcome by SQLFetch, SQLFetchScroll, or SQLGetData.
If the TargetType argument in SQLBindCol or SQLGetData contains a value for an ODBC C data type not shown in the table for a given ODBC SQL data type, SQLFetch, SQLFetchScroll, or SQLGetData returns SQLSTATE 07006 (Restricted data type attribute violation). If the TargetType argument contains a value that specifies a conversion from a driver-specific SQL data type to an ODBC C data type and this conversion is not supported by the driver, SQLFetch, SQLFetchScroll, or SQLGetData returns SQLSTATE HYC00 (Optional feature not implemented).
Although it is not shown in the tables, the driver returns SQL_NULL_DATA in the buffer specified by the StrLen_or_IndPtr argument when the SQL data value is NULL. The length specified by StrLen_or_IndPtr does not include the null-termination byte. If TargetValuePtr is a null pointer, SQLGetData returns SQLSTATE HY009 (Invalid use of null pointer); in SQLBindCol, this unbinds the columns.
The following terms and conventions are used in the tables:
▪ Byte length of data is the number of bytes of C data available to return in * TargetValuePtr, whether or not the data will be truncated before it is returned to the application. For string data, this does not include the space for the null-termination character.
▪ Character byte length is the total number of bytes needed to display the data in character format.
▪ Words in italics represent function arguments or elements of the SQL grammar. See ODBC: Minimum SQL grammar requirements for the syntax of grammar elements.
SQL to C: Character
The character ODBC SQL data types are:
SQL_CHAR
SQL_VARCHAR
SQL_LONGVARCHAR
SQL_WCHAR
SQL_WVARCHAR
SQL_WLONGVARCHAR
The following table shows the ODBC C data types to which character SQL data can be converted. For an explanation of the columns and terms in the table, see Conversion table description (SQL to C).
 
C type identifier
Test
*TargetValuePtr
*StrLen_or_IndPtr
SQLSTATE
SQL_C_CHAR
Byte length of data < BufferLength
Byte length of data >= BufferLength
Data Truncated data
Length of data in bytes
Length of data in bytes
N/A
01004
SQL_C_WCHAR
Character length of data < BufferLength
(Character length of data) >= BufferLength
Data Truncated data
Length of data in characters
Length of data in characters
N/A
01004
EXACT NUMERIC TYPES (7)
SQL_C_STINYINT
SQL_C_UTINYINT
SQL_C_TINYINT
SQL_C_SSHORT
SQL_C_USHORT
SQL_C_SHORT
SQL_C_SLONG
SQL_C_ULONG
SQL_C_LONG
SQL_C_SBIGINT
SQL_C_UBIGINT
SQL_C_NUMERIC
Data converted without truncation (2)
Data converted with truncation of fractional digits (1)
Conversion of data would result in loss of whole (as opposed to fractional) digits (2)
Data is not a numeric-literal (2)
Data
Truncated data Undefined Undefined
Number of bytes of the C data type
Number of bytes of the C data type
Undefined
Undefined
N/A
01S07
22003
22018
APPROXIMATE NUMERIC TYPES (7)
SQL_C_FLOAT
SQL_C_DOUBLE
Data is within the range of the data type to which the number is being converted (1)
Data is outside the range of the data type to which the number is being converted (1)
Data is not a numeric-literal (2)
Data
Undefined
Undefined
Size of the C data type
Undefined
Undefined
N/A
2003
22018
SQL_C_BINARY
Byte length of data <= BufferLength
Byte length of data > BufferLength
Data
Truncated data
Length of data
Length of data
N/A
01004
SQL_C_TYPE_DATE
Data value is a valid date-value (1)
Data value is a valid timestamp-value; time portion is zero (1)
Data value is a valid timestamp-value; time portion is nonzero (1) (3)
Data value is not a valid date-value or timestamp_value (1)
Data
Data
Truncated data
Undefined
6 (2)
6 (2)
6 (2)
Undefined
N/A
N/A
01S07
22018
SQL_C_TYPE_TIME
Data value is a valid time-value and the fractional seconds value is 0 (1)
Data value is a valid timestamp-value or a valid time_value; fractional seconds portion is zero (1) (4)
Data value is a valid timestamp-value; fractional seconds portion is nonzero (1) (4) (5)
Data value is not a valid timestamp-value or time_value (1)
Data
Data
Truncated data
Undefined
6 (2)
6 (2)
6 (2)
Undefined
N/A
N/A
01S07
22018
SQL_C_TYPE_TIMESTAMP
Data value is a valid timestamp-value or a valid time_value; fractional seconds portion not truncated (1) (4)
Data value is a valid timestamp-value or a valid time_value; fractional seconds portion truncated (1)
Data value is a valid date-value (1)
Data value is a valid time_value (1)
Data value is not a valid date_value, time_value, or timestamp_value (1)
Data
Truncated data
Data (6)
Data (7)
Undefined
16 (2)
16 (2)
16 (2)
16 (2)
Undefined
N/A
01S07
N/A
N/A
22018
1 The value of BufferLength is ignored for this conversion. The driver assumes that the size of *TargetValuePtr is the size of the C data type.
2 This is the size of the corresponding C data type.
3 The time portion of the timestamp-value is truncated.
4 The date portion of the timestamp-value is ignored.
5 The fractional seconds portion of the timestamp is truncated.
6 The time fields of the timestamp structure are set to zero.
7 The date fields of the timestamp structure are set to the current date.
8 The exact numeric types include NUMERIC/DECIMAL as well as integer. These data types store the exact value that you specify, as long as it is within the precision of the data type. The approximate data types include FLOAT/REAL, which store only approximately the value that you specify (in some cases, the least significant digit might be slightly different from what you specified).
When character SQL data is converted to numeric, date, time, or timestamp C data, leading and trailing spaces are ignored.
SQL to C: Numeric
SQL_DECIMAL
SQL_NUMERIC
SQL_TINYINT
SQL_SMALLINT
SQL_INTEGER
SQL_BIGINT
SQL_REAL
SQL_FLOAT
SQL_DOUBLE
The following table shows the ODBC C data types to which numeric SQL data can be converted. For an explanation of the columns and terms in the table, see Conversion table description (SQL to C).
 
C type identifier
Test
*TargetValuePtr
*StrLen_or_IndPtr
SQLSTATE
SQL_C_CHAR
Character byte length <
Data
Length of data in
N/A
 
BufferLength
Truncated data
bytes
01004
 
Number of whole (as opposed to fractional) digits <
Undefined
Length of data in bytes
22003
 
BufferLength
 
Undefined
 
 
Number of whole (as opposed to fractional) digits ?
 
 
 
 
BufferLength
 
 
 
SQL_C_WCHAR
Character length < BufferLength
Number of whole (as opposed to fractional) digits < BufferLength
Number of whole (as opposed to fractional) digits ? BufferLength
Data
Truncated data Undefined
Length of data in bytes
Length of data in bytes
Undefined
N/A
01004
22003
EXACT NUMERIC TYPES (3)
SQL_C_STINYINT
SQL_C_UTINYINT
SQL_C_TINYINT
SQL_C_SBIGINT
SQL_C_UBIGINT
SQL_C_SSHORT
SQL_C_USHORT
SQL_C_SHORT a
SQL_C_SLONG
SQL_C_ULONG
SQL_C_LONG
SQL_C_NUMERIC
Data converted without truncation (1)
Data converted with truncation of fractional digits (1)
Conversion of data would result in loss of whole (as opposed to fractional) digits (1)
Data
Truncated data Undefined
Size of the C data type
Size of the C data type
Undefined
N/A
01S07
22003
APPROXIMATE NUMERIC TYPES (3)
SQL_C_FLOAT
SQL_C_DOUBLE
Data is within the range of the data type to which the number is being converted (1)
Data is outside the range of the data type to which the number is being converted (1)
Data Undefined
Size of the C data type
Undefined
N/A
22003
SQL_C_BINARY
Length of data ? BufferLength Length of data > BufferLength
Data Undefined
Length of data Undefined
N/A
22003
1 The value of BufferLength is ignored for this conversion. The driver assumes that the size of *TargetValuePtr is the size of the C data type.
2 This is the size of the corresponding C data type.
3 The exact numeric types include NUMERIC/DECIMAL as well as integer. These data types store the exact value that you specify, as long as it is within the precision of the data type. The approximate data types include FLOAT/REAL, which store only approximately the value that you specify (in some cases, the least significant digit might be slightly different from what you specified).
SQL to C: Binary
The binary ODBC SQL data types are:
SQL_BINARY
SQL_VARBINARY
SQL_LONGVARBINARY
The following table shows the ODBC C data types to which binary SQL data can be converted. For an explanation of the columns and terms in the table, see Conversion table description (SQL to C).
 
C type identifier
Test
*TargetValuePtr
*StrLen_or_IndPtr
SQLSTATE
SQL_C_CHAR
(Byte length of data) * 2 < BufferLength
(Byte length of data) * 2 >= BufferLength
Data Truncated data
Length of data in bytes
Length of data in bytes
N/A
01004
SQL_C_WCHAR
(Character length of data) * 2 < BufferLength
(Character length of data) * 2 >= BufferLength
Data Truncated data
Length of data in bytes
Length of data in bytes
N/A
01004
SQL_C_BINARY
Byte length of data <= BufferLength
Byte Length of data > BufferLength
Data Truncated data
Length of data in bytes
Length of data in bytes
N/A
01004
When binary SQL data is converted 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, a binary 00000001 is converted to "01"and a binary 11111111 is converted to "FF".
The driver always converts individual bytes to pairs of hexadecimal digits and terminates the character string with a null byte. Because of this, if BufferLength is even and is less than the length of the converted data, the last byte of the
*TargetValuePtr 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.)
Application developers are discouraged from binding binary SQL data to a character C data type. This conversion is usually inefficient and slow.
SQL to C: Date
The date ODBC SQL data type is:
SQL_DATE
The following table shows the ODBC C data types to which date SQL data can be converted. For an explanation of the columns and terms in the table, see Conversion table description (SQL to C).
 
C type identifier
Test
*TargetValuePtr
*StrLen_or_IndPtr
SQLSTATE
SQL_C_CHAR
BufferLength > Character byte length
11<= BufferLength <= Character byte length
BufferLength < 11
Data
Truncated data Undefined
10
Length of data in bytes
Undefined
N/A
01004
22003
SQL_C_WCHAR
BufferLength > Character length
11<= BufferLength <= Character length
BufferLength < 11
Data
Truncated data Undefined
10
Length of data in bytes
Undefined
N/A
01004
22003
SQL_C_BINARY
Byte length of data <= BufferLength
Byte length of data > BufferLength
Data Undefined
Length of data in bytes
Undefined
N/A
22003
SQL_C_DATE
None (1)
Data
6 (3)
N/A
SQL_C_TIMESTAMP
None (1)
Data (2)
16 (3)
N/A
1 The value of BufferLength is ignored for this conversion. The driver assumes that the size of *TargetValuePtr is the size of the C data type.
2 The time fields of the timestamp structure are set to zero.
3 This is the size of the corresponding C data type.
When date SQL data is converted to character C data, the resulting string is in the "yyyy-mm-dd"format. This format is not affected by the Windows country setting.
SQL to C: Time
The time ODBC SQL data type is:
SQL_TIME
The following table shows the ODBC C data types to which time SQL data can be converted. For an explanation of the columns and terms in the table, see Conversion table description (SQL to C).
 
C type identifier
Test
*TargetValuePtr
*StrLen_or_IndPtr
SQLSTATE
SQL_C_CHAR
BufferLength > Character byte length
9 <= BufferLength <= Character byte length
BufferLength < 9
Data
Truncated data (1)
Undefined
Length of data in bytes
Length of data in bytes
Undefined
N/A
01004
22003
SQL_C_WCHAR
BufferLength > Character byte length 9 <= BufferLength <= Character byte length BufferLength < 9
Data
Truncated data (1)
Undefined
Length of data in characters
Length of data in characters
Undefined
N/A
01004
22003
SQL_C_BINARY
Byte length of data <= BufferLength
Byte length of data > BufferLength
Data Undefined
Length of data in bytes
Undefined
N/A
22003
SQL_C_DATE
None (1)
Data
6 (3)
N/A
SQL_C_TIMESTAMP
None (1)
Data (2)
16 (3)
N/A
1 The fractional seconds of the time are truncated.
2 The value of BufferLength is ignored for this conversion. The driver assumes that the size of *TargetValuePtr is the size of the C data type.
3 The date fields of the timestamp structure are set to the current date and the fractional seconds field of the timestamp structure is set to zero.
4 This is the size of the corresponding C data type.
When time SQL data is converted to character C data, the resulting string is in the "hh:mm:ss" format.
SQL to C: Timestamp
The timestamp ODBC SQL data type is:
SQL_TIMESTAMP
The following table shows the ODBC C data types to which timestamp SQL data can be converted. For an explanation of the columns and terms in the table, see Conversion table description (SQL to C).
 
C type identifier
Test
*TargetValuePtr
*StrLen_or_IndPtr
SQLSTATE
SQL_C_CHAR
BufferLength > Character byte length
20 <= BufferLength <= Character byte length
BufferLength < 20
Data
Truncated data (2)
Undefined
Length of data in bytes
Length of data in bytes
Undefined
N/A
01004
22003
SQL_C_WCHAR
BufferLength > Character byte length
20 <= BufferLength <= Character byte length
BufferLength < 20
Data
Truncated data (2)
Undefined
Length of data in characters
Length of data in characters
Undefined
N/A
01004
22003
SQL_C_BINARY
Byte length of data <= BufferLength
Byte length of data > BufferLength
Data Undefined
Length of data in bytes
Undefined
N/A
22003
SQL_C_TYPE_DATE
Time portion of timestamp is zero (1)
Time portion of timestamp is non-zero (1)
Data
Truncated data (3)
6 (6)
6 (6)
N/A
01S07
SQL_C_TYPE_TIME
Fractional seconds portion of timestamp is zero (1)
Fractional seconds portion of timestamp is non-zero (1)
Data (4)
Truncated data (4) (5)
6 (6)
6 (6)
N/A
01S07
SQL_C_TYPE_TIMESTAMP
Fractional seconds portion of timestamp is not truncated (1)
Fractional seconds portion of timestamp is truncated (1)
Data (5)
Truncated data (5)
6 (6)
6 (6)
N/A
01S07
1 The value of BufferLength is ignored for this conversion. The driver assumes that the size of *TargetValuePtr is the size of the C data type.
2 The fractional seconds of the timestamp are truncated.
3 The time portion of the timestamp is truncated.
4 The date portion of the timestamp is ignored.
5 The fractional seconds portion of the timestamp is truncated.
6 This is the size of the corresponding C data type.
When timestamp SQL data is converted 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. The format is not affected by the Windows country setting. (Except for the decimal point and fractional seconds, the entire format must be used, regardless of the precision of the timestamp SQL data type.)
Go up to
Converting data from SQL to C data types