JDBC SQL Server : The value is not set for the parameter number

Question

I received the following error from the code that calls a stored procedure from java code:

Exception Trace {} org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call test.usp_xxx_GetCompanyDetails(?, ?, ?, ?, ?, , ?, , ?, ?, ?, ?, ?)}]; SQL state [null]; error code [0]; The value is not set for the parameter number 11.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: The value is not set for the parameter number 11. at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1095) at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1131)

The application is deployed on WAS 8.5.5 and using jdbc driver version 4.2. On restarting the server this issue did not occur again. The following call statement generated looks to be incorrect. There are consecutive commas without ? between them.

{call test.usp_xxx_GetCompanyDetails(?, ?, ?, ?, ?, , ?, , ?, ?, ?, ?, ?)}

The stored procedure has 10 parameters. Following is the definition of the stored procedure:

CREATE PROCEDURE [test].[usp_xxx_GetCompanyDetails]
(
    @ANumber        int,
    @CompanyId      int,
    @UserRole       varchar(15),
    @RequestId      varchar(100),
    @CompanyCode    varchar(5),
    @BaseSystem     varchar(5),
    @PType          varchar(20),    
    @PId            varchar(40),
    @IsActive       bit,     
    @responseData   xml OUT
)

Following is the java code that makes a call to the stored proc. It is using spring data to make the call.

 private String executeProc(Integer aNumber,Integer companyId, String baseSystem, 
                String role,String companyCode,String requestId, String pType,String pId,
                boolean isActive ) throws SQLException {

            SQLXML responseData=null;
            Map<String,Object> inputParams= new HashMap<>();
            inputParams.put("ANumber", aNumber);
            inputParams.put("CompanyId", companyId);
            inputParams.put("UserRole", role);
            inputParams.put("RequestId", requestId);
            inputParams.put("CompanyCode", companyCode);
            inputParams.put("BaseSystem", baseSystem);
            inputParams.put("PType", pType);
            inputParams.put("PId", pId);
            inputParams.put("IsActive", isActive);
            inputParams.put("ResponseData", responseData);

            Map<String, Object> result = this.execute(inputParams);
            String responseXMLString = ((SQLXML) result.get("ResponseData")).getString();

            return responseXMLString;
        }

What could have gone wrong.

The procedure has 10 parameters. The SQL has 12 commas, so it's providing 13 parameters, but only has 11 ? markers, so two of the parameters are undefined. The error message indicates that you're not setting value for the 11th marker, i.e. not calling setXxx(11, xxx). No wonder the JDBC driver is confused about what you're trying to do here. — Andreas
@Andreas, the SQL is auto generated using spring-data and hibernate frameworks. The same code worked after server restart. — pawinder gupta
It look like there's a step between the this.execute() in the code sample, and the JdbcTemplate.call() in the stack? The first parameter to JdbcTemplate.call() is a CallableStatementCreator, which is usually an immutable SimpleCallableStatementCreator based around a String. It sounds like this could be something different here? — df778899
@df778899, restarting the server fixed the issue. Could it be linked to class loader or loaded libraries. I am not able to replicate the issue. — pawinder gupta

user7294900 user7294900 · Accepted Answer · 2018-09-03T05:09:04

You have 11 binding parameters (?) and also empty parameters , ,. Remove it to work with procedure with 9 input parameters and 1 output parameter

{call test.usp_xxx_GetCompanyDetails(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}

JDBC SQL Server : The value is not set for the parameter number

2 Answers