Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Stored procedure and SQLCODE

2 posts in General Discussion Last posting was on 2007-10-14 12:33:37.0Z
Allan S Posted on 2007-10-14 06:20:36.0Z
From: "Allan S" <allanjs@netspace.net.au.NOSPAM>
Newsgroups: ianywhere.public.general
Subject: Stored procedure and SQLCODE
Lines: 53
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: dsl-203-113-253-69-static.nsw.netspace.net.au
X-Original-NNTP-Posting-Host: dsl-203-113-253-69-static.nsw.netspace.net.au
Message-ID: <4711b534@forums-1-dub>
Date: 13 Oct 2007 23:20:36 -0700
X-Trace: forums-1-dub 1192342836 203.113.253.69 (13 Oct 2007 23:20:36 -0700)
X-Original-Trace: 13 Oct 2007 23:20:36 -0700, dsl-203-113-253-69-static.nsw.netspace.net.au
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6421
Article PK: 2885

Hi

I'm currently running SQL Anywhere 10.0.1.3559 . I was originally running
10.0.1 maintenance release but have patched it up to see if it resolves the
following issue. I have a stored procedure that will add words to a word
table if the word doesn't already exist in the table (sywr_word), I've been
checking SQLCODE = 100 (as follows) in ASA 8 and earlier releases of ASA
without any problems as it was my understanding that SQLCODE and SQLSTATE
reflect that status of the last executed SQL statement. Since porting the
database to 10.0.1 the code now returns SQLCODE = 100 when the word does
exist in the table, and then inserts another row with the word duplicated in
the file. The next execution of the stored procedure will cause the
set wrkey=(select wridentkey from sywr_word where wrword = ls_word)
statement to fail with SQLCODE = -185 Select returns more than one row.

ALTER PROCEDURE "DBA"."up_cpos_AddSearchWords"(in ai_identkey integer,in
as_string long varchar)
begin
declare ls_word varchar(60);
declare wrkey integer;
if as_string is null then
return
end if;
while(ascii("right"(as_string,1)) = 10 or ascii("right"(as_string,1)) =
13) loop
set as_string="left"(as_string,length(as_string)-1)
end loop;
set as_string=trim(as_string);
while as_string <> '' loop
call up_getnextword(as_string,ls_word);
set ls_word=ucase(ls_word);
if not ls_word = any(select wxword from sywx_wordexclusions) and
not ls_word = any(select wrword from
cpos_organisationsearch,sywr_word where osonidentkey = ai_identkey and
oswridentkey = wridentkey) then
set wrkey=(select wridentkey from sywr_word where wrword =
ls_word);
if(sqlcode = 100) then
insert into sywr_word(wrword) values(ls_word);
set wrkey=@@identity
end if;
insert into cpos_organisationsearch values(wrkey,ai_identkey)
end if
end loop
end

I can get around the problem by checking the validity of wrkey but I'd like
to know why this is happening? Is this a behavourial change in version 10?

Thanks in advance
Allan


Breck Carter [Team iAnywhere] Posted on 2007-10-14 12:33:37.0Z
From: "Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: Stored procedure and SQLCODE
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <0234h3tk9c5ds8g6pas9dsnjqmui5j8ni5@4ax.com>
References: <4711b534@forums-1-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: bcarter.sentex.ca
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
Date: 14 Oct 2007 05:33:37 -0700
X-Trace: forums-1-dub 1192365217 64.7.134.118 (14 Oct 2007 05:33:37 -0700)
X-Original-Trace: 14 Oct 2007 05:33:37 -0700, bcarter.sentex.ca
Lines: 129
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6422
Article PK: 4748

It looks like the behavior changed a long time ago (see below) but I
am not sure how it explains your observation: Back in V7, it looks
like SQLCODE would *always* be set to zero by the SET regardless of
whether or not the subquery returned any rows.

An argument could be made that the old behavior is correct, since even
though the SELECT returned no rows, the SET worked (it set @x to NULL)
and therefore SQLCODE "should" be set to zero because SET was the last
SQL statement executed. Even the Version 10 Help says this: "The
SQLCODE value is set after each statement."... and the SELECT isn't a
statement, it is a subquery expression inside a statement, and that
statement worked.

However, "correct" and "useful" are sometimes two different things,
and I suspect that SQLCODE values are more useful when they reflect
the last database query. The value of @@ERROR is a different story,
and in V10 it looks like it returns zero from the SET.

If you want us to look at your exact situation, please provide a
reproducible (tables, procedures, data).

Breck

-- Row found.
BEGIN
DECLARE @x INTEGER;
DECLARE @SQLCODE INTEGER;
SET @x = 999;
SET @x = ( SELECT dummy_col FROM dummy WHERE dummy_col = 0 );
IF SQLCODE = 100 THEN
SELECT @@version, @x, 'SQLCODE is 100' AS result;
ELSE
SELECT @@version, @x, 'SQLCODE is not 100' AS result;
END IF;
END;

@@version,@x,result
'7.0.3.2046',0,'SQLCODE is not 100'

@@version,@x,result
'8.0.3.5379',0,'SQLCODE is not 100'

@@version,@x,result
'10.0.1.3559',0,'SQLCODE is not 100'

-- Row not found.
BEGIN
DECLARE @x INTEGER;
DECLARE @SQLCODE INTEGER;
SET @x = 999;
SET @x = ( SELECT dummy_col FROM dummy WHERE dummy_col = 1 );
IF SQLCODE = 100 THEN
SELECT @@version, @x, 'SQLCODE is 100' AS result;
ELSE
SELECT @@version, @x, 'SQLCODE is not 100' AS result;
END IF;
END;

@@version,@x,result
'7.0.3.2046',,'SQLCODE is not 100'

@@version,@x,result
'8.0.3.5379',,'SQLCODE is 100'

@@version,@x,result
'10.0.1.3559',,'SQLCODE is 100'

On 13 Oct 2007 23:20:36 -0700, "Allan S"

<allanjs@netspace.net.au.NOSPAM> wrote:

>Hi
>
>I'm currently running SQL Anywhere 10.0.1.3559 . I was originally running
>10.0.1 maintenance release but have patched it up to see if it resolves the
>following issue. I have a stored procedure that will add words to a word
>table if the word doesn't already exist in the table (sywr_word), I've been
>checking SQLCODE = 100 (as follows) in ASA 8 and earlier releases of ASA
>without any problems as it was my understanding that SQLCODE and SQLSTATE
>reflect that status of the last executed SQL statement. Since porting the
>database to 10.0.1 the code now returns SQLCODE = 100 when the word does
>exist in the table, and then inserts another row with the word duplicated in
>the file. The next execution of the stored procedure will cause the
>set wrkey=(select wridentkey from sywr_word where wrword = ls_word)
>statement to fail with SQLCODE = -185 Select returns more than one row.
>
>ALTER PROCEDURE "DBA"."up_cpos_AddSearchWords"(in ai_identkey integer,in
>as_string long varchar)
>begin
> declare ls_word varchar(60);
> declare wrkey integer;
> if as_string is null then
> return
> end if;
> while(ascii("right"(as_string,1)) = 10 or ascii("right"(as_string,1)) =
>13) loop
> set as_string="left"(as_string,length(as_string)-1)
> end loop;
> set as_string=trim(as_string);
> while as_string <> '' loop
> call up_getnextword(as_string,ls_word);
> set ls_word=ucase(ls_word);
> if not ls_word = any(select wxword from sywx_wordexclusions) and
> not ls_word = any(select wrword from
>cpos_organisationsearch,sywr_word where osonidentkey = ai_identkey and
>oswridentkey = wridentkey) then
> set wrkey=(select wridentkey from sywr_word where wrword =
>ls_word);
> if(sqlcode = 100) then
> insert into sywr_word(wrword) values(ls_word);
> set wrkey=@@identity
> end if;
> insert into cpos_organisationsearch values(wrkey,ai_identkey)
> end if
> end loop
>end
>
>I can get around the problem by checking the validity of wrkey but I'd like
>to know why this is happening? Is this a behavourial change in version 10?
>
>Thanks in advance
>Allan
>

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
breck.carter@risingroad.com