Database file is locked (SQL Exception in Java)

Question

In my code, whenever I call the following function, the database Emitter can be reached. The function basically extracts data from the databases and stores it in variables.

public void fillEmitterDataLists()
{
    EmitterStructVo emitter = new EmitterStructVo();

    try {
        stmt = dbConn.createStatement();

        rs = stmt.executeQuery( "SELECT * FROM Emitter;" );
        while ( rs.next() ) {
            emitter = new EmitterStructVo();
            model.emitterList.add(rs.getString("E_ID"));

            String pri = rs.getString("PRI_ID");
            String pw = rs.getString("PW_ID");
            String rf = rs.getString("RF_ID");
            String reps = rs.getString("Reps");
            String interval = rs.getString("Interval");

            Statement stmt1;
            stmt1 = dbConn.createStatement();

            ResultSet rs1 = stmt1.executeQuery("SELECT * FROM PRI where PRI_ID = '" + pri + "';");
            model.priList.add(rs1.getString("PRI_Values"));

            int priType = Integer.parseInt(rs1.getString("PRI_TYPE"));

            rs1 = stmt1.executeQuery("SELECT * FROM PW where PW_ID = '" + pw + "';");
            model.pwList.add(rs1.getString("PW_Values"));

            rs1 = stmt1.executeQuery("SELECT * FROM RF where RF_ID = '" + rf + "';");
            model.rfList.add(rs1.getString("RF_Values"));
            stmt1.close();
            rs1.close();

        }
        stmt.close();
        rs.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

But if I execute the following code after calling the above function, it gives the error that the Emitter database file is locked. Whereas, if I don't call the above function before, the following code works without a glitch. So, clearly, the problem is with the fillEmitterDataLists() function.

Class.forName("org.sqlite.JDBC");
Connection dbConn = DriverManager.getConnection("jdbc:sqlite:FssDb.db");
Statement stmt = dbConn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM Emitter where E_ID = '" + name + "';");

Now I have closed all of the statements and result sets as suggested in the similar questions on stack overflow so I have no idea why it is still producing the exception.

Please help?

FYI : You are closing your `Statement` and `ResultSet` in the wrong way. Closing a `Statement` should (not all JDBC do it) close the `ResultSet` with it. So if `Sqlite` do it properly, closing `stmt1` will close `rs1`. The next statement would produce an error (I think). Note that this should be done propertly in a finally statement (or using `try-with-resource`) — AxelH, Oct 16 '17 at 12:32
Nopes, it doesn't produce an error. I am closing the result set after the statement at multiple places and it doesn't produce an error anywhere. I also removed the line closing the resultSet and that didn't help as well. — KulaDamian, Oct 16 '17 at 12:34
I never said this was the reason ... but if you want an answer, don't try to open a _file based database_ twice. If you don't close the `dbConn` from the method above and try to get a new connection with the second snippet, this will not be possible, only one `Connection` is possible at the same time. It is good to use a `Singleton` on an SQlite DB to prevent any problem — AxelH, Oct 16 '17 at 12:37
Oh thank you! This was the answer. Apparently, I have been creating multiple connections at a single time which were even working without an error. But this time, it refused to work. — KulaDamian, Oct 16 '17 at 12:50
This is unlikely but I never tried to open two connection simultaneously with SQLite. You could share the same reference of the connection. — AxelH, Oct 16 '17 at 13:04
That is what I did. I created a connection variable shared by the whole code. This solved my problem. — KulaDamian, Oct 16 '17 at 13:41

score 1 · Accepted Answer · answered Oct 16 '17 at 12:41

Based on this answer about multithreading and SQLITE.

You can find out that SQlite don't support multiple connections. The reason is that whenever you ask for a connection on the DB, a lock is set on that file, you need to close the connection to release that lock. Only then you will be able to open a new connection.

So you either have to close dbConn provided in the method above, or simply share the connection.

I suggest you take a look to the Singleton pattern to manage the connection properly.

score 1 · Answer 2 · answered Oct 16 '17 at 12:42

1

SQLite is particularly nitpicking about file locking. Try closing the connection as well, so that every function opens its own connection, uses it and closes it afterwards. See if this answer helps you.

answered Oct 16 '17 at 12:42

jurez

4,436
2
12
20

The link to the answer didn't help but closing the connection did. Thank you! :) – KulaDamian Oct 16 '17 at 12:51

Database file is locked (SQL Exception in Java)

2 Answers2