kadsank kadsank - 1 month ago 9
SQL Question

JDBC error: java.sql.SQLException: Cannot submit empty query

I am trying to query the database using JDBC and using the following commands

CallableStatement call_state =null;
call_state =con.prepareCall("UPDATE district SET d_next_o_id = ?+1 WHERE d_id = ? AND d_w_id = ?");
call_state.setInt(1, d_next_o_id);
call_state.setInt(2, d_id);
call_state.setInt(3, d_w_id);
rs=call_state.executeQuery();`


I got an exception: java.sql.SQLException: Cannot submit empty query.

Answer

Even though CallableStatement (CS) is the "interface used to execute SQL stored procedures", it is also a subinterface of PreparedStatement. For this reason, any CS implementation that abides to the Liskov Substitution Principle should also be able to execute normal UPDATE, SELECT or DELETE operations (anything a plain PreparedStatement can do).

The actual problem with your code is on the last line of the snippet you've provided; you're using executeQuery() on an UPDATE statement, which doesn't work. Instead, you should use either execute() or executeUpdate(). So changing that line into

call_state.executeUpdate();

should work.

If you want to do this "correctly", you should be idiomatic and use a PreparedStatement instead of CallableStatement – because you're not invoking any stored procedure. Additionally, you should take care of closing resources by using the try-with-resources statement:

try (PreparedStatement ps = con.prepareStatement(
        "UPDATE district SET d_next_o_id = ?+1 WHERE d_id = ? AND d_w_id = ?")) {
    ps.setInt(1, d_next_o_id);
    ps.setInt(2, d_id);
    ps.setInt(3, d_w_id);           
    ps.executeUpdate();
} catch (SQLException e) {
  // handle it
}
Comments