Cannot update cursor but why ?

jll63 · #1 (**permalink**) 03-21-11, 12:23

Hello,

My Sybase is: Sybase IQ/15.1.0.5034

It's my first time using cursors and I'm having trouble. I have reduced it to:

Code:

create table testtable (id integer)
insert into testtable (id) values (1)
insert into testtable (id) values (2)
declare testcur cursor for select * from testtable  for update
open testcur
fetch testcur
update testtable set id = 33 where current of testcur
close testcur
select * from testtable

When I run this, I get a:

Code:

Error: SQL Anywhere Error -633: Update operation attempted on a read-only cursor
SQLState:  42W30
ErrorCode: 7732

I thought I had followed the syntax faithfully so what's happening ?

Thanks,
Jean-Louis

trvishi · #2 (**permalink**) 03-23-11, 11:10

We have IQ 12.6 and the SQL you attached works perfectly fine for me.

jll63 · #3 (**permalink**) 03-23-11, 11:27

Quote:

Originally Posted by trvishi

We have IQ 12.6 and the SQL you attached works perfectly fine for me.

Thanks...

Something seems very wrong with my version (or installation?), I have problems with read-only cursors too, when I mix fetches with updates on another table :-(

trvishi · #4 (**permalink**) 03-23-11, 12:48

Can you paste the output of

SELECT connection_property ('CHAINED');

jll63 · #5 (**permalink**) 03-24-11, 04:36

Quote:

Originally Posted by trvishi

Can you paste the output of

SELECT connection_property ('CHAINED');

It's set to Off. Indeed when I set it to On (SquirrelSQL!Session!Transaction!Toggle Autocommit) my example works.

Thanks for pointing this out :-)

I googled around and found no clear statement that autocommit and updatable cursors don't mix...hmmm...also I thought this would work with Chained=Off:

Code:

begin transaction
declare testcur cursor for select * from testtable  for update
open testcur
fetch testcur
update testtable set id = 33 where current of testcur
fetch testcur
update testtable set id = 33 where current of testcur
close testcur
commit transaction

...but I get the same message:

Code:

Error: SQL Anywhere Error -633: Update operation attempted on a read-only cursor
SQLState:  42W30
ErrorCode: 7732

J-L

trvishi · #6 (**permalink**) 03-24-11, 05:46

No problem.

By default the chaining is ON when you build IQ atleast on the lower versions I know. So, I guess you need to find out why this was set to off and by who in your team...

And decide accordingly.

Another option is to use begin tran/end tran around the cursor.

Or set chaining on the session level.

Internet Services

Web Development

Digital Marketing

Consumer Tech