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
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.
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...ENDblock to denote the procedure body, as does the ANSI/ISO SQL standard.
- For a trigger, the lack of a trigger-time (
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 OFtriggers, which were recently introduced in the Adaptive Server Enterprise 15.5 release.
- In a procedure, trigger, function, or
SELECTstatement, the use of the Transact-SQL ‘=’ operator for aliasing
SELECTlist expressions, or variable assignment:
SELECT @var = 'literal string'
rather than the Watcom SQL dialect’s
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
- Use of
OUTafter 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
ROLLBACK TRANSACTION, or
Conversely, there are two instances where specific syntax identifies the statement(s) as being in the Watcom-SQL dialect:
CREATE [OR REPLACE] VARIABLEstatement, and
- a Watcom-SQL dialect
BEGIN...ENDblock with optional label, variable declaration(s), and an
EXCEPTIONclause 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.