Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
spring-projects-issues opened this issue Apr 11, 2018 · 6 comments
Labels
in: data status: waiting-for-triage

Comments

@spring-projects-issues
Copy link
Collaborator

@spring-projects-issues spring-projects-issues commented Apr 11, 2018

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:

Issue Links:

  • #21152 CallMetaDataContext.reconcileParameters doesn't catch output parameters with DatabaseMetaData.procedureColumnResult type (on Postgres)
@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Apr 11, 2018

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?

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Apr 12, 2018

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)

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Apr 12, 2018

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;

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Apr 12, 2018

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.

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Apr 13, 2018

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.

@adolin-negash
Copy link

@adolin-negash adolin-negash commented Jul 2, 2021

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
in: data status: waiting-for-triage
Projects
None yet
Development

No branches or pull requests

2 participants