Development Tools Forum

Re: Refreshing updated row fetched by a scroll cur

Posted By: Art Kagel
Date: Friday, 2 July 2010, at 7:43 a.m.

In Response To: Refreshing updated row fetched by a scroll cursor (RAVI PASUPULETI)

That's because a SCROLL CURSOR actually creates a temp table with the query
results and returns data from the temp table which has not been updated.
The temp table is needed to be able to implement the FETCH FIRST, LAST, NEXT
and PREVIOUS features of a SCROLL CURSOR. But you don't need a SCROLL
CURSOR to implement this logic, especially since 7.31/9.30 implemented the
FIRST and SKIP options to the SELECT statement. Just a bit of programming.

If you want to see updated data on the display, I would fetch a block of
data from a non-scrolling CURSOR into an array and display rows from that
array instead. Then when the user updates the rows in the array, you will
have the updated data to redisplay as the user scrolls around. You can use
a sliding window of data where the array holds say 100 rows. As long as the
user is scrolling within those 100 rows you are good. If the user wants to
scroll outside of the array, say to the 101st row, move the 51st through
100th rows to the beginning of the array and fetch the next 50 from the
cursor to the end of the array. If the user later scrolls back past row 50
to row 49 or lower, slide rows 50-100 back up the array and close and reopen
the cursor to replace the first 50 rows in the array. You can use FIRST and
SKIP to manage the number and subset of rows in the select set that are
returned by the cursor. Your data will always be up-to-date because you
will either be displaying the modified row from your array or after
scrolling around a refetched updated version of the row from the database.
Just make sure that your SELECT statement has an ORDER BY clause so that the
returned result sets are consistently ordered whenever you close and reopen
the cursor. Remember that the SQL standard permits the optimizer to return
data in any order it finds it without an ORDER BY clause and the optimizer
can change the query plan, especially if there are replaceable parameters in
the query, between executions.

I've written many apps like this over the years and it is a very good
technique and very easy to implement if you set up the array management data
structures properly and modularize the code to slide and populate the
array. A colleague dubbed the technique "The Electric Slide" once after I'd
written one of the beasties when that dance was popular - way back. ;-)

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.

On Fri, Jul 2, 2010 at 5:24 AM, RAVI PASUPULETI <rpasupul@gmail.com> wrote:

> Hi,
> I opened a 'SCROLL CURSOR', fetching the rows one by one and displaying one
> at
> a time on a screen. When I update a row, save it, move to the next row and
> come back I would like to see the changes made. The fetch is not getting
> the
> updated row but the old information. How can I see the actual current db
> values on the screen ?
> Thank you,
> RKP
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--0016361644c9e4674f048a6616c8

Messages In This Thread

• Refreshing updated row fetched by a scroll cursor
  RAVI PASUPULETI -- Friday, 2 July 2010, at 5:24 a.m.
  • Re: Refreshing updated row fetched by a scroll cur
    Art Kagel -- Friday, 2 July 2010, at 7:43 a.m.
  • Re: Refreshing updated row fetched by a scroll cur
    Jonathan Leffler -- Wednesday, 7 July 2010, at 12:39 a.m.
    • Re: Refreshing updated row fetched by a scroll....
      Art Kagel -- Wednesday, 7 July 2010, at 12:56 p.m.

Development Tools Forum is maintained by Administrator with WebBBS 5.12.