Search code examples
javapostgresqljdbccallable-statement

How can you use INSERT....RETURNING in a JDBC callableStatement

Using Callable statements I want to return the serial type column from my database without using a function to do it.

So, I tried to use the following statement to pass as parameter to my prepareCall function of my connection to generate a CallableStatement.

{? = INSERT INTO schema.tbl (col1, col2, col3, col5) VALUES (?, ?, ?, ?) RETURNING col6}

Here col6 has datatype as serial.

But this is generating an exception :

org.postgresql.util.PSQLException: Malformed function or procedure escape syntax at offset 5.

I feel the syntax used for this statement is wrong. If that is so what could be the correct statement for this?

NOTE: I am not able to use connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);

NOTE: I also know how it can be done by declaring a function in the Postgresql Database Schema. Please do not answer about this method to solve the problem.

I specifically request that please answer by stating how the statement can be changed so that it can work with CallableStatement

Solution

  • From all the comments I have received on this question simply point to the direction that it is not possible to use INSERT .... RETURNING statement as a parameter to a CallableStatement.

    It can either be used as a parameter PreparedStatement using Statement.RETURN_GENERATED_KEYS as the second parameter as I stated earlier in the question.

    Or it can be used by wrapping it in an Stored Function in the Postgresql Database Schema and then using {? = CALL <STORED FUNCTION>} as parameter in the CallableStatement.