Re: Using Cursors in a Transaction Processing

mrussell_at_beeb.net (Michael Russell) wrote in message news:<c69419da.0202062355.478d94c6_at_posting.google.com>...
> JRStern_at_gte.net (JRStern) wrote in message news:<3c6072ea.29940902_at_news.gte.net>...
> > Well here's the thing -- probably 99% of the stuff you do with
> > relational databases should never involve a cursor at all. If you
> > find yourself using a cursor more than that, you are almost certainly
> > doing things the hard way, the slow way, and, dare I say it, the wrong
> > way. You don't want to emulate an old ISAM system function for
> > function using cursors in a relational database.
> >
> > Joshua Stern
> > JRStern_at_gte.net
>
> Thanks for alerting me, Joshua. At this stage all general "map of the
> universe" tips, advice and travellers' tales are very relevant.
> I've looked for more info on cursors and I'm getting a better picture
> .... they do have a bad, legacy-slanted reputation from a designer's
> viewpoint and seem to suffer from performance issues ....
>
> Michael Russell

On the other hand .....
We really are converting the underlying data-holding mechanism from ISAM to an RDBMS; we are not converting the application any more than "we have to". That means, the user-interface, business rules all stay the same. We'll change our data-access layer to cope with the new (RDBMS) data-source, but we'll not alter the code that calls the data-access layer if we can help it.

Why are we doing this? For several reasons: to obtain an "open" database so that clients can, at some point, use their own tools to access their own data; because we will eventually replace the old Cobol code by Java; because we want to get it 'web-enabled" to work alongside Workflow systems ....

However, back to cursors ..... and questions:

Since all/most commercial applications present data to users interactively in a row-by-row fashion, what reasonable (i.e. efficient/economic) alternatives are there to using cursors? Perhaps languages other than Cobol can use dynamically-sized arrays, arrays the size of which expand or contract to suit some external indication of population? If so, they could read a complete set and cope. Without predicting the size of array needed, how do you select a whole set? I assume that's the alternative to cursor/row-by-row retrieval? (I say a whole set, meaning that any sub-set could really turn out to be 100% of the rows, anyway)...

Granted, cursors may not be the perfect tool, but what are the alternatives that can deliver row-by-row....?

If I have to use cursors, what methods/ideas can I use to prevent each user having new cursors built for their potential sets, which may be 100% of the population? Can I prevent them being destroyed between user-sessions? Can I make them shared between users? Can I keep them & refresh them at intervals the application itself controls? Can I force them to stay "server-side" -- i.e. not constructed on the user's local system?

As you know, the (possibly only) good thing about the ISAM implementation in multi-user systems is that there is one, global set of (dynamic) indices, which are used by all users; there are consequently no large concerns (or penalties?)concerning the number of records(rows) the user wants to see/operate on.

Please enlighten me -- I'd not be surprised to be told there's something important I really should have thought of ....

Regards

Michael Russell Received on Tue Feb 12 2002 - 12:56:36 CET