User's Guide
PART 5. The Adaptive Server Family
CHAPTER 30. Transact-SQL Compatibility
Adaptive Server Anywhere supports a large subset of Transact-SQL, which is the dialect of SQL supported by Sybase Adaptive Server Enterprise. This chapter describes compatibility of SQL between Anywhere and Enterprise.
The goals of Transact-SQL support in Adaptive Server Anywhere are as follows:
Application portability Many applications, stored procedures, and batch files can be written to be used with both Enterprise and Anywhere databases.
Data portability Data can be exchanged and replicated between Anywhere and Enterprise databases with a minimum of effort.
The aim is to make it possible to write applications to work with both Enterprise and Anywhere. Existing Adaptive Server Enterprise applications will generally require some changes to run on a Anywhere database.
Transact-SQL support in Anywhere takes the following form:
Many SQL statements are compatible between Anywhere and Enterprise.
For some statements, particularly in the procedure language used in procedures, triggers, and batches, a separate Transact-SQL statement is supported together with the syntax supported in previous versions of Adaptive Server Anywhere. For these statements, Adaptive Server Anywhere supports two dialects of SQL. In this chapter, we name those dialects Transact-SQL and Watcom-SQL.
A procedure, trigger, or batch is executed in either the Transact-SQL or Watcom-SQL dialect. You must use control statements from one dialect only throughout the batch or procedure. For example, each dialect has different flow control statements.
The following diagram illustrates how the two dialects overlap.
Adaptive Server Anywhere supports a very high percentage of Transact-SQL language elements, functions, and statements for working with existing data. For example, all of the numeric functions are supported, all but one of the string functions are supported, all aggregate functions are supported, and all date and time functions are supported. As another example, Adaptive Server Anywhere supports Transact-SQL outer joins (using =* and *= operators) and extended DELETE and UPDATE statements using joins.
Further, a very high percentage of the Transact-SQL stored procedure language is supported (CREATE PROCEDURE and CREATE TRIGGER syntax, control statements, and so on), and many but not all aspects of Transact-SQL data definition language statements are supported.
There are design differences in the architectural and configuration facilities supported by each product. Device management, user management, and maintenance tasks such as backups tend to be system-specific. Even here, Transact-SQL system tables are provided in Adaptive Server Anywhere as views, where the tables that are not meaningful in Adaptive Server Anywhere have no rows. Also, a set of system procedures is provided for some of the more common administrative tasks.
This chapter looks first at some system-level issues where differences are most marked, before discussing data manipulation and data definition language aspects of the dialects where compatibility is high.
There are SQL statements supported by Adaptive Server Anywhere that are part of one dialect, but not the other. The two dialects cannot be mixed within a procedure, trigger, or batch. For example: the following are supported in Adaptive Server Anywhere, but as part of the Transact-SQL dialect only:
The Transact-SQL control statements IF and WHILE.
The Transact-SQL EXECUTE statement.
The Transact-SQL CREATE PROCEDURE and CREATE TRIGGER statements.
The Transact-SQL BEGIN TRANSACTION statement.
SQL Statements not separated by semicolons are part of a Transact-SQL procedure or batch.
The following statements are among those not supported in Adaptive Server Enterprise:
The control statements CASE, LOOP, and FOR
The Adaptive Server Anywhere versions of IF and WHILE.
The CALL statement.
The Adaptive Server Anywhere versions of the CREATE PROCEDURE, CREATE FUNCTION, and CREATE TRIGGER statements.
SQL Statements separated by semicolons are not supported in Adaptive Server Enterprise.
The two dialects cannot be mixed within a procedure, trigger, or batch. That is:
You can include Transact-SQL-only statements together with statements that are part of both dialects in a batch, procedure, or trigger.
You can include statements not supported by Adaptive Server Enterprise together with statements that are supported by both servers in a batch, procedure, or trigger.
You cannot include Transact-SQL-only statements together with Adaptive Server Anywhere-only statements in a batch, procedure, or trigger.