[LIB] SQLibrary - Database wrappers for all database engines

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

  1. Offline

    PatPeter

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
  2. Offline

    alta189

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    Thanks to @PatPeter for taking this over for me as I do not have time to support/update it due to my involvement with Spout.
    comdude2 likes this.
  3. Offline

    alexh

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    GREAT! will you be producing any documentation for this?
    im developing a plugin that will use this of alta189's old version
  4. Offline

    PatPeter

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    Well, apparently XenForo has some keyboard shortcut that redirects me to the "Create Thread" page, or navigates me back and then forward, because I just lost my post to you.

    You might see some of it in the current version, or maybe not, but I've already started adding Javadoc to every single method. If you're on a schedule with your plugin, I wouldn't suggest testing this. If it doesn't matter, I'd suggest ziping your code to a backup but not with this version. I've already made substantial changes such as changing the package name.

    Anyway, the major changes are to use query() instead of *Query() such as insertQuery(), and the different construtors:

    PHP:
    public MySQL mysql;
    ...
    this.mysql = new MySQL(this.log,"[Plugin Prefix]",hostname,database,username,password);
    Instead of:

    PHP:
    public mysqlCore ms;
    ...
    this.ms = new mysqlCore(...);

    This post has been edited 1 time. It was last edited by PatPeter Aug 30, 2011.
  5. Offline

    alexh

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    :) sexy ! um deadline no not realy im building this for my self really but releasing it when its got everything i want so ill back my project up and keep trying to understand your code in my n00bish ways :p
  6. Offline

    PatPeter

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    Full Javadoc now implemented.

    3.0.2 is stable enough for you to test if you want. If you do: remember to zip backup all your packages, including alta189's in case mine does not work and I cannot help you immediately. The names of the package and the class should stay as they are forever. If you are confused at all by the installation instructions tell me. I wrote them the best I could.

    This post has been edited 1 time. It was last edited by PatPeter Aug 28, 2011.
  7. Offline

    Mr Smith

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    When I use update or insert query I get this error in console:
    Code:
    17:02:41 [SEVERE] Error occurred while disabling Core v0.1 (Is it up to date?): null
    java.lang.NullPointerException
            at lib.PatPeter.SQLibrary.MySQL.writeError(MySQL.java:52)
            at lib.PatPeter.SQLibrary.MySQL.query(MySQL.java:123)
  8. Offline

    PatPeter

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    Yeeesh, sorry I zipped it at the wrong time. I hadn't saved super(log,prefix) in MySQL so that it wasn't setting the superclass attributes. If you look in MySQL.java you should be able to see the line break in the constructor. I uploaded the fix I thought I had released 12 hours ago.
  9. Offline

    68x

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    I am using this to declare/initialize my variables:
    Code:
    public MySQL mySQL;
    
    mySQL = new MySQL(log,"[FFD]",host,database,username,password);
    The MySQl details are 100% correct (I have tested them with other plugins and the connection works); when I try to test the plugin, this error occurs:
    Code:
    2011-08-28 21:07:19 [SEVERE] [FFD] [MySQL] SQLException: Communications link failure
    
    The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
    I call the connection by doing:
    Code:
            try
            {
                mySQL.open();
            }
            catch(Exception e)
            {
                e.printStackTrace();
            }

    This post has been edited 2 times. It was last edited by 68x Aug 29, 2011.
  10. Offline

    PatPeter

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    I have... absolutely no idea why that's happening to you. Here are the questions I can surmise to ask:
    1. What is your hostname and database name?
    2. If you cannot disclose the hostname, is it a URL, IP address, localhost, or 127.0.0.1?
    3. If you cannot disclose the database name, does it have capitals, symbols, numbers, etc.?
    4. What collation are you using?
    5. Are you using the standard MySQL port?
    My MySQL works perfectly on my developer machine, but I'm also connecting locally.
  11. Offline

    alexh

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    ok setting up the lastest version, and about to implement
  12. Offline

    Mr Smith

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    It gives me errors. By the way, update and insert query still does not work.

    This post has been edited 1 time. It was last edited by Mr Smith Aug 29, 2011.
  13. Offline

    alexh

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    the server returns this error,
    Code:
    >03:39:51 [SEVERE] [Qstat][MySQL-pplib] Could not be resolved because of an SQ Exception: Access denied for user 'user'@'%' to database 'user/'.
    what is the added @'%'? and / ??

    hers the same error on a diffrent server.
    Code:
    2011-08-29 14:43:03 [SEVERE] [Qstat][MySQL-pplib] jdbc:mysql://64.79.109.220/user/
    2011-08-29 14:43:03 [SEVERE] [Qstat][MySQL-pplib] Could not be resolved because of an SQL Exception: Access denied for user 'user'@'%' to database 'user/'.
    

    This post has been edited 1 time. It was last edited by alexh Aug 29, 2011.
  14. Offline

    68x

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    That's a pretty easy error to resolve. Does the MySQL user have access to the database?
  15. Offline

    alexh

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    im not an MySQL novice just a java novice, and yes it does, for the purposes of this test it does.
    ive tried it on a web host and local neither works, exact same error

    shouldnt % be localhost or my servers ip?
    Fixed the % that was my fault but still same error just with localhost instead of % :p

    GOOD NEWS!
    Code:
    [SEVERE] [Qstat] [MySQL] SQLException: Communications link failure
    The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
    same error now

    This post has been edited 3 times. It was last edited by alexh Aug 29, 2011.
  16. Offline

    68x

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    Join the club :(.
  17. Offline

    alexh

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    at least i got the other two sorted but this looks like a bug in the library, im going though the code but, if i knew what to do i wouldn't need this library.

    Btw im really glad someone took it over its proably faster than me learning all the connectivity stuff my self

    This post has been edited 1 time. It was last edited by alexh Aug 29, 2011.
  18. Offline

    PatPeter

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    I assume you are speaking of insertQuery() and updateQuery(), correct? These were redundant methods that were removed and merged into simply query(). If you are using query(), please give me an example of a query you are trying to run that is failing. For more information view this post:

    http://forums.bukkit.org/threads/in...ql-tutorial-library.17034/page-11#post-623487

    I would love to help you guys, but you aren't giving me much (or anything...) to work with. EDIT: I just noticed 68x's private message, thanks 68.

    This post has been edited 9 times. It was last edited by PatPeter Aug 30, 2011.
  19. Offline

    68x

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    Sent you the missing information.
  20. Offline

    PatPeter

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    I was fairly sure remote servers were not using the default MySQL port, even if they might be, I changed it to add more functionality:

    PHP:
    public MySQL mysql;
    ...
    this.mysql = new MySQL(this.log,"[Plugin Prefix]",hostname,database,username,password);
    to:

    PHP:
    public MySQL mysql;
    ...
    this.mysql = new MySQL(this.log,"[Plugin Prefix]",hostname,port,database,username,password);
    Which plugins? I'll look at their source and see if they're using jdbc: or not.
  21. Offline

    68x

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    LWC, iConomy, KiwiAdmin and a few others. They can connect to the MySQL server just fine,
  22. Offline

    Mr Smith

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    Nope, when I using query() function and insert or update query I get this:
    Code:
    Error in SQL query: Can not issue data manipulation statements with executeQuery().
  23. Offline

    alexh

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    1. What operating system, Windows, Linux, or Mac? Win7 on home server and CentOS linux on public server
    2. What version of MySQL are you using? MySQL 5.5.15 & PhpMyAdmin 3.4.3.2
    3. What is your hostname and database name? Localhost and thecrow
    4. What collation are you using? UTF8_GENERAL_CI
    5. Are you using the standard MySQL port? 8080
    Code:
    2011-08-30 14:07:43 [SEVERE] [Qstat-mysqllib][MySQL] jdbc:mysql://localhost:8080/thecrow
    2011-08-30 14:07:43 [SEVERE] [Qstat-mysqllib][MySQL] Could not be resolved because of an SQL Exception: Communications link failure
    
    The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server..
    on this server i only run essentials and worldedit
  24. Offline

    PatPeter

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    Thanks for the error message, that let me fix it. In an oversight I forgot to make query() compatible with insert, update, and delete.

    I think I fixed this. Basically a number of functions including query() were copying this.connection to a new connection variable that I believe either lost the connection or lost information.

    I tested MySQL on a Windows 7 localhost and it worked the same as it did before, so I cannot tell with complete certainty that it will work now. I'm still working on getting my database in a shape that I can port it to my Linux box (relations aren't working and MySQL will block the import).

    This post has been edited 1 time. It was last edited by PatPeter Aug 31, 2011.
    Mr Smith likes this.
  25. Offline

    alexh

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    trying the fix now, ill let ya know if it works :)
    EDIT: not working might change to http post requests

    This post has been edited 1 time. It was last edited by alexh Aug 31, 2011.
  26. Offline

    Mika56

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    Hi,

    Will you add prepared queries ?
    I had already changed the MySQL files from alta189, but as you changed a lot of things, I am not very sure I'll be able to do it a second time.

    In DatabaseHandler, code was :
    Code:
    public PreparedStatement prepare(String sql) throws MalformedURLException, InstantiationException, IllegalAccessException
    {
        try
        {
            Connection connection = getConnection();
            PreparedStatement = connection.prepareStatement(sql);
            return statement;
        } catch(SQLException ex) {
            if(!ex.toString().contains("not return ResultSet")) core.writeError("Error at SQL PREPARE Query : " + ex, false);
        }
        return null;
    }
    Mika.

    Edit : I finally did it by myslef. Here's the code :
    Code:
    	public PreparedStatement prepare(String query)
    	throws MalformedURLException, InstantiationException, IllegalAccessException, SQLException {
    		PreparedStatement result = null;
    		this.connection = this.getConnection();
    		result = connection.prepareStatement(query);
    		return result;
    	}
    Tested with MySQL and SQLite.
    Could you please add this to your release ? :)

    Mika.

    This post has been edited 1 time. It was last edited by Mika56 Aug 31, 2011.
  27. Offline

    alexh

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    its now something like
    Code:
        public MySQL mySQL;
    
        public void sqlcon(){
            mySQL = new lib.PatPeter.SQLibrary.MySQL(log,"[Qstat-mysqllib]","localhost","8080","db","user","pass");
    
             try
                {
                    mySQL.open();
                }
                catch(Exception e)
                {
                    e.printStackTrace();
                }
        }
        
    but im not sure im getting errors, let me know how you do with it.
  28. Offline

    HSAR

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    Thanks for taking over this!

    I'm currently having trouble getting started, actually. The JavaDocs are useful, but of limited use when I can't really find out the commands in the first place.

    Could I trouble one of you guys to write-up some sample code or a tutorial? Something simple would do, stretching maybe as far as a sample query? It would be much appreciated.

    This post has been edited 1 time. It was last edited by HSAR Sep 1, 2011.
  29. Offline

    alexh

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    i would but i cant get mine to connect :p
  30. Offline

    PatPeter

    dev.bukkit.org profile:
    CFUSERNAME
    My Plugins (CFCOUNT)
    Minecraft account:
    MCUSERNAME
    How do you mean HTTP POST requests?

    I'll add it next release. If I don't, yell at me.

    PHP:
            public static MySQL mysql;
    ...
            
    mysql = new MySQL(SupplyAndDemand.log,
                              
    SupplyAndDemand.PREFIX,
                              
    "localhost",
                              
    "3306",
                              
    "minecraft",
                              
    "root",
                              
    "password1"); // "It's so easy it's impossible to guess!" -RvB
            
    try {
                
    mysql.open();
            } catch (
    Exception e) {
                
    log.info(e.getMessage());
            }
    PHP:
        try {
                
    rs SupplyAndDemand.mysql.query("SELECT `id`, `data`, `name` FROM `sad_commodity` WHERE `name` = \"" name "\"");

                while (
    rs.next()) {
                    
    System.out.println(rs.getInt("id") + " " rs.getInt("data") + " " rs.getString("name"));
                    if (
    rs.getString("name").equals(name)) {
                        
    result[0] = rs.getInt("id");
                        
    result[1] = rs.getInt("data");
                        return 
    result;
                    }
                }
                
    //success = false;
            
    } catch (MalformedURLException e) {
                
    //success = false;
                
    SupplyAndDemand.log.info("MalformedURLException: " e.getMessage());
                
    //e.printStackTrace();
            
    } catch (InstantiationException e) {
                
    //success = false;
                
    SupplyAndDemand.log.info("InstantiationException: " e.getMessage());
                
    //e.printStackTrace();
            
    } catch (IllegalAccessException e) {
                
    //success = false;
                
    SupplyAndDemand.log.info("IllegalAccessException: " e.getMessage());
                
    //e.printStackTrace();
            
    } catch (SQLException e) {
                
    //success = false;
                
    SupplyAndDemand.log.info("SQLException: " e.getMessage());
                
    //e.printStackTrace();
            
    }
    Works perfectly for me.

    Speak of the devil I actually had been. I have been rewriting the test plugin, "Owner", from the first incarnation of this library. Unlike the previous plugin, this will have an example for every single method in both MySQL and SQLite.

    This post has been edited 4 times. It was last edited by PatPeter Sep 2, 2011.

Share This Page