Home | Previous Page | Next Page   Data Types > Converting Data >

Converting Data from C to SQL

When you call SQLExecute or SQLExecDirect, IBM Informix ODBC Driver retrieves the data for parameters that are bound with SQLBindParameter from storage locations in the application. For data-at-execution parameters, call SQLPutData to send the parameter data. If necessary, IBM Informix ODBC Driver converts the data from the data type that the ValueType argument specifies in SQLBindParameter to the data type that the fSqlType argument specifies in the SQLBindParameter. Finally, IBM Informix ODBC Driver sends the data to the data source.

If the rgbValue and pcbValue arguments specified in SQLBindParameter are both null pointers, then that function returns SQLSTATE S1009 (Invalid argument value). To specify a null SQL data value, set the value that the pcbValue argument of SQLBindParameter points to or the value of the cbValue argument to SQL_NULL_DATA. To specify that the value in rgbValue is a null-terminated string, set these values to SQL_NTS.

The following terms are used in the tables:

C to SQL: Binary

The binary Informix ODBC Driver C data type is SQL_C_BINARY. The following table shows the Informix ODBC Driver SQL data types to which binary C data can be converted. In the Test column, the SQL data length is the number of bytes needed to store the data on the data source. This length might be different from the column length, as defined in Precision, Scale, Length, and Display Size.

fSqlType Test SQLSTATE
SQL_BIGINT
(IDS)
Length of data = SQL data length.

Length of data ≠ SQL data length.

N/A

22003

SQL_BIT
(IDS)
Length of data = SQL data length.

Length of data ≠ SQL data length.

N/A

22003

SQL_CHAR
SQL_LONGVARCHAR
SQL_VARCHAR
Length of data ≤ Column length.

Length of data > Column length.

N/A

01004

SQL_DATE
SQL_TIMESTAMP
Length of data = SQL data length.

Length of data ≠ SQL data length.

N/A

22003

SQL_DECIMAL
SQL_DOUBLE
SQL_INTEGER
SQL_REAL
SQL_SMALLINT
Length of data = SQL data length.

Length of data ≠ SQL data length.

N/A

22003

SQL_LONGVARBINARY Length of data ≤ Column length.

Length of data > Column length.

N/A

01004

C to SQL: Bit (IDS)

The bit Informix ODBC Driver C data type is SQL_C_BIT. The following table shows the Informix ODBC Driver SQL data types to which bit C data can be converted.

fSqlType Test SQLSTATE
SQL_BIGINT
SQL_DECIMAL
SQL_DOUBLE
SQL_INTEGER
SQL_REAL
SQL_SMALLINT
None N/A
SQL_BIT None N/A
SQL_CHAR
SQL_LONGVARCHAR
SQL_VARCHAR
None N/A

IBM Informix ODBC Driver ignores the value that the pcbValue argument of SQLBindParameter points to and the value of the cbValue argument of SQLPutData when it converts data from the boolean C data type. IBM Informix ODBC Driver uses the size of rgbValue for the size of the boolean C data type.

C to SQL: Character

The character Informix ODBC Driver C data type is SQL_C_CHAR. The following table shows the Informix ODBC Driver SQL data types to which C character data can be converted.

fSqlType Test SQLSTATE
SQL_BIGINT
(IDS)
Data converted without truncation.

Data converted with truncation of
fractional digits.

Conversion of data would result in
loss of whole (as opposed to
fractional) digits.

Data value is not a numeric-literal.
N/A

01004


22003



22005
SQL_BIT
(IDS)
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.
N/A

01004


22003


22005
SQL_CHAR
SQL_LONGVARCHAR
SQL_VARCHAR
Length of data ≤ Column length.

Length of data > Column length.

N/A

01004

SQL_DATE
Data value is a valid Informix ODBC
Driver date-literal.

Data value is a valid Informix ODBC
Driver timestamp-literal; time portion is
zero.

Data value is a valid Informix ODBC
Driver timestamp-literal; time portion is
non-zero. IBM Informix ODBC
D
river truncates the time portion of
the time stamp.

