Solved SQL column not found

Discussion in 'Spigot Plugin Development' started by PvPNiK, Feb 4, 2020.

  1. Hi,
    I am trying to get the highest value in a column.
    when I am running the SQL query in phpMyAdmin it works, however in code it doest, and I am getting an error:
    java.sql.SQLException: Column 'soup' not found.
    https://pastebin.com/7HXJuS3Y

    code:

    Code (Java):
        // SELECT MAX(`op`) FROM `elo`;
        public int getHighest(Database database, String column, String table) {
            Connection con = SyrexCore.communicationManager.get(database).getCon();
            try {
                PreparedStatement ps = con.prepareStatement("SELECT MAX(`" + column.toLowerCase() + "`) FROM `" + table.toLowerCase() + "`");
                ResultSet rs = ps.executeQuery();
                if (rs.next())
                    return rs.getInt(column.toLowerCase());
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return -1;
        }
     
  2. Nothing looks incorrect inside that method. Your issue probably lies within, your database implementation.
     
  3. Have you chosen correct database in your code? Maybe you are looking for a table in a wrong database.
     
  4. I am in the correct database.
    changed a little bit the code:
    Code (Java):
        // SELECT MAX(`op`) FROM `elo`;

        public int getHighest(Database database, String column, String table) {
            System.out.println("d: " + database.name() + ", c: " + column + ", t: " + table);
            Bukkit.broadcastMessage("d: " + database.name() + ", c: " + column + ", t: " + table);
            Bukkit.getConsoleSender().sendMessage("d: " + database.name() + ", c: " + column + ", t: " + table);
            Connection con = SyrexCore.communicationManager.get(database).getCon();
            PreparedStatement ps = null;
            ResultSet rs = null;
            try {
                ps = con.prepareStatement("SELECT MAX(`" + column.toLowerCase() + "`) FROM `" + table.toLowerCase() + "`");
                rs = ps.executeQuery();
                if (rs.next())
                    return rs.getInt(column.toLowerCase());
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try { rs.close(); } catch (Exception e) {}
                try { ps.close(); } catch (Exception e) {}
                try { con.close(); } catch (Exception e) {}
            }
            return -1;
        }
    error: https://pastebin.com/aynY6ej8

    I execute the code in another plugin, 200 ticks after the plugin enabled:

    Code (Java):
        private void setupNPC() {
            for (DuelType duelType : DuelType.values()) {
                NPC npc = CitizensAPI.getNPCRegistry().getById(duelType.getId());
    //            Bukkit.broadcastMessage("id: " + r.getId() + ", npc: " + npc);
                if (npc == null)
                    return;
                if (npc.isSpawned()) {
                    npc.teleport(duelType.getLocation(), PlayerTeleportEvent.TeleportCause.PLUGIN);
                } else {
                    npc.spawn(duelType.getLocation());
                }

                int highest = SyrexDuels.playerData.getHighest(duelType, DuelStat.elo);
                if(highest != -1 && highest != 1000) {
                    npc.data().set(NPC.PLAYER_SKIN_UUID_METADATA, SyrexDuels.instance.getConfig().getString(duelType.getUuidPath()));
                    npc.setName(duelType.name() + ", " + SyrexDuels.instance.getConfig().getString(duelType.getNamePath()));
                } else {
                    npc.data().set(NPC.PLAYER_SKIN_UUID_METADATA, "????");
                    npc.setName(duelType.name() + ", " + "????");
                }
                SyrexDuels.npcs.put(duelType, npc);
            }
        }
    Code (Java):
        @Override
        public void onEnable() {
            instance = this;
            npcs = new HashMap<DuelType, NPC>();
            Bukkit.getScheduler().runTaskLater(this, () -> setupNPC(), 200);
        }
     
  5. Hey!
    Are the columns really called "soup"? Most of the time it's case sensitive. Translated with Google because I am German.
     
  6. Strahan

    Benefactor

    I'd enable SQL logging and check what it sees coming in.
     
  7. Check table structure with phpmyadmin maybe?
     
  8. [​IMG]

    where I can see the logs in PHPMyAdmin?
     
  9. Strahan

    Benefactor

    I don't use phpMyAdmin so dunno if it has any built in method for toggling and displaying logs, but all you need to is run these statements:
    Code (Text):
    SET GLOBAL log_output = 'TABLE';
    SET GLOBAL general_log = 'ON';
    Then go do whatever SQL operation is in question. Then do
    Code (Text):
    SELECT * FROM mysql.general_log;
    To see the logs. Don't forget when done to turn it back off:
    Code (Text):
    USE mysql;
    SET GLOBAL general_log = 'OFF';
    RENAME TABLE general_log TO general_log_temp;
    DELETE FROM `general_log_temp`;
    RENAME TABLE general_log_temp TO general_log;
    Skip the rename/del/rename if you need to keep your log of queries. I usually just dump them when I'm done to keep it clean.
     
    • Like Like x 1
  10. okay, checked it, and it returns "MAX('op')".
    added this code:
    Code (Java):
    try {
                    ResultSetMetaData rsmd = rs.getMetaData();
                    int columnCount = rsmd.getColumnCount();
                    for (int i = 1; i <= columnCount; i++ ) {
                        String name = rsmd.getColumnName(i);
                        System.out.println(i + "name: " + name);
                        Bukkit.broadcastMessage(i + "name: " + name);
                        Bukkit.getConsoleSender().sendMessage(i + "name: " + name);
                    }
                } catch (SQLException e) {
                    System.out.println("SQLException: ");
                    Bukkit.broadcastMessage("SQLException: ");
                    Bukkit.getConsoleSender().sendMessage("SQLException: " );
                }
    in the finally block just above the code where i close all connections.
    out put: MAX(`soup`)

    Code (Java):
        // SELECT MAX(`op`) FROM `elo`;

        public int getHighest(Database database, String column, String table) {
            System.out.println("d: " + database.name() + ", c: " + column + ", t: " + table);
            Bukkit.broadcastMessage("d: " + database.name() + ", c: " + column + ", t: " + table);
            Bukkit.getConsoleSender().sendMessage("d: " + database.name() + ", c: " + column + ", t: " + table);
            Connection con = SyrexCore.communicationManager.get(database).getCon();
            PreparedStatement ps = null;
            ResultSet rs = null;
            try {
                ps = con.prepareStatement("SELECT MAX(`" + column.toLowerCase() + "`) FROM `" + table.toLowerCase() + "`");
                rs = ps.executeQuery();
                if (rs.next())
                    return rs.getInt(column.toLowerCase());
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {

                try {
                    ResultSetMetaData rsmd = rs.getMetaData();
                    int columnCount = rsmd.getColumnCount();
                    for (int i = 1; i <= columnCount; i++ ) {
                        String name = rsmd.getColumnName(i);
                        System.out.println(i + "name: " + name);
                        Bukkit.broadcastMessage(i + "name: " + name);
                        Bukkit.getConsoleSender().sendMessage(i + "name: " + name);
                    }
                } catch (SQLException e) {
                    System.out.println("SQLException: ");
                    Bukkit.broadcastMessage("SQLException: ");
                    Bukkit.getConsoleSender().sendMessage("SQLException: " );
                }

                try { rs.close(); } catch (Exception e) {}
                try { ps.close(); } catch (Exception e) {}
                try { con.close(); } catch (Exception e) {}
            }
            return -1;
        }
     
  11. Sounds like you found your column name then. This is a good reason why you should use indexed lookup on a ResultSet instead, it works irregardless of the column names or whatever the driver thinks the column should be.
     
  12. Thank you!
    changed
    return rs.getInt(column.toLowerCase());
    to
    return rs.getInt(1);

    and it worked