Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

I have a stored procedure like this:

create procedure "dba"."xxx"( in @param1 integer,in @param2 integer,in @param3 varchar(4096) default '' ) 
begin
  if @param3 = '' then
/* ... */
  else
/* ... */
  end if
end

Since a long time this worked well as expected. Actually a customer reported a problem and I noticed by testing the following call:

call dba.xxx(11,1)

@param3 comes with value '0'. I am really suprised. Under which circumstances is the default value not reliable?

asked 13 May, 08:54

maba7638's gravatar image

maba7638
11336
accept rate: 0%

edited 13 May, 08:59

Volker%20Barth's gravatar image

Volker Barth
40.4k363552824

SQL Anywhere version is 17.0.11.7458

(13 May, 08:57) maba7638

Additional Info: After the @param3 was specified once: call dba.xxx(11,1,'what the hell?') in the next call call dba.xxx(11,1) @param3 came with the last specified value 'what the hell?'

(13 May, 09:10) maba7638

With 17.0.11.7672, default parameters work as expected, and I have never had issued with those in previous versions.

Even an identially named connection variable does not interfere with parameters, and the results are as expected:

create or replace variable @param3 varchar(4096) = 'Dummy variable might get in the way';
create or replace procedure "dba"."xxx"( in @param1 integer,in @param2 integer,in @param3 varchar(4096) default '' ) 
begin
  if @param3 = '' then
     select '@param3 is empty string';
  else
     select '@param3: "' || @param3 || '".';
  end if;
end;
select @param3;                 -- returns 'Dummy variable might get in the way'
call dba.xxx(1, 2);             -- returns '@param3 is empty string'
call dba.xxx(1, 2, 'What?');    -- returns '@param3: "What?".'
call dba.xxx(1, 2);             -- returns '@param3 is empty string'
call dba.xxx(1, 2, default);    -- returns '@param3 is empty string'
call dba.xxx(1, 2, null);       -- returns '@param3: "".'

permanent link

answered 13 May, 09:32

Volker%20Barth's gravatar image

Volker Barth
40.4k363552824
accept rate: 34%

edited 13 May, 09:32

Thanks for answering! My tests where done with the database components from NativeDB (Third party component by Liodden Data). With sqlanywhere Interactive SQL I have the expected results, as you told. No good result for me :-(

(13 May, 09:44) maba7638

Are you able to collect a request level log ( -zr all -zo requests.rll or via sa_server_option). I would be interested in seeing the difference in how calls from NativeDB are being made. It has been some time, but I seem to recall that one of the "3rd party" connection options for Delphi/C++ are embedded SQL based.

(13 May, 10:01) Chris Keating

Did you or your customer connect via TDS? If so, I believe '' is considered equivalent to NULL and comparing anything against NULL would be false (actually 'unknown')?

permanent link

answered 13 May, 09:56

John%20Smirnios's gravatar image

John Smirnios
12.0k396166
accept rate: 37%

Thanks John, but this is not the issue. The default parameter did never take the value NULL

(13 May, 10:29) maba7638
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:

×8

question asked: 13 May, 08:54

question was seen: 129 times

last updated: 13 May, 10:29