Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.java.programmer > #31556

Re: JDBC: scroll cursor "broken" after fetch absolute beyond end?

Show all headers | View raw

On Sun, 12 Apr 2015 22:10:08 +0000, Andreas Leitgeb wrote:

> Marcel Mueller <news.5.maazl@spamgourmet.org> wrote:
>> On 10.04.15 13.07, Andreas Leitgeb wrote:
>> Did you do some commit in between? This can destroy cursors.
> 
> Good point, but I can rule that out in my case at hand. (I
> single-stepped the application and there was no commit nor rollback
> between the two absolute cursor-positionings.)
>
Some RDBMS default to setting auto-commit ON, which will cause every SQL 
command that can update the database to commit after success and rollback 
on failure. Others default to autocommit OFF.

I've never used Informix so don't know what it's default is. Check its 
manual to find out, because there's nothing to prevent concurrent access 
from interfering with a cursor that's outside a commit unit. If Informix 
supports autocommit and defaults the having it ON, and there's the 
possibility of concurrent database access you need to:

- turn autocommit OFF for sessions that use cursors
- for each use of a cursor in that session
  - start a transaction
  - declare and execute the cursor
  - carry out the cursor-related operations
  - end the transaction by committing it on success or rolling it back 
    on failure

You should do this even if the cursor is doing read-only DB accesses 
because it will be setting locks and, if another transaction is updating 
the same rows, temporary copies of changed rows will be being made so 
your transaction is consistent. The final END TRANSACTION COMMIT/ROLLBACK 
is needed to release locks set by your transaction and allow the DBMS to 
apply any temporary copies that accumulated while your transaction was in 
existence. 

Bear in mind that a transaction involving a cursor can legitimately 
extend over several user interactions.
   
> Apparently, the only other way of getting that information is to
> position on last() and check the rs.getRow().  But this might mean that
> the DB has to search it all through...
>
That should be fast since, depending on implementation, its only handling 
references to or copies of rows held in the cursor's working storage.

If you want to check the cost of a query, "EXPLAIN SQL-statement" or its 
equivalent should help with most RDBMSes. Slow responses are generally 
the result of the DB schema not defining the index(es) needed by the 
query that fills the cursor. If this is the case its up to you and/or the 
DBA to decide whether the query use frequency (or the importance of its 
user) is sufficient make it worthwhile add these indexes to the schema.


-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |

Back to comp.lang.java.programmer | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

JDBC: scroll cursor "broken" after fetch absolute beyond end? Andreas Leitgeb <avl@auth.logic.tuwien.ac.at> - 2015-04-10 11:07 +0000
  Re: JDBC: scroll cursor "broken" after fetch absolute beyond end? Marcel Mueller <news.5.maazl@spamgourmet.org> - 2015-04-10 16:52 +0200
    Re: JDBC: scroll cursor "broken" after fetch absolute beyond end? Andreas Leitgeb <avl@auth.logic.tuwien.ac.at> - 2015-04-12 22:10 +0000
      Re: JDBC: scroll cursor "broken" after fetch absolute beyond end? Martin Gregorie <martin@address-in-sig.invalid> - 2015-04-12 23:17 +0000
      Re: JDBC: scroll cursor "broken" after fetch absolute beyond end? Marcel Mueller <news.5.maazl@spamgourmet.org> - 2015-04-13 01:34 +0200
      Re: JDBC: scroll cursor "broken" after fetch absolute beyond end? Andreas Leitgeb <avl@auth.logic.tuwien.ac.at> - 2015-07-20 22:26 +0000
  Re: JDBC: scroll cursor "broken" after fetch absolute beyond end? Arne Vajhøj <arne@vajhoej.dk> - 2015-04-12 21:51 -0400

csiph-web