Java and SQLite problem

Question

I wrote a java program that should fill a sqlite database with some rows generated by an algorithm. So I access the db and I insert my rows... It goes for some seconds but:

1.30168691E9s 100 --> '0 1 2 5 8', 0, 0, 0
3.163s 200 --> '0 1 2 7 17', 0, 0, 0
3.158s 300 --> '0 1 2 9 30', 0, 0, 0
Exception in thread "main" java.sql.SQLException: unable to open database file
at org.sqlite.DB.execute(DB.java:275)
at org.sqlite.DB.executeUpdate(DB.java:281)
at org.sqlite.Stmt.executeUpdate(Stmt.java:103)
at grid0.GridFill.fillTable(GridFill.java:26)
at grid0.GridFill.writeCombs(GridFill.java:54)
at grid0.Main.main(Main.java:15)
Java Result: 1

the program goes for the first 300 rows, but then crashes... and I can't understand why. I need some help...

Thank you...

public void fillTable( String hand) throws Exception
{
String data = "'" + hand + "', 0, 0, 0";
if(count%100 == 0)
{
System.out.println((System.currentTimeMillis()-time)/(float)1000 + "s " + count +" --> " + data);
time = System.currentTimeMillis();
}
stat.executeUpdate("insert into Hands (Hand, Lock, Done, SubstitutionNumber) VALUES (" + data + ")");
}

Hmmmm...some common culprits: Is this threaded, and if so, are you positive that you do not access the database from multiple threads at the same time? (Bear in mind that the GUI commonly runs in a different thread from other worker threads) Are you opening the database every time, and if so, are you closing it before trying to open it again? Just some things I've run into. — riwalk
– riwalk, Commented Apr 1, 2011 at 20:02
What OS is this? Is it possible another program is touching the file simultaneously? Could you be out of disk space on the partition? — Matthew Flaschen
– Matthew Flaschen, Commented Apr 1, 2011 at 20:02
Its a single-threaded program, I'm on Windows Seven 64, I'm using the zentus jdbc driver and I have 65GB on the HD... — StefanoS
– StefanoS, Commented Apr 1, 2011 at 20:11

Alex Unger · Accepted Answer · 2011-04-07 19:13:28Z

First off I've had much better luck using the sqlite jdbc driver from xerial.org it can be found here: http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC Xerial seems to update their drive much more often than zentus.

When working with jdbc it is best to use PreparedStatements. Prepared Statements will allow you do things like execute a batch of inserts. Instead of executing multiple executeUpdate calls you can do them all at once with a batch. See the following links for examples of Batches and PreparedStatements: http://download.oracle.com/javase/1.3/docs/guide/jdbc/spec2/jdbc2.1.frame6.html http://download.oracle.com/javase/1.4.2/docs/guide/jdbc/getstart/preparedstatement.html

The basic code example you'll need is:

PreparedStatement stmt = con.prepareStatement(
    "INSERT INTO employees('salary', 'name') VALUES (?, ?)");

stmt.setInt(1, 2000);
stmt.setString(2, "Kelly Kaufmann");
stmt.addBatch();

stmt.setInt(1, 3000);
stmt.setString(2, "Bill Barnes");
stmt.addBatch();

// submit the batch for execution
stmt.executeBatch();

setInt(index, value) allows you to replace the '?' in the PreparedStatement with a value. With the batch and the preparedstatement you can do this multiple times and execute them all at once at the end when you call executeBatch().

Also as stated by the commenters make sure to close the connection to the DB once you're done inserting. Also you'll want to close any Result Sets that might be opened by selecting from the DB.

I hope this helps.

-Alex

Simply switching to xerial jdbc driver works for me. Thanks!

Collectives™ on Stack Overflow

Java and SQLite problem

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related