Data value is not a valid Informix
ODBC Driver date-literal or Informix
ODBC Driver timestamp-literal.
N/A


N/A



01004





22008
SQL_DECIMAL
SQL_INTEGER
SQL_SMALLINT
Data converted without truncation.

Data converted with truncation of
fractional digits.

Conversion of data would result in
loss of whole (as opposed to
fractional) digits.

Data value is not a numeric-literal.
N/A

01004


22003



22005
SQL_DOUBLE
SQL_REAL
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_LONGVARBINARY (Length of data) / 2 ≤ Column length.

(Length of data) / 2 > Column length.

Data value is not a hexadecimal value.

N/A

01004

22005

SQL_TIMESTAMP
Data value is a valid Informix ODBC
Driver timestamp-literal; fractional
seconds portion not truncated.

Data value is a valid Informix ODBC
Driver timestamp-literal; fractional
seconds portion truncated.

Data value is a valid Informix ODBC
Driver date-literal. IBM Informix ODBC
D
river sets the time portion of the
time stamp to zero.

Data value is a valid Informix ODBC
Driver time-literal. IBM Informix ODBC
D
river sets the date portion of the
time stamp to the current date.

Data value is not a valid Informix
ODBC Driver date-literal, Informix
ODBC Driver time-literal, or Informix
ODBC Driver timestamp-literal.
N/A



01004



N/A




N/A




22008

When IBM Informix ODBC Driver converts character C data to numeric, date, or time stamp SQL data, it ignores leading and trailing blanks. When IBM Informix ODBC Driver converts character C data to binary SQL data, it converts each two bytes of character data to one byte of binary data. Each two bytes of character data represent a number in hexadecimal form. For example, IBM Informix ODBC Driver converts "01" to binary 00000001 and "FF" to binary 11111111.

IBM Informix ODBC Driver always converts pairs of hexadecimal digits to individual bytes and ignores the null-termination byte. Because of this conversion, 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.

C to SQL: Date

The date Informix ODBC Driver C data type is SQL_C_DATE. The following table shows the Informix ODBC Driver SQL data types to which date C data can be converted.

fSqlType Test SQLSTATE
SQL_CHAR
SQL_LONGVARCHAR
SQL_VARCHAR
Column length ≥ 10.

Column length < 10.

Data value is not a valid date.

N/A

22003

22008

SQL_DATE Data value is a valid date.

Data value is not a valid date.

N/A

22008

SQL_TIMESTAMP
Data value is a valid date.
IBM Informix ODBC
D
river sets the time portion
of the time stamp to zero.

Data value is not a valid date.
N/A




22008

When IBM Informix ODBC Driver converts date C data to character SQL data, the resulting character data is in the "yyyy-mm-dd" format.

IBM Informix ODBC Driver ignores the value that the pcbValue argument of SQLBindParameter points to and the value of the cbValue argument of SQLPutData when it converts data from the date C data type. IBM Informix ODBC Driver uses the size of rgbValue for the size of the date C data type.

C to SQL: Numeric

The numeric Informix ODBC Driver C data types are:

The following table shows the Informix ODBC Driver SQL data types to which numeric C data can be converted.

fSqlType Test SQLSTATE
SQL_BIGINT
(IDS)
Data converted without truncation.

Data converted with truncation of
fractional digits.

Conversion of data would result in
loss of whole (as opposed to
fractional) digits.
N/A

01004


22003
SQL_BIT
(IDS)
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.
N/A

01004


22003
SQL_CHAR
SQL_LONGVARCHAR
SQL_VARCHAR
Number of digits ≤ Column length.

Number of whole (as opposed to
fractional) digits ≤ Column length.

Number of whole (as opposed to
fractional) digits > Column length.
N/A

01004


22003
SQL_DECIMAL
SQL_INTEGER
SQL_SMALLINT
Data converted without truncation.

Data converted with truncation of
fractional digits.

Conversion of data would result in
loss of whole (as opposed to
fractional) digits.
N/A

01004


22003
SQL_DOUBLE
SQL_REAL
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

