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

Groups > comp.lang.java.programmer > #31546 > unrolled thread

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

Back to article view | Back to comp.lang.java.programmer

Contents

  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

#31546 — JDBC: scroll cursor "broken" after fetch absolute beyond end?

Given a scroll-cursor (as in: "ResultSet.TYPE_SCROLL_SENSITIVE"), my
application attempts to move the ResultSet to absolute position 100
just to see, if there are at least 100 rows. Later it calls rs.first()
on the ResultSet to reposition back to start for getting the rows.

If the actual number of result rows is only 10, then positioning with
absolute(100) returns false -- so far, so good.

After that, however, repositioning to row 1 also returns false!
This is what I don't understand.  There are definitely 10 rows.

Reading the docs, I'd have expected that fetching beyond the edge
would just leave the cursor one after last row, and ready to be again
repositioned to any other (valid) row.

PS: whether attempting an rs.absolute(100) is the most elegant approach
  to checking for "at least 100 rows" is not so much the question here,
  than whether it *should* work. I do not receive any exception,
  just boolean false return value.

PPS: this is with informix, but I'd also be interested about 
  experience with that scenario on any other DB ...

[toc] | [next] | [standalone]

#31548

On 10.04.15 13.07, Andreas Leitgeb wrote:
> Given a scroll-cursor (as in: "ResultSet.TYPE_SCROLL_SENSITIVE"), my
> application attempts to move the ResultSet to absolute position 100
> just to see, if there are at least 100 rows. Later it calls rs.first()
> on the ResultSet to reposition back to start for getting the rows.
>
> If the actual number of result rows is only 10, then positioning with
> absolute(100) returns false -- so far, so good.
>
> After that, however, repositioning to row 1 also returns false!
> This is what I don't understand.  There are definitely 10 rows.

Did you do some commit in between? This can destroy cursors.

If not, the JDBC driver might be buggy or has restrictions. You might 
want to create a bug report with a small test case.

Maybe you can use beforeFirst() as work around. It resets the cursor to 
its initial state.

> Reading the docs, I'd have expected that fetching beyond the edge
> would just leave the cursor one after last row, and ready to be again
> repositioned to any other (valid) row.

If you are talking about the Oracle docs I agree.

> PS: whether attempting an rs.absolute(100) is the most elegant approach
>    to checking for "at least 100 rows" is not so much the question here,

Indeed, it is more likely a very inefficient way.

> PPS: this is with informix, but I'd also be interested about
>    experience with that scenario on any other DB ...

No idea, I never used this pattern.


Marcel

[toc] | [prev] | [next] | [standalone]

#31555

Marcel Mueller <news.5.maazl@spamgourmet.org> wrote:
> On 10.04.15 13.07, Andreas Leitgeb wrote:
>> Given a scroll-cursor (as in: "ResultSet.TYPE_SCROLL_SENSITIVE"), my
>> application attempts to move the ResultSet to absolute position 100
>> just to see, if there are at least 100 rows. Later it calls rs.first()
>> on the ResultSet to reposition back to start for getting the rows.
>>
>> If the actual number of result rows is only 10, then positioning with
>> absolute(100) returns false -- so far, so good.
>>
>> After that, however, repositioning to row 1 also returns false!
>> This is what I don't understand.  There are definitely 10 rows.
>
> 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.)

> If not, the JDBC driver might be buggy or has restrictions. You might 
> want to create a bug report with a small test case.

