Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!feeder.erje.net!eu.feeder.erje.net!eternal-september.org!feeder.eternal-september.org!mx02.eternal-september.org!.POSTED!not-for-mail From: Martin Gregorie Newsgroups: comp.lang.java.programmer Subject: Re: JDBC: scroll cursor "broken" after fetch absolute beyond end? Date: Sun, 12 Apr 2015 23:17:19 +0000 (UTC) Organization: A noiseless patient Spider Lines: 58 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Injection-Date: Sun, 12 Apr 2015 23:17:19 +0000 (UTC) Injection-Info: mx02.eternal-september.org; posting-host="e5160e2be48f1ea550cf61512446f525"; logging-data="12152"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1+kUM26Zl40+1jYjKeJAtbFR3ux/U5qFWs=" User-Agent: Pan/0.139 (Sexual Chocolate; GIT bf56508 git://git.gnome.org/pan2) Cancel-Lock: sha1:dHvtojIpc6SmqKwTcQPUyyGFNDE= Xref: csiph.com comp.lang.java.programmer:31556 On Sun, 12 Apr 2015 22:10:08 +0000, Andreas Leitgeb wrote: > Marcel Mueller 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 |