Re: Large queries; fetchsize, cursors and limit/offset - Mailing list pgsql-jdbc

This is the behaviour of the current CVS driver, which will be released
with 7.4

It will work with a 7.3 db and will work with jdk 1.3.1


As far as whether other drivers do it this way I have no idea. The spec
is vague in this regard. setFetchSize is just a hint to the driver, it
can implement it any way it sees fit.

I would expect Oracle to behave this way, but have no idea if it
actually does.

Dave

On Mon, 2003-11-03 at 20:39, David Wall wrote:
> 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@fastcrypt.com>
> To: "David Wall" <d.wall@computer.org>
> Cc: "Rod Taylor" <rbt@rbt.ca>; <pgsql-jdbc@postgresql.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@postgresql.org so that your
> > >       message can get through to the mailing list cleanly
> > >
> > >
>
>