Dynamic Cursor Problem

[stanky23]

I'm having a bit of a problem using a dynamic cursor where I update the underlying data but the cursor does not show the update on the second fetch. Although books can be incorrect the following sysntax is just as it is shown in the book I have:

If original value of flag is 'A'

Declare @date smalldatetime
Declare @flag varchar(100)
DECLARE @test varchar(100)

DECLARE ItemCursor CURSOR GLOBAL SCROLL DYNAMIC FOR
SELECT date,flag FROM itemtemp ORDER BY Item,Date

OPEN ItemCursor

FETCH LAST FROM ItemCursor INTO @date,@flag

PRINT 'Date = ' + CONVERT(varchar(100),@date)
PRINT 'Oos_flag = ' + CONVERT(varchar(100),@flag)

UPDATE itemtemp set flag = 'X' Where date = '9/24/2002'

FETCH LAST FROM ItemCursor INTO @date,@flag

PRINT 'Date = ' + CONVERT(varchar(100),@date)
PRINT 'Oos_flag = ' + CONVERT(varchar(100),@oos_flag)


This code works if I close and re-open the cursor before the second fetch but the book I have does not show this. It is my understanding that the cursor will be rebuilt on each fetch. I'm baffled any help would be appreciated...

 

[sunila7]

Hi,

Try this .......


Declare @date smalldatetime
Declare @flag varchar(100)
DECLARE @test varchar(100)

DECLARE ItemCursor CURSOR GLOBAL SCROLL DYNAMIC FOR
SELECT date,flag FROM itemtemp ORDER BY Item,Date

OPEN ItemCursor

FETCH LAST FROM ItemCursor INTO @date,@flag
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Date = ' + CONVERT(varchar(100),@date)
PRINT 'Oos_flag = ' + CONVERT(varchar(100),@flag)

UPDATE itemtemp set flag = 'X' Where date = '9/24/2002'

PRINT 'Date = ' + CONVERT(varchar(100),@date)
PRINT 'Oos_flag = ' + CONVERT(varchar(100),@oos_flag)
FETCH LAST FROM ItemCursor INTO @date,@flag
END

Sunil

 

[stanky23]

Adding the Fetch_Status = 0 has no effect...Update is still not shown in the cursor...Were you thinking that the second fetch was failing?

 

[sunila7]

Hi,

I have a question

SELECT date,flag FROM itemtemp ORDER BY Item,Date

how many rows does this statement return...

Sunil

 

[sunila7]

Hi,

I just noticed u have a Fetch Last Statement in the Cursor.... what exactly r u tryong to do..... r u just trying to update the table for 1st and last row of the select statement.....



Sunil

 

[stanky23]

I really just simplified this code to explain my point but in this code. Let's say there are 10 records and the last record has the date 9/24/2002, and it is the only record with date 9/24/2002. This is why I'm using fetch last. We could assume that the result of the select is one record, which will have it's flag updated, this update will not be reflected in the cursor after the second fetch. I could see where some of the code could be confusing...

 

[stanky23]

O.K. Found the problem...The problem is that using an ORDER BY in the select statement causes an implicit conversion of the cursor from Dynamic to Static...

Check out this link...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_66sz.asp

Thanks for your help...

 

[sunila7]

Hi,

Found somethin more on a cursor becoming implicitly static....

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q158773

it seems that if a cursor will becoming implicitly static if it does not reference any tables with at least one unique index or primary key constraint

That is new to me

Sunil