[LIB] SQLibrary - Database wrappers for all database engines

Discussion in 'Resources' started by PatPeter, Aug 26, 2011.

Thread Status:
Not open for further replies.
  1. Offline

    Celtic Minstrel

  2. Offline

    Lolmewn Retired Staff

    but how :p
    Just by creating a table with 'locx' DOUBLE or something?
     
  3. Offline

    Celtic Minstrel

    I would assume so.
     
  4. Offline

    Feed_Dante

    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
    1. this.database = database.replace("_", "\\_");

    To:
    Code:java
    1. 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
    1. @Override
    2. public boolean checkConnection() {
    3. Connection connection = open();
    4. if (connection == null) {
    5. open();
    6. return true;
    7. }
    8. return false;
    9. }

    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
    1. @Override
    2. public boolean checkConnection() {
    3. Connection connection = this.open();
    4. if (connection != null)
    5. return true;
    6. return false;
    7. }
     
  5. Offline

    Celtic Minstrel

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

    PatPeter

    @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.
     
  7. Offline

    Lolmewn Retired Staff

    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
     
  8. Offline

    Puppier

    Can I have help with this problem?
     
  9. Offline

    PatPeter

    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.
     
  10. Offline

    Puppier

    It is still happening D:
     
  11. Offline

    blackhatrob

    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.
     
  12. Offline

    oyasunadev

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

    okami35

    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"
     
  14. Offline

    Puppier

    Still getting an error at line 128 in MySQL
     
  15. Offline

    blackhatrob

    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)");
            }
     
  16. Offline

    okami35

    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?
     
  17. Offline

    HSAR

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

    okami35

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

    Thank you
     
  19. Offline

    fritz

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

    okami35

    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
     
  21. Offline

    Father Of Time

    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
     
  22. Offline

    Father Of Time

    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!

    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
    Last edited by a moderator: May 19, 2016
  23. Offline

    okami35

    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
     
    Father Of Time likes this.
  24. Offline

    Father Of Time

    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!
     
  25. Offline

    okami35

    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
     
  26. Offline

    Father Of Time

    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.
     
  27. Offline

    okami35

    I understand, but now I open a connection here:

    Code:
    Connection connexion;
    
    connexion = sqlite.open();
    And after, I use the SAME connection object for my statement:

    Code:
    PreparedStatement prepare = connexion.prepareStatement("INSERT INTO test(nom, age) VALUES(?, ?)");
    So, there is only one object connection... And always the same error

    Thank you very much for your help
     
  28. Offline

    HSAR

    Without meaning any disrespect, it would be much appreciated if you conducted this via PMs since I'd like my notifications on this thread to be about the library itself...
     
  29. Offline

    okami35

    yes I understand, but i think i am not the only one to have problems with this API. And I thought I was allowed to ask help on this thread.

    Sorry
     
  30. Offline

    PatPeter

    @blackhatrob: Will do.

    Yes please, feel free to write any methods and send them to me. I'll work on getting the code on GitHub for commenting, etc. etc.

    @ Everyone else: Do I have to read all those posts to discover something I fixed in the code, or was it an implementation issue?

    Also, if I do get this code on GitHub I'll be uploading the test plugin with example code for MySQL and SQLite. Can I add any of these previous posts to it?

    The thread's purpose is to provide help as well as work on developing the library. Now I haven't read the posts yet, but if it has any good relation to the plugin he should have asked here.

    Naw, feel free to any time. If it's a very specific issue related to SQL and not the library itself, then resort to another thread or PMs.
     
Thread Status:
Not open for further replies.

Share This Page