[LIB] SQLibrary - Database wrappers for all database engines

What docs would you write? There is already Javadoc.

java docs dont tell you much i mean like wiki stuff for n00bs, ya know the basic coding what it does ect

Can I store Floats and Doubles in SQL libraries?

Of course.

but how
Just by creating a table with 'locx' DOUBLE or something?

I would assume so.

For MySQL, underscores need only be escaped in certain situations; databases isn't one of them.

See: http://dev.mysql.com/doc/refman/5.1/en/string-syntax.html

I wind up with this error:

Code:

[SEVERE] [Minecart Pathfinding] [MySQL] jdbc:mysql://poseidon:3306/minecart\_pathfinding
[SEVERE] [Minecart Pathfinding] [MySQL] Could not be resolved because of an SQL Exception: Unknown database 'minecart\_pathfinding'.

If I alter MySQL.java:43 and change:

Code:java

this.database = database.replace("_", "\\_");

To:

Code:java

this.database = database;

I am able to connect successfully.

Although I don't have any underscores in either my host, user or password, I would expect them to be broken as-well if I did.




Part 2:
The MySQL.checkConnection() function seems broken.

Code:java

    @Override
    public boolean checkConnection() {
        Connection connection = open();
        if (connection == null) {
            open();
            return true;
        }
        return false;
    }

According to DatabaseHandler.java:

Code:

    /**
     * <b>checkConnection</b><br>
     * <br>
     * Checks the connection between Java and the database engine.
     * <br>
     * <br>
     * @return the status of the connection, [B]true for up, false for down[/B].
     */

However the reverse is actually observed:

If the connection is successful (and thus not null) the IF will be skipped and false will be returned.
Whereas in it's inverse (if the connection fails) the connection will be null and will always return true (after an unchecked open() witch could again fail to connect).

I was able to solve this for myself by replacing the checkConnection() in MySQL.java with its equivalent from SQLite.java which returns as expected:

Code:java

    @Override
    public boolean checkConnection() {
        Connection connection = this.open();
        if (connection != null)
            return true;
        return false;
    }

...fairly sure that underscores don't normally need escaping anywhere. Obviously the pattern-matching is an exception, though.

@Feed_Dante
@Mr Smith helped me figure this out before I released the latest version, but I forgot to remove it. I was already about 70% sure escaping the underscores wouldn't help and/or would hurt, but it was an act of desperation as it wouldn't hold a connection on Linux. That and I had some three papers to do and I could not be bothered reading any documentation to deny my superstitious testing.

I had made a note of that flaw in checkConnection() (or it was another method, it was one of the less than 5 lines ones), thanks for pointing it out.

That's what the example plugin is for, no ETA though.

Code:

CREATE TABLE `test` (

    x DOUBLE,

    y FLOAT

)

Something like that.

Yar, my mistake.

Hopefully I'll be able to post a fix tonight or tomorrow.

Nijegh, I do an INSERT thing, and it gives a warning in the log "Does not return ResultSet".
Of course it doesn't, lol. Fix please? =D

Can I have help with this problem?

But... but... but... I've fixed this error like three times now.

I made an attempted fix for that problem, as well as fixing the previously mentioned errors and uploading the fix.

It is still happening D:

Here is a quick fix (read: hack) for the INSERT exception.

Modify the SQLite.java file in PatPeter's package such that the query(String query) catch block ignores the phrase "query does not return ResultSet". Here is the original code starting at line 143 in SQLite.java:

Code:

} else {
                this.writeError("Error at SQL Query: " + ex.getMessage(), false);
            }

This is the modified version, again starting at line 143 in SQLite.java

Code:

} else {
                if (ex.getMessage().compareTo("query does not return ResultSet") != 0) {
                    this.writeError("Error at SQL Query: " + ex.getMessage(), false);
                }
            }

There is at least 1 negative implication: you will not receive a message when this happens AT ALL. However, I can't say I wouldn't expect this to ever really happen.

Great library, although I'd prefer writing my own. Could I help with the next release of this?

