CallableStatement: using named parameters to specify only certain parameter values

[gordthompson]

(ref: this Stack Overflow question)

Section 13.3.2 of the JDBC 4.2 specification says

Named parameters can be used to specify only the values that have no default value.

In CODE EXAMPLE 13-21, the procedure COMPLEX_PROC takes ten parameters, but only the first and fifth parameters, PARAM_1 and PARAM_5, are required.

CallableStatement cstmt = con.prepareCall("{CALL COMPLEX_PROC(?, ?)}";
cstmt.setString("PARAM_1", "Price");
cstmt.setFloat("PARAM_5", 150.25);

I just tried this with 6.3.4.jre8-preview.jar and I couldn't get it to work. It seemed that I had to provide a parameter placeholder for every possible parameter, ((?,?,?,?,?,?,?,?,?,?) in the above example), and I didn't see a way to specify DEFAULT for a parameter that I would have otherwise simply omitted.

In other words, it seems like named parameter support for CallableStatement in mssql-jdbc is currently limited to allowing us to specify the parameters in any order, but not to "pick and choose" the parameters we actually supply.

Have I missed something?

[ulvii]

Hi @gordthompson ,
Thank you for creating the issue, I can confirm that this is a deficiency in the implementation. #547 is potentially related, we will address the issue once/when the PR is merged.

[JaapD]

@cheenamalhotra It looks like it is in progress for more than two years. Is there any progress?

[peterbae]

Hi @JaapD, we haven't been able to work on this, the team will let you know when we have an update.

[praskutti]

Hi @JaapD , I actually created a custom library for this. I will post the code in my github soon.

[kliakos]

Any update on whether this will be addressed soon?

[lilgreenbird]

hi @kliakos
Sorry to report that this issue is still in our backlog it didn't make it to the top of the list in triage.

[jocstar]

As part of a large update we included migrating from JTDS to the MS JDBC driver but hit this problem in testing. This is a big issue for us so I have been looking into the possibility of providing a patch for it. I do have a proof of concept test working that will probably be suitable for our internal use but have a question before looking to take it any further publicly.

1. According to the JDBC4.1 spec -

It is not possible to combine setting parameters with ordinals and with names in the same statement. 
If ordinals and names are used for parameters in the same statement, an SQLException is thrown

This rule doesn't currently seem to be enforced, and there is an existing unit test - StatementTest.testJdbc41CallableStatementMethods - that does mix parameters, so a little unsure what to do about that. My POC did enforce this but I took that out after seeing that test fail, so now in my experiment this test still passes, but it is not in keeping with the spec as far as I can see.

[jocstar]

Proposal for changes that allow for default parameters in CallableStatements.
The JDBC spec does forbid the mixing of named and indexed parameters, but this has been allowed in the past and there are a few test cases that have mixed named and indexed parameters.
In my proposed changes, this is still allowed if ALL parameters declared by the proc are provided. If there is an inbalance and the CALL(?,...) has less placeholders than the number of parameters the proc lists then we do not allow mixing.

The main changes in my proposal are in the findColumn(String columName) method, and are largely an extension of the work done for (#1064) which dealt with the case when the user doesn't have permissions to see the parameters via sp_sproc_columns.

When there is not an imbalance and all parameters the proc expects are provided, then it essentially works the same way it did before. We get the param details from sp_sproc_columns and map these names 1::1 to the inOutParam[].

When there is an imbalance, if only indexed parameters are used it works the same way as before with limits. As long as the proc only has defaultable parameters after the mandatory parameters you can call it.
e.g.
a proc with @param1 int, @Param2 int, @param3 int=123
can be called with
try (CallableStatement stmt = connection.prepareCall("{call TEST(?,?)}")) {
stmt.setInt(1, 111);
stmt.setInt(2, 222);
stmt.execute()
}
a proc with @param1 int, @Param2 int=123, @param3 int
can not be called and will complain that @param3 was not set.

When there is an imbalance and you set a named parameter, as before it calls sp_sproc_columns but now it doesn't map these 1::1 to inOutParam[]. It only uses the resultset from this to validate the name exists (if sp_sproc_columns returned values). After that, they are sequentially assigned to a parameter in inOutParams[] and the index for that name is stored so further requests get the same parameter. The key difference is that we now also store the name of the parameter to the Parameter object so we can use it later when building the SQL passed to the server.

The SQLServerConnection.replaceParameterMarkers method has been altered so if it finds a Parameter with a name it uses that as an ?alias? (not sure of the correct term for that) in the SQL. So if the first named parameter provided was called NAME, then the SQL would be @name=@p1 and the rest of the query is constructed exactly as it was before.

The execute methods have been overridden in CallableStatement to allow us to do a final validation before sending the request, and here if we find there was an imbalance and named and indexed parameters have both been provided we throw an exception. Initially I wasnted to do this at the time the parameter was set, but couldnt find a clean way to tap into it as the setXXX(String paramName,...) methods all eventually call their indexed counterpart once findColumn has mapped the name to an index. So the only way to do that would have been to override all the indexed setters, but as I was trying to keep this as small a change as possible I opted not to do that and instead do a final check in the execute.

PR to follow