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.

Using top in a cursor misses the last record?

6 posts in General Discussion Last posting was on 2011-05-04 07:56:43.0Z
Simon Scott Posted on 2011-05-03 03:38:02.0Z
Sender: 3c65.4dbf77aa.1804289383@sybase.com
From: Simon Scott
Newsgroups: sybase.public.ase.general
Subject: Using top in a cursor misses the last record?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4dbf789a.3c76.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 2 May 2011 20:38:02 -0700
X-Trace: forums-1-dub 1304393882 10.22.241.41 (2 May 2011 20:38:02 -0700)
X-Original-Trace: 2 May 2011 20:38:02 -0700, 10.22.241.41
Lines: 80
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30149
Article PK: 72331

Hey all

Witness:

select @@version
go
create table #bugtest (a numeric(4,0), b varchar(10))
go
insert into #bugtest values (1,'A')
insert into #bugtest values (2,'B')
insert into #bugtest values (3,'C')
insert into #bugtest values (4,'D')
insert into #bugtest values (5,'E')
insert into #bugtest values (6,'F')
insert into #bugtest values (7,'G')
go
select top 3 * from #bugtest
go
declare c_a cursor for select top 3 a,b from #bugtest for
read only
go
begin

declare @a numeric(4,0)
declare @b varchar(10)

open c_a
fetch c_a into @a, @b
while @@sqlstatus = 0
begin
print @b
fetch c_a into @a, @b
end
close c_a
end
go
deallocate cursor c_a
go
drop table #bugtest
go


And the results:

column1

----------------------------------------------------------------------------------------------------------------------------
Adaptive Server Enterprise/15.0.3/EBF 16549
ESD#1/P/x86_64/Enterprise Linux/ase1503/2681/64-bit/FBO/Mon
Mar 9 04:10:47 2009

1 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data:
1/ms]



a b
---- ----
1 A
2 B
3 C

3 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data:
1/ms]


Warnings: --->
W (1): A
W (2): B
<---



Why do I only get 2 records in the cursor?

If I make the cursor insensitive, it works as expected.

Bug in 15.0.3?

thanks


Luc Van der Veurst Posted on 2011-05-03 08:26:46.0Z
From: "Luc Van der Veurst" <dba_azvub@hotmail.com>
Newsgroups: sybase.public.ase.general
References: <4dbf789a.3c76.1681692777@sybase.com>
Subject: Re: Using top in a cursor misses the last record?
Lines: 94
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5931
X-RFC2646: Format=Flowed; Original
X-Forwarded: by - (DeleGate/5.8.7)
X-Forwarded: by - (DeleGate/5.8.7)
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4dbfbc46@forums-1-dub>
Date: 3 May 2011 01:26:46 -0700
X-Trace: forums-1-dub 1304411206 10.22.241.152 (3 May 2011 01:26:46 -0700)
X-Original-Trace: 3 May 2011 01:26:46 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30150
Article PK: 72326

Hi ,

The problem does not occur on a ASE 15.5 dataserver. So it must
be a bug fixed in a later release/EBF.

Best thing to do is upgrade to the latest version of ASE 15.0.3 and
see if the problem still occurs.

Best Regards,
Luc.

<Simon Scott> wrote in message news:4dbf789a.3c76.1681692777@sybase.com...
> Hey all
>
> Witness:
>
> select @@version
> go
> create table #bugtest (a numeric(4,0), b varchar(10))
> go
> insert into #bugtest values (1,'A')
> insert into #bugtest values (2,'B')
> insert into #bugtest values (3,'C')
> insert into #bugtest values (4,'D')
> insert into #bugtest values (5,'E')
> insert into #bugtest values (6,'F')
> insert into #bugtest values (7,'G')
> go
> select top 3 * from #bugtest
> go
> declare c_a cursor for select top 3 a,b from #bugtest for
> read only
> go
> begin
>
> declare @a numeric(4,0)
> declare @b varchar(10)
>
> open c_a
> fetch c_a into @a, @b
> while @@sqlstatus = 0
> begin
> print @b
> fetch c_a into @a, @b
> end
> close c_a
> end
> go
> deallocate cursor c_a
> go
> drop table #bugtest
> go
>
>
> And the results:
>
> column1
>
> ----------------------------------------------------------------------------------------------------------------------------
> Adaptive Server Enterprise/15.0.3/EBF 16549
> ESD#1/P/x86_64/Enterprise Linux/ase1503/2681/64-bit/FBO/Mon
> Mar 9 04:10:47 2009
>
> 1 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data:
> 1/ms]
>
>
>
> a b
> ---- ----
> 1 A
> 2 B
> 3 C
>
> 3 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data:
> 1/ms]
>
>
> Warnings: --->
> W (1): A
> W (2): B
> <---
>
>
>
> Why do I only get 2 records in the cursor?
>
> If I make the cursor insensitive, it works as expected.
>
> Bug in 15.0.3?
>
> thanks


