JDBC SQLite driver that supports Insensitive cursor movement

[David Holland]

Is there any JDBC SQLite driver that supports Insensitive cursor movement?

Thanks

[Graeme Reid]

David,

I have made modifications to the JDBC SQLite library to add support for custom collations, which it what you need to get a case-insensitive cursor.
The case-insensitive comparison operator is implemented in Java, and only requires a single line of Java code.

I am happy to make the changes available to the community, and posted as much to this list last year.
I still haven't received a reply on how to submit changes, but I can send the necessary changes to you directly, if you are interested.

Regards,
Graeme

[David Holland]

Graeme,

Thanks for the reply. I am working on my PhD dissertation. I need a INSENSITIVE cursor to move around in the JDBC ResultSet. I need this functionality to efficiently estimate the size of the ResultSet  by using rs.last() and then back to the start with rs.first(). I don't know why SQLite does not have some driver implementing this. Will I need to update the driver? Where can I obtain source code for an open source SQLite driver?  Does this update in anyway degrade or limit other RS cursor functions?  

I am currently using Derby with:    conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

It would really help to just stay with SQLite.

Thanks

David Holland

--
You received this message because you are subscribed to the Google Groups "Xerial" group.
To unsubscribe from this group and stop receiving emails from it, send an email to xerial+un...@googlegroups.com.
To post to this group, send email to xer...@googlegroups.com.
Visit this group at https://groups.google.com/group/xerial.
For more options, visit https://groups.google.com/d/optout.

--

David Holland

512-300-8396

[Graeme Reid]

David,

The source code to the JDBC SQLite driver is available on GitHub. SQLite already includes support for custom collations, but the JDBC driver does not provide access to it. The changes I have made make this available.

However SQLite does not support bidirectional cursors, just FORWARD_ONLY, so there is no way to move backwards through the result set. But if you are only looking to determine the size of the result set, that information should be available in the meta data, without needing to use the cursors at all.

Regards,

Graeme

[David Holland]

Graeme,

Unfortunately, everything I have read requires going from first()  to last()  in the ResultSet.  A CachedRowSet provides the size of rows retrieved into the cache, but many large data sets will not fit into a CahcedRowSet and must be paged. I have looked thru the ResultSet meta data 'ResultSetMetaData' as well as 'DatabaseMetaData' that a connection object provides.  I can move FORWARD thru the RS an count the number of rows, but then I cannot move back to the beginning. I have been searching everywhere for a SQLite driver that will support a INSENSITIVE cursor. No luck so far.

Thanks

[Graeme Reid]

David,

Ah sorry, I misunderstood your original question. What you are looking for is a ResultSet of TYPE_SCROLL_INSENSITIVE, which is not supported by the JDBC SQLite driver. My understanding is that the main reason for this is that SQLite itself only supports forward only cursors.

Having said that, there should be other ways to get the size of a ResultSet. I know that with PostgreSQL you can retrieve the row count directly, but I forgot that you can't do that in SQLite. But at the very least you can use a temporary table to store your ResultSet then execute a SELECT COUNT(1) FROM <temporary table> query.

Regards,

Graeme

[David Holland]

Graeme,

Thanks for the reply. My queries are very intensive and use very large data sets. It's too expensive to store the results in a temporary table and re-execute the query using SELECT COUNT(*).  Also in distributing the RS to other distributed processors, being able to position and mark the rows allows much better performance.   There is SIZE in a CachedRowSet, which may be useful, but this is only the size of the rows fetched into the cache; not the entire count of rows in the RS.   

I was hoping that some SQLite driver might support the INSENSITIVE cursor. Maybe SQLite's design precludes using anything but FORWARD.

Regards