Re: How do I use the backend APIs

Chad wrote:
> Thanks Martijn/Alban,
>
> This look interesting. I'll make some time to try this problem out
> using your approach.
> I have a few questions like:
> -Could I skip the FETCH FORWARD and go straight to the FETCH BACKWARD
> i.e. declare cursor to be at "Mal" and go backwards from there or is
> the cursor limited to going backward only as far as "Mal"?
> -Does the DB avoid transferring the data until the FETCH command?

Indeed it does.

> -When rows change in between opening the cursor and fetching the
> changed rows, will the FETCH retrieve the new data or is a snapshot
> taken when the cursor is declared ?
> -What about concurrency? If a cursor is kept open while other
> transactions change the same table or does it cause those writer
> transactions to block? Perhaps this is configurable.

Transactions are isolated. If data is changed in other transactions,
they don't influence the data in the transaction you're currently
looking at. I assume the same goes for cursors, it's still a query
after all.

I don't know what happens if you change the data you're looping over in
a way that the result set changes, but a test case can't be too hard to
think up. I'm talking about something like this:

OPEN cur FOR SELECT val FROM values WHERE val BETWEEN 1 AND 10 ORDER BY val;
LOOP
    FETCH cur INTO record;

    -- This reverses the order in which the record would be fetched
    UPDATE values SET val = 11 - val WHERE val = record.val;
END LOOP;

Makes me kind of curious what happens... Would it only get to halfway
the values 1..10 and then go backwards again?

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
    7500 AK Enschede

// Integrate Your World //