Support named parameters in CallableStatementCreator implementation [SPR-4406]

[spring-projects-issues]

Mike Calmus opened SPR-4406 and commented

Named parameters have been added for some operations, but the execution of a CallableStatement does not support named parameters.

The following code block from CallableStatementFactory.CallableStatementCreatorImpl would be affected or need to be extended:

int sqlColIndx = 1;
for (int i = 0; i < declaredParameters.size(); i++) {
	SqlParameter declaredParameter = (SqlParameter) declaredParameters.get(i);
	if (!declaredParameter.isResultsParameter()) {
		// So, it's a call parameter - part of the call string.
		// Get the value - it may still be null.
		Object inValue = this.inParameters.get(declaredParameter.getName());
		if (declaredParameter instanceof ResultSetSupportingSqlParameter) {
			// It's an output parameter: SqlReturnResultSet parameters already excluded.
			// It need not (but may be) supplied by the caller.
			if (declaredParameter instanceof SqlOutParameter) {
				if (declaredParameter.getTypeName() != null) {
					cs.registerOutParameter(sqlColIndx, declaredParameter.getSqlType(), declaredParameter.getTypeName());
				}
				else {
					if (declaredParameter.getScale() != null) {
						cs.registerOutParameter(sqlColIndx, declaredParameter.getSqlType(), declaredParameter.getScale().intValue());
					}
					else {
						cs.registerOutParameter(sqlColIndx, declaredParameter.getSqlType());
					}
				}
				if ((declaredParameter).isInputValueProvided() || inValue != null) {
					StatementCreatorUtils.setParameterValue(csToUse, sqlColIndx, declaredParameter, inValue);
				}
			}
		}
		else {
			// It's an input parameter- must be supplied by the caller.
			if (!this.inParameters.containsKey(declaredParameter.getName())) {
				throw new InvalidDataAccessApiUsageException(
						"Required input parameter '" + declaredParameter.getName() + "' is missing");
			}
			StatementCreatorUtils.setParameterValue(csToUse, sqlColIndx, declaredParameter, inValue);
		}
		sqlColIndx++;
	}
}

---

Issue Links:

• Support for named parameters in SimpleJdbcCall [SPR-12801] #17398 Support for named parameters in SimpleJdbcCall
• StoredProcedure enhancement to allow parameters to be optional [SPR-3510] #8193 StoredProcedure enhancement to allow parameters to be optional

10 votes, 8 watchers

[spring-projects-issues]

Thomas Risberg commented

Not sure what the benefit would be. The recommended stored procedure classes are StoredProcedure and SImpleJdbcCall. Both of those create the call string behind the scenes and wouldn't need to use named parameters. The CallableStatementFactory is a helper class and parsing of a named parameter call statement can be done beforehand using NamedParameterUtils.parseSqlStatementIntoString(String s) method.

[spring-projects-issues]

Mike Calmus commented

Using the StoredProcedure class as-is requires users of a stored procedure to know the exact position and sequence of each parameter and prevents the database developer from making changes to the procedure's parameters (such as adding new optional parameters or re-ordering parameters) without also changing all code making calls to it.

[spring-projects-issues]

Thomas Risberg commented

OK, now I understand what you are looking for. This is related to #8193. Both issues require using named parameters for setting and registering parameters which involves some major changes internally. We will try to address this for Spring 3.0.

[jhoeller]

Closing this old ticket along with #30207.