Re: streaming result sets: progress - Mailing list pgsql-jdbc

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.fsf@pooh-sticks-bridge.tapsellferrier.co.uk>
>>>Lines: 24
>>>MIME-Version: 1.0
>>>Content-Type: text/plain; charset=us-ascii
>>>--text follows this line--
>>>
>>>Haris Peco <snpe@snpe.co.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
>
>
>