Solved Database returning empty ResultSet

Discussion in 'Spigot Plugin Development' started by Nuubles, Nov 15, 2019.

  1. I have two object types stored in the sql database: PermanentLocation and ActionBlock.For some reason when I'm trying to load the PermanentLocation from the database normally (for example: /home <name>) the location loads fine. When loading the ActionBlock I need the location to know where the block is to create the ActionBlock object, which is where the loading for PermanentLocation somehow fails. I have a single Connection object to the database which is used to retrieve both the ActionBlock and PermanentLocation of which only the first loads. Loading happens during Plugin.onLoad()

    I've updated all my code to close the connection after using it + the location loading works fine in normal usage.

    Here's the relevant code of PermanentLocation:
    Code (Java):
    /**
         * Loads a permanent location from database using the given uuid
         * @param conn Connection to the database
         * @param uuid uuid of the location
         * @return location loaded or null if none found
         */

        public static PermanentLocation loadLocation(Connection conn, UUID uuid) {
            PermanentLocation location = null;
            try {
                PreparedStatement st = conn.prepareStatement("SELECT world, x, y, z, pitch, yaw FROM location WHERE id = ? AND serverID = ?;");
                st.setString(1, uuid.toString());
                st.setString(2, KaranteeniPlugin.getServerIdentificator());
                ResultSet set = st.executeQuery();
                if(set.next()) {
                    String w = set.getString(1);
                    double x = set.getDouble(2);
                    double y = set.getDouble(3);
                    double z = set.getDouble(4);
                    float pitch = set.getFloat(5);
                    float yaw = set.getFloat(6);
               
                    World world = Bukkit.getWorld(w);
                    if(world == null)
                        return null;
                    else
                        Bukkit.getLogger().log(Level.SEVERE, "Could not find world " + w);
               
                    Location loc = new Location(world, x,y,z,pitch,yaw);
               
                    location = new PermanentLocation(uuid, loc);
                }
                else
                    location = null;
            } catch(SQLException e) {
                Bukkit.getLogger().log(Level.SEVERE, e.getMessage());
            }
       
            return location;
        }

    Relevant code of the ActionBlock loader. The "Could not load location" is always printed to the console
    Code (Java):
        /**
         * Loads the blocks from the database. All registerClass calls must be called before this
         */

        public void loadBlocksFromDatabase() {
            Connection conn = null;
            try {
                conn = KaranteeniCore.getDatabaseConnector().openConnection();
                PreparedStatement stmt = conn.prepareStatement(
                        "SELECT uuid,permission,classtype FROM actionblock WHERE serverID = ?;");
                stmt.setString(1, KaranteeniCore.getServerIdentificator());
                ResultSet set = stmt.executeQuery();
           
                // loop all the blocks from the database
                while(set.next()) {
                    String uuidStr = set.getString(1);
                    String permission = set.getString(2);
                    String classType = set.getString(3);
               
                    UUID uuid = UUID.fromString(uuidStr);

                    // check if loaded block is a block or a sign
                    for(Class<? extends ActionBlock> clazz : subBlockClasses)
                    if(clazz.getName().equals(classType)) {
                        // load the location of the block
                        PermanentLocation loc = PermanentLocation.loadLocation(conn, uuid);
                        if(loc == null) {
                            Bukkit.getLogger().log(Level.WARNING, "Could not load location " + uuid.toString() + ". Has it been deleted from database?");
                            continue;
                        }
                   
                        Block block = loc.getLocation().getBlock();
                        try {
                            // try to create a new class out of this block
                            if(permission != null) {
                                Constructor<? extends ActionBlock> cstr = clazz.getConstructor(Block.class, UUID.class, String.class);
                                ActionBlock aBlock = cstr.newInstance(block, uuid, permission);
                                aBlock.onLoad();
                                registerBlock(aBlock); // register created class
                            } else {
                                Constructor<? extends ActionBlock> cstr = clazz.getConstructor(Block.class, UUID.class);
                                ActionBlock aBlock = cstr.newInstance(block, uuid);
                                aBlock.onLoad();
                                registerBlock(aBlock); // register created class
                            }
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    }
               
                    // load all signs
                    for(Class<? extends ActionSign> clazz : subSignClasses)
                    if(clazz.getName().equals(classType)) {
                        // load the location of the block
                        Block block = PermanentLocation.loadLocation(uuid).getLocation().getBlock();
                        try {
                            // try to create a new class out of this block
                            if(permission != null) {
                                Constructor<? extends ActionSign> cstr = clazz.getConstructor(Block.class, UUID.class, String.class);
                                ActionBlock aBlock = cstr.newInstance(block, uuid, permission);
                                aBlock.onLoad();
                                registerBlock(aBlock); // register created class
                            } else {
                                Constructor<? extends ActionSign> cstr = clazz.getConstructor(Block.class, UUID.class);
                                ActionBlock aBlock = cstr.newInstance(block, uuid);
                                aBlock.onLoad();
                                registerBlock(aBlock); // register created class
                            }
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    }
                }
            } catch(SQLException e) {
                e.printStackTrace();
                // ignored
            } finally {
                try {
                    if(conn != null)
                        conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    Database tables:
    location: id, serverID, world, x, y, z, pitch, yaw
    actionblock: uuid, serverID, permission, classtype
    Both have 1 row of data with the same uuid. Accessing them through Database directly works fine, but the same static load query does not work in Java. No errors printed to console

    Edit: Tried storing data from ResultSet and then closing it and PreparedStatement before trying to load the location. No effect
     
    #1 Nuubles, Nov 15, 2019
    Last edited: Nov 15, 2019
  2. You could try printing the parameters for your prepared statements to check wether they are correct.
     
  3. I did and they are. I also tried to directly get the value from the database and copy-pasted the same query to the java code and it still didn't work (SELECT world, x, y, z FROM location WHERE id = 'long-uuid-from-java' AND serverID = 'hub'; )
     
  4. Strahan

    Benefactor

    Well, if running the query directly against the database returns nothing then looking at your code is irrelevant as the problem obviously isn't there. I assume you get no errors when you run the query directly? If so, do a SELECT * FROM location and look at your data to ensure the query is just not working properly and that you really don't have matching data.

    Oh wait, I think I read that backwards. You mean the query that worked fine directly in the database was ported to the code and failed? In that case I'd turn on logging then run the query in the Java code then go check the log.

    In your database's SQL manager do:
    Code (Text):
    SET GLOBAL log_output = 'TABLE';
    SET GLOBAL general_log = 'ON';
    Then do the plugin function, then back in the SQL manager do SELECT * FROM mysql.general_log and look for the plugin's query and see what it looks like.

    PS this is assuming you are using MySQL. If not, just look up logging for whatever DBMS you use.
     
    • Informative Informative x 1
  5. Yes, the database uses MySQL.
    Heres the actionblock table contents (test data):
    [​IMG]
    And here's the location table data (test data, bottom row relevant only)
    [​IMG]

    And here's the log (had to scroll down but it says "SELECT TABLE_NAME ---" in the parts which can't be seen)
    [​IMG]

    I've never used MySQL log before so I apologize if I made some mistakes!
    I tried to run "SHOW WARNINGS" but nothing happens

    Here's sys.statements_with_errors_or_warnings
    [​IMG]

    The code ran was the same as in the code in my initial post at the top. The Actionblock error is from yesterday when I was still developing the code and I had a slight mistake in the SQL query so it most likely is not relevant to this problem

    Edit: just noticed the database manager didn't display all the rows; here's the statement:
    [​IMG]

    And here's the result of that query being run directly from the DBMS:
    [​IMG]
     
    #5 Nuubles, Nov 16, 2019
    Last edited: Nov 16, 2019
  6. Found answer: Location cannot be loaded whilst the server is starting as the World class needs to have access to server world on boot when this code is ran
     
    #8 Nuubles, Nov 21, 2019
    Last edited: Nov 21, 2019