[LIB] SQLibrary - Database wrappers for all database engines

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

  1. Offline

    alta189

    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.
  2. GREAT! will you be producing any documentation for this?
    im developing a plugin that will use this of alta189's old version
     
  3. 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:
    1. public MySQL mysql;
    2. ...
    3. this.mysql = new MySQL(this.log,"[Plugin Prefix]",hostname,database,username,password);
    Instead of:

    PHP:
    1. public mysqlCore ms;
    2. ...
    3. this.ms = new mysqlCore(...);
     
  4. :) 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
     
  5. 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.
     
  6. Offline

    Mr Smith

    When I use update or insert query I get this error in console:
    Code (Text):
    1. 17:02:41 [SEVERE] Error occurred while disabling Core v0.1 (Is it up to date?): null
    2. java.lang.NullPointerException
    3.         at lib.PatPeter.SQLibrary.MySQL.writeError(MySQL.java:52)
    4.         at lib.PatPeter.SQLibrary.MySQL.query(MySQL.java:123)
     
  7. 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.
     
  8. I am using this to declare/initialize my variables:
    Code (Text):
    1. public MySQL mySQL;
    2.  
    3. 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 (Text):
    1.  
    2. 2011-08-28 21:07:19 [SEVERE] [FFD] [MySQL] SQLException: Communications link failure
    3.  
    4. 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 (Text):
    1.         try
    2.         {
    3.             mySQL.open();
    4.         }
    5.         catch(Exception e)
    6.         {
    7.             e.printStackTrace();
    8.         }
     
  9. 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.
     
  10. ok setting up the lastest version, and about to implement
     
  11. Offline

    Mr Smith

    It gives me errors. By the way, update and insert query still does not work.
     
  12. the server returns this error,
    Code (Text):
    1. >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 (Text):
    1. 2011-08-29 14:43:03 [SEVERE] [Qstat][MySQL-pplib] jdbc:mysql://64.79.109.220/user/
    2. 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/'.
    3.  
     
  13. That's a pretty easy error to resolve. Does the MySQL user have access to the database?
     
  14. 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 (Text):
    1. [SEVERE] [Qstat] [MySQL] SQLException: Communications link failure
    2. 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
     
  15. Join the club :(.
     
  16. 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
     
  17. 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.
     
  18. Sent you the missing information.
     
  19. 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:
    1. public MySQL mysql;
    2. ...
    3. this.mysql = new MySQL(this.log,"[Plugin Prefix]",hostname,database,username,password);
    to:

    PHP:
    1. public MySQL mysql;
    2. ...
    3. 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.
     
  20. LWC, iConomy, KiwiAdmin and a few others. They can connect to the MySQL server just fine,
     
  21. Offline

    Mr Smith

    Nope, when I using query() function and insert or update query I get this:
    Code (Text):
    1. Error in SQL query: Can not issue data manipulation statements with executeQuery().
     
    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 (Text):
    1. 2011-08-30 14:07:43 [SEVERE] [Qstat-mysqllib][MySQL] jdbc:mysql://localhost:8080/thecrow
    2. 2011-08-30 14:07:43 [SEVERE] [Qstat-mysqllib][MySQL] Could not be resolved because of an SQL Exception: Communications link failure
    3.  
    4. 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
     
  22. 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).
     
    Mr Smith likes this.
  23. trying the fix now, ill let ya know if it works :)
    EDIT: not working might change to http post requests
     
  24. Offline

    Mika56

    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 (Text):
    1. public PreparedStatement prepare(String sql) throws MalformedURLException, InstantiationException, IllegalAccessException
    2. {
    3.     try
    4.     {
    5.         Connection connection = getConnection();
    6.         PreparedStatement = connection.prepareStatement(sql);
    7.         return statement;
    8.     } catch(SQLException ex) {
    9.         if(!ex.toString().contains("not return ResultSet")) core.writeError("Error at SQL PREPARE Query : " + ex, false);
    10.     }
    11.     return null;
    12. }
    Mika.

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

    Mika.
     
  25. its now something like
    Code (Text):
    1.     public MySQL mySQL;
    2.  
    3.     public void sqlcon(){
    4.         mySQL = new lib.PatPeter.SQLibrary.MySQL(log,"[Qstat-mysqllib]","localhost","8080","db","user","pass");
    5.  
    6.          try
    7.             {
    8.                 mySQL.open();
    9.             }
    10.             catch(Exception e)
    11.             {
    12.                 e.printStackTrace();
    13.             }
    14.     }
    15.    
    but im not sure im getting errors, let me know how you do with it.
     
  26. Offline

    HSAR

    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.
     
  27. i would but i cant get mine to connect :p
     
  28. How do you mean HTTP POST requests?

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

    PHP:
    1.         public static MySQL mysql;
    2. ...
    3.         mysql = new MySQL(SupplyAndDemand.log,
    4.                           SupplyAndDemand.PREFIX,
    5.                           "localhost",
    6.                           "3306",
    7.                           "minecraft",
    8.                           "root",
    9.                           "password1"); // "It's so easy it's impossible to guess!" -RvB
    10.         try {
    11.             mysql.open();
    12.         } catch (Exception e) {
    13.             log.info(e.getMessage());
    14.         }
    PHP:
    1.     try {
    2.             rs = SupplyAndDemand.mysql.query("SELECT `id`, `data`, `name` FROM `sad_commodity` WHERE `name` = \"" + name + "\"");
    3.  
    4.             while (rs.next()) {
    5.                 System.out.println(rs.getInt("id") + " " + rs.getInt("data") + " " + rs.getString("name"));
    6.                 if (rs.getString("name").equals(name)) {
    7.                     result[0] = rs.getInt("id");
    8.                     result[1] = rs.getInt("data");
    9.                     return result;
    10.                 }
    11.             }
    12.             //success = false;
    13.         } catch (MalformedURLException e) {
    14.             //success = false;
    15.             SupplyAndDemand.log.info("MalformedURLException: " + e.getMessage());
    16.             //e.printStackTrace();
    17.         } catch (InstantiationException e) {
    18.             //success = false;
    19.             SupplyAndDemand.log.info("InstantiationException: " + e.getMessage());
    20.             //e.printStackTrace();
    21.         } catch (IllegalAccessException e) {
    22.             //success = false;
    23.             SupplyAndDemand.log.info("IllegalAccessException: " + e.getMessage());
    24.             //e.printStackTrace();
    25.         } catch (SQLException e) {
    26.             //success = false;
    27.             SupplyAndDemand.log.info("SQLException: " + e.getMessage());
    28.             //e.printStackTrace();
    29.         }
    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.
     

Share This Page