Support for named parameters in `SimpleJdbcCall` [SPR-12801]

[spring-projects-issues]

Kiril Nugmanov opened SPR-12801 and commented

org.springframework.jdbc.core.simple.AbstractJdbcCall uses org.springframework.jdbc.core.metadata.CallMetaDataContext to generate JDBC statement.

In case of Oracle PL/SQL org.springframework.jdbc.core.metadata.CallMetaDataContext.createCallString can be changed to support default values by implementing following changes in constructing JDBC statement from:

{call some_package.some_procedure(?,?,?)}

to

{call some_package.some_procedure(p_parameter_1 => ?, p_parameter_2 => ?, p_parameter_3 =>?)}

Change can be implemented in 2 ways:

• modify org.springframework.jdbc.core.metadata.CallMetaDataContext in case if DB is Oracle
• introduce setter for callMetaDataContext in org.springframework.jdbc.core.simple.AbstractJdbcCall then users can introduce it's own modification of org.springframework.jdbc.core.metadata.CallMetaDataContext

Related to #9084, #8193

---

Affects: 4.1.5

Reference URL: #763

Issue Links:

• StoredProcedure enhancement to allow parameters to be optional [SPR-3510] #8193 StoredProcedure enhancement to allow parameters to be optional
• Support named parameters in CallableStatementCreator implementation [SPR-4406] #9084 Support named parameters in CallableStatementCreator implementation

1 votes, 4 watchers

[spring-projects-issues]

Serge Shikov commented

The same for MS SQL please. At the moment (in spring 3.x versions) the same call syntax is used, with positional parameters only:

{call sp(?,?,?)}

and it prevents us from using parameters defined with default values.

[spring-projects-issues]

Juergen Hoeller commented

We're considering this for 4.2 now. A pull request would be very much appreciated and would strongly increase the chances of this making it into 4.2 RC1!

Juergen

[spring-projects-issues]

Kiril Nugmanov commented

Created pull #763

[spring-projects-issues]

Stéphane Nicoll commented

I have a candidate fix based on your PR and a few modifications (see this commit). I am still not sure if swapping the context is the right move.

You may want to review the Spring Framework Code Style as it contains valuable resources regarding the code format and the structure of the commit message.

[spring-projects-issues]

Kiril Nugmanov commented

Stéphane,

Can you explain why such swapping might be wrong?

[spring-projects-issues]

Stéphane Nicoll commented

Hey, I am not saying it's wrong. I am just confused about the approach of generating always "?" or always a named parameter for a given SimpleJdbcCall instance. I am not sure I actually fully understand how it's being used in practice.

What's wrong with adding the parameter always?

[spring-projects-issues]

Kiril Nugmanov commented

I think if you make named binding for Oracle/MS SQL as default for SimpleJdbcCall - previously working code might be broken. In existing code version there is no big deal how you name your parameters but in case of named parameters IS.

[spring-projects-issues]

Stéphane Nicoll commented

Gotcha. What's the problem of adding a setNamedBinding() on the existing CallMetadataContext and handle everything from there? There are already other setters on this class so if the idea is to either do everything with ? or do everything with parameter name, the existing class should be able to support both.

[spring-projects-issues]

Kiril Nugmanov commented

Adding setter in CallMetadataContext wont provide much flexibility. Moreover how you set namedBinding?

Current dependencies are following: AbstractJdbcCall<-CallMetaDataContext (CallMetaDataContext provided via constructor). So you need to create new instance of CallMetadataContext anyway and call setNamedBinding() on it.

[spring-projects-issues]

Stéphane Nicoll commented

Fixed in a slightly different way, adding the notion of named binding in the current CallMetadataContext instead of relying on an extension.

A new 4.2.0.BUILD-SNAPSHOT should be available in the next couple of hours. Please give that a try!