SAP SQL Anywhere

Question

Some skripts we've used for years with ASA-9 up to SA-16 start throwing errors when used with SA17, complaining about Transact Syntax in a Watcom function. Minimalistic example:

create or replace procedure FuncTest (@charVal char(30), @intVal integer = null)
begin
  select @charVal, @intVal;
end

This throws SQLCODE=-131, Status="42000". If the "=" sign is replaced with "default", the definition is accepted.

The interesting thing: when rebuilding a database created with an earlier version (e.g. SA16) containing SPs define this way, there's no such error, the procedures work a designed.

So this is my Enhancement Request:
Extend the syntax for the parameter definition part of stored procedures and functions
[ DEFAULT expression ] to [ {DEFAULT | = } expression ] (or sth. like that).

This would achieve better compatibility with syntax from older versions as well as ensure consistent behaviour within SA-17.

Answer 1

As described in another question, the issue is that one cannot mix the SQL standard (and WATCOM syntax) IN/OUT/INOUT parameter declaration with the TSQL default syntax - that combination has never been supported in the grammar.

TSQL does not support IN/OUT/INOUT and still does not.

If you wish, you can still use the TSQL '=' for default assignment, even for a WATCOM procedure (with BEGIN/END). I wouldn't recommend it necessarily because the '=' syntax is specifically tagged as TSQL during parsing and this has the potential to cause other syntactic compatibilities with the rest of the procedure definition, but it will work with the simple example posted above as long as IN/OUT/INOUT is not specified.

Answer 2

The error you are seeing is because the parser identifies this SQL as being a Transact-SQL and that is what is causing the syntax error and the issue is not about tDEFAULT syntax but the semicolon (';' ) inside the body of the procedure at the end of the select statement.

Syntax error near ';' on line 4 (Transact-SQL) SQLCODE=-131, ODBC 3 State="42000" Line 1, column 1

Converting the body of the procedure definition to be pure Transact SQL, as in the following, also eliminates the syntax error:

    create or replace procedure FuncTest (@charVal char(30), @intVal integer = null)
    begin
      select @charVal, @intVal   // ';' removed
    end
    go

So you are hitting a case of mixed dialect issue here and avoiding the mixing has always been recommended approach. More specifically ... the use of

 <_parameter_identifier_> [= <_default_value_>]

is actually part of the Transact SQL dialect and was never Watcom SQL. Switching that to standard (and Watcom) syntax of

 <_parameter_identifier_> [DEFAULT <_default_value_>]

makes the parser identify the procedure as standard SQL (and that way is not later confused by semicolons within the body).

Backward compatability is, of course, a double edged sword when one tries to tighten up the parser to permit for evolving SQL syntax. In this case the issue is not one of a pure T/SQL usage or a pure-{Watcom | ANSI | SQL Standard version xxx} syntax but a mixture so backward or even ongoing compatability is never going to be assured; reference the notes section on this page.

HTH

Answer 3

1

Thanks Nick, my sample was obviously to short to make my intentions clear. I know, what the error messages means. I omitted the keyword "AS" in the procedure definition intentionaly, because all the procedures and functions I'm talking about where written in Watcom syntax, the only exception being the assignment of the default value. Here's another example:

create function FuncTest (@charVal char(30), @intVal integer = null)
returns integer
begin
  -- Watcom function using TSQL style für default value
  declare @res integer;
  if @len(isnull(@charVal,'')) > 0 then 
    set @res= @intVal;
  else
    set @res= 0;
  end if;
  return @intVal;
end

Let me point out again, that this function definition is accepted by versions 9, 10, 11, 12 and 16 and even survives a rebuild with v17. So IMHO the request to accept this syntax also in version 17 seems not unreasonable.

permanent link

answered 25 Nov '15, 03:53

Reimer Pods
4.5k●38●48●91
accept rate: 11%

1

I share your view that it seems strange that v17 accepts that (officially invalid) syntax for rebuilt functions but not for freshly created ones. That being said, I remember similar surprises when migrated code from older versions (e.g. v8) suddenly showed invalid syntax when re-created in a newer version (e.g. 12.0.1) although the rebuild to that version did accept the same syntax - say, "COMMIT TRAN" or "SELECT myLocalVariable =" in a Watcom-SQL procedure that had originally been written in T-SQL syntax. In those cases, I was finally glad to fix these mixed dialect issues but luckily there were not too many of them...

Note, even with v12, the according function is considered T-SQL, not Watcom-SQL (corresponding to Nick's statement):

select SQLDIALECT('create function FuncTest (@charVal char(30), @intVal integer = null)
returns integer
begin
  -- Watcom function using TSQL style für default value
  declare @res integer;
  if @len(isnull(@charVal,'''')) > 0 then 
    set @res= @intVal;
  else
    set @res= 0;
  end if;
  return @intVal;
end');

returns "Transact-SQL". With v17, it returns "Error at character 170". Only if you replace "= null" with "default null", that turns into "Watcom-SQL".

(25 Nov '15, 07:07) Volker Barth

1

The particular change to force the use of the TSQL '=' to be recognized as ONLY Transact-SQL was made in January 2007. I'm trying to track down the more recent change in V17 that now causes mixed-mode procedures to fail with a syntax error.

(20 Sep '18, 17:38) Glenn Paulley

Replies hidden

1

> mixed-mode procedures

Please explain what you mean by that term.

According to the latest docs "The two dialects cannot be mixed within a procedure, trigger, or batch." which has always been a fiction, and it would be great to have an accurate statement.

(21 Sep '18, 07:55) Breck Carter

1

According to the latest docs "The two dialects cannot be mixed within a procedure, trigger, or batch." which [...]

...and which has always been documented as such, at least going back to v10...:)

I've always read that as "You must not mix those dialects, and if you still do, it's on your own risk".

I surely second your RFC.

(21 Sep '18, 08:09) Volker Barth

Thanks for sharing that information. Now I'm just failing to see why that didn't stop this construct from working with ASA 7.0 up to SA 16.0.0.2673.

(24 Sep '18, 08:53) Reimer Pods

flat view

SAP SQL Anywhere

Enhancement Request: in SP definition allow "=" instead of "default" for Watcom dialect (SA-17)

Follow this question

Related questions