The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

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.

asked 24 Nov '15, 08:15

Reimer%20Pods's gravatar image

Reimer Pods
4.2k334583
accept rate: 11%


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

permanent link

answered 24 Nov '15, 09:14

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
7.3k35107
accept rate: 31%

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%20Pods's gravatar image

Reimer Pods
4.2k334583
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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×106
×86
×11

question asked: 24 Nov '15, 08:15

question was seen: 460 times

last updated: 25 Nov '15, 07:08