Java and PostgreSQL stored procedure - return registered as out parameter, causing issues with in parameters

Question

I'm trying to call a PostgreSQL stored procedure from a Java app; the procedure has a DATE type parameter so I'm using a java.sql.Date type with CallableStatement.setDate(). However, executing the statement always results in an exception and the SQL logs show this:

LOG:  execute <unnamed>: select * from athlete.create_athlete($1,$2,$3,$4,$5,$6,$7) as result
DETAIL:  parameters: $1 = '', $2 = '[email protected]', $3 = 'Joe', $4 = 'Blow', $5 = 'foobar', $6 = 'M', $7 = '1979-03-22 -04:00:00'
ERROR:  column "dob" is of type date but expression is of type text at character 122
HINT:  You will need to rewrite or cast the expression.
QUERY:  INSERT INTO athlete.athlete (email, first_name, last_name, password, gender, dob) VALUES ( $1 ,  $2 ,  $3 ,  $4 ,  $5 ,  $6 )
CONTEXT:  PL/pgSQL function "create_athlete" line 2 at SQL statement
STATEMENT:  select * from athlete.create_athlete($1,$2,$3,$4,$5,$6,$7) as result

The stored procedure actually has 6 parameters (and should receive values $2 through $7 above) - the 7th comes from registering the return value as an out parameter. This has me confused - is it correct that it appears as 7 parameters when I register an out parameter for the return value?

From all the docs I've read I'm under the impression that the return value has to be registered as the first parameter:

registerQuery = "{? = call athlete.create_athlete(?,?,?,?,?,?)}";
...
CallableStatement cs = conn.prepareCall(registerQuery);
cs.registerOutParameter(1, Types.BOOLEAN);
cs.setString(2, email);
...

The error above suggests to me that there's a mismatch between stored procedure parameters and the parameters supplied to the insert statement. I've been following documentation for all of this but am clearly doing something wrong. How do I supply the proper parameters to the stored procedure and retrieve the return value after the call?

Answer 1

Depends on signature of your stored procedure type (function/procedure).

For function like one below, out parameter will be first one and will have param1 and param2 as second and third parameters.

DB Procedure (for function):

CREATE FUNCTION my_func (
param1 INT,
param2 INT) 
    RETURNS INT
AS 
    :
    :

Java code (for function):

registerQuery = "{? = call my_func(?,?)}";
...
CallableStatement cs = conn.prepareCall(registerQuery);
cs.registerOutParameter(1, Types.INTEGER);
cs.setInteger(2, 10);
cs.setInteger(3, 10);
...

.

However for procedure like one below, out parameter will be third one and will have param1 and param2 as first and second parameters.

DB Procedure (for procedure):

CREATE PROCEDURE my_proc (
param1 INT,
param2 INT,
OUT param3 INT)
BEGIN
    :
    :
END;

Java code (for procedure):

registerQuery = "{call my_func(?,?,?)}";
...
CallableStatement cs = conn.prepareCall(registerQuery);
cs.registerOutParameter(3, Types.INTEGER);
cs.setInteger(1, 10);
cs.setInteger(2, 10);
...

.

Note that you can have multiple out parameters while only one return value.

Answer 2

It turns out the issue was that the order of parameters passed to the stored procedure did not match the order those parameters were passed to the insert statement. I don't understand why PostgreSQL would use named parameters if order is significant.

For example, the signature of the stored procedure was as follows:

CREATE FUNCTION insert_Person (IN in_name TEXT, IN in_gender CHAR(1), IN in_bdate DATE) RETURNS BOOLEAN...

The INSERT statement contained within that stored procedure was as follows:

INSERT INTO Person (name, bdate, gender) VALUES (in_name, in_bdate, in_gender);

Changing the order of the parameters such that they matched in either the stored procedure signature or insert statement (I went with the former) resolved the issue.