Spring SimpleJdbcCall with rowMapper set fails on execution (on Postgres) [SPR-16709]

Ivan Vasilev opened SPR-16709 and commented

Further to my previous issue #21152.

spring-jdbc: 4.3.15 org.postgresql:postgresql: 9.4.1212.jre7

I've got 4.3.15 yesterday. And tested it with demo project which I attached to #21152. Test passes, so issue is resolved, thank you.

I wrote a stored function that returns a table row. When I call it returning a map of values, everything works properly. But when I set jdbcCall.returningResultSet("resultvalue", rowMapper), something goes wrong:

org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{? = call find_customer(?, ?)}]; nested exception is org.postgresql.util.PSQLException: A CallableStatement function was executed and the out parameter 1 was of type java.sql.Types=4 however type java.sql.Types=1111 was registered. at org.postgresql.jdbc.PgCallableStatement.executeWithFlags(PgCallableStatement.java:140) at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:157) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114) at com.sun.proxy.$Proxy90.execute(Unknown Source) at org.springframework.jdbc.core.JdbcTemplate$6.doInCallableStatement(JdbcTemplate.java:1144) at org.springframework.jdbc.core.JdbcTemplate$6.doInCallableStatement(JdbcTemplate.java:1141) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1089) at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1141) at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:406) at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:366) at org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:197) at com.example.demo.DemoApplicationTests.callProcedureReturningResultSetWithRowMapper(DemoApplicationTests.java:54)

I attach the sample project below.

---

Affects: 4.3.15

Attachments: - demo.zip (5.50 kB)

Issue Links: - #21152 CallMetaDataContext.reconcileParameters doesn't catch output parameters with DatabaseMetaData.procedureColumnResult type (on Postgres)

Comment From: spring-projects-issues

Juergen Hoeller commented

So the Postgres driver complains that the call was declared with an out parameter of type OTHER when it actually resulted in INTEGER? Could you may fine-tune your call declaration accordingly and explicitly declare the out parameter as Types.INTEGER? Or is this related to ref cursor support where we use Types.OTHER by default?

Comment From: spring-projects-issues

Ivan Vasilev commented

I wrote one more test with explicitly declared parameters (note the commented line):

@Test
public void callStoredFunction() throws Exception {
    SimpleJdbcCall call = new SimpleJdbcCall(jdbc)
            .withFunctionName("find_customer")
//               .returningResultSet("customer", (rs, i) -> new Customer(rs.getInt("id"), rs.getString("name")))
            .withoutProcedureColumnMetaDataAccess()
            .declareParameters(
                    new SqlParameter("in_id", Types.INTEGER),
                    new SqlOutParameter("id", Types.INTEGER),
                    new SqlOutParameter("name", Types.VARCHAR)
            );

    MapSqlParameterSource params = new MapSqlParameterSource("in_id", 2);

    call.execute(params);
}

This test passes successfully.

But if I uncomment line setting RowMapper to call, test fails with following stack trace:

org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{? = call find_customer(?, ?, ?)}]; nested exception is org.postgresql.util.PSQLException: A CallableStatement was executed with an invalid number of parameters at org.postgresql.jdbc.PgCallableStatement.executeWithFlags(PgCallableStatement.java:102) at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:157) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114) at com.sun.proxy.$Proxy90.execute(Unknown Source) at org.springframework.jdbc.core.JdbcTemplate$6.doInCallableStatement(JdbcTemplate.java:1144) at org.springframework.jdbc.core.JdbcTemplate$6.doInCallableStatement(JdbcTemplate.java:1141) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1089) at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1141) at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:406) at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:366) at org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:197) at com.example.demo.DemoApplicationTests.callStoredFunction(DemoApplicationTests.java:115)

Comment From: spring-projects-issues

Ivan Vasilev commented

I think it may be handy to add an Postgres initialization code. As you can see, I do not use cursors.

CREATE TABLE customer
(
  id   INTEGER PRIMARY KEY,
  name VARCHAR
);

CREATE FUNCTION find_customer(IN in_id INTEGER)
  RETURNS customer
AS $$
  SELECT * FROM customer WHERE id = in_id LIMIT 1;
$$ LANGUAGE SQL;

Comment From: spring-projects-issues

Juergen Hoeller commented

I guess Postgres goes into a different mode of execution once you specify a ResultSet there, not considering the ResultSet-extracted values as out parameters.

At this point it's still unclear to me what we could be doing about it at Spring JDBC level. It might help to recode this using the plain JDBC API against the Postgres driver and see whether you get the same results, or ideally what can you do at that level to make it work with a ResultSet.

Comment From: spring-projects-issues

Ivan Vasilev commented

I think I found something.

PostgresCallMetaDataProvider.isReturnResultSetSupported() (here) returns false.

I don't know the code base so it can be correct. But why? I guess the problem is here.

Comment From: adolin-negash

Hello everyone. I got same issue, when tried to call postgress function. Watch my stackoverflow.com question

Comment From: snicoll

Thanks for the sample. Upgrading to a recent version of the framework and postgres, this leads to:

org.springframework.dao.InvalidDataAccessApiUsageException: Required input parameter 'id' is missing

    at org.springframework.jdbc.core.CallableStatementCreatorFactory$CallableStatementCreatorImpl.createCallableStatement(CallableStatementCreatorFactory.java:207)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1186)
    at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1246)
    at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:412)
    at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:372)
    at org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:198)

I believe this should be fixed by https://github.com/spring-projects/spring-framework/issues/31550. We can get back to this problem once we've made progress there.

Comment From: snicoll

I was wrong. I did investigate this a bit more but the function in the sample doesn't return a ResultSet so even if we change that flag, it doesn't fix the issue. I don't know enough about postgres to adapt the sample. If someone can then we can reopen this issue.

Comment From: snicoll

@adolin-negash I've also tried to run your sample but didn't manage to get postgres to return a ResultSet. If you can attach a sample we can run, this can be reconsidered.