Thread: Scrolling/Updating Cursors
My apologies for posting what is almost certainly somewhat of a repeat question but I have searched and attempted everythingI can think of and cannot figure it out myself. The basic question is… Is it possible to get a scrollable cursor that, within a transaction, can insert/update/delete rowsand see those changes? Why you ask? Because I am implementing the JDBC 4.1 spec and it (optionally) provides this ability and I’d like to provideit if possible. I thought just using a scrollable cursor with the isolation-level set to repeatable-read would dothe trick but it still seems to see no changes (inserts/updates/deletes). Effectively I want roughly this sequence of events to work. CREATE TABLE test (val text); BEGIN; # Could include ISOLATION LEVEL REPEATABLE READ DECLARE c1 CURSOR FOR SELECT * FROM test; FETCH ABSOLUTE 1; # Returns No Row INSERT INTO test VALUES (‘1'); INSERT INTO test VALUES (‘2'); INSERT INTO test VALUES (‘3'); FETCH ABSOLUTE 1; # Returns “1” FETCH NEXT: # Returns “2" DELETE FROM test WHERE CURRENT OF c1; # Deletes “2” FETCH ABSOLUTE 1; # Returns “1” FETCH NEXT; # Returns “3” UPDATE test SET val=‘4' WHERE CURRENT OF c1; FETCH RELATIVE 0; # Returns “4” COMMIT; Thanks! -kw
On Nov 19, 2013, at 6:33 PM, Kevin Wooten <kdubb@me.com> wrote: > My apologies for posting what is almost certainly somewhat of a repeat question but I have searched and attempted everythingI can think of and cannot figure it out myself. > > The basic question is… Is it possible to get a scrollable cursor that, within a transaction, can insert/update/delete rowsand see those changes? > > Why you ask? Because I am implementing the JDBC 4.1 spec and it (optionally) provides this ability and I’d like to provideit if possible. I thought just using a scrollable cursor with the isolation-level set to repeatable-read would dothe trick but it still seems to see no changes (inserts/updates/deletes). > > Effectively I want roughly this sequence of events to work. > > CREATE TABLE test (val text); > > BEGIN; # Could include ISOLATION LEVEL REPEATABLE READ > > DECLARE c1 CURSOR FOR SELECT * FROM test; > > FETCH ABSOLUTE 1; # Returns No Row > > INSERT INTO test VALUES (‘1'); > INSERT INTO test VALUES (‘2'); > INSERT INTO test VALUES (‘3'); > > FETCH ABSOLUTE 1; # Returns “1” > FETCH NEXT: # Returns “2" > DELETE FROM test WHERE CURRENT OF c1; # Deletes “2” > > FETCH ABSOLUTE 1; # Returns “1” > FETCH NEXT; # Returns “3” > UPDATE test SET val=‘4' WHERE CURRENT OF c1; > FETCH RELATIVE 0; # Returns “4” > > COMMIT; > > Thanks! > > -kw > Should I take silence to mean this cannot be done? Or should I send to -hackers?
Why not both? I keep on searching even when I post up a question. On 11/22/2013 2:27 PM, Kevin Wooten wrote: > On Nov 19, 2013, at 6:33 PM, Kevin Wooten <kdubb@me.com> wrote: > >> My apologies for posting what is almost certainly somewhat of a repeat question but I have searched and attempted everythingI can think of and cannot figure it out myself. >> >> The basic question is… Is it possible to get a scrollable cursor that, within a transaction, can insert/update/deleterows and see those changes? >> >> Why you ask? Because I am implementing the JDBC 4.1 spec and it (optionally) provides this ability and I’d like to provideit if possible. I thought just using a scrollable cursor with the isolation-level set to repeatable-read would dothe trick but it still seems to see no changes (inserts/updates/deletes). >> >> Effectively I want roughly this sequence of events to work. >> >> CREATE TABLE test (val text); >> >> BEGIN; # Could include ISOLATION LEVEL REPEATABLE READ >> >> DECLARE c1 CURSOR FOR SELECT * FROM test; >> >> FETCH ABSOLUTE 1; # Returns No Row >> >> INSERT INTO test VALUES (‘1'); >> INSERT INTO test VALUES (‘2'); >> INSERT INTO test VALUES (‘3'); >> >> FETCH ABSOLUTE 1; # Returns “1” >> FETCH NEXT: # Returns “2" >> DELETE FROM test WHERE CURRENT OF c1; # Deletes “2” >> >> FETCH ABSOLUTE 1; # Returns “1” >> FETCH NEXT; # Returns “3” >> UPDATE test SET val=‘4' WHERE CURRENT OF c1; >> FETCH RELATIVE 0; # Returns “4” >> >> COMMIT; >> >> Thanks! >> >> -kw >> > > Should I take silence to mean this cannot be done? Or should I send to -hackers? > > >
On Nov 22, 2013, at 2:32 PM, John Meyer <johnmeyer@pueblocomputing.com> wrote: > Why not both? I keep on searching even when I post up a question. > Just attempting to be polite and not cross post to too much. I have pretty much exhausted my own search and now am relyingon the kindness and knowledge of others ;) > On 11/22/2013 2:27 PM, Kevin Wooten wrote: >> On Nov 19, 2013, at 6:33 PM, Kevin Wooten <kdubb@me.com> wrote: >> >>> My apologies for posting what is almost certainly somewhat of a repeat question but I have searched and attempted everythingI can think of and cannot figure it out myself. >>> >>> The basic question is… Is it possible to get a scrollable cursor that, within a transaction, can insert/update/deleterows and see those changes? >>> >>> Why you ask? Because I am implementing the JDBC 4.1 spec and it (optionally) provides this ability and I’d like to provideit if possible. I thought just using a scrollable cursor with the isolation-level set to repeatable-read would dothe trick but it still seems to see no changes (inserts/updates/deletes). >>> >>> Effectively I want roughly this sequence of events to work. >>> >>> CREATE TABLE test (val text); >>> >>> BEGIN; # Could include ISOLATION LEVEL REPEATABLE READ >>> >>> DECLARE c1 CURSOR FOR SELECT * FROM test; >>> >>> FETCH ABSOLUTE 1; # Returns No Row >>> >>> INSERT INTO test VALUES (‘1'); >>> INSERT INTO test VALUES (‘2'); >>> INSERT INTO test VALUES (‘3'); >>> >>> FETCH ABSOLUTE 1; # Returns “1” >>> FETCH NEXT: # Returns “2" >>> DELETE FROM test WHERE CURRENT OF c1; # Deletes “2” >>> >>> FETCH ABSOLUTE 1; # Returns “1” >>> FETCH NEXT; # Returns “3” >>> UPDATE test SET val=‘4' WHERE CURRENT OF c1; >>> FETCH RELATIVE 0; # Returns “4” >>> >>> COMMIT; >>> >>> Thanks! >>> >>> -kw >>> >> >> Should I take silence to mean this cannot be done? Or should I send to -hackers? >> >> >>