SQLite - The database file is locked?

I have that error
[SEVERE] Database query error: [SQLITE_BUSY] The database file is locked (database is locked)
error is only in SQLite (i have one code for MySql and SQLite in that part of code)
error show if i use a result set in PlayerLoginEvent and PlayerJoinEvent. How to fix that? (I can't delete any result) and if error show that one time then not work any command using datebase ;/ just db is locked...

 

Make sure you always only have one active connection to your sqlite database. Create one connection and share it everywhere you need to connect to the database, OR close your connections after you are finished using them. On disable: close the connection too.

 

hyym how to connect to db in one code? that must work in all code . on enable, on commend and in events.
And/or how to close

for db i use that code from this forum

Code:java

 
public class Db {
private final Plugin plugin;
private final String url;
private Logger log;
 
public Db(final Plugin plugin, final String host, final String database,
final String user, final String password) {
this.plugin = plugin;
url = "jdbc:mysql://" + host + "/" + database + "?user=" + user
+ "&password=" + password;
log = plugin.getServer().getLogger();
initDriver("com.mysql.jdbc.Driver");
}
 
public Db(final Plugin plugin, final String filePath) {
this.plugin = plugin;
url = "jdbc:sqlite:" + new File(filePath).getAbsolutePath();
log = plugin.getServer().getLogger();
initDriver("org.sqlite.JDBC");
}
 
private void initDriver(final String driver) {
try {
Class.forName(driver);
} catch (final Exception e) {
log.severe("Database driver error:" + e.getMessage());
}
}
 
public int resultInt(ResultSet result, int column) {
if (result == null)
return 0;
try {
result.next();
int integer = result.getInt(column);
result.close();
 
return integer;
} catch (SQLException e) {
log.severe("Database result error: " + e.getMessage());
}
return 0;
}
 
public String resultString(ResultSet result, int column) {
if (result == null)
return null;
try {
result.next();
String string = result.getString(column);
result.close();
return string;
} catch (SQLException e) {
log.severe("Database result error: " + e.getMessage());
}
return null;
}
 
public ResultSet query(final String query) {
return query(query, false);
}
 
public ResultSet query(final String query, final boolean retry) {
try {
final Connection connection = DriverManager.getConnection(url);
final PreparedStatement statement = connection
.prepareStatement(query);
if (statement.execute())
return statement.getResultSet();
} catch (final SQLException e) {
final String msg = e.getMessage();
log.severe("Database query error: " + msg);
if (retry && msg.contains("_BUSY")) {
log.severe("Retrying query...");
plugin.getServer().getScheduler()
.scheduleSyncDelayedTask(plugin, new Runnable() {
@Override
public void run() {
query(query);
}
}, 20);
}
}
return null;
}
}
 

 

Code:

public ResultSet query(final String query, final boolean retry) {
try {
final Connection connection = DriverManager.getConnection(url); // <- the Connection should be closed after execution
final PreparedStatement statement = connection
.prepareStatement(query);  // <- the (Prepared)Statement should be closed after execution
if (statement.execute())
return statement.getResultSet(); // <- the ResultSet should be closed after execution
} catch (final SQLException e) {
final String msg = e.getMessage();
log.severe("Database query error: " + msg);
if (retry && msg.contains("_BUSY")) {
log.severe("Retrying query...");
plugin.getServer().getScheduler()
.scheduleSyncDelayedTask(plugin, new Runnable() {
@Override
public void run() {
query(query);
}
}, 20);
}
}
return null;
}
}

Also, instead of closing the connection or the prepared statements, you could set them up on top of your class and keep using them (the same object), instead of creation a new one each time..

 

hyym that is another class. in mainclass I using only

Code:java

 
Db base = null; // for use MySql and SQlite in one code (or 2 if that not possible in syntax)
if (MySqlStatus == true) {
String Host = config.getString("Global.MySql.host");
String user = config.getString("Global.MySql.user");
String db = config.getString("Global.MySql.db");
String pass = config.getString("Global.MySql.pass");
base = new Db(this, Host, db, user, pass);
} else {
base = new Db(this, getDataFolder() + File.separator + "SQLite.db");
}

All code in "onEnable" "on command" and some another. Works great.
I use tak code in every place. one in "onEnable" one in "onCommand"ane in... I don't know how to write only one
only in that 2 events. (register: getServer().getPluginManager().registerEvents(this, this); )

f5

EDIT by Moderator: merged posts, please use the edit button instead of double posting.

 

blablubbabc you did not suggest leaving a connection open after executing your queries didn't you?

Of course you can share the same connection for multiple queries (i hope that is what you ment xD) but don't leave a connection open when you are done with whatever you wanted to do as this might lead to file corruption when the connection is still open and the server crashes.

 