Well, for now that's what I suspect, and it wouldn't be the first case
of non-jdbc-conforming behaviour in that particular driver, either. :-(
My hope for finding a workaround is still higher than that for getting
it fixed, even if I presented them an SSCCE.

> Maybe you can use beforeFirst() as work around. It resets the cursor to 
> its initial state.

Tried it, but unfortuately it didn't help.  Even after rc.beforeFirst(),
the rc.absolute(1) still returned false.
Btw.,  ( pos==1 ? rc.first() : rc.absolute(pos) ) also didn't help.

>> PS: whether attempting an rs.absolute(100) is the most elegant approach
>>    to checking for "at least 100 rows" is not so much the question here,
> Indeed, it is more likely a very inefficient way.

While I originally didn't want to focus on that, I've changed my mind:

Assuming that the number of results might just as well be >10000 and if
all I want to know is: "is it >= 100 ?", then just positioning it to that row 
didn't appear like "very inefficient" to me -- if only it worked as promised.

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...

>> PPS: this is with informix, but I'd also be interested about
>>    experience with that scenario on any other DB ...
> No idea, I never used this pattern.

Anyway, your answer is appreciated.

[toc] | [prev] | [next] | [standalone]

#31556

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       |

[toc] | [prev] | [next] | [standalone]

#31557

On 13.04.15 00.10, Andreas Leitgeb wrote:
>> Maybe you can use beforeFirst() as work around. It resets the cursor to
>> its initial state.
>
> Tried it, but unfortuately it didn't help.  Even after rc.beforeFirst(),
> the rc.absolute(1) still returned false.
> Btw.,  ( pos==1 ? rc.first() : rc.absolute(pos) ) also didn't help.

I thought more of beforeFirst() followed by the usual read access with 
next().


>>> PS: whether attempting an rs.absolute(100) is the most elegant approach
>>>     to checking for "at least 100 rows" is not so much the question here,
>> Indeed, it is more likely a very inefficient way.
>
> While I originally didn't want to focus on that, I've changed my mind:
>
> Assuming that the number of results might just as well be >10000 and if
> all I want to know is: "is it >= 100 ?", then just positioning it to that row
> didn't appear like "very inefficient" to me -- if only it worked as promised.

Well, the most efficient way is to read the first 100 rows if you need 
them anyway. If you do not need them, you would not concern about 
absolute(1).
Even if you only need the first 10 rows it is more straight forward to 
read them and then jump to row 100 to see whether there are more than 100.

All of this won't run into your problem and will be happy with a forward 
only cursor. A forward cursor will cause less locking or less snapshot 
memory respectively in case of optimistic locking.


> 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...

Indeed, bad idea.


Marcel

[toc] | [prev] | [next] | [standalone]

#32098

>> On 10.04.15 13.07, Andreas Leitgeb wrote:
>>> Given a scroll-cursor (as in: "ResultSet.TYPE_SCROLL_SENSITIVE"), my
>>> application attempts to move the ResultSet to absolute position 100
>>> just to see, if there are at least 100 rows. Later it calls rs.first()
>>> on the ResultSet to reposition back to start for getting the rows.
>>> If the actual number of result rows is only 10, then positioning with
>>> absolute(100) returns false -- so far, so good.
>>> After that, however, repositioning to row 1 also returns false!
>>> This is what I don't understand.  There are definitely 10 rows.

This topic had some time to rest, as it didn't seem to disrupt the
users' workflow all that much in practice...

Now, the error-report came back, with some extra observation: namely
that this bug hit only in very specific circumstances... much more
specific than what I expected from this far-beyond-end-thing.

It turned out, that code above the DB-layer actually had a bug,
and positioning far-beyond-end shouldn't really have happened
in the first place. My description in the OP of this thread was
my desperate attempt to rationalize a need for positioning past
the end, as I saw it happen.

So, while I still think that a jdbc-driver ought to handle beyond-end
absolute positioning gracefully, it just doesn't matter anymore for
the application.

Thanks for all who shared their thoughts on it.

[toc] | [prev] | [next] | [standalone]

#31558

On 4/10/2015 7:07 AM, Andreas Leitgeb wrote:
> Given a scroll-cursor (as in: "ResultSet.TYPE_SCROLL_SENSITIVE"), my
> application attempts to move the ResultSet to absolute position 100
> just to see, if there are at least 100 rows. Later it calls rs.first()
> on the ResultSet to reposition back to start for getting the rows.
>
> If the actual number of result rows is only 10, then positioning with
> absolute(100) returns false -- so far, so good.
>
> After that, however, repositioning to row 1 also returns false!
> This is what I don't understand.  There are definitely 10 rows.

So .first() returns false.

Out of curiosity what does .absolute(1) return?

> Reading the docs, I'd have expected that fetching beyond the edge
> would just leave the cursor one after last row, and ready to be again
> repositioned to any other (valid) row.

Yes.

> PS: whether attempting an rs.absolute(100) is the most elegant approach
>    to checking for "at least 100 rows" is not so much the question here,

I guess you know what people think about that topic ...

:-)

Arne

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.java.programmer

csiph-web