[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

    HSAR

    Sounds like just the thing I was looking for.
     
  2. Offline

    alexh

    here is my current code cant see anything wrong with it or my database(s)
    Show Spoiler
    Show Spoiler

    Code:
        public void sqlcon(){[/S]
    [S]        this.log.info(this.logPrefix + "sqlloading");[/S]
    
    [S]        mysql = new MySQL(log,"[Qstat-mysqllib]","127.0.0.1","8080","user","user","pass");[/S]
    
    [S]        this.log.info(this.logPrefix + "attempting to connect");[/S]
    
    [S]        try {[/S]
    [S]            this.log.info(this.logPrefix + "trying");[/S]
    [S]            mysql.open();[/S]
    [S]            this.log.info(this.logPrefix + "try finished");[/S]
    [S]        } catch (Exception e) {[/S]
    [S]            log.info(e.getMessage());[/S]
    [S]        }[/S]
    [S]        this.log.info(this.logPrefix + "sqlloaded");[/S]
    [S]    }



    the server log outputs:
    Show Spoiler
    Show Spoiler

    Code:
    2011-09-02 21:51:40 [INFO] [Qstat] sqlloading[/S]
    [S]2011-09-02 21:51:40 [INFO] [Qstat] attempting to connect[/S]
    [S]2011-09-02 21:51:40 [INFO] [Qstat] trying[/S]
    [S]2011-09-02 21:56:41 [SEVERE] [Qstat-mysqllib][MySQL] jdbc:mysql://127.0.0.1:8080/thecrow[/S]
    [S]2011-09-02 21:56:41 [SEVERE] [Qstat-mysqllib][MySQL] Could not be resolved because of an SQL Exception: Communications link failure[/S]
    
    [S]The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server..[/S]
    [S]2011-09-02 21:56:41 [INFO] [Qstat] try finished[/S]
    [S]2011-09-02 21:56:41 [INFO] [Qstat] sqlloaded[/S]
    [S]


    i have fix it apparently my sql and phpmyadmin config files were wrong on both servers ITS ALL FIXED! time to go wild!
    [/CODE][/S]
     
  3. Offline

    Mika56

    I really think this comes from your server...
    Can you connect to it from an other software ? (PHP script, Navicat, etc...).
    Please try to ping yourself (Windows + R, cmd, ping -c 100 127.0.0.1) and verify you get no error.

    Mika.
     
  4. Offline

    alexh

    its all sorted some weird config error on both my server and my webhosts, phpmyadmin and sql settings, all sorted now. Qstat is ago !
    came to me to check when a freind of mine couldnt get his home dev server working, silly reli
     
  5. Offline

    PatPeter

    Glad you got it sorted. Now we just have to check in with Mr. Smith and 68x.
     
  6. Offline

    nil0bject

    Please use ActiveRecord from Ruby on Rails. It is less specific, therefore you can add many database backends, with just one ActiveRecord front end. This makes it much easier to learn, and easier for a server admin to migrate to a different DBMS.

    THANKYOU!!!
     
  7. Offline

    PatPeter

    I don't think this is possible with the limitations of Java, as Java has no references. Although I haven't read that page and have merely skimmed it.
     
  8. Offline

    codename_B

    Anyone willing to help me add this into bPermissions?
     
  9. Offline

    PatPeter

    Always, what do you need help with?
     
  10. Offline

    HSAR

    How's the tutorial going?
     
  11. Offline

    codename_B

    Storing arrays in mysql - as in a String[]
     
  12. Offline

    fritz

    I suspect that this is a really dumb question, but what is the correct way to run an INSERT with sqlite using this library? I constantly get a warning that no resultset is being returned when I use query, but I'm not expecting a resultset.
     
  13. Offline

    Zonr_0

    Excellent work on this plugin, even as a moderately experienced programmer, this plugin helps take the edge off of the learning curve for databases and SQL.

    My only two issues are what Fritz above has stated about seemingly extraneous warnings about no result set being returned, and an apparent lack of a way to sanitize input. From what I can tell, if you were just using the java sql library, you could use Prepared Statements to make sure somebody doesn't drop your tables or that you don't end up with a user who accidently creates an invalid query because of some pretty common punctuation. There doesn't seem to be any kind of way to duplicate that functionality as from what I can tell, queries only accept strings, and not prepared statements. Perhaps a simple overloaded method would fix this? For queries without direct user textual input, this is not an issue and strings are a great way to manipulate the database, however in any plugin where users enter text that gets placed in the database (for example, a mail plugin, or in my case a profile plugin), this becomes a severe issue.
     
  14. Offline

    fritz

    I also seem to be having trouble with database locking using SQLite. I don't know enough about SQLite to know if it is this library or my own code, but my database inserts are fairly simple. I even went as far as closing and reopening the connection before every query and I'm still running into SQLite errors that crash my server with some sort of endless loop of query attempts even though none of my queries are in loops or configured on my end to retry.

    So, I looked through the library and found out that the query method automatically retries forever. This was crashing my server because database lock was not always being properly released. I seem to have fixed this by wrapping the createStatement line in SQLite.java with autoCommit settings.

    this.connection.setAutoCommit(false);
    statement = this.connection.createStatement();
    this.connection.setAutoCommit(true);

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

    PatPeter

    You have to make a 1:M table in your database that stores each value of the string based on a primary key.

    Copy the error. It sounds like an issue I fixed in MySQL and forgot to fix in SQLite.

    I actually already made that change in the next version, I just haven't had time to test or add a few finishing touches on it before I submit it. Basically I'm thinking of adding a second parameter to query() that would be named 'secure' internally, if true, use prepared statements, if not, don't.

    Oh. Yeah, that's retryResult(), it forces SQLite into an infinite loop. I actually disabled it a few versions ago because of this, but then I added it back because I went back to the original library and thought it had a limit. Nope.

    Thanks, I'll check that and I'll check retryResult to make sure neither are causing infinite loops.

    ----

    Hopefully I will be able to post the next version tomorrow, but I have more work than I've had in a long while.
     
  16. Offline

    Celtic Minstrel

    Sorry, how is this any better than the java.sql package? It seems worse to me, and the java.sql package isn't even that great to begin with.

    Also, I think this is probably a step backwards:
    Or at least, retaining specialized functions for updateQuery, insertQuery, etc that take arguments and build an SQL statement would I think be helpful.

    Ugh, why would you want to base anything on the PHP MySQL module... and the outdated one at that, not even the MySQLi one, though that's not really much better. If you want to use anything PHP as a model, I'd point at PDO. There's also the database abstraction layer used by phpBB, which isn't as nice as PDO in my opinion but does have some nice features that it lacks. That said, I'm not really sure why you'd want to use PHP as a model in the first place.
     
  17. Offline

    fritz

    I get this on every basic update like INSERT:
    Code:
    [WARNING] SaveRegions[SQLite] Error at SQL Query: query does not return ResultSet
    (In case you can't tell, SaveRegions is the name of the plugin I'm currently working on)
     
  18. Offline

    Celtic Minstrel

    Uh, that shouldn't be an error...
     
  19. Offline

    dark navi

    Hey guys, I was wondering if I could adjust how long it takes a connection to timeout upon a mysql.Open(). Any h elp?

    Are you trying to assign a resultset the result of an insert query?

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

    fritz

    The library is, yes. The only query method expects a result set. There needs to be some way of submitting a query that doesn't return something, that is exactly the problem.
     
    alta189 likes this.
  21. Offline

    alta189

    That was how I originally wrote it
     
  22. Offline

    Lolmewn Retired Staff

    With Alta's version I'm getting errors.
    Code:
    [SEVERE] [Skillz] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''id' INT PRIMARY KEY, 'player' TEXT NOT NULL, 'skill' TEXT NOT NULL, 'xp' int , ' at line 1
    
    2011-09-13 08:12:43 [WARNING] [Skillz] Starting conversion from Flatfile to MySQL. Expect huge lag!
    2011-09-13 08:12:43 [INFO] [Skillz] MySQL connection successful
    2011-09-13 08:12:43 [INFO] [Skillz] Creating table skillz...
    2011-09-13 08:12:43 [SEVERE] [Skillz] Error at Wipe Table: table, Skillz, does not exist
    2011-09-13 08:12:43 [INFO] Converting jawr.txt to MySQL..
    2011-09-13 08:12:43 [INFO] [Skillz] Conversion complete. Using MySQL now.
    2011-09-13 08:13:08 [WARNING] [Skillz] Error at SQL Query: Table 'minecraft.Skillz' doesn't exist
    2011-09-13 08:13:08 [WARNING] [Skillz] Something seems to be wrong with your MySQL database!
    
    This is the code:
    Code:
    public void loadMySQL() {
            mysql = new mysqlCore(log, logPrefix, dbHost, dbDB, dbUser, dbPass);
            mysql.initialize();
            if (mysql.checkConnection().booleanValue()) {
                log.info(logPrefix + "MySQL connection successful");
                if (!mysql.checkTable("Skillz").booleanValue()) {
                    log.info(logPrefix + "Creating table skillz...");
                    String query = "CREATE TABLE IF NOT EXISTS Skillz ('id' INT PRIMARY KEY, 'player' TEXT NOT NULL, 'skill' TEXT NOT NULL, 'xp' int , 'level' int, PRIMARY KEY(id) ) ;";
                    mysql.createTable(query);
                }
            } else {
                log.severe(logPrefix + "MySQL connection failed");
                useMySQL = false;
            }
        }
     
  23. Offline

    PatPeter

    I say it's a step forward, why have a different function for every single type of query (selectQuery(), insertQuery(), updateQuery(), deleteQuery(), etc.), when you can have a single function that intelligently determines what type of query it is and how to return based on that query?


    No, no no, no no no no no no. That might be possible for SQLite, maybe, but have you seen the documentation for MySQL? Take UPDATE and INSERT for example, UPDATE is fairly simple, but that's at minimum seven parameters unless you want to pass an ADT with the properties of the query. With INSERT it's ~7 depending on how you set it up.

    Point is, give programmers the autonomy to make their queries as complex as they want instead of limiting them to the development of this library. The method returns null for update and insert, how is that any worse than void?

    It was an example of adding more functionality than there already is. The structure, whether PHP MySQL, MySQLi, PDO, or ActiveRecord, does not matter because those libraries aren't coded in Java. What matters is the functionality, not the library or the language.

    Yeah, I should have used equalsIgnoreCase() instead of equals() in DatabaseHandler.getStatement(). If you want you can Find/Replace it. Alternatively, just capitalize INSERT. I cannot guarantee I will upload the next version until the end of the week.

    Not sure, I'll look into it.

    You know you can run a method that returns something and not process the return, correct? For example:

    PHP:
    Integer.toString(integer);
    This returns a String but it neither outputs nor saves it. Completely redundant in this example, but in the case of a query, it would still run.

    No longer supported.
     
  24. Offline

    Lolmewn Retired Staff

    I guess I go update my code then.

    @PatPeter All the insertQuery's and updateQuery stuff has been replaced to one query() I think?

    Also, is it possible to not let it throw the exceptions, but to simply let them catch it?
    Is less messy code for me in that way.

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

    fritz

    I am not currently assigning anything myself. I am simply running the sqlite.query(querystring) method. The warning is being generated within the query method, not from me trying to assign it somewhere. It isn't preventing anything from running, it just prints out to console every time the query is run.
     
  26. Offline

    Celtic Minstrel

    Having the separate function abstracts away from the specifics of the various dialects of SQL. It would essentially build an SQL query string compatible with the chosen database system, pass it through to the generic query(), do some sanity testing on the output, and return the result.

    Well, you'd base it on the SQL standard, so maybe half of the things MySQL can do wouldn't even be possible through that function; people would have to use the generic query() function for something like that. The point of separate functions for insert, update, delete, select, etc would be that a programmer can use those and know that the code will work on any database the user chooses. And they'll also know exactly which parts of the code risk not working on some databases, namely the parts where they call query() directly.

    That's what the generic query is for; it's not the addition of this that I'm complaining about, rather the removal of the more specific ones.
    Yay, NullPointerExceptions! ;) Granted, that's unlikely to be an issue if the function is used correctly, but still.

    True, but my objection was related to the MySQL and MySQLi libraries being essentially procedural libraries, which makes the general design a bad fit for Java. PDO on the other hand is an object-oriented library, which is why I pointed it out. I don't know ActiveRecord.
     
  27. Offline

    Kanlaki101

    PatPeter, how's that tutorial/documentation for this coming along?
    That would be very helpful.
     
  28. Offline

    HSAR

    I'm with you there. My project has kind of stalled while I wait for this...
     
  29. Offline

    DrAgonmoray

    I figured out how to use it this morning, and it's working perfectly. I think I might be able to help you out, so gime yo' questions.
     
  30. Offline

    HSAR

    So, um... I heard you can store data with this library?

    That's kind of the level I'm coming from. I figured I'd read through which functions did what and build myself up from there, but that's not really an option at the moment. I've gleaned enough from this thread to open up a connection and catch any errors that might be thrown, but that's the absolute limit of what I know about this right now.
     
Thread Status:
Not open for further replies.

Share This Page