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?

Gladwin Burboz · Accepted Answer · 2010-02-17 02:26:28Z

4

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.

answered Feb 17, 2010 at 2:26

Gladwin Burboz

3,56918 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Matt Over a year ago

I'm using the former stored procedure signature, so I guess that's not the problem. I find it strange that if I pass a Date to a Date parameter that I'd need to cast it for the insert (which is inserting to a Date column.)

Gladwin Burboz Over a year ago

Can you please update your question with signature of your stored proc

ManiP · Accepted Answer · 2011-01-19 09:42:27Z

1

Postgresql server supports named parameters but the jdbc driver does not support it(well not yet to my knowledge), so until such time only positional parameters is supported.

answered Jan 19, 2011 at 9:42

ManiP

7432 gold badges8 silver badges20 bronze badges

Comments

Matt · Accepted Answer · 2010-03-02 16:33:41Z

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.

Surjeet Katwal · Accepted Answer · 2013-07-04 08:16:19Z

0

Due to a restriction in the OCI layer, the JDBC drivers do not support the passing of BOOLEAN parameters to PL/SQL stored procedures. If a PL/SQL procedure contains BOOLEAN values, you can work around the restriction by wrapping the PL/SQL procedure with a second PL/SQL procedure that accepts the argument as an INT and passes it to the first stored procedure. When the second procedure is called, the server performs the conversion from INT to BOOLEAN.

answered Jul 4, 2013 at 8:16

Surjeet Katwal

1161 silver badge2 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related