[CLOSED] Error when trying ti insert data in mysql

Discussion in 'Spigot Plugin Development' started by mlgcraftnetwork, May 21, 2016.

  1. Okay so I started learning mysql yesterday and its going pretty well, I read the wiki on w3schools.com but I'm having trouble solving one error. I'm getting this error when trying to insert data in the table I created if a new player joins.

    Code:
    Code (Text):
    package mcgglobal;

    import java.sql.ResultSet;
    import java.sql.SQLException;
    import org.bukkit.entity.Player;
    import org.bukkit.plugin.Plugin;

    public abstract class Coins implements Plugin {

       public static void createTable() throws SQLException {
       Main.c.createStatement().executeUpdate("CREATE TABLE IF NOT EXISTS coins(player varchar(36) NOT NULL, coins int(255));");
       }

       public static void defaultBal(Player p) throws SQLException {
       ResultSet res = Main.c.createStatement().executeQuery("SELECT coins FROM coins WHERE player = '" + p.getUniqueId() + "';");
       res.next();
       if(res.getString("player") == null) {
       Main.c.createStatement().executeUpdate("INSERT INTO coins (player, coins) VALUES (" + p.getUniqueId() + ", '0');");
       }
       }
    }
     
    Console error:
    Code (Text):
    [10:33:18] [User Authenticator #1/INFO]: UUID of player SmokingIsBadMkay is 5d70cba5-bb7f-4b07-b946-966872e3da15
    [10:33:18] [Server thread/ERROR]: Could not pass event PlayerJoinEvent to MCGGlobal v1.0
    org.bukkit.event.EventException
        at org.bukkit.plugin.java.JavaPluginLoader$1.execute(JavaPluginLoader.java:310) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at org.bukkit.plugin.RegisteredListener.callEvent(RegisteredListener.java:62) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at org.bukkit.plugin.SimplePluginManager.fireEvent(SimplePluginManager.java:502) [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at org.bukkit.plugin.SimplePluginManager.callEvent(SimplePluginManager.java:487) [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.PlayerList.onPlayerJoin(PlayerList.java:333) [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.PlayerList.a(PlayerList.java:159) [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.LoginListener.b(LoginListener.java:144) [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.LoginListener.c(LoginListener.java:54) [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.NetworkManager.a(NetworkManager.java:233) [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.ServerConnection.c(ServerConnection.java:140) [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.MinecraftServer.D(MinecraftServer.java:825) [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.DedicatedServer.D(DedicatedServer.java:399) [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.MinecraftServer.C(MinecraftServer.java:665) [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.MinecraftServer.run(MinecraftServer.java:564) [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at java.lang.Thread.run(Unknown Source) [?:1.8.0_91]
    Caused by: java.sql.SQLException: Column 'player' not found.
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:959) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:862) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1076) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5206) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at mcgglobal.Coins.defaultBal(Coins.java:17) ~[?:?]
        at mcgglobal.Listeners.onJoin(Listeners.java:21) ~[?:?]
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_91]
        at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) ~[?:1.8.0_91]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[?:1.8.0_91]
        at java.lang.reflect.Method.invoke(Unknown Source) ~[?:1.8.0_91]
        at org.bukkit.plugin.java.JavaPluginLoader$1.execute(JavaPluginLoader.java:306) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        ... 14 more
    [10:33:18] [Server thread/INFO]: SmokingIsBadMkay[/192.168.0.114:56193] logged in with entity id 26 at ([world]1.362461621001483, 57.0, 25.565142204245735)
    I know it says column 'player' not found and I checked both my code and my database but it seems like this column does exists.
    Screenshot:
    [​IMG]
    Does anyone know what is causing this?
     
  2. Its not a column, its a row, if you want the column containing player it would be "Name".
     
    • Like Like x 1
  3. Oh sorry didnt look at the right place, didnt see you were in structures xD
    Searching the error.
     
    • Like Like x 1
  4. See this?
    "SELECT coins FROM coins WHERE player = '" + p.getUniqueId() + "';"
    Change it to this and tell me if it works.
    "SELECT * FROM coins WHERE player = '" + p.getUniqueId() + "';"
     
    • Useful Useful x 1
  5. Lesson time:
    You see your first statement? You're selecting coins column and right after that asking for player ... row...
    So, basically I replaces your selection to '*' which, means, select everything, so my statement does: Select everything in table coins where we select row player + name.
     
    • Friendly Friendly x 1
  6. I switched that line of code with your code and I'm not getting the "can't find column" error anymore so that is a good thing. I'm getting a different error though, do you have any idea what is causing this and how I can fix this? Thanks a lot!

    Error:
    Code (Text):
    [11:09:40] [Server thread/ERROR]: Could not pass event PlayerJoinEvent to MCGGlobal v1.0
    org.bukkit.event.EventException
        at org.bukkit.plugin.java.JavaPluginLoader$1.execute(JavaPluginLoader.java:310) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at org.bukkit.plugin.RegisteredListener.callEvent(RegisteredListener.java:62) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at org.bukkit.plugin.SimplePluginManager.fireEvent(SimplePluginManager.java:502) [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at org.bukkit.plugin.SimplePluginManager.callEvent(SimplePluginManager.java:487) [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.PlayerList.onPlayerJoin(PlayerList.java:333) [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.PlayerList.a(PlayerList.java:159) [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.LoginListener.b(LoginListener.java:144) [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.LoginListener.c(LoginListener.java:54) [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.NetworkManager.a(NetworkManager.java:233) [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.ServerConnection.c(ServerConnection.java:140) [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.MinecraftServer.D(MinecraftServer.java:825) [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.DedicatedServer.D(DedicatedServer.java:399) [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.MinecraftServer.C(MinecraftServer.java:665) [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.MinecraftServer.run(MinecraftServer.java:564) [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at java.lang.Thread.run(Unknown Source) [?:1.8.0_91]
    Caused by: java.sql.SQLException: Illegal operation on empty result set.
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:959) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:862) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.ResultSetImpl.checkRowPos(ResultSetImpl.java:790) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:5244) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5167) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5206) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at mcgglobal.Coins.defaultBal(Coins.java:17) ~[?:?]
        at mcgglobal.Listeners.onJoin(Listeners.java:21) ~[?:?]
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_91]
        at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) ~[?:1.8.0_91]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[?:1.8.0_91]
        at java.lang.reflect.Method.invoke(Unknown Source) ~[?:1.8.0_91]
        at org.bukkit.plugin.java.JavaPluginLoader$1.execute(JavaPluginLoader.java:306) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        ... 14 more
    Updated code:
    Code (Text):
    package mcgglobal;

    import java.sql.ResultSet;
    import java.sql.SQLException;
    import org.bukkit.entity.Player;
    import org.bukkit.plugin.Plugin;

    public abstract class Coins implements Plugin {

       public static void createTable() throws SQLException {
       Main.c.createStatement().executeUpdate("CREATE TABLE IF NOT EXISTS coins(player varchar(36) NOT NULL, coins int(255));");
       }

       public static void defaultBal(Player p) throws SQLException {
       ResultSet res = Main.c.createStatement().executeQuery("SELECT * FROM coins WHERE player = '" + p.getUniqueId() + "';");
       res.next();
       if(res.getString("player") == null) {
       Main.c.createStatement().executeUpdate("INSERT INTO coins (player, coins) VALUES (" + p.getUniqueId() + ", '0');");
       }
       }
    }
     
    I think this is a problem with the code to set data because it looks like its trying to set an invallid value.
     
  7. Try changing that:
    "INSERT INTO coins (player, coins) VALUES (" + p.getUniqueId() + ", '0');"
    to that:
    "INSERT INTO coins ('player', 'coins') VALUES ('" + p.getUniqueId() + "', '0');"
    Just see where I added these ' into your line, keep me updated please.
     
  8. @mlgcraftnetwork it doesn't work that way. res.next() returns a boolean, if it's true it found something, if not you should call the insert statement. Never check for nulls as that won't work
     
  9. Did you ever work with MySQL? res.next() is a way to enter the result.
     
  10. @DevCubia did you ever work with SQL in java? res.next() returns true as long as it has a next search result. If it returns false, retrieving a value throws an SQLException
     
  11. I did, so, first lets calm down, then yes, I made a mistake and its true res.next() is the error, then checking for a null works in MySQL.
    So, @mlgcraftnetwork be sure to do this,
    Code (Text):
    if(res.next){// Here you got to add the null checking}
     
  12. You should really ditch the static cling and opt for a connection pool in place of a single connection, it'll make your plugin more reliable and the code far more maintainable and pleasant to work with. Although not a big deal, using PreparedStatements over regular ones with manual string concatenation is also considered good practice. :)

    Don't be a prick.

    Static cling is a code smell used to describe the undesirable coupling introduced by accessing static (global) functionality, either as variables or methods.
     
  13. You have to check if the result is not empty.
    Code (Java):
    if (res.next()) {
        res.get...
    } else {
        //code
    }
    Your code is terrible.
    • Why is this class abstract?
    • Why does this class implement Plugin?
    • Don't abuse static
    • Don't abuse static to get a connection object from the Main class
    • Close your resources (connections, statements, result sets)
    • Use a connection pool
    • ...
     
    • Agree Agree x 1
  14. I'm just not in a real good mood, been sick lately, I excuse myself for that, its a stupid reflection from myself. Srry.
     
  15. My code probably isn't the best but if you read the thread I clearly said "I started learning mysql yesterday". The reason why I used abstract and static is because I was getting errors and eclipse gave me this suggestion and because it fixed the problems I didn't think about it being a bad way to do this.
     
  16. Then here's a tutorial!
    https://bukkit.org/threads/using-mysql-in-your-plugins.132309/
     
  17. Be sure to have a correct syntax, even some missing ' can cause errors! Otherwise the solution to your problem has been given out mate!
     
  18. Most of the things are basic Java and Eclipse is not a programmer for you, it's an IDE and you should know what you are doing and you should know the language before coding Spigot plugins.

    This tutorial is not good especially not for beginners. There are no words about closing resources, handling exceptions, using connection pools and so on. I have also looked at his MySQL library and there are also some issues with not closed statements (and result sets) and things like that.
     
  19. I agree, but I think its a pretty good heads up for learning a bit the syntax.