[LIB] SQLibrary - Database wrappers for all database engines

Sounds like just the thing I was looking for.

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

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]    }[/CODE][/S]
[S][/spoiler][/S]

[S]the server log outputs:[/S]
[S][spoiler][/S]
[S][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][/CODE][/S]
[S][/spoiler][/S]
i have fix it apparently my sql and phpmyadmin config files were wrong on both servers ITS ALL FIXED! time to go wild![S]
[/S][/CODE][/S]

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.

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

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

• PHP-esque database functions: http://www.php.net/manual/en/ref.mysql.php. Unless anyone thinks a language has a better MySQL API.

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!!!

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.

Anyone willing to help me add this into bPermissions?

Always, what do you need help with?

How's the tutorial going?

Storing arrays in mysql - as in a String[]

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.

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.

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);

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.

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.

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)

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

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?

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.

That was how I originally wrote it

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;
        }
    }

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.

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.

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.

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.

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