[Q] Scrollable cursor with PostgresV2 / current Glorp version for Squeak

Hello,
I have questions in two areas. Because both are related to RDBMSs I've
put them in one mail.

1) Scrollable cursors
I've fiddled with scrollable cursors using the current PostgresV2 libary
for squeak. But I've found no code that implemented that feature. Is
everybody always loading the whole result set or did I oversee
something? Is there some high level code available that uses the cursor
handling of PostgreSQL?

2) Current Glorp version of squeak
 From reading the Glorp mailing list I've saw the VW Glorp version is
newer (somebody talked about loading a version 0.4.67) than the squeak
version. Does somebody know what has changed since the version that was
ported to squeak? Any major advantages added?
Btw, I've already asked the maintainer of the Glorp port (Radu) about
the effort og doing a port and he told me, it would be about one week.
Has anybody else experience with such a port?

Regards,
Franz Josef Konrad

Franz Josef wrote:
> 1) Scrollable cursors
> I've fiddled with scrollable cursors using the current PostgresV2 libary
> for squeak. But I've found no code that implemented that feature. Is
> everybody always loading the whole result set or did I oversee
> something? Is there some high level code available that uses the cursor
> handling of PostgreSQL?

I don't remember ever trying to use the CURSOR feature,
but from reading, it should just work. So, I just tried
it by finding an example at:
     http://www.postgresql.org/docs/8.0/interactive/sql-fetch.html
and modifying it slightly. In the code snippet below, "self"
refers to an already connected PGConnection I have in an
inspector. Then I ran doIt/inspectIt on the various lines.
The FETCH result was just what I expected - 5 more lines
of the result, each time I ran it.

self execute: 'BEGIN WORK'
self execute: 'DECLARE liahona SCROLL CURSOR FOR SELECT * FROM
public.squeak_method'
self execute: 'FETCH FORWARD 5 FROM liahona'
self execute: 'FETCH PRIOR FROM liahona'
self execute: 'CLOSE liahona'
self execute: 'COMMIT WORK'

If that's not the feature you're looking for,
then please explain further. Also, you might
want to look at this thread from the Seaside
list:
 
http://lists.squeakfoundation.org/pipermail/seaside/2006-August/008518.html

Thanks for answering. I've already tried nearly the same you you did
before I've wrote my question. I repeat it because there is one new
information about getting the number of rows for a cursor:

conn := PGConnection new startup connection.
"let's make a test table'"
conn execute: 'create table t3 (c1 int primary key, c2 char(30)) '.

"fill in 50 rows"
 | iS sql |
1 to: 50 do: [ :i | iS := i printString.
    sql := 'insert into t3 values (', iS, ', ''spalte 2 wert ', iS, ''')'.
    conn execute: sql.
    ].
"begin a transaction (necessary for working with a cursor)"
conn execute: 'begin'.
"open the cursor"
conn execute: 'DECLARE cur1 SCROLL CURSOR FOR SELECT * FROM t3'.
"try to move a very high number to get the number of rows"
conn execute: 'MOVE 999999 in cur1'.
"postgresql moves the cursor to the last row and tells us the number. We
now know that we have 50 tuples in the result set)"
conn result resultSets first completedResponse value
"now move around as you like and fetch as you like"
conn execute: 'MOVE absolute 10 in cur1'.
conn execute: 'FETCH forward 10 FROM cur1'.
conn result resultSets first rows
"close the cursor"
conn execute: 'end'.
"clear the table"
conn execute: 'drop table t3 '.
"close the connection"
conn terminate.

That was what I've tried out. And I wanted to know if there is already
some code that hides all that low level stuff including error handling
etc and doing something like this:

scrollCursor := connection executeAsScrollCursor: 'select * from t3'.
srollCrursor bufferSize: 10.
scrollCursor firstResultSet.
scrollCursor nextResultSet.
... and so on...

I hope my intention is clear now. And because I ideally don't want to
care about SQL I've asked about  the Glorp port :-)

I'm learning seaside at the moment and IMO such a scrollable cursor
would fit very nice to a batched list. Above all when you have to deal
with browsing large data sets.
Background information: The last time I've did something in Smalltalk
was 10 years ago. I used a lib called Openlink and as far as I remember
there was support for scrollable cursors. Anyway the current PostgresV2
interface could be easily enhanced with scrollable cursors. If I will
use PostgeSQL for persistency I would try...

Regards,
Franz Josef Konrad

Yanni Chiu schrieb:

Franz Josef wrote:
It's clear. I've not seen any code that already does it.
In the message I linked to in the previous response,
the Glorp author says:

    "Glorp doesn't do anything in terms of actually declaring named
    cursors and issuing fetch statements."

 > Anyway the current PostgresV2 interface could be easily enhanced with
 > scrollable cursors. If I will use PostgeSQL for persistency I would
 > try...

It would be a nice framework to have available. I'm not sure
it should be part of the postgres interface though, since it
ought to be useable with any RDB that supports cursors.

I second this.  Is there a generic interface for RDB's in squeak?  If not,
that would be a good project and cursers could be supported in the back end
DB's that support them, and maybe later they could be simulated in the DB's
that don't. This is how I have seen it done in other DB interfaces from a
few other languages.

_________________________________________________________________
All-in-one security and maintenance for your PC.  Get a free 90-day trial!
http://clk.atdmt.com/MSN/go/msnnkwlo0050000002msn/direct/01/?href=http://clk.atdmt.com/MSN/go/msnnkwlo0050000001msn/direct/01/?href=http://www.windowsonecare.com/?sc_cid=msn_hotmail