Home

	Expand Search

Access Service User's Guide DirectConnect for AS/400

Chapter 4 Converting datatypes

Converting Open Client/Server datatypes to target datatypes

Converting Open Client/Server datatypes to target datatypes

An access service converts or performs SQL transformation on incoming data it receives in a client request when the incoming data include:

• Data values embedded as strings within the text of select, insert, delete, update, and execute language commands

• Data values as parameters of RPC, cursor, or dynamic SQL commands

• Datatype names as part of create table or alter table commands

Data values embedded as strings

This section describes datatype conversion and SQL transformation of data values embedded as strings in language commands.

When an access service receives data values embedded in strings, it does not automatically convert the incoming data values. The client application must format the strings correctly with valid datatypes before sending them to the target database. The access service provides a string template for the target datatypes. To find the correct datatype, the client application receives this string template for the target datatypes from the sp_columns CSP.

When the access service receives a SQL command that has embedded data values, the SQL transformation mode in effect determines whether or not any transformation is applied to these values:

• If the access service is in Passthrough mode, it does not perform transformation.

• If the access service is in Sybase mode, it performs the following transformation:

  • Removes the currency symbol from money constants

  • Transforms quoted strings to quoting conventions specific to the target DBMS

For more information about SQL transformation modes, see "SQL Transformation modes" .

Datatype constants are transformed only as shown in the preceding paragraphs. When passing datatype constants, the client must ensure that the constants are in the proper format that the target DBMS requires.

Data values received from the client as parameters

This section describes how the access service handles data values received from the client as parameters of RPC, cursor, or dynamic SQL commands.

Only CT-Library clients can issue cursor or dynamic commands. For DB-Library clients, this section applies only to RPC commands.

When a client application sends a parameter description as part of an RPC command, cursor command, or dynamic SQL command, the access service automatically converts Open Client/Server datatypes to default target DBMS datatypes. In most cases, Open Client/Server datatypes directly map to target datatypes.

Some Open Client/Server datatypes do not directly map to target datatypes. For example, Open Client/Server CS_DATETIME and CS_DATETIME4 datatypes do not directly map to AS/400 DATE, TIME, or TIMESTAMP datatypes.

When defaults are not appropriate for these datatype conversions, the CT-Library client can specify the intended AS/400 datatype by using the usertype field in the parameter's CS_DATAFMT structure. The client fills in the CS_DATAFMT structure and a pointer to the structure is passed to the ct_param Open Client function call.

The access service then converts the Open Client/Server datatype to the precise target datatype.

DB-Library clients cannot take advantage of this feature and are limited to the default datatype conversions.

usertype values

The usertype field of the CS_DATAFMT structure is a 32-bit integer.

To find the usertype value, the client application executes sp_columns to obtain a description of the REMOTE_DATA_TYPE column from the sp_columns result set. The REMOTE_DATA_TYPE column returns the integer ID of the ODBC (target) datatype.

The usertype value returned by sp_columns uses various fields in the 32-bit integer for flags, precision and scale, or length. The least- significant byte of the value specifies to which target datatype to convert the parameter. The client application places the value in the usertype field. If a 0 (zero) value is placed in the usertype field, the default conversion applies.

For more information about the CS_DATAFMT structure, ct_param Open Client function call, and usertype field, see the Open Client Client-Library/C Reference Manual. For more information about sp_columns, see.

When you configure an access service for an SJIS or EUCJIS character set, the access service performs double-byte character set (DBCS) translation on character parameters, including those related to graphic columns. The access service translates the contents of graphic columns to Kanji characters.

When you configure an access service for a single-byte character set (SBCS) code page, parameters related to graphic columns must be described as binary types. The access service stores binary data in the column without translation and returns the data exactly as it was sent. The number of bytes of data supplied for a binary parameter must be even.

The following table lists the possible conversions from Open Client/Server datatypes to AS/400 datatypes for parameters.

The default AS/400 type correspondence does not have to be exact. For example, the AS/400 accepts either CS_CHAR or CS_VARCHAR for either an AS/400 CHAR or VARCHAR column. It also accepts any numeric type for any numeric column.

Datatype names

An access service receives datatype names as part of create table or alter table commands. If the access service is in Passthrough mode, the datatype names are not modified. If the access service is in Sybase mode, Open Client/Server datatype names are converted to the target-specific datatype names that correspond to the Open Client/Server datatypes. A target database may not be able to support all Open Client/Server datatypes, but permits conversion to an equivalent or compatible datatype. For example, the Open Client/Server CS_MONEY datatype can be converted to a numeric (19,4) or equivalent datatype.

For more information about Passthrough mode and Sybase mode, see "SQL Transformation modes" .