Question Re: default parameter empty string in Technology Q&A

default parameter empty string

kakom — Mon, 13 May 2024 08:54:38 GMT

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?

Re: default parameter empty string

kakom — Mon, 13 May 2024 08:57:19 GMT

SQL Anywhere version is 17.0.11.7458

Re: default parameter empty string

kakom — Mon, 13 May 2024 09:10:04 GMT

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?'

Re: default parameter empty string

VolkerBarth — Mon, 13 May 2024 09:32:01 GMT

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: "".'

Re: default parameter empty string

kakom — Mon, 13 May 2024 09:44:50 GMT

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 😞

Re: default parameter empty string

johnsmirnios — Mon, 13 May 2024 09:56:31 GMT

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')?

Re: default parameter empty string

chris_keating — Mon, 13 May 2024 10:01:34 GMT

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.

Re: default parameter empty string

kakom — Mon, 13 May 2024 10:29:19 GMT

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