<< Back to previous view

[JDBC-26] Add sqlite3 support Created: 13/Feb/12  Updated: 23/Apr/12  Resolved: 23/Apr/12

Status: Resolved
Project: java.jdbc
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Enhancement Priority: Minor
Reporter: Nelson Morris Assignee: Sean Corfield
Resolution: Completed Votes: 0
Labels: None

Attachments: Text File mssql-error.log     Text File sqlite3-support.patch     Text File sqlite_transaction_test.patch    
Patch: Code and Test

 Description   

sqlite3 has some problems:

1) PreparedStatements.addBatch does not do anything without some parameters, so executeBatch doesn't do anything.
2) Transaction support appears to require closing the ResultSet object from generatedKeys.

I've only tested the included patch with the default test dbs and sqlite3.

 Comments   
Comment by Sean Corfield [ 14/Feb/12 1:01 AM ]

This will break things - see JDBC-16 which required that .addBatch be called when there are no param-groups. I'm not going to reject this out of hand but patches for new database support need testing against MySQL, PostgreSQL and MS SQL Server.

Comment by Nelson Morris [ 14/Feb/12 1:48 PM ]

Test suite runs correctly for TEST_DBS=mysql,postgres,derby,hsqldb,sqlite3 and TEST_DBS=mysql-str,postgres-str. This just leaves MS SQL server, which I do not have the ability to run.

Regarding JDBC-16, when I revert to just using .executeBatch for the no param-groups case I can see the errors produced in the test-suite. Using .executeUpdate for the no params-group case continues to fix these errors (note MS SQL server untested). I do not see any other information in the ticket that would explain why using .executeUpdate instead of .addBatch/.executeBatch for the no params-group case would break JDBC-16. Is there a reason I am missing?

Comment by Nelson Morris [ 17/Feb/12 10:05 PM ]

I ran the test suite against an ec2 instance with MS SQL server 2008 R2. Current test suite on master fails with attached mssql-error.log. Using patch produces the same output.

Please let me know if there is anything that you would like changed.

Comment by Sean Corfield [ 17/Feb/12 10:14 PM ]

Thanx Nelson. I've been a bit busy with work this week but I'll try to look at this at the weekend. I appreciate your thoroughness on testing this!

Comment by Sean Corfield [ 29/Feb/12 11:33 PM ]

Integrated parts of the patch. SQLite 3 is now supported. Thanx!

Comment by Nelson Morris [ 01/Mar/12 12:57 AM ]

I still get the transaction issue (part 2 from original description) when clojure.java.jdbc tries to return the generated keys, in a transaction, using sqlite. https://gist.github.com/1947746

I believe this is because the ResultSet from .getGeneratedKeys never gets .close called, so sqlite keeps the db locked and can't rollback. Would you like me to make a new issue?

Comment by Sean Corfield [ 01/Mar/12 1:03 AM ]

The tests pass locally (Mac OS X) and on the Clojure build server (Linux) but you're right that I didn't include the close call on the record set.

I didn't include your additional test which I'm guessing is the key to highlighting the errant behavior for SQLite?

(I couldn't actually apply your patch because I'd spent some time getting all the tests passing for MS SQL Server with both the Microsoft and jTDS drivers)

Comment by Nelson Morris [ 01/Mar/12 1:19 AM ]

Attached patch that fails with `mvn test`.

Comment by Sean Corfield [ 23/Apr/12 3:35 AM ]

I believe this was fixed in 0.1.3 (at the end of February). As far as I can tell, all of the patch components are applied. Specifically, your transaction test is included in the test suite and passes.

Generated at Wed Jun 19 01:10:21 CDT 2013 using JIRA 4.4#649-r158309.