[Solved] SQLite query hanging server

So, I've been using the SQLibrary to handle SQLite for me, and up till now it was doing just fine. Well, it still does well for all other methods and commands in my plugin. Its not till I hit this snag that is breaking things...

Here's the snippet where I try to "reset" a player's amount in a SQLite database.
For whatever reason, when I do the database query at the "has" section, the server hangs. No console messages, no exceptions thrown, nothing :\

Code:

//attempt to parse name
String name = args[1];
// SQL query to get player count for specified name
String query = "SELECT COUNT(*) FROM players WHERE playername='"
        + name + "'";
ResultSet rs = ks.getLiteDB().select(query);
// Check ResultSet
boolean has = false;
try
{
    if (rs.next())
    {
        //Check if only received 1 entry
        if (rs.getInt(1) == 1)
        {
            //we have a single name
            has = true;
        }
        else if(rs.getInt(1) > 1)
        {
            sender.sendMessage(ChatColor.RED
                + prefix
                + " Got more than one result. Possibly incomplete name?");
        }
        else
        {
            // Player not in database, therefore error
            // on player part
            sender.sendMessage(ChatColor.RED + prefix
                    + " Player " +ChatColor.WHITE+ name + ChatColor.RED +" not in database.");
            sender.sendMessage(ChatColor.RED + prefix
                    + " Player names are case sensitive.");
        }
    }
    else
    {
        //Error in query...
        sender.sendMessage(ChatColor.RED + prefix
                + " SQL query error");
        }
    }
catch (SQLException e)
{
    // INFO Auto-generated catch block
    sender.sendMessage(ChatColor.RED + prefix
            + "Could not reset " + name + "'s karma");
    e.printStackTrace();
}
if(has)
{
    //ERROR Still very much broken
    int amount;
    try
    {
        query = "UPDATE players SET karma='" + 0
	+ "' WHERE playername='" + name + "'";
        sender.sendMessage(ChatColor.YELLOW+ prefix + " Player amount set to: " + amount);
    }
    catch (SQLException e)
    {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

Here's the getPlayerAmount method. However, this has been working just fine in other methods and returns the proper amount as reported in the database:

Code:

/**
 * Retrieves amount value of a player from the database. Forces player to be
 * added to database if they don't exist
 *
 * @param Player name
 * @return amount value associated with name
 */
public int getPlayerAmount(String name) throws SQLException {
    String query = "SELECT * FROM players WHERE playername='" + name + "'";
    ResultSet rs = ks.getLiteDB().select(query);
    int amount = 0;
    // Retrieve amount from database
    try
    {
        if (rs.next())
        {
            do
            {
                // Grab player amount value
                amount = rs.getInt(2);
            }
            while (rs.next());
        }
        else
        {
            // Player not in database, therefore add them
            query = "INSERT INTO players VALUES ('" + name + "','" + amount
                    + "')";
            ks.getLiteDB().standardQuery(query);
        }
    }
    catch (SQLException e)
    {
        throw e;
    }
    return amount;
}

I don't believe there's anything wrong with my database format, considering its been working for all other methods. Here's the actual SQLite create query:

Code:

database.createTable("CREATE TABLE `players` (`playername` varchar(32) NOT NULL,`amount` SMALLINT NOT NULL,UNIQUE (`playername`));");

Is there something about SQLite where I can't update a table entry until after a SELECT lookup or some other weird nuance of SQLite that I'm missing?

Any help on this is greatly appreciated.
Also, sorry I couldn't collapse the code snippets. the collapse bbcode tag doesn't like me for whatever reason...

 

i think i already said this sometime ago, do not use libs...not to be mean, but you can always make your own libs
in fact you dont have to create libs. just study this http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC

 

Have you tried doing the same exact UPDATE in a sqlite shell to confirm that it does what you expect?
Often best to assemble the query string first, post it to log.info() when debugging (so you see the exact query string), then do the query. Also, I really recommend you learn about SQL placeholders for safety and performance reasons. Assembling things like a player name into the query string itself can mess things up.

 

I tried your advice and tried the SQL query using terminal. The commands were fine and working as they should have been.
I found out the reason why it was hanging, the SQLite database was locked by my previous ResultSet object.

(Via SQLite.org)

Its weird that it wasn't doing that for a different table that I was working on, although I suppose somehow my other ResultSet objects were closing automatically and it was just this one that decided to stick around and keep its lock on the database.

I've updated my code to call ResultSet.close() when appropriate, for all instances. Now it works.

Thank you both for your help.