SqlServer changes cursor to "read only"

[php newbie]

I am trying to write a cursor to update certain rows in a particular
order as follows: (I need the cursor version, not SQL, as the update
logic depends on the order of rows and some other conditions. I
removed the Order-By clause from the statement to simplify it; it
gives the same error message with or without it.)

DECLARE prod_cursor CURSOR
FORWARD_ONLY
KEYSET
FOR SELECT 1 FROM all_products WHERE p_qty = 0 FOR UPDATE

This gives the following error message: "FOR UPDATE cannot be
specified on a READ ONLY cursor."

I have tried a few different combinations of cursor types (like SCROLL
instead of FORWARD_ONLY) but they all give this error, although the
statement seems identical to what I have seen in the books and in
books online.

Any ideas on how to convert this into an updating cursor?

[Simon Hayes]

Re: SqlServer changes cursor to "read only"


"php newbie" <newtophp2000@y ahoo.com> wrote in message
news:124f428e.0 405282010.7ffed 5f5@posting.goo gle.com...[color=blue]
> I am trying to write a cursor to update certain rows in a particular
> order as follows: (I need the cursor version, not SQL, as the update
> logic depends on the order of rows and some other conditions. I
> removed the Order-By clause from the statement to simplify it; it
> gives the same error message with or without it.)
>
> DECLARE prod_cursor CURSOR
> FORWARD_ONLY
> KEYSET
> FOR SELECT 1 FROM all_products WHERE p_qty = 0 FOR UPDATE
>
> This gives the following error message: "FOR UPDATE cannot be
> specified on a READ ONLY cursor."
>
> I have tried a few different combinations of cursor types (like SCROLL
> instead of FORWARD_ONLY) but they all give this error, although the
> statement seems identical to what I have seen in the books and in
> books online.
>
> Any ideas on how to convert this into an updating cursor?[/color]

Assuming that your real SQL statement has "SELECT col1, col2, ..." and not
"SELECT 1", then one possibility is that the table all_products has no
unique index or primary key. If there is no unique index, then keyset
cursors are converted to read-only - see "Implicit Cursor Conversions" in
Books Online. That section also lists a number of other reasons why your
cursor may be converted to a static (read-only) one.

If this doesn't help, perhaps you could post the complete DDL for
all_products, including constraints and indexes.

Simon

[Erland Sommarskog]

Re: SqlServer changes cursor to &quot;read only&quot;

php newbie (newtophp2000@y ahoo.com) writes:[color=blue]
> I am trying to write a cursor to update certain rows in a particular
> order as follows: (I need the cursor version, not SQL, as the update
> logic depends on the order of rows and some other conditions.[/color]

That's is far from certain. Usually, if you are creative enough, you
can find more effective ways around it. I don't know about your case,
but another posting had a similar case, to which I made a suggestion.
Have a look at:

http://groups.google.com/groups?hl=sv&lr=&ie=UTF-8&selm=Xns94F6821D6ABYazorman%40127.0.0.1

Maybe this can get you started.
[color=blue]
> DECLARE prod_cursor CURSOR
> FORWARD_ONLY
> KEYSET
> FOR SELECT 1 FROM all_products WHERE p_qty = 0 FOR UPDATE
>
> This gives the following error message: "FOR UPDATE cannot be
> specified on a READ ONLY cursor."
>
> I have tried a few different combinations of cursor types (like SCROLL
> instead of FORWARD_ONLY) but they all give this error, although the
> statement seems identical to what I have seen in the books and in
> books online.[/color]

Don't use FOR UPDATE. You only need this when you use UPDATE WHERE
CURRENT OF, and you could just as well use a regular WHERE clause.
Myself, I never use WHERE CURRENT OF.

As for the cursor type, I recommend INSENSITIVE CURSOR.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp