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.

sqlstate in asa 8.0.2

3 posts in General Discussion Last posting was on 2003-06-05 20:36:03.0Z
ubag Posted on 2003-06-04 20:48:39.0Z
From: ubag
Date: Wed, 4 Jun 2003 16:48:39 -0400
Newsgroups: ianywhere.public.general
Subject: sqlstate in asa 8.0.2
Message-ID: <11BFC8B044A721340072513F85256D3B.0072514F85256D3B@webforums>
Lines: 27
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub ianywhere.public.general:1158
Article PK: 3403

There is a strange behaviour that I found in ASA 8.0.2 Build 4089
on the return value of sqlstate. In the code segment below:

begin
declare sqlstate_notfound exception for sqlstate value '02000';
select f_test(var1)
into var2
from test_state
where test_state.col1 = var2;
message sqlstate;
message @@rowcount;
end\

If the function f_test returns a value that is not equal to
test_state.col1, the sqlstate is '00000' and rowcount is 0.

If you test this in SQL Anywhere 5.5, the same code will return
sqlstate '02000' and rowcount is 0.

We just recently upgraded to 8.0.2 from 5.5 and this behaviour change was
not covered in the documentation.

With this change, does it mean that it is always safer to check row returns
using @@rowcount rather than sqlstate?

Thanks,
HErald


Nick Elson Posted on 2003-06-05 16:57:08.0Z
From: "Nick Elson" <no_spam_nicelson@sybase.com>
References: <11BFC8B044A721340072513F85256D3B.0072514F85256D3B@webforums>
Subject: Re: sqlstate in asa 8.0.2
Date: Thu, 5 Jun 2003 12:57:08 -0400
Lines: 64
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <O2lo5R4KDHA.317@forums-2-dub>
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: nicelson-xp.sybase.com 172.31.142.76
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub ianywhere.public.general:1162
Article PK: 3407

That is a bit odd.

<<((also the question reminds of some old thread I just can't find for some
reason))>>

If it helps at all, this works for me in dbisql version 8.0.2 #4251:

CREATE VARIABLE orders INT;
BEGIN
SELECT id INTO Orders
FROM DBA.customer
WHERE DBA.customer.id = 999;
message sqlstate;
END;

You could try the higher ebfs, and if they don't affect this
try decomposing this a little further (sans function, sans
equality predicate against a variable, ....)


If after all that you don't make any progress a more complete
repro may be required.


As an aside (which may not matter in this case but worth
mentioning)some SQLStates have been tightened up since
5.5.0x in both the IEC/ISO 'standard SQL' and the ASA
code line, so there may be some special conditions affecting
this.

<ubag> wrote in message
news:11BFC8B044A721340072513F85256D3B.0072514F85256D3B@webforums...
>
> There is a strange behaviour that I found in ASA 8.0.2 Build 4089
> on the return value of sqlstate. In the code segment below:
>
> begin
> declare sqlstate_notfound exception for sqlstate value '02000';
> select f_test(var1)
> into var2
> from test_state
> where test_state.col1 = var2;
> message sqlstate;
> message @@rowcount;
> end\
>
> If the function f_test returns a value that is not equal to
> test_state.col1, the sqlstate is '00000' and rowcount is 0.
>
> If you test this in SQL Anywhere 5.5, the same code will return
> sqlstate '02000' and rowcount is 0.
>
> We just recently upgraded to 8.0.2 from 5.5 and this behaviour change was
> not covered in the documentation.
>
> With this change, does it mean that it is always safer to check row
returns
> using @@rowcount rather than sqlstate?
>
> Thanks,
> HErald


ubag Posted on 2003-06-05 20:36:03.0Z
From: ubag
Date: Thu, 5 Jun 2003 16:36:03 -0400
Newsgroups: ianywhere.public.general
Subject: Re: sqlstate in asa 8.0.2
Message-ID: <06A5BD2F05F2619500712A1D85256D3C.006479EE85256D3C@webforums>
References: <11BFC8B044A721340072513F85256D3B.0072514F85256D3B@webforums> <O2lo5R4KDHA.317@forums-2-dub>
Lines: 13
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub ianywhere.public.general:1168
Article PK: 3413

The same SQL statement wil work for me , no problem.
The problem occurs when you start to search the value returned by a
function against the table your are selecting from. So from your SQL, if
for example you have some user defined function to convert id to some
format and use the return value to search for its occurence in a table, it
does not give the appropriate SQLSTATE if the record is not found.

But then again, My version is 8.0.2 Build 4089. If anybody here knows if
this is fixed in a leter build please sound off.


Tnx