I have several questions involving paging with Sybase Anywhere. I believe I've read all the posts concerning paging with Hibernate, but I haven't run across any that pertain to Sybase Anywhere specifically.

My first question concerns the SybaseAnywhereDialect with Sybase 9.0. A few examples I've coded seem to work fine, however once I attempted to implement a paging example using Query.setMaxResults() and Query.setFirstResult() I received an exception:



This resulted from the following calls with the Query class:




If the SybaseAnywhereDialect is attempting to use absolute with the TYPE_FORWARD_ONLY result set type, I wouldn't imagine this works
with ASA version 8 either. Is this correct?

My second question is, if it is concluded that this is a bug in Hibernate, is there another method for performing paging?


My third question is, since Sybase 9 has introduced the ability to select with "START AT", are there plans to create a SybaseAnywhere9Dialect that takes advantage of this? I'm assuming this is more efficient than retrieving a FORWARD_ONLY result set and attempting to advance to the
specified offset.

I'll probably have more questions after Gavin answers these :)

Derek Greer

There are no differences between the Sybase dialects considering setMaxResult/setFirstResults. I'd rather suppose this is a driver problem. Have you tried to set hibernate.jdbc.use_scrollable_resultset=false?

I haven't tried that yet, but I assumed it was already set to false for several reasons. One, I assumed that the developer of that Dialect was aware of the Sybase driver's problems with supporting scrollable cursors (i.e. it has to store the retrieved records in memory causing out of memory errors to occur for large result sets) and therefore would have set this to false. Second, since the exception I got indicates that the result set type is being set to TYPE_FORWARD_ONLY rather than TYPE_SCROLL_INSENSITIVE, I assumed this was already set to false. I'll give it a try anyway and post my results.

Concerning differences between the Sybase dialects, I didn't suggest that there were any differences. My question is whether there will be a new Dialect created to take advantage of the addition of "START AT' with ASA version 9.

Have you (or any other reader's of this post) successfully implemented paging using the setMaxResults() and setFirstResult() methods with Sybase 8 or 9? Of course I'm more interested in version 9 results, but if you have implemented it successfully in version 8 then that might indicate something is either incorrect with my settings/implementation or with the compatiability of the SybaseAnywhereDialect with version 9.

Derek

I tried setting hibernate.jdbc.use_scrollable_resultset to false and it now works. Of course, this leaves me with the question of how the Dialect was attempting to accomplish using a scrollable resultset if the result set was being set to TYPE_FORWARD_ONLY (which wouldn't have allowed this even if the code weren't attempting to use the absolute() method). Even though setting this to false causes it to work, the Sybase JConnect 5.x driver does support scrollable resultsets (just very inefficiently), therefore setting hibernate.jdbc.use_scrollable_resultset to true shouldn't cause exceptions to be thrown. Therefore, there still seems to be a bug.

Given these findings, I'll refine my first question. Since Hibernate throws exceptions when using the scrollable resultset feature of ASA version 9, does it have the same limitations with version 8?

These results seem to answer my second question, but I'm still interested in getting an answer to the third.


I would also like to find out exactly how this paging is being accomplished short of diving into the code myself. Is the dialect now selecting the query and manually advancing from the beginning to the given offset?

Thanks.

Derek

Is anyone out there? I know no one is under any obligation to answer these questions, but this is the only avenue I'm aware of right now to ask the Hibernate community questions such as these. A user's mailing list would probably be more benificial for getting questions answered, since more people are likely to read the posts.

Unless one of the developers can answer the questions concerning Sybase Anywhere 9, I guess I'll just have to keep checking back with Hibernate to see if a new Dialect is created to take advantage of the new Sybase features.

As a generic Sybase question, I am curious whether it is more efficient to use a select with START AT verses using a forward only result set and moving to the offset with resultSet.next() each time (as I suppose the SybaseAnywhereDialect is doing). If the new START AT does the same steps, but just offers the convenience of not having to do it yourself with JDBC then I guess there would be no need for a SybaseAnywhere9Dialect (at least for this new feature).

Derek[/b]