Re: streaming result sets: progress

Hello Barry,
What think You about next idea
I have study C interface and it is easy solve my problem without cursor.
JDBC load complete qyery result in method QueryExecutor.execute
I think that method QueryExecutor.execute work like now except for query
(select) - for select this method should do something like PQExec method
in C (this is compatible with ResultSet in JDBC specification)
When we call ResultSet.next (or like method) we should be initialize
fetchSize rows and load rows with method like C method PQgetvalue -
consecutive call ResultSet.next should scroll row window (length for window is fetchSize)
and if need call new fetchSize rows with method like PQgetvalue
'OutOfmemory' error for large table is not because Postgresql do so than
JDBC QueryExecutor.execute method load all rows.
If this work in C without cursor then no reason that work in Java
I have tried more queries with large table in C and it work fine without cursors.

regards
Haris Peco
On Monday 18 November 2002 05:54 pm, Barry Lind wrote:
> Haris,
>
> I understand your problem. But unless the database supports cursors
> that span transactions, I don't see any solution for you, other than to
> issue multiple sql statements to mimic cross transaction queries in your
> application.
>
> --Barry
>
> Haris Peco wrote:
> > On Monday 18 November 2002 05:14 pm, Barry Lind wrote:
> >>Nic,
> >>
> >>Here are my thoughts on this topic.
> >>
> >>1) Since the server doesn't support cursors across transactions, I don't
> >>think the driver should either. In fact in jdbc3 the DatabaseMetaData
> >>object has a supportsResultSetHoldability() method that explicitly lets
> >>the driver tell the application what is does/doesn't support in this
> >> area.
> >>
> >>I think running multiple sql statements to mimic this behavior is a very
> >>bad idea. Since the select statements will run at different times they
> >>will return different data (since they will pick up commited changes
> >>between runs), and if you don't include an order by the results are
> >>completely unpredictable. If someone wants this very unpredictable
> >>behavior they can issue the multiple statements themselves.
> >>
> >>2) I think the use of cursors should be optional. In fact since most
> >>queries don't need them since most queries return a small number of rows
> >>, I think the use of cursors needs to be turned on. I think there
> >>should be two ways to do this: the first is by setting the fetchSize()
> >>and the second would be a jdbc url parameter.
> >>
> >>3) I think the transaction characteristics of the current patch are just
> >>fine and conform to the jdbc specification. The code should
> >>automatically close the resultset when a commit occurs. One thing that
> >>will be confusing is that noncursor based result sets will work accross
> >>commits, but cursor based ones won't. But I think that is reasonable.
> >
> > My problem :
> > master-detail
> > I select one from many rows master with cursor (big table and only this
> > is possible) - In detail I do change and commit (or rollback)
> > My select is lost.
> > How can I do that ?
> >
> >>Nic Ferrier wrote:
> >>>Message-ID: <87fztyexea(dot)fsf(at)pooh-sticks-bridge(dot)tapsellferrier(dot)co(dot)uk>
> >>>Lines: 24
> >>>MIME-Version: 1.0
> >>>Content-Type: text/plain; charset=us-ascii
> >>>--text follows this line--
> >>>
> >>>Haris Peco <snpe(at)snpe(dot)co(dot)yu> writes:
> >>>>I have tried.
> >>>>DatabaseMetaData is fine, but ResultSet.[get|set]FetchSize don't
> >>>>work
> >>>
> >>>What error do you get?
> >>>
> >>>>Prepared command don't work, but my greatest problem are trasnaction
> >>>>I hope that cursor in 7.4 will be out of a transaction
> >>>
> >>>I don't think there's much I can do about the cursor problem.
> >>>
> >>>>Can Yoy yet another :
> >>>>set driver's flag btw jdbc:pgsql:...?cursor=yes
> >>>>for use cursor or old way
> >>>
> >>>What does everyone else think? Is a system doing a different query
> >>>each time worth looking into?
> >>>
> >>>
> >>>Nic
> >>>
> >>>
> >>>---------------------------(end of broadcast)---------------------------
> >>>TIP 5: Have you checked our extensive FAQ?
> >>>
> >>>http://www.postgresql.org/users-lounge/docs/faq.html
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 6: Have you searched our list archives?
> >>
> >>http://archives.postgresql.org