Mark A. Parsons Posted on 2011-05-03 11:02:30.0Z
Sender: 2934.4dbeb4b9.1804289383@sybase.com
From: Mark A. Parsons
Newsgroups: sybase.public.ase.general
Subject: Re: Using top in a cursor misses the last record?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4dbfe0c6.4580.1681692777@sybase.com>
References: <4dbf789a.3c76.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 3 May 2011 04:02:30 -0700
X-Trace: forums-1-dub 1304420550 10.22.241.41 (3 May 2011 04:02:30 -0700)
X-Original-Trace: 3 May 2011 04:02:30 -0700, 10.22.241.41
Lines: 99
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30151
Article PK: 72330

What happens when you run your test via the isql command
line tool?

On 15.0.3/ESD#2 I get the following output from the cursor
loop when executed via isql:

==================
A
(1 row affected)
B
(1 row affected)
C
(0 rows affected)
==================

> Hey all
>
> Witness:
>
> select @@version
> go
> create table #bugtest (a numeric(4,0), b varchar(10))
> go
> insert into #bugtest values (1,'A')
> insert into #bugtest values (2,'B')
> insert into #bugtest values (3,'C')
> insert into #bugtest values (4,'D')
> insert into #bugtest values (5,'E')
> insert into #bugtest values (6,'F')
> insert into #bugtest values (7,'G')
> go
> select top 3 * from #bugtest
> go
> declare c_a cursor for select top 3 a,b from #bugtest for
> read only
> go
> begin
>
> declare @a numeric(4,0)
> declare @b varchar(10)
>
> open c_a
> fetch c_a into @a, @b
> while @@sqlstatus = 0
> begin
> print @b
> fetch c_a into @a, @b
> end
> close c_a
> end
> go
> deallocate cursor c_a
> go
> drop table #bugtest
> go
>
>
> And the results:
>
> column1
>
> ----------------------------------------------------------
> ----------------------------------------------------------
> --------
> Adaptive Server Enterprise/15.0.3/EBF 16549
> ESD#1/P/x86_64/Enterprise
> Linux/ase1503/2681/64-bit/FBO/Mon Mar 9 04:10:47 2009
>
> 1 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data:
> 1/ms]
>
>
>
> a b
> ---- ----
> 1 A
> 2 B
> 3 C
>
> 3 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data:
> 1/ms]
>
>
> Warnings: --->
> W (1): A
> W (2): B
> <---
>
>
>
> Why do I only get 2 records in the cursor?
>
> If I make the cursor insensitive, it works as expected.
>
> Bug in 15.0.3?
>
> thanks


Luc Van der Veurst Posted on 2011-05-03 14:04:55.0Z
From: "Luc Van der Veurst" <dba_azvub@hotmail.com>
Newsgroups: sybase.public.ase.general
References: <4dbf789a.3c76.1681692777@sybase.com> <4dbfe0c6.4580.1681692777@sybase.com>
Subject: Re: Using top in a cursor misses the last record?
Lines: 115
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5931
X-RFC2646: Format=Flowed; Original
X-Forwarded: by - (DeleGate/5.8.7)
X-Forwarded: by - (DeleGate/5.8.7)
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4dc00b87$1@forums-1-dub>
Date: 3 May 2011 07:04:55 -0700
X-Trace: forums-1-dub 1304431495 10.22.241.152 (3 May 2011 07:04:55 -0700)
X-Original-Trace: 3 May 2011 07:04:55 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30152
Article PK: 72329

I had noticed the difference between a 12.5.4 ESD 8 and a 15.5 server using
isql.

So the bug must have been solved in 15.0.3/ESD#2 since the OP was using
15.0.3/ESD#1.

The problem isn't referenced explicitily (i.e. top + cursor) in the buglist
for that release, though.

Luc.

