[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

    okami35

    Thank you for your answer, but i think my error comes from the library, you don't have methods for INSERT. The method "query" must return a resultset...
    A new example with the latest version would by very apreciated.
     
  2. Hi,
    Is it possible to get a list of the results returning from the database?
    Something like this returning a list with the results
    PHP:
    SELECT FROM database WHERE world " + world + ";

    The coordinates are stored seperatly in the database: X, Y and Z all have a seperate column.
    Is this possible to get?
     
  3. Offline

    herghost

    Can someone provide a example of there connection code for the MySQL?

    Thanks
     
  4. Offline

    Aleksander

    Does this library support num rows like mysql_num_rows()? I really need this function.

    EDIT: I might have created a way to, try this snippet for the guys that need it.
    Code:
    	public int numRows(ResultSet query)
    	{
    		try
    		{
    			if(sqlite.checkConnection())
    			{
    				query.last();
    				int rowCount = query.getRow();
    				return rowCount;
    			}
    		}
    		catch (SQLException e)
    		{
    			e.printStackTrace();
    		}
    		return 0;
    	}
    
    EDIT2: No, that doesn't work, hoping for a better response.
     
  5. Offline

    untergrundbiber

    I have a porblem with SQLite an UPDATE-Query

    Code:

    Code:
    
    String query5 = "UPDATE unlock SET unlock = 1 ,unlockname = '" + player.getName().toLowerCase() + "' ,unlocktime = '" + plugin.getDateTime() + "' WHERE unlock.id = " + id + ";";
    
    bcguestunlock.sqlite.query(query5);
    
    
    Github

    When i throw the query the server hang with this message:

    [SQLite] Error at SQL Query: query does not return ResultSet

    Same error with other queries without ResultSet like DELETE

    With the old lib from alta189 and updateQuery it works perfectly

    Anyone can help?
     
  6. Offline

    PatPeter

    I've been trying to create a universal method for query() for a while, are you sure it's the most recent version of the library? I tried a trick to stop it from doing this.

    http://forums.bukkit.org/threads/lib-tut-mysql-sqlite-bukkit-drivers.33849/#post-641927

    This is on my list of things to do but I have nowhere near enough time for it right now.

    As I said above, I've been trying to create a universal query() method just as I made an interface for all databases. I'll try to look into it this weekend and may have to abandon that goal.
     
  7. Offline

    herghost

    Download link is dead?
     
  8. Offline

    Lolmewn

    Noos! The Attachement cannot be shown at this time..
    It's not working :O (The download, I mean)
     
  9. Offline

    PatPeter

    Download links are working for me.
     
  10. Offline

    Lolmewn

    seems to work again, yes.
     
  11. Offline

    Nick Ippoliti

    Can anyone point me in the direction for documentation for this?
     
  12. Offline

    PatPeter

    Right now it's only in the Javadoc and the examples in the previous pages (look at the bottom of page 1).

    I'll be working on this a lot more come mid-December.
     
  13. Offline

    Nick Ippoliti

    EDIT: Nevermind, just a forgotten Parentheses...
     
  14. Offline

    digga

    @ Aleksander

    Code:
    public int countRows(ResultSet rs) {
                int rowCount = 0;
                try {
                    if (rs == null) return 0;
                    rs.next();
                    rowCount = rs.getInt(1);
                    rs.close();
                } catch (SQLException ex) {
                    Logger.getLogger(MySQL.class.getName()).log(Level.SEVERE, null, ex);
                }
                return rowCount;
            }
    I use this funtion;)
     
  15. Offline

    Nick Ippoliti

    Bug to report. Seems that if a query fails, the server crashes without warning. For example:
    This is the table:
    Code:
    [id INTEGER PRIMARY KEY] [TestText text]
    1                        'test'
    2                        'Bob'
    and if I used
    Code:
    dbManager.query("SELECT TestText as testvar where TestText = 'Bob'")
    ** assuming dbManager is an SQLite object.
    there would be no error and no random crash. However, if I used:
    Code:
    dbManager.query("SELECT TestText as testvar where TestText = 'Joseph'")
    the server crashes without logging or any warning. Maybe it's just my newness to SQL, but I've checked my query and it runs perfectly unless there is no value returned ( value does not exist in table), and then the server crashes. Can anyone confirm?
     
  16. Offline

    PatPeter

    @Nick Ippoliti I'll look into it when I have free time Nick. Generally I work on this at the same time as my plugin, so if I don't have any time to work on that you know I have no time to work on this.
     
  17. Offline

    Nick Ippoliti

    Thanks. If you thought I sounded demanding, I wasn't trying to be, just giving you a heads up on an issue.
     
  18. Offline

    SwearWord

    Just a policy we have here, make sure your files have a non-attachment download link.
     
  19. Offline

    PatPeter

    Naw, I love when people give me a heads up.

    Really? I never knew, thanks Swear. I'll get the next version up on MediaFire and remove the older version at the next opportunity. Are the guidelines posted anywhere?
     
  20. Offline

    SwearWord

    You can use MediaFire if you want, but I'd rather you use something that allows direct linking. I don't know why this isn't on github, it automatically will package the source into a zip. Perfect for this.
     
  21. Offline

    Celtic Minstrel

    Is the policy because you have to be registered to download attachments? Because if so, I wholeheartedly approve.
     
  22. Offline

    DomovoiButler

  23. Offline

    Ghost1227

    Following your 'How to Install' section, I am getting a 'The import lib.PatPeter cannot be resolved' error.
     
  24. Offline

    DomovoiButler

    not being mean here, but you can make your own library
    if you only want to use sqlite you dont have to have sql library
    study this http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC
     
  25. Offline

    PatPeter

    Create a folder called 'lib' in the root folder of your plugin/src directory (for Eclipse), followed by a folder called 'PatPeter' inside of the 'lib' folder.

    Domo, the point of this library is to make a shortcut to that method. Please refrain deflecting users away from this library.
     
  26. Offline

    Nick Ippoliti

    Seems that the server crash is fixed. May have been bukkit updates, or it may have been the try{ statement I added to the SQLite.java. The change is on my plugin's Github page. The change is on line 132. EDIT: seems I have multiple lib folders. The one I am referring to is the one under ~/src/lib/PatPeter/SQLibrary/SQLite.java
     
  27. Offline

    PatPeter

    Good. There's also a problem where you can get a "Too many connections" error but I think that's only MySQL. I fixed it but I'm working on putting my code on GitHub so I haven't uploaded it yet. If anyone runs into it I'll post the fix asap.
     
  28. Offline

    wouter0100

    When i'm trying to do this query:
    ("UPDATE users SET time = '9999' WHERE name = '"+ player.getName() +"'");
    my server crash.
    SQLite and this query:
    ("INSERT INTO users (name, time) VALUES ('" + paramPlayer +"', '" + i + "')");
    giving an MySQL error, that it dont return an ResultSet.
    Both sqlite.
     
  29. Offline

    PatPeter

    I haven't touched the SQLite side of the library in a while; I'll get to it when I can thanks for the report.
     
  30. Offline

    mxt3

    If I am right, this is what you did:
    PHP:
    try {
                
    connection this.open();
                
    statement connection.createStatement();

                switch (
    this.getStatement(query)) {
                    case 
    SELECT:
                        
    result statement.executeQuery(query);
                        return 
    result;

                    default:
                        
    statement.executeQuery(query);
                        return 
    result;
                }
            } catch (
    SQLException ex) {
                if (
    ex.getMessage().toLowerCase().contains("locking") || ex.getMessage().toLowerCase().contains("locked")) {
                    return 
    retryResult(query); // Happens when locked
                    //this.writeError("",false);
                
    } else {
                    
    this.writeError("Error at SQL Query: " ex.getMessage(), false);
                }

            }
    When the database is locked, it will go to retryResult:
    PHP:
    while (!passed /*&& count <= 5 */) { //Comment = later adjustment to prevent endless loop
                
    try {
                    
    //count++;
                    
    statement connection.createStatement();
                    
    result statement.executeQuery(query);
                    
    passed true;
                    return 
    result;
                } catch (
    SQLException ex) {
                    if (
    ex.getMessage().toLowerCase().contains("locking") || ex.getMessage().toLowerCase().contains("locked")) {
                        
    passed false;
                       
    // this.writeError("Attempt " + (count + 1) + " failed, database locked...", false);
                    
    } else {
                        
    this.writeError("Error at SQL Query: " ex.getMessage(), false);
                    }
                }
    I have just learned SQLite yesterday, so correct me if I am wrong, but I suppose that you (or PatPeter, I don't know) did this because the database could react slowly sometimes. The problem is that if the database is locked for some other reason, you will throw the server in a endless loop. It will not suddenly become unlocked by keeping on trying.
    Locking the database is quite simple if you are a SQL noob like me :p. My plugin kept crashing when I was trying the following code:
    PHP:
    ResultSet count_result SlimeGlue.dbManager.query("SELECT Count(P_id) AS Count FROM " SlimeGlue.db_table_name);
            
    String query "INSERT INTO " SlimeGlue.db_table_name +
                    
    " VALUES ( " +
                    
    count_result.getInt("Count") + ", " +
                    
    block.getX() + ", " +
                    
    block.getY() + ", " +
                    
    block.getZ() + ", " +
                    
    "'Test'" ", '" +
                    
    owner.getName() + "')";
            
    count_result.close(); // Prevents Lock of Database!!!!
            
    SlimeGlue.dbManager.query(query);
    I forgot the count_result.close(), simply because I didn't knew that this was necessary, and so my server crashed instead of giving a SQL database locked error. I lost hours at trying to find the bug, until I looked up in the library, found the loop, disabled it, googled the error, and found the solution: http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked. Frustrating :p
     
Thread Status:
Not open for further replies.

Share This Page