Question 352180 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Java QueryRunner : How to execute a query with a DO $$ Statement

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!

Tags:

2

Answers


  1. 0

    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.

    DO $$
    DECLARE
        declared_id integer;
    BEGIN
        SELECT id INTO declared_id FROM db.table WHERE name = ? AND description = ?;
        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
$$
    Login or Signup to reply.
  2. 0

    A DO block is not a query. Per the manual:

    DO executes an anonymous code block, or in other words a transient
    anonymous function in a procedural language.

    The code block is treated as though it were the body of a function
    with no parameters, returning void.

    The following query performs the same deletions as the DO block in the original post:

    WITH
  ids AS (
    SELECT id
      FROM db.table
      WHERE name = ?
        AND description = ?),
  dt2 AS (
    DELETE FROM db.table2 t2
      USING ids
      WHERE t2.id = ids.id),
  dt3 AS (
    DELETE FROM db.table3 t3
      USING ids
      WHERE t3.id = ids.id),
  dt4 AS (
    DELETE FROM db.table3 t4
      USING ids
      WHERE t4.id = ids.id)
DELETE FROM db.table3 t5
  USING ids
  WHERE t5.id = ids.id;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search