Re: Large queries; fetchsize, cursors and limit/offset

Does that happen with the JDBC driver that comes with PG 7.3.4 or do I need
a more recent version? Does it with JDK1.3.1? Is this behavior standard
for PG JDBC (that is, will it be supported going forward as "the way" to
handle cursors via JDBC?)? That certainly is a great way to implement
matters as far as I can tell.

Does anybody know if other JDBC drivers are going to do the same thing? One
critical aspect for our application is that I also have to make it work
under Oracle 8i (and soon 9i and beyond) right now, with the probability
that we'd have to support DB2/UDB and MSFT SQLServer over time.

Thanks for this tip, though.

David

----- Original Message -----
From: "Dave Cramer" <pg(at)fastcrypt(dot)com>
To: "David Wall" <d(dot)wall(at)computer(dot)org>
Cc: "Rod Taylor" <rbt(at)rbt(dot)ca>; <pgsql-jdbc(at)postgresql(dot)org>
Sent: Monday, November 03, 2003 4:01 PM
Subject: Re: [JDBC] Large queries; fetchsize, cursors and limit/offset

> The driver uses cursors now if you do two things
>
> 1) request must be made inside a transaction ( cursors for large result
> sets should be anyway)
> 2) call setFetchSize()
>
> Dave
>
>
>
> On Mon, 2003-11-03 at 18:49, David Wall wrote:
> > Does anybody have some sample code that shows how to declare a simple
cursor
> > and fetch it 100 rows at a time, for example? I'm curious how to
formulate
> > this. It sounds like I need to keep the same Connection object, which
is
> > good info to have. How do I issue the DECLARE CURSOR and FETCH (using
> > PreparedStatement.execute() with the FETCH being an executeQuery() so
that I
> > get a result set back)?
> >
> > >From what I gather, the SQL itself looks something like:
> >
> > DECLARE my_cursor CURSOR FOR SELECT x,y,z FROM abc_table WHERE x>4;
> >
> > FETCH 100 FROM my_cursor;
> >
> > CLOSE cursor;
> >
> > What do I call when I'm doing the DECLARE CURSOR, versus the FETCH
versus
> > the CLOSE commands?
> >
> > Does anybody know if this sort of code would then work in Oracle 8i if I
> > used a modified set of Oracle commands, i.e. something like:
> >
> > DECLARE CURSOR my_cursor FOR SELECT x,y,z FROM abc_table WHERE x>4;
> > END;
> >
> > FOR 100 FETCH my_cursor; ??? No "host variables" with JDBC so I'm not
sure
> > I can do this since the syntax implies an "INTO" clause for using host
> > variables.
> >
> > Thanks,
> > David
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> > message can get through to the mailing list cleanly
> >
> >