[LIB] SQLibrary - Database wrappers for all database engines

Can someone explain what I'm doing wrong?

Code:

public void addBlock(Location location)
{
    World world = location.getWorld();
    String query = "INSERT INTO locations (location, world) VALUES (\"" + location + "\", \"" + world + "\");";
    plugin.log.info("addblock: "+ query); //DEBUG
    plugin.SQLite.query(query);
    return;
}

When this got called, my whole server won't respond to anything.

edit:
the query that will be executed will look like:
INSERT INTO locations (location, world) VALUES ("Location{world=CraftWorld{name=world},x=82.0,y=64.0,z=240.0,pitch=0.0,yaw=0.0}", "CraftWorld{name=world}");

You could try multiple threads with an instance of MySQL in each connected to a separate database. In theory it should work.

That would be the current bug with SQLite. I partially fixed it but not entirely. To fix it yourself look at the last query you're calling with SQLite and make sure to close it with rs.close().

EDIT: Forgot to say that I'll try uploading the fix today.

Attention everyone! The long-awaited fix has been processed. I have updated the GitHub as well as posting our first stable download off of GitHub (yay).

I hope it's fixed... Than I'm sure I do something wrong....

I get this error in my log:
the first two rules are debug, which are the commands send to query();

Code:

2012-03-12 18:57:37 [INFO] SELECT * FROM locations WHERE location = "Location{world=CraftWorld{name=world},x=82.0,y=64.0,z=240.0,pitch=0.0,yaw=0.0}"
2012-03-12 18:57:37 [INFO] INSERT INTO locations (location, world) VALUES ("Location{world=CraftWorld{name=world},x=82.0,y=64.0,z=240.0,pitch=0.0,yaw=0.0}", "CraftWorld{name=world}");
2012-03-12 18:57:40 [WARNING] [PLUGIN] [SQLite] Error in SQL query: cannot commit transaction - SQL statements in progress
2012-03-12 18:57:40 [SEVERE] Could not pass event PlayerInteractEvent to PLUGIN
org.bukkit.event.EventException
    at org.bukkit.plugin.java.JavaPluginLoader$1.execute(JavaPluginLoader.java:303)
    at org.bukkit.plugin.RegisteredListener.callEvent(RegisteredListener.java:62)
    at org.bukkit.plugin.SimplePluginManager.callEvent(SimplePluginManager.java:441)
    at org.bukkit.craftbukkit.event.CraftEventFactory.callPlayerInteractEvent(CraftEventFactory.java:168)
    at net.minecraft.server.ItemInWorldManager.dig(ItemInWorldManager.java:92)
    at net.minecraft.server.NetServerHandler.a(NetServerHandler.java:526)
    at net.minecraft.server.Packet14BlockDig.handle(SourceFile:43)
    at net.minecraft.server.NetworkManager.b(NetworkManager.java:226)
    at net.minecraft.server.NetServerHandler.a(NetServerHandler.java:111)
    at net.minecraft.server.NetworkListenThread.a(NetworkListenThread.java:78)
    at net.minecraft.server.MinecraftServer.w(MinecraftServer.java:536)
    at net.minecraft.server.MinecraftServer.run(MinecraftServer.java:434)
    at net.minecraft.server.ThreadServerApplication.run(SourceFile:465)
Caused by: java.lang.NullPointerException
    at PLUGIN.addBlock(SQLite.java:116)
    at PLUGIN.onPlayerInteract(PlayerListener.java:51)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.bukkit.plugin.java.JavaPluginLoader$1.execute(JavaPluginLoader.java:301)
    ... 12 more

Hopefully does someone see what I'm doing wrong.

This are the two parts of my code that are run by my plugin.

Code:

public int checkBlock(Location location)
{
    String query = "SELECT * FROM locations WHERE location = \"" + location + "\"";
    plugin.log.info(query);
    ResultSet rs = plugin.SQLite.query(query);
    int iResult = -1;
 
    try
    {
        if (rs != null && rs.next())
        {
            iResult = 1;
        }
        else
        {
            iResult = 2;
        }
        rs.close();
    }
    catch (SQLException e)
    {
        plugin.log.info(e.getMessage());
        iResult = -1;
    }
    return iResult; 
}
 
public void addBlock(Location location)
{
    World world = location.getWorld();
    String query = "INSERT INTO locations (location, world) VALUES (\"" + location + "\", \"" + world + "\");";
    plugin.log.info(query);
    ResultSet rs = plugin.SQLite.query(query);
    try
    {
        rs.close();
    }
    catch (SQLException e)
    {
        plugin.log.info(e.getMessage());
    }
    return;
}

Close the ResultSet in checkBlock() before running addBlock().

Correct me if i'm wrong...
there is a rs.close(); in my checkBlock? If this isn't right, how must it be done?

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.

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.

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.

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.

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

Ok, here it is
http://bit.ly/H8BrZG
Included pretty must the starter of a MySQL project . Enjoy, Commented pretty well, post feedback in comments!

Alright, so I was working on implementing this, when I came against a roadblock. I tried the getString method to get a value, but to no avail. More information here, any help would be appreciated!
http://forums.bukkit.org/threads/retrieving-a-ip-to-ban-from-mysql.68678/

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?

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.

Same here.

Please drop your error log

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

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

Ideias?

How do i get a name from the db.

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+"'"

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?

Using mySQL in your project is very simple:

Just use:

Code:java

 
public MySQL mysql;
 
onEnable:
mysql.open();
 

Code:java

 
onDisable:
mysql.close();

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

@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?

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

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

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

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?

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

Hi,

Neodork has some nice basic posts on it on the previous page:
http://forums.bukkit.org/threads/lib-tut-mysql-sqlite-bukkit-drivers.33849/page-7#post-986501
^ that post and the post 2 posts below it.