Java - Database locked

Question

I am looking to populate my database with values from a csv file. But it returns SQLITE_BUSY: Database file is locked.

Here is my code

try {
    BufferedReader br=new BufferedReader(new FileReader("v.csv"));
    String line;

    while((line=br.readLine())!=null) {
        System.out.println(line);
        String[]value = line.split(",");
        System.out.println(value.length);

        String sql = "INSERT into main ([Ticket #], Status, Priority, Department, [Account Name]) "
                + "values ('"+value[0]+"','"+value[1]+"','"+value[2]+"','"+value[3]+"','"+value[4]+"')";

        System.out.println("test");

        PreparedStatement pst = null;
        try {
            pst = DatabaseConnection.ConnectDB().prepareStatement(sql);
            pst.executeUpdate();
        } finally {
            if(pst != null) {
                pst.close();
            }
        }

    }
    br.close();

} catch(Exception e) {
    JOptionPane.showMessageDialog(null, e);
}

I'm using different print statements to check where the error is, it seems it can't executeUpdate(); when the loop executes the second time. This is what I get in the console

Ticket,Status,Priority,Department,Account Name
5
test

Here is my DatabaseConnection just for your reference

public class DatabaseConnection {
    Connection conn = null;
    Statement stmt = null;

    public static Connection ConnectDB() {

        try {
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection("jdbc:sqlite:database.db");
            conn.setAutoCommit(true);
            return conn;
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);
            return null;
        }

    }
}

possible duplicate of [SQLITE\_BUSY The database file is locked (database is locked) in wicket](http://stackoverflow.com/questions/8559623/sqlite-busy-the-database-file-is-locked-database-is-locked-in-wicket) — Scary Wombat, Mar 12 '14 at 00:54
Sqlite allows only one writer to the whole database at a time and, unless you selected "WAL" journal mode, no reader while writing. Moreover unless you explicitly ask it to wait, it simply returns the SQLITE_BUSY status for any attempt to access the database while conflicting operation is running. — Scary Wombat, Mar 12 '14 at 00:55
Uh, well do you have more than one connection? If so - don't — Scary Wombat, Mar 12 '14 at 01:05
I actually don't. Right now, I've commented all the code that used the connection but the error remains. What could it be? See the discussion below. Its basically unable to excute the execute.Update(); - not even for the first time in the loop — satnam, Mar 12 '14 at 01:07
it is not read-only or something is it. I recently moved a derby db project to linux and forgot about file permissions - did not work initially. — Scary Wombat, Mar 12 '14 at 01:10
OMG! Can't believe this. It was something with the file. What a coincident. I just moved to Windows from Linux yesterday. lol — satnam, Mar 12 '14 at 01:17

score 2 · Accepted Answer · answered Mar 12 '14 at 00:35

You are connecting to your database each time you go through the loop.

Create your database connection outside your loop and consume it with in, so it doesnt getConnection on each iteration.

try
{
    BufferedReader br=new BufferedReader(new FileReader("v.csv"));
    String line;

    db = DatabaseConnection.ConnectDB();
    while((line=br.readLine())!=null){
        System.out.println(line);
        String[]value = line.split(",");
        System.out.println(value.length);

        String sql = "INSERT into main ([Ticket #], Status, Priority, Department, [Account Name]) "
         + "values ('"+value[0]+"','"+value[1]+"','"+value[2]+"','"+value[3]+"','"+value[4]+"')";

        System.out.println("test");

        PreparedStatement pst = null;
        try{   
          pst = db.prepareStatement(sql);
          pst.executeUpdate();
        }finally{
          if(pst != null) {
            pst.close();
          }
        }
    }
    br.close();
}
catch(Exception e)
{
    JOptionPane.showMessageDialog(null, e);
}

@Satnamxv63 what is the value of the `sql` variable on the second iteration? — crthompson, Mar 12 '14 at 00:49
it doesn't display. I replaced "test" with sql before PreparedStatement pst = null. And the query is printed only once. So I guess the loop doesn't execute the second time at all. — satnam, Mar 12 '14 at 00:54

score 1 · Answer 2 · answered Mar 12 '14 at 01:19

1

Check to make sure that your Database it is not read-only or something is it. If you move a db project to linux do not forgot about file permissions.

answered Mar 12 '14 at 01:19

Scary Wombat

44,617
6
35
64

Java - Database locked

2 Answers2