0

I'm working on a project in Java, where, I have to make modifications in my SQLite Database. I connected to it and is working pretty fine, except for this weird error.

s.executeUpdate("INSERT INTO STUDENTS VALUES('S0',11)");
...
//many statements... including queries
...
String c2="INSERT INTO STUDENTS VALUES ('S1', 2)";
s.executeUpdate(c2);
s.executeUpdate("DROP TABLE STUDENTS");

The statements s.executeUpdate("INSERT INTO STUDENTS VALUES('S0',11)"); and s.executeUpdate(c2); run perfectly and insert rows into the database. But when it comes to the statement below, I'm getting the weird Database Locked error. When I changed the query to another, it also worked pretty fine. The error comes when it reaches the ending statement. More precisely, all the queries written above, i.e., the first statement of the code here work pretty fine.

Please help me to find the bug.

Greg Bacon
  • 134,834
  • 32
  • 188
  • 245
Rajesh
  • 79
  • 2
  • 3
  • 9
  • Please don't vote down, without reading my question. I know that many posts exist on Stack Overflow regarding this type, but my one is peculiar. It is showing me that exception only one time at the end, I also closed the Statement s and connections at last. – Rajesh Dec 06 '12 at 15:32
  • 1
    Have you started a transaction that you're not showing us? My guess is that you can't drop the table because the updates are locking it pending a commit. DDL statements like drop are not transactional. – Paolo Dec 06 '12 at 15:50
  • @Paolo: in SQLite, DDL statements also are transactional. – CL. Dec 06 '12 at 15:51
  • @Paolo: No, all the statements are like Inserting, querying and updating. That's all. – Rajesh Dec 06 '12 at 15:53
  • A `DROP TABLE` command does not return the number of affected records, so you should use `execute` instead of `executeUpdate`. – CL. Dec 06 '12 at 15:54
  • Thanks for the reply but nothing happened, even if changed to execute. : ( – Rajesh Dec 06 '12 at 15:57

1 Answers1

2

I guess that the "s" variable is a Statement. Try closing the resources after you execute:

PreparedStatement updateStatement = connection.prepareStatement("INSERT INTO STUDENTS VALUES ('S1', 2)");
        try {
            updateStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            updateStatement.close();
        }

Do this after every call to the database.

Sinisha Mihajlovski
  • 1,811
  • 1
  • 20
  • 34
  • It worked. Actually, I found the bug. The main bug is I called another method, which does the query on same database and forgot to close the statement and connection there. Thanks alot for clarification. – Rajesh Dec 06 '12 at 16:33