but how to close it. using that code?

 

bfgbfggf You can check out my github page and checkout the database folder in xAuth plugin. Its not the best written code as i didnt have really spend much time in refactoring the database backend but you can use the codebase if you like. Should at least give you a good start or show you how you can do it.

Remove everything you dont need like the autoupdater methods and so on. Basically you need ConnectionPool.java, Table.java, DatabaseController.

Of course you can use the DatabaseUpdater too but i dont suggest using it as it stores the current state of the table in a file and thats what i really dont like.

 

But... new code ? ;/ I have some code. that too many changes. There are any method to close without new code? ;/
just edit that code http://pastebin.com/SzxdH5fN or use something in mainclass? (I have only 3 class. one for db, one for auto updates and mainClass with all code)
or another method to fix that bug

EDIT: My events http://pastebin.com/bfbJSF7y // sorry for polish in some places (I don't have time to translate it )

 

I already showed you where in your code you have to take care about closing the Connection/the Statement/the ResultSet. You can close them via "object.close();" with "object" being one of those 3 types (Connection, Statement, ResultSet). If you return a result set in one of your methods, you will have to close this ResultSet in the calling method, otherwise you will get memory leaks.

 

blablubbabc
if I use that in my main class I have error.
ResultSet.close(); this same with Connection and Statement.
Hymm I have one another question. If that SQLite not support multi threads then... how write code like this:

Code:java

 
ResultSet res1 = base.query("SELECT id,PTimed,TimeEnd FROM ProWarns WHERE Ostrzegany = '"+ cel + "'");
String prl = "a";
long TimeEnd = 0l;
int Timed = 0;
while (res1.next()) {
    Timed = res1.getInt("Timed");
    if (Timed == 1) {
        TimeEnd = res1.getLong("TimeEnd");
        Date dt = new Date();
        long EndTime = dt.getTime();
        if (TimeEnd <= EndTime) {
            int id = res1.getInt("id");
            base.query("DELETE FROM ProWarns WHERE id = '"+ id + "'");
            }
       }
}
 

In MySql that works

ok i find error in my close... but how about that code?
blablubbabc
EDIT: but works only ResultSet.close because in my mainclass i not have a Connection.

EDIT by Moderator: merged posts, please use the edit button instead of double posting.

 

Take a look at your query-method: it creates a new connection there and it doesn't close it or re-use it later. SQLite only allows you to have one open connection to the database. Every time you call the query method it will try to create a new connection currently without the old one being closed before. So when you call the second query
base.query("DELETE FROM ProWarns WHERE id = '"+ id + "'");

You try to create a second connection to the SQLite database -> not supported by SQLite -> Error

You have 2 possibilities what you can do here: Only create 1 connection once somehwere in your code and reuse it for every query, or close the connection when you are done.

I would prefer to re-use 1 connection.

 

Hyym I learning a java very short time. and I don't know how to reuse ;/and how to close connection if isn't in main class

 

..

Code:

// Somewhere in ypur DB class:
private Connection connection;
 
public void closeConnection() {
        try {
            connection.close();
        } catch(Exception e) {
            e.printStackTrace();
        }
    }
 
public boolean isConnected() {
        try {
            return((connection==null || connection.isClosed()) ? false:true);
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }
 
public void initialize() {
        try {
            connection = DriverManager.getConnection(url);
        } catch (SQLException ex) {
            log("ERROR: SQL Exception!");
            ex.printStackTrace();
        } catch (ClassNotFoundException ex) {
            log("ERROR: Could not find SQLite driver class!");
            ex.printStackTrace();
        }
    }
 
//Some class that should help you to close the statement and the resultSet from outside of this class:
 
public class Result {
    private ResultSet resultSet;
    private Statement statement;
 
    public Result(Statement statement, ResultSet resultSet) {
        this.statement = statement;
        this.resultSet = resultSet;
    }
 
    public ResultSet getResultSet() {
        return this.resultSet;
    }
 
    public void close() {
        try {
            this.statement.close();
            this.resultSet.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
 
}
 
// Your query method (with bad formatting):
public Result query(final String query, final boolean retry) {
 
PreparedStatement statement;
try {
if (!isConnected()) initialize();
statement = connection.prepareStatement(query);
if (statement.execute())
return new Result(statement, statement.getResultSet());
} catch (final SQLException e) {
final String msg = e.getMessage();
log.severe("Database query error: " + msg);
if (retry && msg.contains("_BUSY")) {
log.severe("Retrying query...");
plugin.getServer().getScheduler()
.scheduleSyncDelayedTask(plugin, new Runnable() {
@Override
public void run() {
query(query);
}
}, 20);
}
}
if (statement != null) statement.close();
return null;
}
}