Re: Suspending SELECTs

Alessandro Baretta <a.baretta@barettadeit.com> writes:
>I am aware that what I am dreaming of is already available through
>cursors, but in a web application, cursors are bad boys, and should be
>avoided. What I would like to be able to do is to plan a query and run
>the plan to retreive a limited number of rows as well as the
>executor's state. This way, the burden of maintaining the cursor "on
>hold", between activations of the web resource which uses it, is
>transferred from the DBMS to the web application server,

I think you're trying to do something at the wrong layer of your architecture.  This task normally goes in your
middlewarelayer, not your database layer. 

There are several technologies that allow you to keep persistent database sessions open (for example, mod_perl, mod_cgi
amongothers).  If you combine these with what's called "session affinity" (the ability of a load-balancing server to
routea particular user back to the same persistent session object every time), then you can create a middleware layer
thatdoes exactly what you need. 

Basically, you create a session object that holds all of the state (such as your cursor, and anything else you need to
maintainbetween requests), and send back a cookie to the client.  Each time the client reconnects, your server finds
theuser's session object using the cookie, and you're ready to go. 

The main trick is that you have to manage your session objects, primarily to flush the full state to the database, if
toomuch time elapses between requests, and then be able to re-create them on demand.  Enterprise Java Beans has a large
fractionof its design devoted to this sort of object management. 

There are solutions for this in just about every middleware technology, from Apache/perl to EJB to CORBA.  Search for
"sessionaffinity" and you should find a lot of information. 

Craig