Data conversion tables from C to SQL

solidDB Help : solidDB reference : ODBC: Data types : Converting data from C to SQL data types : 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 ODBC: Minimum SQL grammar requirements.

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 can 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 can 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 can 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 can 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 can 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 can 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

The following table shows the ODBC SQL data types to which time C data can 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 can 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.

Go up to

Converting data from C to SQL data types