Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
JasonHinsperger
Advisor
Advisor
0 Kudos

In this post, originally written by Glenn Paulley and posted to sybase.com in May of 2009, Glenn talks about SQL Anywhere's built-in support for 2 dialects of SQL: TSQL and Watcom SQL, and how mixing dialects can potentially cause problems within your stored procedures.

In November 1995 we launched the first version of SQL Anywhere (version 5.0) that offered support for Transact-SQL, in addition to SQL Anywhere's existing dialect, which we continue to call Watcom SQL. (By the way, SQL Anywhere 5.0 was my first SQL Anywhere release, as I had joined the firm just a month earlier). There are many SQL constructions common between the two SQL dialects, but there are also significant and important differences. Certainly a significant difference between the two is the use of statement delimiters: in Watcom SQL, a semicolon is used to delimit each statement, whereas in the Transact-SQL dialect supported by Sybase Adaptive Server Enterprise, no statement delimiters are specified between statements or within a BEGIN...END block.

Supporting two SQL dialects, one without statement delimiters, is a significant technical challenge. The SQL Anywhere server must be able to parse constructions of either dialect - since it has no idea what an application might send - and recognize when one dialect is being used, or the other. Why? Perhaps the most important semantic difference between the two dialects is how errors are handled. The SQL Anywhere documentation states:

Default procedure error handling is different in the Watcom SQL and Transact-SQL dialects. By default, Watcom SQL dialect procedures exit when they encounter an error, returning SQLSTATE and SQLCODE values to the calling environment. Explicit error handling can be built into Watcom SQL stored procedures using the EXCEPTION statement, or you can instruct the procedure to continue execution at the next statement when it encounters an error, using the ON EXCEPTION RESUME statement. When a Transact-SQL dialect procedure encounters an error, execution continues at the following statement. The global variable @@error holds the error status of the most recently executed statement. You can check this variable following a statement to force return from a procedure. For example, the following statement causes an exit if an error occurs.
IF @@error != 0 RETURN
When the procedure completes execution, a return value indicates the success or failure of the procedure. This return status is an integer, and can be accessed as follows:
DECLARE @Status INT
EXECUTE @Status = proc_sample
IF @Status = 0
   PRINT 'procedure succeeded'
ELSE
   PRINT 'procedure failed'

Hence it is important that the server properly recognize which SQL dialect is being used.

Syntactic clues

On input, the SQL Anywhere server parser may expect a Watcom SQL batch, a Transact-SQL batch, a Watcom SQL dialect statement (eg. CREATE PROCEDURE), or a Transact-SQL one. There are several specific syntactic constructions that indicate to the SQL Anywhere parser that the dialect of the batch, procedure or trigger is Transact-SQL. These include:

  • The use of the ASclause before the procedure body, as in the following Transact-SQL procedure:
    CREATE PROCEDURE showdept @deptname varchar(30)
    AS
       SELECT Employees.Surname, Employees.GivenName
       FROM Departments, Employees
       WHERE Departments.DepartmentName = @deptname
       AND Departments.DepartmentID = Employees.DepartmentID;
    Watcom-dialect procedures use a BEGIN...END block to denote the procedure body, as does the ANSI/ISO SQL standard.
  • For a trigger, the lack of a trigger-time (BEFORE, AFTER, INSTEAD OF, or RESOLVE). In the Transact-SQL dialect supported by Adaptive Server Enterprise 15.0, all triggers are statement-level triggers and in SQL Anywhere such triggers are created as AFTER STATEMENTtriggers. The supported Transact-SQL syntax is:
    CREATE TRIGGER [owner .]trigger_name
    ON [owner .]table_name
    FOR [ INSERT | UPDATE | DELETE ]
    AS ...
    SQL Anywhere's Transact-SQL support does not (yet) include support for Transact-SQL INSTEAD OF triggers, which were recently introduced in the Adaptive Server Enterprise 15.5 release.
  • In a procedure, trigger, function, or SELECT statement, the use of the Transact-SQL '=' operator for aliasing SELECT list expressions, or variable assignment:
    SELECT @var = 'literal string'
    rather than the Watcom SQL dialect's SET statement:
    SET @var = 'literal string';
  • Use of '=' to denote a default value in the argument to a stored procedure, rather than the Watcom SQL dialect's DEFAULT clause.
  • Use of OUTPUT or OUT after the specification of a stored procedure parameter:
    CREATE PROCEDURE showdept @deptname varchar(30) OUTPUT
    AS ...
    rather than the Watcom SQL syntax
    CREATE PROCEDURE showdept ( OUT @deptname varchar(30) )
    BEGIN ...
  • Use of the Transact-SQL statements COMMIT TRANSACTION, ROLLBACK TRANSACTION, or PREPARE TRANSACTION.