<Mark A. Parsons> wrote in message
news:4dbfe0c6.4580.1681692777@sybase.com...
> What happens when you run your test via the isql command
> line tool?
>
> On 15.0.3/ESD#2 I get the following output from the cursor
> loop when executed via isql:
>
> ==================
> A
> (1 row affected)
> B
> (1 row affected)
> C
> (0 rows affected)
> ==================
>
>
>
>> Hey all
>>
>> Witness:
>>
>> select @@version
>> go
>> create table #bugtest (a numeric(4,0), b varchar(10))
>> go
>> insert into #bugtest values (1,'A')
>> insert into #bugtest values (2,'B')
>> insert into #bugtest values (3,'C')
>> insert into #bugtest values (4,'D')
>> insert into #bugtest values (5,'E')
>> insert into #bugtest values (6,'F')
>> insert into #bugtest values (7,'G')
>> go
>> select top 3 * from #bugtest
>> go
>> declare c_a cursor for select top 3 a,b from #bugtest for
>> read only
>> go
>> begin
>>
>> declare @a numeric(4,0)
>> declare @b varchar(10)
>>
>> open c_a
>> fetch c_a into @a, @b
>> while @@sqlstatus = 0
>> begin
>> print @b
>> fetch c_a into @a, @b
>> end
>> close c_a
>> end
>> go
>> deallocate cursor c_a
>> go
>> drop table #bugtest
>> go
>>
>>
>> And the results:
>>
>> column1
>>
>> ----------------------------------------------------------
>> ----------------------------------------------------------
>> --------
>> Adaptive Server Enterprise/15.0.3/EBF 16549
>> ESD#1/P/x86_64/Enterprise
>> Linux/ase1503/2681/64-bit/FBO/Mon Mar 9 04:10:47 2009
>>
>> 1 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data:
>> 1/ms]
>>
>>
>>
>> a b
>> ---- ----
>> 1 A
>> 2 B
>> 3 C
>>
>> 3 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data:
>> 1/ms]
>>
>>
>> Warnings: --->
>> W (1): A
>> W (2): B
>> <---
>>
>>
>>
>> Why do I only get 2 records in the cursor?
>>
>> If I make the cursor insensitive, it works as expected.
>>
>> Bug in 15.0.3?
>>
>> thanks


Simon Scott Posted on 2011-05-04 00:56:57.0Z
Sender: 41f1.4dbfc4a4.1804289383@sybase.com
From: Simon Scott
Newsgroups: sybase.public.ase.general
Subject: Re: Using top in a cursor misses the last record?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4dc0a459.63b1.1681692777@sybase.com>
References: <4dc00b87$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 3 May 2011 17:56:57 -0700
X-Trace: forums-1-dub 1304470617 10.22.241.41 (3 May 2011 17:56:57 -0700)
X-Original-Trace: 3 May 2011 17:56:57 -0700, 10.22.241.41
Lines: 31
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30153
Article PK: 72332


> So the bug must have been solved in 15.0.3/ESD#2 since the
> OP was using 15.0.3/ESD#1.

Interestingly, I ran the same script on another server here,
and got the correct result. Then I ran it on the same server
as yesterday, and got the wrong result again.

Both return @@version Adaptive Server Enterprise/15.0.3/EBF
16549 ESD#1/P/x86_64/Enterprise
Linux/ase1503/2681/64-bit/FBO/Mon Mar 9 04:10:47 2009

So then I ran it on the "broken" server using sqsh:

A
(1 row affected)
B
(1 row affected)

and isql:

A
(1 row affected)
B
(1 row affected)



So it looks like it is specific to the installation, not the
version!


Luc Van der Veurst Posted on 2011-05-04 07:56:43.0Z
From: "Luc Van der Veurst" <dba_azvub@hotmail.com>
Newsgroups: sybase.public.ase.general
References: <4dc00b87$1@forums-1-dub> <4dc0a459.63b1.1681692777@sybase.com>
Subject: Re: Using top in a cursor misses the last record?
Lines: 25
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5931
X-RFC2646: Format=Flowed; Original
X-Forwarded: by - (DeleGate/5.8.7)
X-Forwarded: by - (DeleGate/5.8.7)
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4dc106bb$1@forums-1-dub>
Date: 4 May 2011 00:56:43 -0700
X-Trace: forums-1-dub 1304495803 10.22.241.152 (4 May 2011 00:56:43 -0700)
X-Original-Trace: 4 May 2011 00:56:43 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30154
Article PK: 72333


<Simon Scott> wrote in message news:4dc0a459.63b1.1681692777@sybase.com...
>
>> So the bug must have been solved in 15.0.3/ESD#2 since the
>> OP was using 15.0.3/ESD#1.
>
>
> Interestingly, I ran the same script on another server here,
> and got the correct result. Then I ran it on the same server
> as yesterday, and got the wrong result again.
>
> Both return @@version Adaptive Server Enterprise/15.0.3/EBF
> 16549 ESD#1/P/x86_64/Enterprise
> Linux/ase1503/2681/64-bit/FBO/Mon Mar 9 04:10:47 2009
>
> So it looks like it is specific to the installation, not the version!

Then I would look at the output of 'sp_configure' (or diff cfg files) and
'show switch' to find
the differences.

Luc.