Solved Few SQLite Database problems

Hi, I've got following problem. I've got SQLite database with some columns and primary key set as 'player'. Queries works fine (I can create table), but I can't set any value in database and I can load only one value (weird, isn't it?). By saying "only one value" I mean when I call query to get first value it works, but every next returns always 0 (zero).

I've got the following code:

Code:java

public static long getPlayerLongValueFromDatabase(String player, String key)
{
    long res = getLongResult(Database.getInstance().query("SELECT `player`,`" + key + "` FROM `stats` WHERE `player`='" + player + "'"), 2);
    if(res == 0)
    {
         if(!key.equals("strength") && !key.equals("dexterity") && !key.equals("mana") && !key.equals("exp") && !key.equals("level") && !key.equals("learnpoints") && !key.equals("health"))
        {
            gothicCraft.getLogger().severe("Requested invalid player value type \""+ key +"\" <DatabaseHelper::getPlayerLongValueFromDatabase()>");
            return res;
        }
 
        setPlayerLongValueInDatabase(player, key, res);
    }
 
    return res;
}

Code:java

public static void setPlayerLongValueInDatabase(String player, String key, long value)
{
        Database.getInstance().query("INSERT OR IGNORE INTO `stats`(`player`,`" + key + "`) VALUES('" + player + "', '" + value + "')");
        Database.getInstance().query("UPDATE `stats` SET `" + key + "`='" + value + "' WHERE `player`='" + value + "'");
    }

I get no errors. I used these queries in few projects and it always worked. The only thing that changed this time is that I added caching data, so now I call saving to database this way:

Code:java

public static void savePlayerDataToDatabase(String player)
    {
        PlayerData tmp = null;
        for(int i = 0; i < cachedData.size(); i++)
        {
            tmp = cachedData.get(i);
            if(tmp.getPlayerName().equals(player))
            {
                DatabaseHelper.setPlayerLongValueInDatabase(player, "strength", tmp.getStrength());
                DatabaseHelper.setPlayerLongValueInDatabase(player, "dexterity", tmp.getDexterity());
                DatabaseHelper.setPlayerLongValueInDatabase(player, "mana", tmp.getMana());
                DatabaseHelper.setPlayerLongValueInDatabase(player, "exp", tmp.getExperience());
                DatabaseHelper.setPlayerLongValueInDatabase(player, "level", tmp.getLevel());
                DatabaseHelper.setPlayerLongValueInDatabase(player, "health", tmp.getHealth());
                DatabaseHelper.setPlayerLongValueInDatabase(player, "learnpoints", tmp.getLearnPoints());
                DatabaseHelper.setPlayerStringValueInDatabase(player, "extraskills", tmp.getExtraSkills());
 
                return;
            }
        }
 
        gothicCraft.getLogger().severe("Could not save data for player \""+ player + "\"!");
    }

I made check and I am sure that data is cached correctly. All values are in cache, they just are not saved in database.

Thanks for your effort in reading this. I hope you can help.

Anyone? Please, it's really important And I can't figure out what is the problem. I spent today over 6 hours trying to fix that!

Come on, please. Need help with that!

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

 

I recommend to change your save queries to a single one, and to use a PreparedStatement:

Code:java

// put this where you connect to the database
//replace "..." with all fields, and make sure to use the correct amount of '?'s, or alternatively use the "`field`=?" syntax
PreparedStatement s = Database.getInstance().prepare("REPLACE INTO `stats` (`player`, `strength`, `dexterity`, ...) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");

Code:java

// and use this in savePlayerDataToDatabase():
int i = 1;
s.setString(i++, player);
s.setLong(i++, tmp.getStrength(),);
s.setLong(i++, tmp.getDexterity(),);
// etc.
 
s.execute();

edit: This is how I read values from my database, using a PreparedStatement and a ResultSet:

Code:java

monitorQuery = ((Database) db).prepare("SELECT name, type, value, rowid FROM " + TABLE_NAME + " WHERE rowid > ? AND update_guid != ?");
 
// ...
 
monitorQuery.setLong(1, lastRowID);
monitorQuery.setString(2, guid);
monitorQuery.execute();
ResultSet r = null;
try {
r = monitorQuery.getResultSet();
loadVariables(r);
} finally {
if (r != null)
r.close();
}
 
// ...
 
private void loadVariables(final ResultSet r) throws SQLException {
synchronized (syncDeserializing) {
while (r.next()) { // iterates over all returned rows
int i = 1;
final String name = r.getString(i++);
final String type = r.getString(i++);
final Blob value = r.getBlob(i++);
// etc.

 

Njol
Thank You very much. Also I was thinking about PreparedStatement's. Thanks for nice code. I hope it will fix my problem. I'll check it and tell if it works.

#Edit
I've got problem with prepare() method. What should I call it for? I mean what is the class? I use my custom one Database Class and I have not the following method there.

 

You likely need to use "connection.prepareStatement(query)". prepare() is a method of SQLibrary which I use in my plugin.

 

Njol
Help me, still does not work. I don't understand it, why? Value reading works, not just saving (reading is still query, not a prepared statement).

Why the hell on the earth I can't set value in database and other queries work fine. Well I can set value but ONCE. Every next setting value does not work ;/

I just found that when using PreparedStatement I get error that tells SQL statement does not return ResultSet. How is it connected with my problem?

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

 

You must use .executeQuery() , it returns ResultSet

Code:java

 
PreparedStatement stmt = yourConnection.prepareStatement("UPDATE `stats` SET ?=? WHERE `player`=?");
stmt.setString(1,key);
stmt.setString(2,value);
stmt.setString(3,player);
stmt.executeUpdate();}

Something like this?

 

Code:

14:51:29 [SEVERE] java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near "?": syntax error)

maciekmm
That's what I get.

 

put System.out.println(stmt.toString()); after executeUpdate and paste it here.

 

maciekmm
It throws exception on prepareStatement() method. I can't get it to string then...

YEAAAAAAAAAAAAAAAAAAAAAAAAA!!!
It works, I changed statement to:

Code:

"UPDATE `stats` SET `"+key+"`=? WHERE `player`=?"

And changed setString() etc. to match new format.

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