Hello, I would like to use your API with Sqlite, so i have installed the API (and now I have a package lib.PatPeter.SQLibrary" with mysql.java, sqlite.java.
But, how can I initialize sqlite?
And where should I put the database?

Thank you for your help

I have tried:

Code:

public void onEnable() {
        // TODO Auto-generated method stub
        sqlite = new SQLite(log, "[SQLite]", "mydatabase", getDataFolder().getAbsolutePath());
        if(sqlite.checkConnection()) {
        log.info("[SQLite]Connection ok");
        String table_faction = "CREATE TABLE blocks (id INT AUTO_INCREMENT PRIMARY_KEY, owner VARCHAR(255), x INT, y INT, z INT);";
        sqlite.createTable(table_faction);
        if(sqlite.checkTable("factions"))
            log.info("[SQLite]block ok");
        }
        else
            log.info("[SQLite]ERROR");
    }

but i have juste "[SQLite]Connection" and not "[Sqlite]block ok"

Still getting an error at line 128 in MySQL

okami35,
sqlite will (should) autoinitialize the database file when the plugin is loaded, the onEnable method is executed, and the db connection opened (see my example below). It will be initialized in the location you denote in the constructor.

As far as _where_ you should put it, that's up to you really. I tend to play nice and keep all of my files together in the plugin directory. Here's what I use to create my sqlite connection and create the necessary table structures (other lines omitted for brevity and clarity):

Code:

public void onEnable() {
        //create our logger
        logger = Logger.getLogger("Minecraft");

        //create the database connection, creating a new db file if necessary
        dbconn = new SQLite(logger, "", "MyPluginName", "./plugins/MyPluginName/");
        //now we actually open the database, creating the file if necessary
        dbconn.open();

        //if the xyz table doesn't exist (i.e. new database) create it.
        if (!dbconn.checkTable("xyz")) {
            logger.log(Level.INFO, "Creating table \"xyz\" in database \""+dbconn.name+"\"");
            dbconn.createTable("CREATE TABLE xyz(uid INTEGER NOT NULL PRIMARY KEY, a text, b text)");
        }

Thank you for your help, it runs now.

I have an another problem, this is my code:

Code:

public void onEnable() {
		// TODO Auto-generated method stub
		try {
		sqlite = new SQLite(log, "[test]", "perm", getDataFolder().getAbsolutePath());
		sqlite.open();
		if(sqlite.checkConnection()) {
		log.info("[SQLite]Connection reussie");
		if(!sqlite.checkTable("test")) {
			sqlite.createTable("CREATE TABLE test(id INT AUTO_INCREMENT PRIMARY_KEY, nom VARCHAR(255), age INT)");
			log.log(Level.INFO, "creation de la table test dans la base: "+sqlite.name);
		}
		sqlite.query("INSERT INTO test(nom, age) VALUES('"+nom+"', "+age+")");
		ResultSet res = sqlite.query("SELECT * FROM test");
		while(res.next()) {
		System.out.println(res.getString("nom"));
		System.out.println(res.getInt("age"));
		}
		}
		}catch(Exception e){e.printStackTrace();}
	}

When i run it many times , it gets stuck on " sqlite.query("INSERT INTO test(nom, age) VALUES('"+nom+"', "+age+")");"
But when i delete the database and when i run the code, it works perfectly.

Di you know where is the problem?

What's the purpose of the log parameter when creating a table?

now, i have a SqlException, sqlite is locked.
What is the problem?

Thank you

I had that problem and fixed it with the change mentioned in this previous post.

Thank you for your answer but it does not work, this is my new code:

Code:

public void onEnable() {
        // TODO Auto-generated method stub
        try {
        sqlite = new SQLite(log, "[test]", "perm", getDataFolder().getAbsolutePath());
        sqlite.open();
        if(sqlite.checkConnection()) {
        log.info("[SQLite]Connection reussie");
        connexion = sqlite.getConnection();
        connexion.setAutoCommit(false);
        if(!sqlite.checkTable("test")) {
            Statement stat = connexion.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            connexion.setAutoCommit(true);
            stat.executeUpdate("CREATE TABLE test(id INT AUTO_INCREMENT PRIMARY_KEY, nom VARCHAR(255), age INT)");
            stat.close();
            log.log(Level.INFO, "creation de la table test dans la base: "+sqlite.name);
        }

        PreparedStatement prepare = connexion.prepareStatement("INSERT INTO test(nom, age) VALUES(?, ?)");
        connexion.setAutoCommit(true);
        prepare.setString(1, "Tom");
        prepare.setInt(2, 14);
        prepare.executeUpdate();
        prepare.close();

        PreparedStatement prep = connexion.prepareStatement("SELECT * FROM test WHERE nom=?");
        prep.setString(1, "Tom");
        ResultSet result = prep.executeQuery();
        result.next();
        System.out.println(result.getString("nom"));
        System.out.println(result.getInt("age"));
        }
        }catch(Exception e){e.printStackTrace();}
    }

I still have a SQLException sql busy

I'm actually having this exact same problem. I've been browsing the connection and database files but can't seem to find any way to set locks. I'm kind of lost atm. I'm going to continue searching, but I figured I would colaborate with those who are experiencing the same problem as I am.

I have this thread going here related to this issue. (is a bit outdated)

My code at this point looks like this:

Code:

                Connection conn = RiftCraft.RiftCraftSQLDB.getConnection();
                PreparedStatement prepare = conn.prepareStatement("INSERT INTO RiftBookLocations(PlayerName, LocationName, World, XCoord, YCoord, ZCoord) VALUES(?, ?, ?, ?, ?, ?)");
                conn.setAutoCommit(true);
                conn.
                prepare.setString(1, playername);
                prepare.setString(2, locname);
                prepare.setString(3, world);

                prepare.setDouble(4, xcoord);
                prepare.setDouble(5, ycoord);
                prepare.setDouble(6, zcoord);

                RiftCraft.log.info( "RiftCraft: Executing Query - " + prepare.toString() );
                prepare.executeUpdate();

                prepare.close();

Which results in the following error in my console:

The server doesn't crash, doesn't do anything, it just throws that error. The server is creating the database fine and loading it back into the server fine after restart, but I just can't get it to save the data to the SQLite database correctly. Anyone able and willing to lend us their experience with this subject would be greatly appriciated

EDIT:
I've not been able to find a "solution" from this writing, but it speficically addresses our issue and is from the sqlite documentations. I figured I would post this finding in hopes that it will spark some ideas.

This was found here:
http://www.sqlite.org/lockingv3.html

So I have some good news, I think I figured out the problem here. Everything I've read (and I've read a ton of articles on SQLite locking) it seems that the issue was being caused by something outside the scope of your current connection accessing the database.

So I began to think about this and decided to remove ALL code that refers to my database except the actual statement creator. so I commented everything out and sure enough when I booted it up I could write to the database just fine.

What I was doing was creating a local variable that refered to my database stored in my core. Then I was Opening the connection to that instance of my database.

I then was building the statement and then Executing it, but there lies the problem. The connection I initially manually opened was Connection A that was communicating with my database, and hence locking it. Then when the statement building is told to execute (assuming here) it creates its own connection to the database, but recieves the java.sql [SQL_BUSY] error.

Here is my restructured function that is working properly. This obviously wont be cut and paste as it refers to a lot of variables unique to my server, but it will hopefully give people an idea of the structure to use when writing to an SQLite database. By the way I am far from an expert on this subject, simply a person who was hell bent to get this to work, so in no way do I gurentee this is the most efficient or stable way of handling this.

Code:

    public static void Serialize()
    {
        for (Map.Entry<Player, RiftBook> DBEntry : RiftCraft.RiftCraftDB.entrySet())
        {
            Player player = DBEntry.getKey();
            RiftBook book = DBEntry.getValue();

            for (Map.Entry<String, Location>RiftBookEntry : book.Inscriptions.entrySet())
            {
                String locname = RiftBookEntry.getKey();
                String playername = player.getName();
                Location loc = RiftBookEntry.getValue();
                String world = loc.getWorld().getName();
                double xcoord = loc.getX();
                double ycoord = loc.getY();
                double zcoord = loc.getZ();
                PreparedStatement prepare = null;

                try
                {
                    Connection conn = RiftCraft.RiftCraftSQLDB.getConnection();
                    prepare = conn.prepareStatement("INSERT INTO RiftBookLocations(PlayerName, LocationName, World, XCoord, YCoord, ZCoord) VALUES(?, ?, ?, ?, ?, ?)");
                    conn.setAutoCommit(false);
                    prepare.setString(1, playername);
                    prepare.setString(2, locname);
                    prepare.setString(3, world);

                    prepare.setDouble(4, xcoord);
                    prepare.setDouble(5, ycoord);
                    prepare.setDouble(6, zcoord);
                    prepare.executeUpdate();
                    conn.commit();

                    prepare.close();
                    conn.setAutoCommit(true);
                }
                catch(Exception e)
                {
                    RiftCraft.log.info("RiftCraft Error: " + e.toString() );
                }
            }
            RiftCraft.log.info("RiftCraft Database save complete.");
        }
    }

The code that is of paticular interest to those using this plugin is the following section, but I have included the whole function so that people can see there is no other SQL or connections going on outside this try and catch method.

Code:

                try
                {
                    Connection conn = RiftCraft.RiftCraftSQLDB.getConnection();
                    prepare = conn.prepareStatement("INSERT INTO RiftBookLocations(PlayerName, LocationName, World, XCoord, YCoord, ZCoord) VALUES(?, ?, ?, ?, ?, ?)");
                    conn.setAutoCommit(false);
                    prepare.setString(1, playername);
                    prepare.setString(2, locname);
                    prepare.setString(3, world);

                    prepare.setDouble(4, xcoord);
                    prepare.setDouble(5, ycoord);
                    prepare.setDouble(6, zcoord);
                    prepare.executeUpdate();
                    conn.commit();

                    prepare.close();
                    conn.setAutoCommit(true);
                }
                catch(Exception e)
                {
                    RiftCraft.log.info("RiftCraft Error: " + e.toString() );
                }

I hope this manages to help someone and keep them from aimlessly having to browse SQLite tutorials for their entire weekend.

@okami35
Look at your try statement, you create an SQLite connection here:

Code:

    sqlite = new SQLite(log, "[test]", "perm", getDataFolder().getAbsolutePath());
    sqlite.open();
    connexion = sqlite.getConnection();

And then you later execute the statement creator:

Code:

            Statement stat = connexion.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            connexion.setAutoCommit(true);
            stat.executeUpdate("CREATE TABLE test(id INT AUTO_INCREMENT PRIMARY_KEY, nom VARCHAR(255), age INT)");
            stat.close();

I think the problem is from the first bit of code, its uncessecary. The CreateStatement knows the connection you are trying to use:

Code:

    connexion.createStatement(...

And I think when you execute the update:

Code:

    stat.executeUpdate(...

It actually opens the connection on its own, but because you have already opened one manually above It can't establish a write permission connection to the database because the database is write locked (can't write while someone has read access).

I can't gurentee this will fix your problem, but this was the last remedy I tried for this problem and it got it working. Also I haven't fully read threw your code, just spotted those few lines that were giving me issues, so there might be additional problems I didnt catch, but use my example from above and you should be able to piece things together.
I hope this helps, take care!

Thank you very much, i will try this tomorow. But you are right, and i think it's the solution.

Thank you, i've got you know

It's my pleasure, I'm happy to help! If it continues to give you problems post the entire function here and I will take a looksy to see if I can spot whats going wrong.

Take care Okami35, and goodluck with the project!

Finally, i don't understand what do you mean.I must create a Statement with the method createStatement(), so i must use connexion.

the error comes from this part of code:

Code:

PreparedStatement prepare = connexion.prepareStatement("INSERT INTO test(nom, age) VALUES(?, ?)");
        connexion.setAutoCommit(true);
        prepare.setString(1, "Tom");
        prepare.setInt(2, 14);
        prepare.executeUpdate();
        prepare.close();

Thank you for your help

Yes and no...

The error is being triggered by that code, but the error is being caused by earlier code. See SQLite locks the database for any processes other than readonly. It does this to keep 2 people from writing to the same record at once and corrupting the record. Now this isn't useful to us because it's unlikely that any minecraft plug-ins will have multiple threads contacting the same database at once, but remember SQLite wasn't developed with minecraft in mind.

So... any time you connect to a SQLite database it “locks” the database, and the database will not issue any EXCLUSIVE permissions (which is what a connection needs to write to the database) until all existing connections are disposed.

So, what is happening is you are opening a connection with the following code:

Code:

        sqlite = new SQLite(log, "[test]", "perm", getDataFolder().getAbsolutePath());
        sqlite.open();

Which locks the entire database, but you never close that connection. You then later call this code:

Code:

        PreparedStatement prepare = connexion.prepareStatement("INSERT INTO test(nom, age) VALUES(?, ?)");
        connexion.setAutoCommit(true);

Which opens a connection of its own. The prepared statement is kind of a pre packaged SQL connection, you simply provide it an SQL statement and it does the rest. So the PreparedStatement is attempting to connect to the SQL database so it can execute a query but it can't because the database is currently locked from the connection you established earlier.

Looking closer it looks like you are attempting to mix 2 different SQL technics, manually opening, writing, executing, and closing an SQL statement mixed with using a Statement Preparer. You need to pick one or the other and use that as your primary method of SQL statement execution.