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.

Answer 1

You need to register the out parameter, You can try like this

cs.registerOutParameter("ResponseData", java.sql.Types.VARCHAR);

Answer 2

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(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}