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

user_8044041 0
Viewed 553

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

procedure psql jdbc postgresql java
2 Answers

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(?, ?)");
user_3303150

The curly braces around the procedure inocation ({call reduceStock(?, ?)}) mean that this is not native SQL, but rather JDBC syntax. You can read more about it here: Why do JDBC calls to stored procedures wrap the call in curly brackets?.

So calls like this still have to get translated to the native SQL by the JDBC driver. It happens that the Postgres driver, by default, treats such statements as function calls and translates them to SELECT reduceStock(?, ?) SQL query. This is not how stored procedures shall be called in Postgres. In Postgres a stored procedure call SQL is call reduceStock(?, ?).

One way to make it work would be, like @a_horse_with_no_name wrote in his answer, to remove the curly braces. This makes the statement a native call and because it's a valid Postgres SQL this is going to work. The downside is that it makes it less cross-platform as it will not work for DBs that don't support the call procname() syntax. For example this won't work for Oracle, so if you have to support multiple JDBC drivers, this is the less-preferable way to go.

A better fix would be to hint Postgres JDBC driver to treat this syntax like a stored procedure call rather than a function call and translate it to SQL accordingly. For this purpose the Postgres driver exposes a escapeSyntaxCallMode configuration property (check out the EscapeSyntaxCallMode enum as well):

Specifies how the driver transforms JDBC escape call syntax into underlying SQL, for invoking procedures or functions. (backend >= 11) In escapeSyntaxCallMode=select mode (the default), the driver always uses a SELECT statement (allowing function invocation only). In escapeSyntaxCallMode=callIfNoReturn mode, the driver uses a CALL statement (allowing procedure invocation) if there is no return parameter specified, otherwise the driver uses a SELECT statement. In escapeSyntaxCallMode=call mode, the driver always uses a CALL statement (allowing procedure invocation only).

As you can see all {call something()} statements are treated like function calls by default and always translated to SELECTs. Setting escapeSyntaxCallMode to call will make the driver translate them to call SQL statements instead. The callIfNoReturn option seems most reasonable for most use-cases as it will transform JDBC calls to stored procedure calls if no return parameter has been specified and as function calls otherwise.

You can find an example of using this setting in Postgres docs (Chapter 6. Calling Stored Functions and Procedures):

// set up a connection
String url = "jdbc:postgresql://localhost/test"; 
Properties props = new Properties(); 
// ... other properties ... 
// Ensure EscapeSyntaxCallmode property set to support procedures if no return value
props.setProperty("escapeSyntaxCallMode", "callIfNoReturn");
Connection con = DriverManager.getConnection(url, props);

// Setup procedure to call.
Statement stmt = con.createStatement();
stmt.execute("CREATE TEMP TABLE temp_val ( some_val bigint )");
stmt.execute("CREATE OR REPLACE PROCEDURE commitproc(a INOUT bigint) AS '"
    + " BEGIN "
    + " INSERT INTO temp_val values(a); "
    + " COMMIT; "
    + " END;' LANGUAGE plpgsql");
stmt.close();

// As of v11, we must be outside a transaction for procedures with transactions to work.
con.setAutoCommit(true);

// Procedure call with transaction
CallableStatement proc = con.prepareCall("{call commitproc( ? )}");
proc.setInt(1, 100);
proc.execute(); proc.close();>

-- https://jdbc.postgresql.org/documentation/head/callproc.html#call-procedure-example

user_44945770
Related