MySQL Int and ResultSet Help

Discussion in 'Plugin Development' started by MCMatters, Jun 20, 2014.

Thread Status:
Not open for further replies.
  1. Offline

    MCMatters

    Code:java
    1. public void getTokens(String Player) throws SQLException {
    2. ResultSet res = statement2.executeQuery("SELECT * FROM tokens WHERE username = '" + Player + "';");
    3. res.next();
    4. if(res.getString("username") == null) {
    5. tokens = 0;
    6. } else {
    7. tokens = res.getInt("tokens");
    8. }
    9. return tokens;
    10. }

    Error:
    Code:error
    1. Type mismatch: cannot convert from Int to ResultSet

    Errors
    Line 29.
    Code:java
    1. try {

    Line 48.
    Code:java
    1. ItemStack gold = new ItemStack(Material.GOLD_INGOT);

    I dont think those matter lol.
    Line 68. (The Real Error)
    Code:java
    1. c = openConnection();

    Code:java
    1. public Connection openConnection(){
    2. try {
    3. Class.forName("com.mysql.jdbc.Driver");
    4. c = DriverManager.getConnection("jdbc:mysql://" + hostname + ":" + port + "/" + database, user, password);
    5. } catch (SQLException e) {
    6. Bukkit.getServer().getLogger().severe("Could not connect to MySQL server! because: " + e.getMessage());
    7. Bukkit.getServer().getLogger().severe("JDBC Driver not found!");
    8. }
    9. return c;
    10. }
     
  2. Offline

    viper_monster

    MCMatters please post the whole class and error.
     
  3. Offline

    MCMatters

    Ok

    viper_monster do u know how to change int to resultset

    Full class:
    Code:java
    1. package com.mcmnetwork.pda;
    2.  
    3. import java.sql.Connection;
    4. import java.sql.DriverManager;
    5. import java.sql.ResultSet;
    6. import java.sql.SQLException;
    7. import java.sql.Statement;
    8. import java.util.logging.Level;
    9.  
    10. import org.bukkit.Bukkit;
    11. import org.bukkit.ChatColor;
    12. import org.bukkit.Material;
    13. import org.bukkit.command.Command;
    14. import org.bukkit.command.CommandExecutor;
    15. import org.bukkit.command.CommandSender;
    16. import org.bukkit.entity.Player;
    17. import org.bukkit.event.Listener;
    18. import org.bukkit.inventory.Inventory;
    19. import org.bukkit.inventory.ItemStack;
    20. import org.bukkit.inventory.meta.ItemMeta;
    21.  
    22. import com.mcmnetwork.settings.Logger;
    23.  
    24. public class PDA implements Listener, CommandExecutor {
    25. Logger log = new Logger();
    26. public static Inventory pda = Bukkit.createInventory(null, 27, "Personal Digital Assistant (PDA)");
    27. public boolean onCommand(CommandSender sender, Command cmd, String label, String[] args) {
    28. if (cmd.getName().equalsIgnoreCase("pda")){
    29. try {
    30. Player player = (Player) sender;
    31. openPDA(player);
    32. return true;
    33. } catch (SQLException ex) {
    34. log.severe("" + ex);
    35. }
    36. }
    37. return false;
    38. }
    39. public void openPDA(Player player) throws SQLException {
    40. ItemStack spawn = new ItemStack(Material.ENDER_PEARL);
    41. ItemMeta spawndata = spawn.getItemMeta();
    42. spawndata.setDisplayName(ChatColor.GOLD + "Lobby");
    43. spawn.setItemMeta(spawndata);
    44. pda.setItem(13, spawn);
    45. //econ.format(econ.getBalance(player.getName()))
    46. //getConfig().getString(sender.getName() + ".cookies")
    47.  
    48. ItemStack gold = new ItemStack(Material.GOLD_INGOT);
    49. ItemMeta golddata = gold.getItemMeta();
    50. golddata.setDisplayName(ChatColor.RED + "Tokens: Comming Soon");
    51. //Broken
    52. //getTokens("MCMatters");
    53. //golddata.setDisplayName(ChatColor.RED + "Tokens: " + tokens);
    54. gold.setItemMeta(golddata);
    55. pda.setItem(26, gold);
    56.  
    57. player.openInventory(pda);
    58. }
    59. public static int tokens;
    60. public int getToken() {
    61. return tokens;
    62. }
    63. Edit: Removed SQL Details
    64. public Connection c = null;
    65. public void getTokens(String Player) throws SQLException {
    66. c = openConnection();
    67. Statement statement = c.createStatement();
    68. log.info("First task finished");
    69. statement.executeUpdate("CREATE TABLE IF NOT EXISTS `tokens` (`tokens` int(100000) NOT NULL AUTO_INCREMENT, `username` varchar(255) NOT NULL, PRIMARY KEY (`tokens`), UNIQUE KEY `username` (`username`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;");
    70. Statement statement2 = c.createStatement();
    71. ResultSet res = statement2.executeQuery("SELECT * FROM tokens WHERE username = '" + Player + "';");
    72. res.next();
    73. if(res.getString("username") == null) {
    74. tokens = 0;
    75. } else {
    76. tokens = res.getInt("tokens");
    77. }
    78. //return tokens;
    79. }
    80. //SQL
    81. public ResultSet querySQL(String query) {
    82. Connection c = null;
    83.  
    84. if (checkConnection()) {
    85. c = getConnection();
    86. } else {
    87. c = openConnection();
    88. }
    89.  
    90. Statement s = null;
    91.  
    92. try {
    93. s = c.createStatement();
    94. } catch (SQLException e1) {
    95. e1.printStackTrace();
    96. }
    97.  
    98. ResultSet ret = null;
    99.  
    100. try {
    101. ret = s.executeQuery(query);
    102. } catch (SQLException e) {
    103. e.printStackTrace();
    104. }
    105.  
    106. closeConnection();
    107.  
    108. return ret;
    109. }
    110.  
    111. public void updateSQL(String update) {
    112.  
    113. Connection c = null;
    114.  
    115. if (checkConnection()) {
    116. c = getConnection();
    117. } else {
    118. c = openConnection();
    119. }
    120.  
    121. Statement s = null;
    122.  
    123. try {
    124. s = c.createStatement();
    125. s.executeUpdate(update);
    126. } catch (SQLException e1) {
    127. e1.printStackTrace();
    128. }
    129.  
    130. closeConnection();
    131.  
    132. }
    133. public Connection openConnection(){
    134. try {
    135. Class.forName("com.mysql.jdbc.Driver");
    136. c = DriverManager.getConnection("jdbc:mysql://" + hostname + ":" + port + "/" + database, user, password);
    137. } catch (SQLException e) {
    138. Bukkit.getServer().getLogger().severe("Could not connect to MySQL server! because: " + e.getMessage());
    139. Bukkit.getServer().getLogger().severe("JDBC Driver not found!");
    140. }
    141. return c;
    142. }
    143. public boolean checkConnection() {
    144. return c != null;
    145. }
    146.  
    147. public Connection getConnection() {
    148. return c;
    149. }
    150.  
    151. public void closeConnection() {
    152. if (c != null) {
    153. try {
    154. c.close();
    155. } catch (SQLException e) {
    156. Bukkit.getServer().getLogger().log(Level.SEVERE, "Error closing the MySQL Connection!");
    157. e.printStackTrace();
    158. }
    159. }
    160. }
    161. }
    162.  
    163. [USER=90690711]viper_monster[/USER]
    164.  
    165. The error is above
    166.  
    167. EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
    Last edited by a moderator: Jun 9, 2016
  4. Offline

    TechNotes

    You don't really need this; I don't know why you have it. You should never have a null in the username column

    Code:java
    1. if(res.getString("username") == null) {
    2. tokens = 0;
    3. } else {
    4. tokens = res.getInt("tokens");
    5. }


    Just use:

    Code:java
    1. tokens = res.getInt("tokens");
    2. //or
    3. tokens = res.getInt(1);
     
  5. Offline

    viper_monster

    MCMatters I'm not that experienced with MySQL, but yeah... I think it would be better to open the connection in onEnable() and then, this should return the tokens that a player has:

    Code:java
    1. public int getTokens(String playerName) throws SQLException {
    2. int tokens = 0;
    3. Statement statement = c.createStatement();
    4. statement.executeUpdate("CREATE TABLE IF NOT EXISTS `tokens` (`tokens` int(100000) NOT NULL AUTO_INCREMENT, `username` varchar(255) NOT NULL, PRIMARY KEY (`tokens`), UNIQUE KEY `username` (`username`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;");
    5. ResultSet res = statement.executeQuery("SELECT * FROM tokens WHERE username = '" + playerName + "';");
    6. while (res.next()) {
    7. tokens = res.getInt(1);
    8. }
    9. return tokens;
    10. }


    NOTE: I do not guarantee that this method will actually work!
     
  6. Offline

    MCMatters

  7. Offline

    mythbusterma

    Use prepared statements to avoid SQL injections (somewhat).

    Code:java
    1. int tokens = 0;
    2.  
    3. PreparedStatement ps = conn.prepareStatement("SELECT * FROM tokens WHERE username = ?");
    4. ps.setString(1,playername);
    5. ResultSet rs = ps.executeQuery();
    6. if (res.next()) {
    7. tokens = rs.getInt("tokens");
    8. }
    9.  




    As for your errors, please post a stack trace so we can have some sort of idea what went wrong.
     
  8. Offline

    MCMatters

    mythbusterma
    Code:error
    1. 20.06 17:09:19 [Server] INFO ... 13 more
    2. 20.06 17:09:19 [Server] INFO at org.bukkit.command.PluginCommand.execute(PluginCommand.java:44) ~[craftbukkit_beta.jar:git-Bukkit-1.7.9-R0.1-10-g8688bd4-b3092jnks]
    3. 20.06 17:09:19 [Server] INFO at com.mcmnetwork.pda.PDA.onCommand(PDA.java:29) ~[?:?]
    4. 20.06 17:09:19 [Server] INFO at com.mcmnetwork.pda.PDA.openPDA(PDA.java:48) ~[?:?]
    5. 20.06 17:09:19 [Server] INFO at com.mcmnetwork.pda.PDA.getTokens(PDA.java:68) ~[?:?]
    6. 20.06 17:09:19 [Server] INFO Type mismatch: cannot convert from int to ResultSet
    7. 20.06 17:09:19 [Server] INFO Type mismatch: cannot convert from int to ResultSet
    8. 20.06 17:09:19 [Server] INFO Caused by: java.lang.Error: Unresolved compilation problems:
    9. 20.06 17:09:19 [Server] INFO at net.minecraft.server.v1_7_R3.ThreadServerApplication.run(SourceFile:628) [craftbukkit_beta.jar:git-Bukkit-1.7.9-R0.1-10-g8688bd4-b3092jnks]
    10. 20.06 17:09:19 [Server] INFO at net.minecraft.server.v1_7_R3.MinecraftServer.run(MinecraftServer.java:469) [craftbukkit_beta.jar:git-Bukkit-1.7.9-R0.1-10-g8688bd4-b3092jnks]
    11. 20.06 17:09:19 [Server] INFO at net.minecraft.server.v1_7_R3.MinecraftServer.u(MinecraftServer.java:558) [craftbukkit_beta.jar:git-Bukkit-1.7.9-R0.1-10-g8688bd4-b3092jnks]
    12. 20.06 17:09:19 [Server] INFO at net.minecraft.server.v1_7_R3.DedicatedServer.v(DedicatedServer.java:260) [craftbukkit_beta.jar:git-Bukkit-1.7.9-R0.1-10-g8688bd4-b3092jnks]
    13. 20.06 17:09:19 [Server] INFO at net.minecraft.server.v1_7_R3.MinecraftServer.v(MinecraftServer.java:667) [craftbukkit_beta.jar:git-Bukkit-1.7.9-R0.1-10-g8688bd4-b3092jnks]
    14. 20.06 17:09:19 [Server] INFO at net.minecraft.server.v1_7_R3.ServerConnection.c(SourceFile:134) [craftbukkit_beta.jar:git-Bukkit-1.7.9-R0.1-10-g8688bd4-b3092jnks]
    15. 20.06 17:09:19 [Server] INFO at net.minecraft.server.v1_7_R3.NetworkManager.a(NetworkManager.java:157) [craftbukkit_beta.jar:git-Bukkit-1.7.9-R0.1-10-g8688bd4-b3092jnks]
    16. 20.06 17:09:19 [Server] INFO at net.minecraft.server.v1_7_R3.PacketPlayInChat.handle(PacketPlayInChat.java:47) [craftbukkit_beta.jar:git-Bukkit-1.7.9-R0.1-10-g8688bd4-b3092jnks]
    17. 20.06 17:09:19 [Server] INFO at net.minecraft.server.v1_7_R3.PacketPlayInChat.a(PacketPlayInChat.java:28) [craftbukkit_beta.jar:git-Bukkit-1.7.9-R0.1-10-g8688bd4-b3092jnks]
    18. 20.06 17:09:19 [Server] INFO at net.minecraft.server.v1_7_R3.PlayerConnection.a(PlayerConnection.java:817) [craftbukkit_beta.jar:git-Bukkit-1.7.9-R0.1-10-g8688bd4-b3092jnks]
    19. 20.06 17:09:19 [Server] INFO at net.minecraft.server.v1_7_R3.PlayerConnection.handleCommand(PlayerConnection.java:956) [craftbukkit_beta.jar:git-Bukkit-1.7.9-R0.1-10-g8688bd4-b3092jnks]
    20. 20.06 17:09:19 [Server] INFO at org.bukkit.craftbukkit.v1_7_R3.CraftServer.dispatchCommand(CraftServer.java:701) ~[craftbukkit_beta.jar:git-Bukkit-1.7.9-R0.1-10-g8688bd4-b3092jnks]
    21. 20.06 17:09:19 [Server] INFO at org.bukkit.command.SimpleCommandMap.dispatch(SimpleCommandMap.java:180) ~[craftbukkit_beta.jar:git-Bukkit-1.7.9-R0.1-10-g8688bd4-b3092jnks]
    22. 20.06 17:09:19 [Server] INFO at org.bukkit.command.PluginCommand.execute(PluginCommand.java:46) ~[craftbukkit_beta.jar:git-Bukkit-1.7.9-R0.1-10-g8688bd4-b3092jnks]
    23. 20.06 17:09:19 [Server] INFO org.bukkit.command.CommandException: Unhandled exception executing command 'pda' in plugin Plugin v1.2
    24. 20.06 17:09:19 [Server] ERROR null
    25. 20.06 17:09:13 [Multicraft] MCMatters ran command Message of the Day
    26. 20.06 17:09:13 [Connect] User MCMatters, IP 127.0.0.1
    27. 20.06 17:08:33 [Server] Startup Done (1.621s)! For help, type "help" or "?"
     
  9. Offline

    mythbusterma

    Just out of curiosity, do you use an IDE? If so, which one?
     
    MCMatters likes this.
  10. Offline

    MCMatters

    mythbusterma i use eclipse, please no hate comments because I would need a tutorial for everytihng
     
  11. Offline

    mythbusterma

    I was just wondering, because if it is indeed a compilation error, Eclipse should have caught it before it let you export the plugin, are there any warnings or errors in your project?
     
  12. Offline

    MCMatters

    mythbusterma nope

    mythbusterma I'm sorry for asking for code but how do I fix it? Code?!

    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
    Last edited by a moderator: Jun 9, 2016
  13. Offline

    mythbusterma

    To be frank with you, I have no idea what the problem is. It looks like something Eclipse should have picked up on, and if it hasn't, then I don't know. It is also quite likely that you're using an outdated build of your plugin, because the line numbers don't seem to make sense. Make sure you re-export your plugin and restart the server, then see if the numbers change.
     
  14. Offline

    1Rogue

    executeUpdate() returns an int representing the number of rows changed, you don't get a ResultSet. Updates affect rows, they aren't for selection.

    ResultSets are returned from executeQuery(), because a query should contain results from a specific SELECT statement.
     
  15. Offline

    MCMatters

    Read my post again please:
    Code:java
    1.  
     
  16. Offline

    1Rogue

     
  17. Offline

    MCMatters

    New Code
    Code:java
    1. log.info("Debug");
    2. Statement statement2 = c.createStatement();
    3. log.info("Debug");
    4. ResultSet res = statement2.executeQuery("SELECT * FROM tokens WHERE username = '" + Player + "';");
    5. log.info("Debug");
    6. res.next();
    7. log.info("Debug");
    8. tokens = res.getInt("tokens");
    9. log.info("Debug");
    10. return tokens;

    Error:
    Code:error
    1. ERROR java.sql.SQLException: Illegal operation on empty result set

    My debug msgs proved that
    Code:java
    1. tokens = res.getInt("tokens");
    is a problem

    1Rogue mythbusterma viper_monster

    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
    Last edited by a moderator: Jun 9, 2016
  18. Offline

    1Rogue


    ResultSet#next will return a boolean if there is a value available. You don't check this return value and just assume there is a value, hence your error when it's an empty ResultSet.
     
  19. Offline

    mythbusterma


    I.e.

    Code:java
    1. if(res.next()) {
    2. tokens = res.getInt("tokens");
    3. }
     
Thread Status:
Not open for further replies.

Share This Page