Programmer Guide : Data types : Data conversion tables from C to SQL
  
Data conversion tables from C to SQL
The tables in the following sections describe how the driver or data source converts data sent to the data source; drivers are required to support conversions from all ODBC C data types to the ODBC SQL data types that they support.
Conversion table description (C to SQL)
The following columns are included in the tables:
▪For a given ODBC C data type, the first column of the table lists the legal input values of the ParameterType argument in SQLBindParameter.
▪The second column lists the outcomes of a test that the driver performs to determine if it can convert the data.
▪The third column lists the SQLSTATE returned for each outcome by
SQLExecDirect, SQLExecute, or SQLPutData. Data is sent to the data source only if SQL_SUCCESS is returned.
If the ParameterType argument in SQLBindParameter contains a value for an ODBC SQL data type that is not shown in the table for a given C data type, SQLBindParameter returns SQLSTATE 07006 (Restricted data type attribute violation). If the ParameterType argument contains a driver-specific value and the driver does not support the conversion from the specific ODBC C data type to that driver-specific SQL data type, SQLBindParameter returns SQLSTATE HYC00 (Optional feature not implemented).
If the ParameterValuePtr and StrLen_or_IndPtr arguments specified in SQLBindParameter are both null pointers, that function returns SQLSTATE HY009 (Invalid use of null pointer). Although it is not shown in the tables, an application sets the value pointed to by the StrLen_or_IndPtr argument of SQLBindParameter or the value of the StrLen_or_IndPtr argument to SQL_NULL_DATA to specify a NULL SQL data value. (The StrLen_or_IndPtr argument corresponds to the SQL_DESC_OCTET_LENGTH_PTR field of the APD.) The application sets these values to SQL_NTS to specify that the value in *ParameterValuePtr in SQLBindParameter or *DataPtr in SQLPutData (pointed to by the SQL_DESC_DATA_PTR field of the APD) is a null-terminated string.
The following terms are used in the tables:
▪Byte length of data is the number of bytes of SQL data available to send to the data source, regardless of whether the data will be truncated before it is sent to the data source. For string data, this does not include the null-termination character.
▪Column byte length is the number of bytes required to store the data at the data source.
▪Character byte length is the maximum number of bytes needed to display data in character form.
▪Number of digits is the number of characters used to represent a number, including the minus sign, decimal point, and exponent (if needed).
▪Words in italics represent elements of the ODBC SQL grammar. For the syntax of grammar elements, see Minimum SQL grammar requirements for ODBC.
C to SQL: Character
The character ODBC C data type is:
SQL_C_CHAR SQL_C_WCHAR
The following table shows the ODBC SQL data types to which C character data may be converted. For an explanation of the columns and terms in the table, see Conversion table description (C to SQL).
Note The length of the Unicode data type must be an even number when character C data is converted to Unicode SQL data.
SQL type identifier
Test
SQLSTATE
SQL_CHAR
SQL_VARCHAR
SQL_LONGVARCHAR
Byte length of data <= Column length
Byte length of data > Column length
N/A
22001
SQL_WCHAR
SQL_WVARCHAR
SQL_WLONGVARCHAR
Character length of data <= Column length
Character length of data > Column length
N/A
22001
SQL_DECIMAL
SQL_NUMERIC
SQL_TINYINT
SQL_SMALLINT
SQL_INTEGER
SQL_BIGINT
Data converted without truncation
Data converted with truncation of fractional digits [5]
Conversion of data would result in loss of whole (as opposed to fractional) digits [5]
Data value is not a numeric-literal
N/A
22001
22001
22018
SQL_REAL
SQL_FLOAT
SQL_DOUBLE
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 value is not a numeric-literal
N/A
22003
22005
SQL_BIT
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.
Note solidDB® does not support SQL_BIT.
N/A
22001
22003
22018
SQL_BINARY
SQL_VARBINARY
SQL_LONG-VARBINARY
(Byte length of data) / 2 <= Column byte length
(Byte length of data) / 2 > Column byte length
Data value is not a hexadecimal value
N/A 22001 22018
SQL_TYPE_DATE
Data value is a valid ODBC_date_literal
Data value is a valid ODBC_timestamp_literal; time portion is zero
Data value is a valid ODBC_timestamp_literal; time portion is non-zero [1]
Data value is not a valid ODBC_date_literal or ODBC_timestamp_literal
N/A
N/A
22008
22018
SQL_TYPE_TIME
Data value is a valid ODBC_time_literal
Data value is a valid ODBC_timestamp_literal; fractional seconds portion is zero [2]
Data value is a valid ODBC_timestamp_literal; fractional seconds portion is non-zero [2]
Data value is not a valid ODBC_time_literal or ODBC_timestamp_literal
N/A
N/A
22008
22018
SQL_TYPE_TIMESTAMP
Data value is a valid ODBC_timestamp_literal; fractional seconds portion not truncated
Data value is a valid ODBC-timestamp-literal; fractional seconds portion truncated
Data value is a valid ODBC-date-literal [3]
Data value is a valid ODBC-time-literal [4]
Data value is not a valid ODBC-date-literal, ODBC-time-literal, or ODBC-timestamp-literal
N/A
22008
N/A
N/A
22018
[1] The time portion of the timestamp is truncated.
[2] The date portion of the timestamp is ignored.
[3] The time portion of the timestamp is set to zero.
[4] The date portion of the timestamp is set to the current date.
[5] The driver/data source effectively waits until the entire string has been received (even if the character data is sent in pieces by calls to SQLPutData) before attempting to perform the conversion.
When character C data is converted to numeric, date, time, or timestamp SQL data, leading and trailing blanks are ignored.
When character C data is converted to binary SQL data, each two bytes of character data are converted to a single byte (8 bits) of binary data. Each two bytes of character data represent a number in hexadecimal form. For example, "01" is converted to a binary 00000001 and "FF" is converted to a binary 11111111.
The driver always converts pairs of hexadecimal digits to individual bytes and ignores the null termination byte. Because of this, if the length of the character string is odd, the last byte of the string (excluding the null termination byte, if any) is not converted.
Note Because binding character C data to a binary SQL data type is inefficient and slow, refrain from doing this.
C to SQL: Numeric
The numeric ODBC C data types are:
▪SQL_C_STINYINT
▪SQL_C_SLONG
▪SQL_C_UTINYINT
▪SQL_C_ULONG
▪SQL_C_TINYINT
▪SQL_C_LONG
▪SQL_C_SSHORT
▪SQL_C_FLOAT
▪SQL_C_USHORT
▪SQL_C_DOUBLE
▪SQL_C_SHORT
▪SQL_C_NUMERIC
▪SQL_C_SBIGINT
▪SQL_C_UBIGINT
For more information about the SQL_C_TINYINT, SQL_C_SHORT, and SQL_C_LONG data types, see C data types.
Numeric C data to ODBC SQL data types
The following table shows the ODBC SQL data types to which numeric C data may be converted. For an explanation of the columns and terms in the table, see Conversion table description (C to SQL).
Parameter type
Test
SQLSTATE
SQL_CHAR
SQL_VARCHAR
SQL_LONGVARCHAR
Number of digits <= Column byte length
Number of digits > Column byte length
N/A
22001
SQL_WCHAR
SQL_WVARCHAR
SQL_WLONGVARCHAR
Number of characters <= Column character length
Number of characters > Column character length
N/A
22001
SQL_DECIMAL [1]
SQL_NUMERIC [1]
SQL_TINYINT [1]
SQL_SMALLINT [1]
SQL_INTEGER [1]
SQL_BIGINT [1]
Data converted without truncation or with truncated of fractional digits
Data converted with truncation of whole digits
N/A
22003
SQL_REAL
SQL_FLOAT
SQL_DOUBLE
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
N/A
22003
[1] For the “n/a” case, a driver may optionally return SQL_SUCCESS_WITH_INFO and 01S07 when there is a fractional truncation.
The driver ignores the length or indicator value when converting data from the numeric C data types and assumes that the size of the data buffer is the size of the numeric C data type. The length or indicator value is passed in the StrLen_or_IndPtr argument in SQLPutData and in the buffer specified with the StrLen_or_IndPtr argument in SQLBindParameter. The data buffer is specified with the DataPtr argument in SQLPutData and the ParameterValuePtr argument in SQLBindParameter.
C to SQL: Bit
The bit ODBC C data type is:
SQL_C_BIT
The following table shows the ODBC SQL data types to which bit C data may be converted. For an explanation of the columns and terms in the table, see For an explanation of the columns and terms in the table, see Conversion table description (C to SQL).
SQL type identifier
Test
SQLSTATE
SQL_CHAR
SQL_VARCHAR
SQL_LONGVARCHAR
SQL_WCHAR
SQL_WVARCHAR
SQL_WLONGVARCHAR
None
N/A
SQL_DECIMAL
SQL_NUMERIC
SQL_TINYINT
SQL_SMALLINT
SQL_INTEGER
SQL_BIGINT
SQL_REAL
SQL_FLOAT
SQL_DOUBLE
None
N/A
The driver ignores the length or indicator value when converting data from the bit C data types and assumes that the size of the data buffer is the size of the bit C data type. The length or indicator value is passed in the StrLen_or_Ind argument in SQLPutData and in the buffer specified with the StrLen_or_IndPtr argument in SQLBindParameter. The data buffer is specified with the DataPtr argument in SQLPutData and the ParameterValuePtr argument in SQLBindParameter.
C to SQL: Binary
The binary ODBC C data type is:
SQL_C_BINARY
The following table shows the ODBC SQL data types to which binary C data may be converted. For an explanation of the columns and terms in the table, see Conversion table description (C to SQL).
Binary C data to ODBC SQL data types
SQL type identifier
Test
SQLSTATE
SQL_CHAR
SQL_VARCHAR
SQL_LONGVARCHAR
Byte length of data <= Column byte length
Byte length of data > Column length
N/A
22001
SQL_WCHAR
SQL_WVARCHAR
SQL_WLONGVARCHAR
Character length of data <= Column character length
Character length of data > Column character length
N/A
22001
SQL_DECIMAL
SQL_NUMERIC
SQL_TINYINT
SQL_SMALLINT
SQL_INTEGER
SQL_BIGINT
SQL_REAL
SQL_FLOAT
SQL_DOUBLE
SQL_TYPE_DATE
SQL_TYPE_TIME
SQL_TYPE_TIMESTAMP
Byte length of data = SQL data length
Length of data <> SQL data length
N/A
22003
SQL_BINARY
SQL_VARBINARY
SQL_LONGVARBINARY
Length of data <= Column length
Length of data > Column length
N/A
22001
C to SQL: Date
The date ODBC C data type is:
SQL_C_DATE
The following table shows the ODBC SQL data types to which date C data may be converted. For an explanation of the columns and terms in the table, see Conversion table description (C to SQL).
SQL type identifier
Test
SQLSTATE
SQL_CHAR
SQL_VARCHAR
SQL_LONGVARCHAR
Column byte length >= 10
Column byte length < 10
Data value is not a valid date
N/A
22001
22008
SQL_CHAR
SQL_VARCHAR
SQL_LONGVARCHAR
Column character length >= 10
Column character length < 10
Data value is not a valid date
N/A
22001
22008
SQL_TYPE_DATE
Data value is a valid date
Data value is not a valid date
N/A
22007
SQL_TYPE_TIMESTAMP
Data value is a valid date 1
Data value is not a valid date
N/A
22007
[1] The time portion of the timestamp is set to zero.
For information about what values are valid in an SQL_C_TYPE_DATE structure, see C data types.
When date C data is converted to character SQL data, the resulting character data is in the "yyyy-mm-dd "format.
The driver ignores the length or indicator value when converting data from the date C data types and assumes that the size of the data buffer is the size of the date C data type. The length or indicator value is passed in the StrLen_or_Ind argument in SQLPutData and in the buffer specified with the StrLen_or_IndPtr argument in SQLBindParameter. The data buffer is specified with the DataPtr argument in SQLPutData and the ParameterValuePtr argument in SQLBindParameter.
C to SQL: Time
The time ODBC C data type is:
SQL_C_TIME
T he following table shows the ODBC SQL data types to which time C data may be converted. For an explanation of the columns and terms in the table, see Conversion table description (C to SQL).
SQL type identifier
Test
SQLSTATE
SQL_CHAR
SQL_VARCHAR SQL_LONGVARCHAR
Column byte length >= 8
Column byte length < 8
Data value is not a valid time
N/A
22001
22008
SQL_WCHAR
SQL_WVARCHAR
SQL_WLONGVARCHAR
Column character length >= 8
Column character length < 8
Data value is not a valid time
N/A
22001
22008
SQL_TYPE_TIME
Data value is a valid time
Data value is not a valid time
N/A
22007
SQL_TYPE_TIMESTAMP
Data value is a valid time [1]
Data value is not a valid time
N/A
22007
[1] The date portion of the timestamp is set to the current date and the fractional seconds portion of the timestamp is set to zero.
For information about what values are valid in an SQL_C_TYPE_TIME structure, see C data types.
When time C data is converted to character SQL data, the resulting character data is in the "hh:mm:ss" format.
The driver ignores the length or indicator value when converting data from the time C data types and assumes that the size of the data buffer is the size of the time C data type. The length or indicator value is passed in the StrLen_or_Ind argument in SQLPutData and in the buffer specified with the StrLen_or_IndPtr argument in SQLBindParameter. The data buffer is specified with the DataPtr argument in SQLPutData and the ParameterValuePtr argument in SQLBindParameter.
C to SQL: Timestamp
The timestamp ODBC C data type is:
SQL_C_TIMESTAMP
The following table shows the ODBC SQL data types to which timestamp C data may be converted. For an explanation of the columns and terms in the table, see Conversion table description (C to SQL).
SQL type identifier
Test
SQLSTATE
SQL_CHAR
SQL_VARCHAR
SQL_LONGVARCHAR
Column byte length >= Character byte length
19 <= Column byte length < Character byte length
Column byte length < 19
Data value is not a valid date
N/A
22001
22001
22008
SQL_WCHAR
SQL_WVARCHAR
SQL_WLONGVARCHAR
Column character length >= Character length of data
19 <= Column character length < Character length of data
Column character length < 19
Data value is not a valid timestamp
N/A
22001
22001
22008
SQL_TYPE_DATE
Time fields are zero
Time fields are non-zero
Data value does not contain a valid date
N/A
22008
22007
SQL_TYPE_TIME
Fractional seconds fields are zero [1]
Fractional seconds fields are non-zero [1]
Data value does not contain a valid time
N/A
22008
22007
SQL_TYPE_TIMESTAMP
Fractional seconds fields are not truncated
Fractional seconds fields are truncated
Data value is not a valid timestamp
N/A
22008
22007
[1] The date fields of the timestamp structure are ignored.
For information about what values are valid in an SQL_C_TIMESTAMP structure, see C data types.
When timestamp C data is converted to character SQL data, the resulting character data is in the "yyyy-mm-dd hh:mm:ss [ .f. ..]" format.
The driver ignores the length or indicator value when converting data from the timestamp C data types and assumes that the size of the data buffer is the size of the timestamp C data type. The length or indicator value is passed in the StrLen_or_Ind argument in SQLPutData and in the buffer specified with the StrLen_or_IndPtr argument in SQLBindParameter. The data buffer is specified with the DataPtr argument in SQLPutData and the ParameterValuePtr argument in SQLBindParameter.
See also
Data types