12

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.

pawinder gupta
  • 1,225
  • 16
  • 35
  • remove unnecessary comma(,) to execute code properly. – Mostch Romi Sep 03 '18 at 05:10
  • 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 Sep 03 '18 at 05:20
  • @Andreas, the SQL is auto generated using spring-data and hibernate frameworks. The same code worked after server restart. – pawinder gupta Sep 04 '18 at 00:10
  • 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 Sep 08 '18 at 13:26
  • @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 Sep 13 '18 at 04:28

2 Answers2

7

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(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}
Ori Marko
  • 56,308
  • 23
  • 131
  • 233
  • 3
    I almost up-voted, but the procedure has 10 parameters. The `OUT` parameter is still a parameter. – Andreas Sep 03 '18 at 05:21
  • The code is registering 10 parameters. The call statement is generated by spring data library. After server restart the same code is working. – pawinder gupta Sep 03 '18 at 05:38
4

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

cs.registerOutParameter("ResponseData", java.sql.Types.VARCHAR);
Sathish
  • 196
  • 7