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
– AxelH, Commented Oct 16, 2017 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
– KulaDamian, Commented Oct 16, 2017 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
– AxelH, Commented Oct 16, 2017 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
– KulaDamian, Commented Oct 16, 2017 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
– AxelH, Commented Oct 16, 2017 at 13:04

AxelH · Accepted Answer · 2017-10-16 12:41:30Z

1

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.

answered Oct 16, 2017 at 12:41

AxelH

14.6k2 gold badges27 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jurez · Accepted Answer · 2017-10-16 12:42:35Z

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, 2017 at 12:42

jurez

4,6652 gold badges15 silver badges24 bronze badges

1 Comment

KulaDamian Over a year ago

The link to the answer didn't help but closing the connection did. Thank you! :)

Collectives™ on Stack Overflow

Database file is locked (SQL Exception in Java)

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related