Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Updating through cursors Expand / Collapse
Author
Message
Posted Wednesday, July 23, 2008 8:40 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:40 PM
Points: 5,930, Visits: 8,180
Comments posted to this topic are about the item Updating through cursors


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #539819
Posted Thursday, July 24, 2008 12:26 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, June 16, 2014 4:03 AM
Points: 488, Visits: 403
I used the following extract from BOL ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/7b28942f-252b-4448-88bc-dedbe53b0a82.htm (Changing Rows with Positioned Operations - Performing Positioned Updates with APIs) to kinda-guess the right answer.
"After positioning in the cursor, execute an UPDATE or DELETE statement with a WHERE CURRENT OF clause referencing the name returned by SQLGetCursorName. But this method is not recommended. Instead, it is better to use the positioned update functions in the ODBC API."
Was it a fluke, or is it some indirect help from BOL? Or maybe direct help - cursors are a bit of a *gasp* grey area for me!
Post #539883
Posted Thursday, July 24, 2008 1:42 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:45 AM
Points: 2,826, Visits: 3,866
I find this question a little confusing. It should be clearly stated in the question that you do not need to see updates made to the underlying data. Because that is a requirement for the suggested answer.

Best Regards,
Chris Büttner
Post #539917
Posted Thursday, July 24, 2008 1:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:19 AM
Points: 5,018, Visits: 10,521
I agree that this question was a bit confusing. I would never have chosen the "right" answer just because this is not any way to update the cursor, but the underlying data without using the cursor...

I always try to avoid using cursors and almost always succeed, but when I use cursors I always use FAST_FORWARD.


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #539921
Posted Thursday, July 24, 2008 2:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:40 PM
Points: 5,930, Visits: 8,180
brewmanz (7/24/2008)
I used the following extract from BOL
(...)
Was it a fluke, or is it some indirect help from BOL? Or maybe direct help - cursors are a bit of a *gasp* grey area for me!


Hi Brewmanz,

That bit in Books Online is about client-side cursors, not about T-SQL cursors. When I submitted the question, it didn't occur to me to think about possible confusion with client-side cursors. That's the problem with beiing a "pure" T-SQL coder - one often tends to forget that the data is eventuallly served to and consumed by a client!



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #539932
Posted Thursday, July 24, 2008 2:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:40 PM
Points: 5,930, Visits: 8,180
Christian Buettner (7/24/2008)
I find this question a little confusing. It should be clearly stated in the question that you do not need to see updates made to the underlying data. Because that is a requirement for the suggested answer.


Hi Christian,

I will readily admit that it had never occured to me that anyone would want to use a cursor to update data and then go back over the same data and read it again! (I already have problems realizing that there are people that use cursors on a regular basis at all!!)

However, after reading your reply I decided to go back and test it for myself, for I have never read anything to support this requirement. Here is the test code I used:

create table test (a int primary key, b int)
insert into test (a, b) select 1, 1 union all select 2, 2
go
declare @a int, @b int;
declare c cursor scroll
for select a, b from test order by a
for update of b; -- Comment this line to test alternative
open c;
fetch first from c into @a, @b;
select @a, @b;
update test set b = 10
where current of c; -- Comment this line to test alternative
--where a = @a; -- Uncomment this line to test alternative
fetch next from c into @a, @b;
select @a, @b;
fetch first from c into @a, @b;
select @a, @b;
close c;
deallocate c;
go
drop table test;
go

As you'll see if you run this code, you get the changed value back both when using FOR UPDATE and WHERE CURRENT Of, and when not using FOR UPDATE and using WHERE a = @a. All variations of cursor options I tried either showed the same result, or resulted in error messages because of illegal option combinations.

I'd apppreciate it if you can post any code that does show that changes are only visible when using FOR UPDATE.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #539935
Posted Thursday, July 24, 2008 3:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:40 PM
Points: 5,930, Visits: 8,180
Gianluca Sartori (7/24/2008)
I agree that this question was a bit confusing. I would never have chosen the "right" answer just because this is not any way to update the cursor, but the underlying data without using the cursor...


Hi Gianluca,

I agree that the words "need to change the data in the cursor rows" (emphasis added) can be a little confusing. With hindsight, I should have formulated this as "need to change the data retrieved by the cursor" or something similar.

But the three answer options given all do the same thing: update data in the underlying table. So even if there does exist some way to update just the cursor without updating the underlying data (which I doubt, given that a cursor is in fact a positioning mechanism and not a data colection), it should have been clear from the answer options that the intention is to change the data in the underlying table.

I always try to avoid using cursors and almost always succeed, but when I use cursors I always use FAST_FORWARD.


You might wish to check out the question for July 8, which deals with FAST_FORWARD and other options for fast (readonly) cursors.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #539939
Posted Thursday, July 24, 2008 3:11 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:45 AM
Points: 2,826, Visits: 3,866
Hi Hugo,

This is a misunderstanding. What I meant to say was that when you open a static cursor, you have a point in time copy of the data as it was when you opened the cursor.
You will not see any updates made to the underlying data after that, since you are working with a copy of the data, not the stored data itself.



Best Regards,
Chris Büttner
Post #539941
Posted Thursday, July 24, 2008 3:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:19 AM
Points: 5,018, Visits: 10,521

Hugo Kornelis (7/24/2008)
[quote]Gianluca Sartori (7/24/2008)
I agree that this question was a bit confusing. I would never have chosen the "right" answer just because this is not any way to update the cursor, but the underlying data without using the cursor...


Hi Gianluca,

I agree that the words "need to change the data in the cursor rows" (emphasis added) can be a little confusing. With hindsight, I should have formulated this as "need to change the data retrieved by the cursor" or something similar.

But the three answer options given all do the same thing: update data in the underlying table. So even if there does exist some way to update just the cursor without updating the underlying data (which I doubt, given that a cursor is in fact a positioning mechanism and not a data colection), it should have been clear from the answer options that the intention is to change the data in the underlying table.


Hi Hugo, thanks for your reply!

I don't know how SQLServer handles internally the updating of the cursor using CURRENT OF, but I think it simply identifies the row to update in the underlying table from the position in cursor, without issuing additional statements.
I found myself working with an old VB6 application some years ago, using DAO to access data. DAO handles server-side cursors better than client-side cursors and I used to check for long running queries with profiler: updates to a recordset resulted in some sort of sp_cursor in profiler.
I don't know exactly what sqlserver does when you update with CURRENT OF, but I would expect something similar to this.


I always try to avoid using cursors and almost always succeed, but when I use cursors I always use FAST_FORWARD.


You might wish to check out the question for July 8, which deals with FAST_FORWARD and other options for fast (readonly) cursors.


I'm one of those who would have answered "it depends", if only the option was there.


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #539957
Posted Thursday, July 24, 2008 3:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:40 PM
Points: 5,930, Visits: 8,180
Christian Buettner (7/24/2008)
This is a misunderstanding. What I meant to say was that when you open a static cursor, you have a point in time copy of the data as it was when you opened the cursor.
You will not see any updates made to the underlying data after that, since you are working with a copy of the data, not the stored data itself.


Hi Christian,

That's true. The options STATIC and FOR UPDATE are mutually exclusive, though.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #539963
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse