With the recently applied EBF Build 4793 we've encountered a problem when creating procedures in Watcom syntax, that use default values for parameters. Example

create or replace procedure SetValues (in @arg1 integer, in @arg2 char(30) =  null)
begin
  declare @res integer;
  -- do something 
  return @res;
end 

In the last 20 years, that has always been accepted as valid syntax. Using the version mentioned before we're getting an error message

Syntax error at '=' in line 1 (Watcom SQL)

If '=' is replaced with 'default', the procedure gets created sucessfully. If a database, that was created with an earlier SQLA version, is rebuilt the procedure keeps working, but can't be modified without the noted change.

Is this a bug or intentional behaviour change? Any ideas for a workaround? Our data model contains ~ 75 such prodedures, so I'm not enthusiastic about the idea to have to change them all.

asked 04 Sep, 06:11

Reimer%20Pods's gravatar image

Reimer Pods
4.4k374890
accept rate: 12%

edited 17 Sep, 04:28

Volker%20Barth's gravatar image

Volker Barth
32.6k328476696

Replies hidden

FWIW this bright idea didn't help: SET TEMPORARY OPTION ANSINULL = 'OFF';

(04 Sep, 09:35) Breck Carter
1

Just to add to Nick's answer there:

AFAIK, the syntax with '=' instead of 'default'

"[...]parameter-name data-type = expression"

has never been officially documented for Watcom-SQL procedures, only for T-SQL.

(04 Sep, 10:31) Volker Barth

So I should suppose that this change was a bugfix eliminating an undocumented feature?

Btw, your comment to my other post regarding SQLDIALECT made me look at your sample again. In SC (12.0.1.4436) I can add that function using ISQL and then convert it to Watcom (changes syntax) and to TSQL (ditto). So it seems somewhat inbetween.

(07 Sep, 05:07) Reimer Pods

So I should suppose that this change was a bugfix eliminating an undocumented feature?

Well, I'm just another customer so I'm not supposed to be able to answer that one...:)

(07 Sep, 05:21) Volker Barth
1

FWIW the parser seems to be [cough] flexible when dealing with so-called TSQL features; i.e., some of those features are freely available in Watcom-SQL scripts and they don't change the so-called "dialect".

One disadvantage of "flexible" may be that when changes are made to the parser, flexible features may bend in a different direction.

At this point, the question may be "In order to fix this feature, which other feature would you like to see broken?" :)

My personal answer is, "Don't fix it, Reimer will cope" :)

(07 Sep, 08:46) Breck Carter

"Reimer will cope": I'll do my very best ;)

(10 Sep, 06:22) Reimer Pods

Seems particularly reasonable due to the unfortunate lack of comments by the SQL Anywhere developers...

(10 Sep, 06:45) Volker Barth
Comment Text Removed

Well, after Glenn's elaborate answer my statement surely is moot:)

(25 Sep, 03:42) Volker Barth
showing 2 of 9 show all flat view

Perhaps I'm missing something, but as far as I can tell the syntax above has never been supported by SQL Anywhere.

First a disclaimer - I've gone through more than 10 years of source code changes and tried the above in 12.0.1 and latest v17, but I haven't tried all possible releases and/or EBFs.

Second - as you might imagine, TSQL compatibility is extraordinarily difficult since TSQL is a moving target. In particular the move by Microsoft to include statement delimiters (semicolons) in their TSQL dialect makes Microsoft TSQL much closer to SQL Anywhere's WATCOM dialect in terms of syntax, but moves it further away from ASE's version of TSQL.

Specifically, the CREATE PROCEDURE and CREATE FUNCTION statements in SQL Anywhere have never supported the combination of using IN, OUT, or INOUT with the TSQL default parameter assignment ('='). Transact-SQL has never supported the IN, OUT, INOUT declarations for procedure variables and still does not; see https://docs.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql?view=sql-server-2017.

However, if one omits the IN declaration for the parameter, as in the enhancement request here (Enhancement Request: in SP definition allow "=" instead of "default" for Watcom dialect (SA-17)) then the TSQL default assignment will work even though the procedure uses WATCOM syntax. The example used in that other post does work for me with current SA17 software and with 12.0.1.

permanent link

answered 24 Sep, 16:54

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k572106
accept rate: 43%

1

@Glenn: Do you plan to have your new account merged with your old and wellknown one? (AFAIK, that should be possible, in contrast to the strange ID situation in the SAP Community...)

(25 Sep, 03:45) Volker Barth
Replies hidden

Many thanks, Glen, for your detailed explanation. Now I begin to see the light ...

(25 Sep, 06:43) Reimer Pods
1

Wow, not only planned, but already done:)

(25 Sep, 10:56) 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:

×113
×108

question asked: 04 Sep, 06:11

question was seen: 224 times

last updated: 25 Sep, 10:56