Data types and data conversion in CLI applications

You must work with both SQL data types and C data types when you code a CLI application. The SQL data types are associated with the DBMS, while the C data types are referenced by the application. The application, therefore, must match C data types to SQL data types when applications calls CLI functions to transfer data between the DBMS and the application.

To facilitate this, CLI provides symbolic names for the various data types, and manages the transfer of data between the DBMS and the application. It also performs data conversion (from a C character string to an SQL INTEGER type, for example) if required. CLI needs to know both the source and target data type. This requires the application to identify both data types using symbolic names.

Data type conversion can occur under one of two conditions:

The application specified a C type that is not the default C type for the SQL type.
The application specified an SQL type that does not match the base column SQL type at the server, and there was no describe information available to the CLI driver.

Note:

GRAPHIC and VARGRAPHIC columns are not supported by Informix® data server. Due to this limitation, conversion from sql_c_dbchar (C data type) and sql_graphic (SQL Datatype) are not supported. NCHAR and NVARCHAR datatypes and SQL_C_BINARY and SQL_BINARY conversions may be used instead of GRAPHIC and VARGRAPHIC.
The SQL_XML data type is not supported for use with an Informix data server.

Example of how to use data types

Because the data source contains SQL data types and the CLI application works with C data types, the data to be retrieved needs to be handled with the correct data types. The following example shows how SQL and C data types are used by an application to retrieve data from the source into application variables. The following code snippet examines how data is retrieved from the DEPTNUMB column of the ORG table in the sample database.

The DEPTNUMB column of the ORG table is declared as the SQL data type SMALLINT.
The application variable which will hold the retrieved data is declared using C types. Since the DEPTNUMB column is of SQL type SMALLINT, the application variable needs to be declared using the C type SQLSMALLINT, which is equivalent to the SQL type SMALLINT.
```
    struct
    {   SQLINTEGER ind;
        SQLSMALLINT val;
    } deptnumb;         /* variable to be bound to the DEPTNUMB column */   
```
SQLSMALLINT represents the base C type of short int.
The application binds the application variable to the symbolic C data type of SQL_C_SHORT:
```
    sqlrc = SQLBindCol(hstmt, 1, SQL_C_SHORT, &deptnumb.val, 0,
                       &deptnumb.ind);
```
The data types are now consistent, because the result data type SQL_C_SHORT represents the C type SQLSMALLINT.

Data conversion

CLI manages the transfer and any required conversion of data between the application and the DBMS. Before the data transfer actually takes place, either the source, the target or both data types are indicated when calling SQLBindParameter(), SQLBindCol() or SQLGetData(). These functions use the symbolic type names to identify the data types involved.

For example, to bind a parameter marker that corresponds to an SQL data type of DECIMAL(5,3), to an application's C buffer type of double, the appropriate SQLBindParameter() call would look like:

     SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT, SQL_C_DOUBLE,
                       SQL_DECIMAL, 5, 3, double_ptr, 0, NULL);

The functions mentioned in the previous paragraph can be used to convert data from the default to other data types, but not all data conversions are supported or make sense.

Note: When using CLI with an Informix data server, binary values can not be inserted into CHAR columns. User can only insert binary values into a LOB column. This is an Informix data server limitation on converting to STRING types. When binding a TIMESTAMP column to a sql_c_char/sql_c_wchar string with an Informix data server, the input value must be specified using the ODBC escape sequence or as a literal. DATETIME functions can not be specified.

The rules that specify limits on precision and scale, as well as truncation and rounding rules for type conversions apply in CLI, with the following exception: truncation of values to the right of the decimal point for numeric values may return a truncation warning, whereas truncation to the left of the decimal point returns an error. In cases of error, the application should call SQLGetDiagRec() to obtain the SQLSTATE and additional information about the failure. When moving and converting floating point data values between the application and CLI, no correspondence is guaranteed to be exact as the values may change in precision and scale.