CODEWITHSUNDEEP
javasql-serverspringstored-procedures
Developer Dude
Developer Dude

Reputation: 128

Spring SimpleJdbcCall & SQL Server system Stored Procedure - expects parameter not supplied, but it is?

I've googled, I've search StackOverflow, I've read examples and tutorials, I've looked through the Spring JavaDocs and other docs. I've tried a number of different permutations.

Pretty much no matter what I try I get the same or similar error, varying only on which param it complains is not supplied:

org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback;     
uncategorized SQLException for SQL [{? = call sys.sp_sequence_get_range(?)}]; 
SQL state [S0004]; error code [201]; 
Procedure or function 'sp_sequence_get_range' expects parameter '@range_size',   
which was not supplied.; nested exception is  
com.microsoft.sqlserver.jdbc.SQLServerException: Procedure or function 
'sp_sequence_get_range' expects parameter '@range_size', which was not supplied.

Java code:

SimpleJdbcCall jdbcCall = new SimpleJdbcCall(dataSource())
        .withProcedureName("sp_sequence_get_range")
        .withoutProcedureColumnMetaDataAccess()         
        .withCatalogName("sys")
        .declareParameters(new SqlParameter("sequence_name", Types.NVARCHAR), new SqlParameter("range_size", Types.BIGINT))

        .withReturnValue();

    MapSqlParameterSource params = new MapSqlParameterSource()
        .addValue("sequence_name", "dbo.M_PRODUCT_PROD_ID_SEQ")
        .addValue("range_size", Integer.valueOf(rangeSize_));

    Map<String, Object> retVals = jdbcCall.execute(params);

The Stored Procedure is the SQL Server 2012 (v 11) sys.sp_sequence_get_range():

https://msdn.microsoft.com/en-us/library/ff878352%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396

I have a standard sequence defined - I don't think that matters as I can't even get to the SP from my code. From within SQL Server Studio the SP works when I call it like this:

EXEC sys.sp_sequence_get_range
@sequence_name = N'dbo.M_PRODUCT_PROD_ID_SEQ'
, @range_size = 500
, @range_first_value = @FirstSeqNum OUTPUT 
, @range_last_value = @LastSeqNum OUTPUT  ;

-- The following statement returns the output values
SELECT
@FirstSeqNum AS FirstVal
, @LastSeqNum AS LastVal;

I wrote a unit test where rangeSize_ == 100;

The param types match those defined by MS in their docs.

Unless I am blind, the param names match and they are in the same order as the SP expects.

I am using "withoutProcedureColumnMetaDataAccess()" which is the usual suggested remedy for this problem - to no avail.

It seems to get past the first param, but rejects the second?

If I change the params source to include the types:

MapSqlParameterSource params = new MapSqlParameterSource()
        .addValue("sequence_name", "dbo.M_PRODUCT_PROD_ID_SEQ", Types.NVARCHAR)
        .addValue("range_size", BigInteger.valueOf(rangeSize_), Types.BIGINT);

I get a different error:

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The value is not set for the parameter number 1.

Does this mean I made it further and I just need to register an OUT parameter?

Suggestions?

Thanks in advance.

Upvotes: 3

Views: 4128

Answers (2)

user129260
user129260

Reputation: 23

I know it's very late to answer but the SP can be called as below:

public Long getStartingSequenceNumber(String sequenceName, Long incrementBy) throws SQLException {
Connection con = dataSource.getConnection();
CallableStatement cstmt = null;

try {
  cstmt = con.prepareCall(
      "{call sys.sp_sequence_get_range(?, ?, ?)}");

  cstmt.setString("sequence_name", sequenceName);
  cstmt.setLong("range_size",incrementBy);
  cstmt.registerOutParameter("range_first_value", microsoft.sql.Types.SQL_VARIANT);
  cstmt.execute();
  return cstmt.getLong("range_first_value");
}finally {
  if (cstmt != null) {
    cstmt.close();
  }
}

}

Upvotes: 0

Developer Dude
Developer Dude

Reputation: 128

I didn't exactly come to the resolution I was looking for; I handed this to another dev who came up with a workaround. Instead of calling the system SP directly, he wrote a stored proc wrapper around it that just took one argument and returned 6 values. We finally got that working. It wasn't pretty, but it works and cuts down drastically on the amount of time we take to generate sequence values.

From the work around he made, I think the answer would have been to register all of the out params too. Someday I will get around to doing that and maybe post a better answer, but right now I am trying to wrap up this project that is past its deadline.

Upvotes: 1

Related Questions