Issuing SQL statements

This chapter describes the SQL transformation modes that the DirectConnect Anywhere Access Service uses to interpret data.

This chapter covers the following topics:

Overview

SQL transformation modes affect the way the DirectConnect Anywhere Access Service modifies SQL statements that are written for one database but are processed against another.

The transformation mode primarily affects the way in which the access service treats incoming SQL statements, but it also affects these functional areas:

Transaction
Datatype handling
set statements
RSPs
ASE/CIS (formerly OmniConnect) interoperability
ODBC driver interoperability

When you configure the access service with a specific transformation mode, that mode is effective for all client connections unless you use a set statement to alter it for a specific connection.

To make dialects appear as common SQL, the access service supports the following standard transformation modes:

passthrough
sybase

passthrough Mode

Use passthrough mode when you want the client to have direct access to the capabilities of a DBMS target. The SQL dialect is for ODBC, not the actual DBMS dialect to which the ODBC driver is connected.

The access service performs virtually no SQL transformation. passthrough mode converts carriage returns and line feeds. All other commands are passed directly to the ODBC driver and the results are returned to the client.

With DirectConnect Anywhere, you can prepare multiple SQL statements in a single statement. When you do this, be sure to separate the statements with semicolons.

sybase Mode

Use sybase mode for maximum compatibility between different target databases. This allows client applications that use sybase mode to operate independently of the target they are accessing.

When sybase mode is in effect, the access service performs a limited amount of Transact-SQLŽ syntax transformation on the SQL text that it receives, including text found in the following commands:

language
cursor declare
dynamic prepare
dynamic execute immediate

In sybase mode, the access service transforms the SQL text it receives into syntax that the target DBMS supports. If the access service receives syntax that it does not recognize, it passes the text to the DBMS for execution.

Because an application uses this mode for purposes of compatibility with all access services provided by Sybase, it should not issue SQL commands that are unique to any single target DBMS.

You can prepare multiple SQL statements in a single statement. Separate the statements with semicolons.

The differences between sybase mode and passthrough mode are shown in the following figure:

passthrough mode transfers similar dialect and syntax directly from the client application to the target database.
sybase mode performs translation functions, changing the select statement from lowercase in the client application to uppercase in the target database.

Figure 6-1: passthrough and sybase transformation modes raster

sybase Mode transformations

sybase mode makes the following transformations to SQL syntax:

Provides standard transformations of parameter marker names beginning with the "@" character.
Removes Transact-SQL comments in the form /*.
Converts Transact-SQL comparison operators (such as ! and =) into the target DBMS equivalent.
Converts single and double quotation marks used as string delimiters to the appropriate delimiter for the target DBMS.
Strips dollar signs from money constants.

Unsupported syntax passes unchanged to the DBMS for processing.

Sybase mode does not convert nonquoted tokens to uppercase.

Standard transformations for Transact-SQL commands

Standard transformations are provided for the commands listed in the following table. Only those portions of the Transact-SQL syntax that are directly translatable into target DBMS syntax are transformed.

Standard transformations for T-SQL commands
Command	Description of Transformation
alter table	Adds new columns to an existing table
begin transaction	Begins a new transaction
commit transaction	Commits all work performed for this transaction
create index	Creates a new index on a table
create table	Creates new tables
create view	Creates a new view
delete	Deletes rows from a table
delete (cursor)	Removes rows from a table
delete (dynamic)	Removes rows from a table
drop index	Removes an index from a table
drop table	Removes a table
drop view	Removes one or more views
execute	Runs system or user defined stored procedures.
grant	Assigns authorizations to users
insert	Adds new rows to a table or view
insert bulk	(Currently not supported)
prepare transaction	Prepares to commit a transaction
revoke	Revokes permission from users
rollback transaction	Rolls back or aborts the current transaction
select	Retrieves rows from database objects
truncate table	Truncates a table by removing all rows (this statement is not logged and is not part of any transaction)
update	Adds or modifies data in existing rows
update (cursor)	Positional update: changes data in row made current by a read cursor
update (dynamic)	Dynamic update: changes data in existing rows
use	Accesses an existing database

Backward compatible modes

The DirectConnect Anywhere Access Service supports the following three Database Gateway transformation modes for backward compatibility:

tsql0 - This mode works in the same way as passthrough mode and will be phased out eventually.
tsql1 - This mode is provided for backward compatibility only and also will be phased out. It works as it does in the MDI Database Gateway for DB2, Version 2.05.
tsql2 - This mode is provided for backward compatibility only. It works in the same way as sybase mode.


	Expand Search