I am not able to execute a query with the java Query Runner if the query contains a DO $$ – END $$ clause.
The Prepared Statement Query in question:
DO $$
declared_id integer := (select id from db.table where name = ? and description = ?);
BEGIN
delete from db.table2 where id = declared_id;
delete from db.table3 where id = declared_id;
delete from db.table4 where id = declared_id;
delete from db.table5 where id = declared_id;
END $$
The Code I am using with Query Runner.
Watered down the code so only the important parts are showing but this is the gist of it.
(I pass in the sql above as the sql parameter)
import org.apache.commons.dbutils.QueryRunner;
import java.sql.Connection;
public class dbTest {
public void execute query(String sql, String name, String description) {
Connection connection = new Connection(//Connection details etc....///);
new QueryRunner().execute(connection, sql, name, descripton);
}
}
Then I would get this error:
java.lang.RuntimeException: Could not execute update
Caused by: java.sql.SQLException: Wrong number of parameters: expected 0, was given 2
I am so confused by this because I clearly gave 2 ? parameters in the declare statement above.
Just so we are all clear, all other queryrunner executions work it is only the one that has a DO $$ block that does not.
Thanks in advance!
2
Answers
I think you need to modify your query to have the parameters be replaced inside the
BEGIN/END
. As @Belayer mentioned in the comments, the DO block cannot receive parameters.A
DO
block is not a query. Per the manual:The following query performs the same deletions as the
DO
block in the original post: