locked
SQL Server JDBC Driver fails to call a Stored Procedure in SQL Server RRS feed

  • Question

  • I have a SQL server (2014 server) that has a stored procedure that takes a parameter and returns a resultset.

    When I call this stored procedure using the SQL JDBC 6.0 Driver, it fails:

    com.microsoft.sqlserver.jdbc.SQLServerException: Parameter @pJobStatusID was not defined for stored procedure dbo.ImportQueueJobStateGetByJobStatusID.
    
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:191)
    
    at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.findColumn(SQLServerCallableStatement.java:1275)
    
    at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.setInt(SQLServerCallableStatement.java:1683)
    
    at com.zillow.db.CallableStatementImpl.setInt(CallableStatementImpl.java:338)
    
    at com.zillow.dataimport.countydirect.queue.QueueManager$2.doInCallableStatement(QueueManager.java:124)


    This is how I am calling it:

                    tasks = (List<QueueEntry>) jdbcTemplate.execute(
                            "{ call dbo.ImportQueueJobStateGetByJobStatusID(?, ?, ?) }",
                            new CallableStatementCallback() {
                                public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
    
                                    List<QueueEntry> tasks = new ArrayList<QueueEntry>();
    
                                    cs.setInt("@pJobStatusID", jobStatusId); // return all entries
                                    cs.setInt("@pImportQueueIDStart", queueIdStart);
                                    cs.setInt("@pBatchSize", batchSize);
    
                                    ResultSet rs = cs.executeQuery();
    

    This is the definition of the stored procedure:

    ALTER PROCEDURE dbo.ImportQueueJobStateGetByJobStatusID (
        @pJobStatusID int,
        @pImportQueueIDStart int,
        @pBatchsize int
    )
    AS


    I am calling it through Spring Framework JDBC template.

    This same call works if I use the JTDS driver.

    Any idea what I am doing wrong?


    feroze
    --
    My blog (including System.Net topics

     Subscribe in a reader

    Instruction on how to create a tracelog with your System.Net application
    System.Net Links and HOWTOs

    Sunday, October 9, 2016 12:51 AM

Answers

All replies