Conversely, there are two instances where specific syntax identifies the statement(s) as being in the Watcom-SQL dialect:

  • the CREATE [OR REPLACE] VARIABLE statement, and
  • a Watcom-SQL dialect BEGIN...END block with optional label, variable declaration(s), and an EXCEPTION clause where a semicolon is used to separate the individual statements.

An example: Common Table Expressions

The SQL:2008 standard, and SQL Anywhere, support the SQL construction known as common table expressions, which use the WITH keyword to declare what is effectively an in-lined view definition. WITH RECURSIVE is the syntax used to construct a recursive query. Supporting common table expressions in a SQL dialect that does not utilize statement delimiters is difficult, because of the use of the WITH keyword in various other SQL constructions. For example, with SQL Anywhere, a common table expression definition would conflict with the use of the optional Transact-SQL WITH clause on a constraint definition. Hence SQL Anywhere does not support common table expressions in a Transact-SQL procedure, though they can be used when embedded within a derived table in a query's FROM clause (where a grammar conflict is not an issue).

As an aside, Microsoft SQL Server 2008 supports Transact-SQL procedures that optionally contain statement delimiters (semicolons), and in fact have deprecated the original (no-semicolons) syntax of Transact-SQL. Unlike Adaptive Server Enterprise, Microsoft SQL Server does support common table expressions, but requires, for example, that if used in a batch the preceding statement must be terminated by a semicolon - no doubt because, if it was permitted, of the grammar conflicts it would produce in an LALR(1) - or even LALR(2) - parser.

How SQL Anywhere parses SQL input

As mentioned above, SQL Anywhere has to support both the Watcom SQL and Transact-SQL dialects. Since the server has no idea what the application might send, the SQL Anywhere parser iteratively attempts to the parse input in multiple ways, termed "parse goals": a WATCOM SQL batch, a Transact-SQL batch, an SQL statement. For greater efficiency, the dialect tried first is the dialect of the last successfully parsed statement for that connection.

What happens when there is an error? In that case, the server will try an alternative goal. To illustrate, suppose we have the following compound statement:



begin
  declare @var int
  select @var = 100
  WITH CountEmployees( DepartmentID, n ) AS
  ( SELECT DepartmentID, COUNT( * ) AS n
    FROM Employees GROUP BY DepartmentID )
  SELECT DepartmentID, n
  FROM CountEmployees
  WHERE n <= @var
end;


Here, we have a Transact-SQL compound statement, as it lacks statement delimiters. The server, however, does not know this a priori and attempts to parse it, first, as a single SQL statement, which fails. The server then tries to parse the block as a Watcom SQL dialect compound statement, but this fails because the parser doesn't understand DECLARE @var INT SELECT on lines 25-26, since there is no semicolon to delimit the end of the DECLARE statement. It then tries to parse it as a Transact-SQL batch; this too fails, however, because of the common table expression on beginning on line 27, which isn't supported in Transact-SQL in SQL Anywhere.


So which error gets returned from these three separate attempts? The short answer is "the best one". The metric that is used in SQL Anywhere is that the further along the parser proceeded, the greater the match with the anticipated dialect. So the error that is returned in this example is for the Transact-SQL attempt, which is "Syntax error near 'WITH' on line 4". The Watcom SQL attempt yielded the error "Syntax error near 'DECLARE' on line 3", which the server suppressed after trying the Transact-SQL dialect.


The point of this example is to illustrate that when mixing SQL dialects, the error message returned to the client is based on the server's best attempt at parsing the input, and consequently the particular error may be counter-intuitive. In the example above, there is nothing wrong per se with the common table expression used in the BEGINblock; it's just that common table expressions are not supported in the SQL Anywhere implementation of the Transact-SQL dialect. The Transact-SQL dialect was assumed because the parser proceeded further through the compound statement using Transact-SQL grammar. If one had added semicolons to the block above:


BEGIN
  DECLARE @var int;
  SELECT @var = 100;
  WITH CountEmployees( DepartmentID, n ) AS
  ( SELECT DepartmentID, COUNT( * ) AS n
    FROM Employees GROUP BY DepartmentID )
  SELECT DepartmentID, n
  FROM CountEmployees
  WHERE n <= @var;
END;


then this compound statement, too, would yield an error. The statement delimiters would quickly terminate parsing the compound statement as Transact-SQL, yet the use of the Transact-SQL syntax for variable assignment on line 36 would contradict parsing the statement using the Watcom SQL dialect. This latter error would be the one returned in this case.