Feature Request Redo
Art made the below Post on Jan 26, 2006. I would like to re-explore the
idea because we got completely sidetracked (some of it my fault) into
making it happen with Enterprise Replication.
So, the original simple and good idea got gilded and expanded until it
was no longer feasible. The basic idea is to have cursors stored on the
server side that allow access by more than one session. The cursors
don't even have to allow simultaneous access by more than one session.
The problem that needs to be solved as Madison Pruet exhorted us to
request by, is that many internet based applications have no state.
Furthermore, each request by a user may be handled by a different
application server. So client side cursors are of no use in this
architecture. What one does to work around this is to use "first" and
"skip" to simulate paging through a cursor for when a client needs to
page through data.
So, if you want to present 20 records at a time to a user then the
first request is
select first 20 skip 0 * from <table> where <where> order by <uniqueordering>;
The second is
select first 20 skip 20 * from <table> where <where> order by <uniqueordering>;
The problem is that you "pay" for the query each time. So, I want some
way to page through data like a cursor but allow more than one client
have access to it. I even wondered if rentrable stored procedures would
work.
Here is Arts request:
http://groups.google.com/group/comp.databases.informix/browse_frm/thread/1d0fe4e38f53f7a2/b5a50e5314f5f669?q=global+cursors&rnum=1#b5a50e5314f5f669
Several days ago I proposed the following as part of a reply. I got no
comments on it. Did everyone miss it? Or is it just dumb?
Explicit globally accessible cursors. It would make writing middleware
and
WEB services with context so much easier if there were a concept of a
globally accessible cursor. I implemented something like that here,
but
full implementation required us to complete the query and drain the
cursor
into a shared cache in order to fulfill the 'next/previous N rows'
requests.
We cannot use dedicated connections, our application servers are
forbidden
to connect directly to the database for many reasons I won't go into.
Further, a user's follow-up requests may come from a different copy of
the
application server (indeed it may come from a different machine on the
net)
and be handled by a different copy of the middleware server, similar to
a
WEB server environment. A shift to a multi-threaded middleware model
would
help somewhat, but historical global data accesses make multi-threaded
applications particularly difficult around here and not significantly
more
efficient than multi-tasking ones.
So, here's a proposed syntax:
DECLARE :cursor_id_var CURSOR FOR ....;
OPEN :cursor_id_var USING .... ASSIGN GLOBAL CURSOR TO :global_id_var;
FETCH :global_id_var INTO ...;
Essentially the new ASSIGN GLOBAL CURSOR clause would prompt IDS to
create a
globally accessible cursor structure and return the equivalent of a
cookie
that can be used to access it later instead of or in addition to the
local
cursor id.
At this point I can place global_id_var into shared memory where any
copy of
the middleware server can access it. To prevent multiple connections
from
accessing a global cursor simultaneously we use the shared connection
model.
The connection which currently owns the global cursor relinquishes it
with:
RELEASE :global_id_var;
To gain access to a global cursor a connection would:
ACQUIRE :global_id_var;
Closing either the global cursor in some connection, or the underlying
cursor in the originating connection would deallocate the global cursor
and
the underlying cursor.
Anyone else like this one?
Art S. Kagel