Access Service User's Guide DirectConnect for AS/400

Home

Converting Open Client/Server datatypes to target datatypes

Access Service User's Guide DirectConnect for AS/400

Chapter 4 Converting datatypes

Converting target datatypes to Open Client/Server datatypes

When the access service retrieves data from the target database, it converts the target data to default Open Client/Server datatypes for delivery to the client application. To configure datatype conversions:

Change the datatype conversion property values when you configure an access service
Set the conversions required by a particular client application by issuing set statements from the client application

For a complete description of datatype conversion configuration properties and values, see "Access service Datatype Conversion properties" .

Issuing set statements to change datatype conversions

You can issue set statements to change one or more datatype conversions for the current client connection. These settings remain in effect only for the duration of the current client connection or until you issue another set statement.

For more information about set statements, see Chapter 3, "Querying and setting operating values"

Single datatype conversions

You can set the datatype conversion of a specific datatype by issuing a set statement that contains the specific datatype configuration property and value.

For example, to set the datatype conversion of the AS/400 FLOAT datatype to Open Client/Server CS_CHAR datatype, issue the following statement:

set FloatResults char

where FloatResults is the datatype conversion configuration property that maps to the AS/400 FLOAT datatype, and char is the property value that maps to the Open Client/Server datatype CS_CHAR.

Group datatype conversions

To change a group of datatype conversion settings to the default setting for each datatype or to Open Client/Server CS_CHAR, issue the following set statement:

set AllResults [ autoconvert | char ]

Character representations

The following table lists the character representations that an access service returns to the client application when you choose char as the output type. This table includes configuration property values for date and time character forms. For example, AS/400 DATE type can convert to Open Client/Server CS_CHAR type in one of the four character formats shown in the table.

Character representations
AS/400 Input Type	Open Client/Server Character Form Returned to the Client Application
DECIMAL (scale = 0)	±n
DECIMAL (scale > 0)	±n.n
DECIMAL (scale = precision)	±.n
DATE	char_eur format dd.mm.yyyychar_iso format yyyy-mm-ddchar_jis format yyyy-mm-ddchar_usa format mm/dd/yyyy
REAL	±n.nnnnnnnE±nn
FLOAT	±n.nnnnnnnnnnnnnnnnE±nn
INT	±nnnnnnnnnn
SMALLINT	±nnnnn
TIME	char_eur format hh.mm.ss in 24-hour clock time char_iso format hh.mm.ss in 24-hour clock time char_jis format hh:mm:ss in 24-hour clock time char_usa format hh:mm [AM \| PM] in 12-hour clock time
TIMESTAMP	char_eur format dd.mm.yyyy hh.mm.sschar_iso format yyyy-mm-dd-hh.mm.ss.nnnnnnchar_jis format yyyy-mm-dd hh:mm:sschar_usa format mm/dd/yyyy hh.mm [AM \| PM]

Data conversion errors

A data conversion error occurs when a value is out of the valid range. For example, if an AS/400 source column with a DATE datatype has a value of Jan 1, 1899, and the access service datatype conversion property DateResults is set to datetime4, a value error occurs because the AS/400 DATE value cannot be expressed in an Open Client/Server CS_DATETIME4 datatype.

Configuration properties control the behavior of an access service when it encounters data conversion errors. For example, when converting AS/400 DATE or TIME values to Open Client/Server CS_DATETIME values, an access service can insert default information, which is configured by data conversion error properties, for the unknown date or time.

For a description of data conversion error properties and associated values, refer to "Access service Data Conversion Error properties" .

The following table indicates conditions that cause value errors.

Conversions that cause errors
AS/400 Datatype & Open Client/Server Conversion	AS/400 Value
FLOAT, REAL, or DECIMAL and FloatResults value is real	Greater than 3.402823466E38 or less than -3.402823466E38
DECIMAL, FLOAT, or REAL and DecimalResults value is money	Greater than 922337203685477.5807 or less than -922337203685477.5808
DECIMAL, FLOAT, REAL or INT and DecimalResults value is money4	Greater than 214748.3646 or less than -214748.3647
DATE and DateResults value is datetime	DATE value is less than January 1, 1753
DATE and DateResults value is datetime4	DATE value is less than January 1, 1900, or greater than June 6, 2079
TIMESTAMP and DateTimeResults value is datetime	TIMESTAMP year is less than 1753
TIMESTAMP and DateTimeResults value is datetime4	TIMESTAMP date is less than January 1, 1900, or greater than June 6, 2079

FLOAT and REAL conversion

AS/400 FLOAT and REAL datatypes have approximately the same precision but different ranges from the corresponding Open Client/Server types. Values that are valid in Open Client/Server may be out of range for the AS/400. The following table shows the approximate ranges for AS/400 FLOAT and REAL datatypes and Open Client/Server CS_FLOAT and CS_REAL datatypes.

Ranges for AS/400 and Open Client/Server FLOAT and REAL
AS/400 Datatype	Approximate Range	Open Client/Server Datatype	Approximate Range
FLOAT	-7.2E75 to +7.2E75	CS_FLOAT	-1.7977E308 to +1.7977E308
REAL	-7.2E75 to +7.2E75	CS_REAL	-3.4028E38 to +3.4028E38

Real number testing

In the AS/400, real numbers (datatypes REAL, FLOAT, DOUBLE, and DOUBLE PRECISION) are stored as approximations. This means that equality tests on real numbers may not locate all rows expected. The recommended way to test for a real number value is to use a range test. For example, if you want to locate all rows where the column "A" has a value of 1.2345678, issue the following select statement:

select.....from.....where a between 1.234567 and 1.234568


	Expand Search