IBM Informix ODBC Driver ignores the value that the pcbValue argument of SQLBindParameter points to and the value of the cbValue argument of SQLPutData when it converts data from the numeric C data types. IBM Informix ODBC Driver uses the size of rgbValue for the size of the numeric C data type.

C to SQL: Time Stamp

The time-stamp Informix ODBC Driver C data type is SQL_C_TIMESTAMP. The following table shows the Informix ODBC Driver SQL data types to which time-stamp C data can be converted.

fSqlType Test SQLSTATE
SQL_CHAR
SQL_LONGVARCHAR
SQL_VARCHAR
Column length ≥ Display size.

19 ≤ Column length < Display size.
IBM Informix ODBC
D
river truncates the fractional
seconds of the time stamp.

Column length < 19.

Data value is not a valid date.
N/A

01004




22003

22008
SQL_DATE
Time fields are zero.

Time fields are non-zero.
IBM Informix ODBC
D
river truncates the time fields
of the time stamp structure.

Data value does not contain a valid
date.
N/A

01004




22008
SQL_TIMESTAMP
Fractional seconds fields are not
truncated.

Fractional seconds fields are
truncated.
IBM Informix ODBC
D
river truncates the fractional
seconds fields of the
time stamp structure.

Data value is not a valid time stamp.
N/A


01004






22008

When IBM Informix ODBC Driver converts time stamp C data to character SQL data, the resulting character data is in the "yyyy-mm-dd hh:mm:ss[.f...]" format.

IBM Informix ODBC Driver ignores the value that the pcbValue argument of SQLBindParameter points to and the value of the cbValue argument of SQLPutData when it converts data from the time stamp C data type. IBM Informix ODBC Driver uses the size of rgbValue for the size of the time stamp C data type.

C-to-SQL Data Conversion Examples

The following table illustrates how IBM Informix ODBC Driver converts C data to SQL data. "\0" represents a null-termination byte. The null-termination byte is required only if the length of the data is SQL_NTS. For SQL_C_DATE, the numbers that are in the C Data Value column are the numbers that are stored in the fields of the DATE_STRUCT structure. For SQL_C_TIMESTAMP, the numbers that are in the C Data Value column are the numbers that are stored in the fields of the TIMESTAMP_STRUCT structure.

C Data Type C Data Value SQL Data Type Column Length SQL Data Value SQLSTATE
SQL_C_CHAR tigers\0 SQL_CHAR 6 tigers N/A
SQL_C_CHAR tigers\0 SQL_CHAR 5 tiger 01004
SQL_C_CHAR 1234.56\0 SQL_DECIMAL 8
(In addition to bytes for numbers, one byte is required for a sign and another for the decimal point.)
1234.56 N/A
SQL_C_CHAR 1234.56\0 SQL_DECIMAL 7
(In addition to bytes for numbers, one byte is required for a sign and another for the decimal point.)
1234.5 01004
SQL_C_CHAR 1234.56\0 SQL_DECIMAL 4 22003
SQL_C_FLOAT 1234.56 SQL_FLOAT not applicable 1234.56 N/A
SQL_C_FLOAT 1234.56 SQL_INTEGER not applicable 1234 01004
SQL_C_FLOAT 1234.56 SQL_TINYINT not applicable 22003
SQL_C_DATE 1992,12,31 SQL_CHAR 10 1992-12-31 N/A
SQL_C_DATE 1992,12,31 SQL_CHAR 9 22003
SQL_C_DATE 1992,12,31 SQL_TIMESTAMP not applicable 1992-12-31
00:00:00.0
N/A
SQL_C_TIMESTAMP 1992,12,31,
23,45,55,
120000000
SQL_CHAR 22 1992-12-31
23:45:55.12
N/A
SQL_C_TIMESTAMP 1992,12,31,
23,45,55,
120000000
SQL_CHAR 21 1992-12-31
23:45:55.1
01004
SQL_C_TIMESTAMP 1992,12,31,
23,45,55,
120000000
SQL_CHAR 18 22003
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]