More SQL Errors...

so i'm trying to add a row in the event of a player placing a fence post. Before it adds the row, it checks if it exists. If it doesn't, it adds the row. If it does, then it checks if the chunk belongs to the player. If it doesn't belong to the player, it sends the message that the area belongs to someone else, ( eventually will cancel the event ). But I'm getting errors :-( ....
heres my code and stacktrace...

Code:

@EventHandler
    public void onFencePlace(BlockPlaceEvent event) throws SQLException {
        String player = event.getPlayer().getName();
        String world = event.getBlock().getWorld().getName();
        int x = event.getBlock().getChunk().getX();
        int z = event.getBlock().getChunk().getZ();
        String message = sql.addChunk(this, player, world, x, z);
        event.getPlayer().sendMessage(ChatColor.DARK_BLUE + message);
    }

Code:

public static String addChunk(ChunkProtection plugin, String player, String world, int x, int z) throws SQLException {
        String Status = "Error When Attemping To Claim Chunk!";
        Connection con = connect(plugin);
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery("SELECT * FROM CHUNKS WHERE (" +
                "WORLD='" + world + "' AND " +
                        "X_POS=" + x + " AND " +
                                "Z_POS=" + z + ");");
        ResultSet pid_rs = st.executeQuery("SELECT * FROM PLAYERS WHERE NAME='" + player + "';");
        int pid = pid_rs.getInt("P_ID");
        if (!rs.next()) {
            st.executeUpdate("INSERT INTO CHUNKS(P_ID,WORLD,X_POS,Z_POS)" +
                    "VALUES(" + pid + ",'" + world + "'," + x + "," + z + ");");
            Status = "Welcome to your newly claimed land!";
        } else {
            if (!(rs.getInt("P_ID") == pid)) {
                Status = "This already land belongs to someone else!";
            }
        }
        return Status;
    }

 

what line is the error line?

 

When you execute a query and get back the result, the current line of the result is before the first row (so basically nothing). In this case you're trying to get the value for the column "P_ID" in that row, thus being before the first row, it gives you an error. What you need to do is put that line into the if to let it first go into the first line (if it exists) and then get the "P_ID" from that row.

 

I don't understand what you're telling me here.

 

Code:

int pid = pid_rs.getInt("P_ID");
if (!rs.next()) {
  [...]
} else {
  if (!(rs.getInt("P_ID") == pid)) {
      [...]
  }
}

In the first line of this code

meaning you're trying to receive the value in that row from the column 'P_ID'. Because it's before the first row, it's nothing, so you're, as an example, calling a method on a null value hypothetically spoken. If you now use 'rs.next()' or 'rs.first()' you move to the first available row. When you now retrieve any values from columns of that row, it'll work fine.

 

ok, so i threw it inside of an if statement, to access the first row...(haven't tested it yet) but I now seeing this, it's basically doing the same thing... how would I go about assigning a variable the value of the specified column and row?

So I think I got the values to assign to variables. But now I'm having a different error... It's saying that the operation can not happen because the result set was closed? I took out all the result set closes from my code and it still throws this error. Here is my code and stacktrace, help pls?

Code:

    @EventHandler
    public void onFencePlace(BlockPlaceEvent event) throws SQLException {
        String player = event.getPlayer().getName();
        String world = event.getBlock().getWorld().getName();
        int x = event.getBlock().getChunk().getX();
        int z = event.getBlock().getChunk().getZ();
        if ((sql.checkChunk(this, event, player, world, x, z)) == false) {
            sql.addChunk(this, event, player, world, x, z);
        }
    }

Code:

    public static boolean checkChunk(ChunkProtection plugin,
            BlockPlaceEvent event, String player, String world, int x, int z) throws SQLException {
        boolean status = false;
        Connection con = connect(plugin);
        Statement st = con.createStatement();
        ResultSet chunkrs = st.executeQuery("SELECT * FROM CHUNKS WHERE (" +
                "WORLD='" + world + "' AND " +
                "X_POS=" + x + " AND " +
                "Z_POS=" + z + ");");
        ResultSet playerrs = st.executeQuery("SELECT * FROM PLAYERS WHERE NAME='" + player + "';");
        if (chunkrs.next()) {
            int owner = chunkrs.getInt("P_ID");
            if (playerrs.next()) {
                int pid = playerrs.getInt("P_ID");
                if (!(pid == owner)) {
                    event.setCancelled(true);
                    event.getPlayer().sendMessage(ChatColor.RED + "This land already belongs to someone else!");
                }
            }
            status = true;
        }
        return status;
    }
 
    public static void addChunk(ChunkProtection plugin,
            BlockPlaceEvent event, String player, String world, int x, int z) throws SQLException {
        Connection con = connect(plugin);
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery("SELECT * FROM PLAYERS WHERE NAME='" + player + "';");
        if (rs.next()) {
            int pid = rs.getInt("P_ID");
            st.executeUpdate("INSERT INTO CHUNKS(P_ID, WORLD, X_POS, Z_POS)" +
                    "VALUES(" + pid + ",'" + world + "'," + x + "," + z + ");");
            event.getPlayer().sendMessage(ChatColor.GREEN + "Welcome to your newly claimed land!");
        }
    } 
}

EDIT by Moderator: merged posts, please use the edit button instead of double posting.

 

After some time google'ing around I've found the solution:
If you use the same statement variable to do two seperate quesries, the first resultset gets closed. Thus you're getting this error. I don't know why the statement does that but we have to live with that.

 

oh ok, i see. so i have to make different statements for each result set... thanks again man, your always save my ...