SqlCall calculates the number of input parameters wrong [DATAJDBC-7]

[spring-projects-issues]

Catwalker Liu opened DATAJDBC-7 and commented

SqlCall line 132-152 composing sql call string. The number of input parameters represented by "?" is calculated wrong if backend is function (vs. procedure) and SqlReturnResultSet is used.

MyStoredProcedure extends StoredProcedure ...

super(...)
declareParameter(new SqlReturnResultSet("rs", new MyMapper()));
declareParameter(new SqlParameter("FIRST_NM", Types.VARCHAR));

---

No further details from DATAJDBC-7

[spring-projects-issues]

Thomas Risberg commented

Does your code call setFunction(true)?

http://static.springsource.org/spring/docs/3.0.x/javadoc-api/org/springframework/jdbc/object/SqlCall.html#setFunction%28boolean%29

That's necessary if you are calling a function.

Also, anything that is part if Spring Framework should go in as a Spring JIRA issue (https://jira.springsource.org/browse/SPR). I'll move this later if necessary.

-Thomas

[spring-projects-issues]

Catwalker Liu commented

Yes I did call setFunction(true) after these declareParameter(...)

In code line 132-152 parameterCount is set to -1 for function, but the code also exclude SqlReturnResultSet effectively. (parameterCount++ is inside of instanceof check).
This is double de-count for (function+SqlReturnResultSet) case.

[spring-projects-issues]

Thomas Risberg commented

Yes, that is correct - SqlReturnResultSet is used with SQL Server and Sybase that return resultsets from regular stored procedure calls. Didn't think of that initially, and since you are calling a function it's not the right parameter type to use. Depending on the database your parameter would look a bit different. If you are using Oracle try the following:

declareParameter(new SqlOutParameter("rs", oracle.jdbc.OracleTypes.CURSOR, new MyMapper()));

and for Postgres you would use:

declareParameter(new SqlOutParameter("rs", Types.OTHER, new MyMapper()));

Let me know how this works out or if you are using some other database.

-Thomas

[spring-projects-issues]

Thomas Risberg commented

I'm closing this issue. If you are still having problems you can open a new Spring Framework issue (https://jira.springsource.org/browse/SPR).

-Thomas