Search code examples
javapostgresqljdbcpsqlprocedure

"x is a procedure, use "call"" when I am already using call

I'm using Postgres 12 and have written this procedure:

CREATE OR REPLACE PROCEDURE reduceStock(id INTEGER, soldQuantity INTEGER)
    LANGUAGE plpgsql AS
    $$
    BEGIN
    UPDATE inventory SET ProductStockAmount = ProductStockAmount - soldQuantity WHERE ProductID = id;     
    END;
    $$;

It works perfectly if I open up psql on the command line and run call reduceStock(1,1);

However, calling it from my Java program as follows:

CallableStatement stmt = conn.prepareCall("{call reduceStock(?, ?)}");
stmt.setInt(1, productID);
stmt.setInt(2, quantity);
stmt.execute();

Gives me the following error: enter image description here

What I've Tried

  • running call reduceStock(1,1); from the psql client - works perfectly
  • dropping the database and starting over to see if some old definition was cached - didn't work
  • different capitalisations, spacings of call

Any ideas would be appreciated

Solution

  • You need to remove the curly braces, which are the JDBC escape for calling a procedure. But because Postgres has it's own call command, they are not needed (and collides with the JDBC escape).

    CallableStatement stmt = conn.prepareCall("call reducestock(?, ?)");