[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. Correct me if i'm wrong...
    there is a rs.close(); in my checkBlock? If this isn't right, how must it be done?
     
  2. Offline

    macintosh264

    How can I get the id of the last inserted row without getting the error

    Error in SQL query: cannot commit transaction - SQL statements in progress

    that occurs when I do any query immediately after an INSERT query. I am sure someone has had this problem before, and I wonder what they did to solve it.
     
  3. Offline

    PatPeter

    Hmm... I didn't see those the first time I read your post.

    query() returns null on errors so you have to explicitly check for nulls:

    Code:
    if (rs != null)
        rs.close();
    else
        System.out.println("Last query returned a null ResultSet. An error exists either in the query or the database.");
    You have to close the SQL statement in process and then do another query to find the last ID inserted.

    EDIT: SQLite is using executeQuery() instead of executeUpdate() for data manipulation (exempt SELECT). I'll commit a fix.
     
  4. Offline

    dillyg10

    Well, after using MySQL for a few weeks now, I've actually made a lot of little shortcut methods for querying ;P. Does anyone want them? If so I'll post a little dl link for it.. nothin to much just stuff like getting multiple strings, getting multiple ints etc etc.
     
  5. Offline

    PatPeter

    Yes, very much please. If it's possible I want to add more functionality to the library than exists currently. I've been thinking of a concise table generator for the MySQL class.
     
  6. Offline

    dillyg10

    Ok, I'll put it up in a bit, have some stuff to do :p.
     
  7. Offline

    dillyg10

    Ok, here it is
    <Edit by Moderator: Redacted bit url>
    Included pretty must the starter of a MySQL project :). Enjoy, Commented pretty well, post feedback in comments!
     
    Last edited by a moderator: Feb 21, 2017
  8. Offline

    xDrapor

  9. Offline

    FurmigaHumana

    For exemple:

    String query = "DELETE FROM blocks WHERE x = " + block.getX() + " AND y = " + block.getY() + " AND z = " + block.getZ() + ";";

    This doesn't work, what is wrong?
     
  10. Offline

    messageofdeath

    Can you help me i cant seem to get anything from mysql. I'm able to connect/write to it but not read from it please help. Your reading tutorial did not help me. Please help if you can.
     
  11. Offline

    xDrapor

    Same here.
     
  12. Offline

    Neodork

    Please drop your error log
     
  13. Offline

    xDrapor

    I removed it awhile back to release a bug fix.. let me see what I can do though..
     
  14. Offline

    FurmigaHumana

    "[SQLite] Please close your previous ResultSet to run the query:"

    Ideias?
     
  15. Offline

    messageofdeath

    How do i get a name from the db.
     
  16. Offline

    Neodork

    Meaning if you are NOT able to load stuff with this, from a single 1x1 table:

    You probably got errors in you query:
    "Select playername FROM databasename WHERE playername = '"+playername+"'"
     
  17. Offline

    zolcos

    I added MySQL support to my plugin via SQLibrary. Now, server admins using my plugin complain that my plugin is starting a ton of connections and not closing them, clogging up the MySQL server or something. I am already closing all my ResultSets like I should, so what can I do to fix this?
     
  18. Offline

    Neodork

    Using mySQL in your project is very simple:

    Just use:

    Code:java
    1.  
    2. public MySQL mysql;
    3.  
    4. onEnable:
    5. mysql.open();
    6.  


    Code:java
    1.  
    2. onDisable:
    3. mysql.close();


    Just the 2 of those more will mean repeating the connection more and more.
     
  19. Offline

    zolcos

    Neodork

    Ah, I was confused at first because it totally didn't work that way in the version I was using. But after I updated to the latest version, the mysql class now keeps track of its connection so the code you posted is of course the right way to use it. Thanks.

    Btw, the way I use this lib is by creating an object of the superclass Database and putting either a sqlite or mysql object in there based on what the user put in my plugin's configuration. But, it doesn't look like this lib was really intended to be used that way because none of the methods in the Database class are public?
    I did it by making them public as well as some other customizations to facilitate this usage. I'd fork and submit a pull request but I wanted to make sure it wasn't an intentional limitation?
     
  20. Offline

    Neodork

    Haven't had anything alike, I just drop it into my plugin and use the .query ^^ the guy did all the other work!
     
  21. Offline

    dillyg10

    BTW out of curiosity, is any1 using the MySQLHandler thing? If so, how is it working out for every1?
     
  22. Offline

    Neodork

    I haven't had any limitation using .query so no..
     
  23. Offline

    zolcos

    It doesn't seem to handle mysql connections very well. It keeps a single connection for all queries (which is a good thing) but never checks if this connection times out, so my users are reporting that after a while my plugin just stops working and they have to restart their bukkit server because the mysql connection was lost. SQLibrary should automatically make a new connection when the old one expires.

    Btw, is this lib inactive? When was the last time the dev responded?
     
  24. Offline

    Neodork

    Have had no such issue's yet, try putting a check in a repeated task.. The developer updates it when needed.
     
  25. Offline

    Serpent36

  26. Offline

    Neodork

    This uses the bukkit default MySQL and made a nice api from it. Instead of connection/query setting yourself you will use this and it's functions. So you will just use these extra libs, add them to your project and enjoy.

    when added just follow the steps serpent pointed at.

     
  27. Offline

    inbredfreak

    Just a heads up for Mac users using MAMP (Pro)

    If your plugin is failing to connect to your MySQL server then test the connection in Terminal with telnet:

    Code:
    #> telnet <server ip> <port>
    ie:

    telnet localhost 3306

    if it fails to connect even if its local ip then go to the server tab of MAMP, then to the MySQL tab and un-tick 'Allow local access only' and restart your MAMP server.

    For some reason allowing remote access permits a local craftbukkit server plugin to connect to a local MySQL database..



    :confused:
     
  28. Offline

    HariboPenguin

    Hey,

    I keep on getting this coming up when i try and make a report (It works just this keeps coming up for each report).

    Code:
    16:15:38 [WARNING] uReport[SQLite] Error at SQL Query: query does not return ResultSet
    It is an INSERT statement so shouldn't return a result set.

    Does anybody know why this is happening or how to stop this happening?

    Here is the code that handles the report:

    Code:
    public boolean sendReport(String reporter, String reportedPlayer, String reason, String textDate, Integer reportID) throws SQLException {
            String query = "INSERT INTO  `reports` (`reportID` ,`reportdate` ,`reporter` ,`reportedplayer` ,`reason`) VALUES ('" + reportID + "', '" + textDate + "',  '" + reporter + "',  '" + reportedPlayer + "',  '" + reason + "')";
            plugin.dbManage.query(query);
            return true;
     
        }
     
    Jake111119 likes this.
  29. Offline

    sk8r2k11

    I am also getting this problem... Any ideas? Using a very similar query to the one that HariboPenguin states in his post, and I get the same error spam in console. Thanks in advance.
    ~Sk8
     
    Jake111119 likes this.
  30. Offline

    wirher

    How to keep connection opened?
    I check everytime I do something
    but it doesn't renew connection so I get NPE errors after few minutes because of lost connection.
    EDIT:
    So I have looked at the lib code and I see that "checkConnection" isn't for checking if connection is up. This is for checking if "connection" object is created. So...
    I can do mysql.open() before each query but isn't there any more clever way of doing it? Like check if it is connected.

    EDIT2:
    No, opening connection on every query is lagging server. But I have found another solution. Added "?autoReconnect=true" to "url" in library's mysql class. I'll see if it works after 8 hours.

    EDIT3:
    Didn't help.. Uhmm. Acutally when connection is lost and I try to query connection is opened after query error. I could do query again in exception part but I don't like so trickish methods.

    Now I just use async thread to query database every "x" (for test 30 seconds) with query "SELECT 1". Keeps my connection alive and don't lag server. I think this is one of the best solution for my problem. I'm learning how to code things so forgive me noobish coding style.
     
Thread Status:
Not open for further replies.

Share This Page