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.
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
}
PreparedStatementinstead of aCallableStatement.PreparedStatementandPreparedStatement.executeUpdate(). Anupdatestatement does not return aResultSetCallableStatementis used to call procedures and functions, not to run queries. Follow the advice from the previous comments and usePreparedStatement.