[Q] Scrollable cursor with PostgresV2 / current Glorp version
franzl at fjkonrad.de
Tue Nov 14 22:34:18 UTC 2006
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"
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.
... 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...
Franz Josef Konrad
Yanni Chiu schrieb:
> 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:
> 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
> 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
More information about the Squeak-dev