CallMetaDataContext.reconcileParameters doesn't catch output parameters with DatabaseMetaData.procedureColumnResult type (on Postgres) [SPR-16611]

[spring-projects-issues]

Ivan Vasilev opened SPR-16611 and commented

I added a comment here but I think, it'll be better to create a new issue.

I use spring-jdbc with postgres driver. To call stored procedure which returns a table row, I use the following code:

        MapSqlParameterSource params = new MapSqlParameterSource()
                .addValue("in_id", name);

        Map<String, Object> resultMap = new SimpleJdbcCall(jdbcTemplate)
                .withFunctionName(STORED_FUNCTION_NAME)
                .execute(params);

It produces following exception:

org.springframework.dao.InvalidDataAccessApiUsageException: Required input parameter 'id' is missing
at org.springframework.jdbc.core.CallableStatementCreatorFactory$CallableStatementCreatorImpl.createCallableStatement(CallableStatementCreatorFactory.java:211)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1083)
at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1141)
at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:405)
at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:365)
at org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:198)
at com.example.demo.DemoApplicationTests.callProcedure(DemoApplicationTests.java:35)

It wants me insert an output parameter.

I searched a little and found following:

• PgDatabaseMetaData returns java.sql.DatabaseMetaData.procedureColumnResult.
• CallMetaDataContext doesn't check parameter type for equality to this value. It checks for DatabaseMetaData.procedureColumnReturn only.

I created a sample project (in attachment). It uses Flyway to initialize and Testcontainers to use real postgres db. Change driver name and url if you do not want to use docker.

---

Affects: 4.3.13

Attachments:

• demo.zip (28.39 kB)

Issue Links:

• PostgresTableMetaDataProvider.isGetGeneratedKeysSimulated() does not detect Postgres 10 [SPR-16556] #21099 PostgresTableMetaDataProvider.isGetGeneratedKeysSimulated() does not detect Postgres 10
• SimpleJdbcCall with rowMapper set fails on execution (on Postgres) [SPR-16709] #21250 SimpleJdbcCall with rowMapper set fails on execution (on Postgres)

Referenced from: commits b7c4238, 301ba01

Backported to: 4.3.15

[spring-projects-issues]

Juergen Hoeller commented

This is in master now, becoming available in the upcoming 5.0.5.BUILD-SNAPSHOT. I'll backport it to 4.3.15 tomorrow.