| |
Home |
![[Table of Contents]](/dwicons/b_toc.gif)
|
Access Service User's Guide DirectConnect Anywhere |
![[-]](/dwicons/i_colpse.gif)
| Chapter 4 Converting datatypes |
Chapter 4
This chapter describes datatype conversions between ODBC and Open Client/Server. The following topics are included:
An access service converts or performs SQL transformation on incoming Open Server(TM) data it receives in a client request when the data includes:
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
The access service does not perform automatic incoming datatype conversions on data values embedded in strings or received as parameters. Instead, the client application receives a string template from the target datatypes so that it can format the strings correctly before sending them to the target DBMS. The formatting is set up through the sp_columns catalog stored procedure.
When the access service receives a SQL command with embedded data values, the SQL transformation mode in effect determines whether any transformation is applied to these values. The following rules apply:
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 datatypes
Transforms quoted strings to quoting conventions specific to the target DBMS
Datatype constants are not transformed in any way except as described previously. When passing datatype constants, the client must ensure that the constants are in the proper format required by the target DBMS.
For more information about SQL transformation modes, see Chapter 6, "Issuing SQL statements"
When an access service receives data values as parameters to RPC commands, cursor commands, or dynamic SQL commands, it converts Open Client/Server datatypes to default target DBMS datatypes.
Only CT-Library clients can issue cursor or dynamic commands. For DB-Library clients, this section applies only to RPC commands.
In most cases, Open Client/Server datatypes directly map to target datatypes, and the service library defines default mapping rules. However, if the defaults are not valid, the CT-Library client specifies the intended target datatype through the usertype field of the CS_DATAFMT structure. DB-Library clients cannot take advantage of this feature and are limited to the default datatype mappings.
An Open Server datatype without an associated user-defined datatype is transformed to an ODBC datatype as follows:
An Open Server datatype is assigned a user-defined datatype before it is transformed to a TDS datatype. This user-defined datatype reflects the associated ODBC datatype.
The user-defined datatype is used in the child process to transform the TDS datatype to the ODBC datatype.
The client application can obtain the actual target DBMS datatype for a particular column via the sp_columns catalog stored procedure.
ODBC datatypes are not supported by all data sources. This table lists the ODBC datatypes and the CS_DATATYPE to which each translates.
The following table shows Open Server datatypes, assigned user-defined datatypes, TDS LAN datatypes, and the resulting ODBC datatypes.
Open Server Datatype | Assigned User Defined Datatype | TDS LAN Datatype | ODBC SQL Datatype | ODBC "C" Datatype |
CS_BINARY | SQL_BINARY | CS_CHAR | SQL_BINARY | SQL_C_BINARY |
CS_ LONGBINARY | SQL_ LONGBINARY | CS_CHAR | SQL_ LONGBINARY | SQL_C_BINARY |
CS_VARBINARY | SQL_ VARBINARY | CS_CHAR | SQL_ VARBINARY | SQL_C_BINARY |
CS_BIT | SQL_BIT | CS_SMALLINT | SQL_BIT | SQL_C_BIT |
CS_CHAR | SQL_CHAR | CS_CHAR | SQL_CHAR | SQL_C_CHAR |
CS_LONGCHAR | SQL_ LONGVARCHAR | CS_CHAR | SQL_ LONGVARCHAR | SQL_C_CHAR |
CS_VARCHAR | SQL_VARCHAR | CS_CHAR | SQL_VARCHAR | SQL_C_CHAR |
CS_DATETIME | SQL_ TIMESTAMP | CS_CHAR | SQL_ TIMESTAMP | SQL_C_ TIMESTAMP |
CS_DATETIME4 | SQL_ TIMESTAMP | CS_CHAR | SQL_ TIMESTAMP | SQL_C_ TIMESTAMP |
CS_TINYINT | SQL_TINYINT | CS_SMALLINT | SQL_TINYINT | SQL_C_STINYINTSQL_C_UTINYINT |
CS_SMALLINT | SQL_SMALLINT | CS_SMALLINT | SQL_SMALLINT | SQL_C_SSHORT SQL_C_USHORT |
CS_INT | SQL_INT | CS_INT | SQL_INT | SQL_C_LONG/ SQL_C_ULONG |
CS_DECIMAL | SQL_DECIMAL | CS_CHAR | SQL_DECIMAL | SQL_C_CHAR |
CS_NUMERIC | SQL_NUMERIC | CS_CHAR | SQL_NUMERIC | SQL_C_CHAR |
CS_FLOAT | SQL_FLOAT | CS_FLOAT | SQL_FLOAT | SQL_C_DOUBLE |
CS_REAL | SQL_REAL | CS_REAL | SQL_REAL | SQL_C_FLOAT |
CS_MONEY | SQL_DECIMAL | CS_CHAR | SQL_DECIMAL | SQL_C_CHAR |
CS_MONEY4 | SQL_DECIMAL | CS_CHAR | SQL_DECIMAL | SQL_C_CHAR |
CS_TEXT | SQL_ LONGVARCHAR | CS_CHAR | SQL_ LONGVARCHAR | SQL_C_CHAR |
CS_IMAGE | SQL_LONG- VARBINARY | CS_CHAR | SQL_LONG- VARBINARY | SQL_C_BINARY |
The usertype field of the CS_DATAFMT structure is a 32-bit integer. The client application can unambiguously specify a target DBMS datatype for a given column. The client application obtains the column datatype indicator from the REMOTE_DATA_TYPE column of the sp_columns result set.
The client application must place the value from sp_columns in the least-significant byte of the usertype field. The remainder of the value is ignored. If a value of 0 is used, the default conversion applies.
An Open Server datatype with an associated user defined datatype is transformed to an ODBC datatype as follows:
The Open Server datatype is transformed to a TDS datatype.
The user defined datatype is used in the child process to transform the TDS datatype to the ODBC datatype.
The following table shows parameter datatypes, user-defined datatypes, TDS datatypes, and the resulting ODBC datatypes.
Parameter Datatype | User Defined Datatype (Byte Value) | TDS Datatype | ODBC Datatype |
CS_CHAR | SQL_CHAR(1) | CS_CHAR | SQL_CHAR(1) |
CS_CHAR | SQL_DATE(9) | CS_CHAR | SQL_TYPE_DAAE(9) |
CS_CHAR | SQL_TIME(10) | CS_CHAR | SQL_TYPE_TIME(10) |
CS_CHAR | SQL_TIMESTAMP(11) | CS_CHAR | SQL_TYPE_TIMESTAMP(11) |
CS_NUMERIC | SQL_NUMERIC(2) | CS_CHAR | SQL_NUMERIC(2) |
CS_DECIMAL | SQL_DECIMAL(3) | CS_CHAR | SQL_DECIMAL(3) |
CS_INT | SQL_INT(4) | CS_INT | SQL_INT(4) |
CS_SMALLINT | SQL_SMALLINT(5) | CS_SMALLINT | SQL_SMALLINT(5) |
CS_FLOAT | SQL_FLOAT(6) | CS_FLOAT | SQL_FLOAT(6) |
CS_FLOAT | SQL_DOUBLE(8) | CS_FLOAT | SQL_DOUBLE(8) |
CS_FLOAT | SQL_BIGINT(-5) | CS_FLOAT | SQL_BIGINT(-5) |
CS_REAL | SQL_REAL(7) | CS_REAL | SQL_REAL(7) |
CS_DATETIME | SQL_DATE(91) | CS_CHAR | SQL_TYPE_DATE(91) |
CS_DATETIME4 | SQL_DATE(91) | CS_CHAR | SQL_TYPE_DATE(91) |
CS_DATETIME | SQL_TIME(92) | CS_CHAR | SQL_TYPE_TIME(92) |
CS_DATETIME4 | SQL_TIME(92) | CS_CHAR | SQL_TYPE_TIME(92) |
CS_DATETIME | SQL_TIMESTAMP(93) | CS_CHAR | SQL_TYPE_TIMESTAMP(93) |
CS_DATETIME4 | SQL_TIMESTAMP(93) | CS_CHAR | SQL_TYPE_TIMESTAMP(93) |
CS_VARCHAR | SQL_VARCHAR(12) | CS_CHAR | SQL_VARCHAR(12) |
CS_LONGCHAR | SQL_LONGVARCHAR(-1) | CS_CHAR | SQL_LONGVARCHAR(-1) |
CS_BINARY | SQL_BINARY(-2) | CS_CHAR | SQL_BINARY(-2) |
CS_VARBINARY | SQL_VARBINARY(-3) | CS_CHAR | SQL_VARBINARY(-3) |
CS_LONGBINARY | SQL_LONGVARBINARY(-4) | CS_CHAR | SQL_LONGVARBINARY(-4) |
CS_MONEY | SQL_DECIMAL(3) | CS_CHAR | SQL_DECIMAL(3) |
CS_MONEY4 | SQL_DECIMAL(3) | CS_CHAR | SQL_DECIMAL(3) |
CS_TINYINT | SQL_TINYINT(-6) | CS_SMALLINT | SQL_TINYINT(-6) |
CS_TEXT | SQL_LONGVARCHAR(-1) | CS_CHAR | SQL_LONGVARCHAR(-1) |
CS_IMAGE | SQL_LONGVARBINARY(-4) | CS_CHAR | SQL_LONGVARBINARY(-4) |
CS_BIT | SQL_BIT(-7) | CS_SMALLINT | SQL_BIT(-7) |
An access service receives datatype names as part of create table or alter table commands as follows:
If the access service is in passthrough mode, the datatype names are not modified.
If the access service is in sybase mode, Sybase names are assumed and are converted to corresponding target-specific datatype names.
A given target may not be able to support all Open Client/Server datatypes, but it permits conversion to an equivalent or compatible datatype. For example, the CS_MONEY datatype can be converted to a numeric (19,4) or equivalent datatype.
For more information about transformation modes, see Chapter 6, "Issuing SQL statements"
When you retrieve data from the target database, the access service converts the target data to default Open Client/Server datatypes for delivery to the client application as follows:
The ODBC datatype has an associated user defined datatype assigned to it.
The ODBC datatype is transformed to the TDS datatype, which is then transported to the parent process.
The parent process uses the user defined datatype to transform the TDS datatype to the Open Server datatype.
The following table shows ODBC datatypes, user-defined datatypes, TDS datatypes, and the resulting Open Server datatypes.
ODBC Datatype | User Defined Datatype (Byte Value) | TDS Datatype | Open Server Datatype |
SQL_CHAR | SQL_CHAR(1) | CS_CHAR | CS_CHAR |
SQL_VARCHAR | SQL_VARCHAR(12) | CS_CHAR | CS_VARCHAR |
SQL_LONGVARCHAR | SQL_LONGVARCHAR(-1) | CS_CHAR | CS_LONGCHAR |
SQL_DECIMAL | SQL_DECIMAL(3) | CS_CHAR | CS_DECIMAL |
SQL_NUMERIC | SQL_NUMERIC(2) | CS_CHAR | CS_NUMERIC |
SQL_SMALLINT | SQL_SMALLINT(5) | CS_SMALLINT | CS_SMALLINT |
SQL_INTEGER | SQL_INTEGER(4) | CS_INT | CS_INTEGER |
SQL_REAL | SQL_REAL(7) | CS_REAL | CS_REAL |
SQL_FLOAT | SQL_FLOAT(6) | CS_FLOAT | CS_FLOAT |
SQL_DOUBLE | SQL_DOUBLE(8) | CS_FLOAT | CS_FLOAT |
SQL_BIT | SQL_BIT(-7) | CS_SMALLINT | CS_BIT |
SQL_TINYINT | SQL_TINYINT(-6) | CS_SMALLINT | CS_TINYINT |
SQL_BIGINT | SQL_BIGINT(-5) | CS_CHAR | CS_FLOAT |
SQL_BINARY | SQL_BINARY(-2) | CS_CHAR | CS_BINARY |
SQL_VARBINARY | SQL_VARBINARY(-3) | CS_CHAR | CS_VARBINARY |
SQL_LONGVARBINARY | SQL_LONGVARBINARY(-4) | CS_CHAR | CS_LONGBINARY |
SQL_DATE | SQL_DATE(91) | CS_CHAR | CS_CHAR |
SQL_TIME | SQL_TIME(92) | CS_CHAR | CS_CHAR |
SQL_TIMESTAMP | SQL_TIMESTAMP(93) | CS_CHAR | CS_CHAR |
Data values returned from the target DBMS to a client application, are converted into a format that Open Client/Server can handle. This conversion may encounter inconsistencies, particularly in supported ranges. The access service must convert the value from the target into an Open Client/Server datatype that is compatible before it sends the value back to the client.
To do this, the access service uses configuration properties. Each target datatype has a default Open Client/Server mapping, but these may be overridden either by the configuration property (thus affecting the entire service), or via a set statement (thus affecting only the client connection).
For more information on configuration values that affect data conversion, see "Data Conversion error properties" .
CHAR and VARCHAR data shorter than 256 characters are returned to the client application as CS_CHAR. Data longer than 255 are returned as CS_TEXT.
DECIMAL data is returned to the client application as CS_DECIMAL. Otherwise, the configuration settings shown in the DecimalResults configuration property apply.
For clients with System 10(TM) and earlier versions, DECIMAL data is returned as CS_FLOAT.
|
|