SQL Server JDBC Driver fails to call a Stored Procedure in SQL Server

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

Answer 1

Hello,

Please see how they handle the parameters on the following thread:

http://stackoverflow.com/questions/9361538/spring-jdbc-template-for-calling-stored-procedures

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

Answer 2

@pJobStatusID is output parameter.

You should have something like cstmt.registerOutParameter.

There is a good sample here that you can refer:

https://programmaticponderings.wordpress.com/2012/08/24/calling-sql-server-stored-procedures-with-java-using-jdbc/

chanmm

---

chanmm

Answer 3

It is not an output parameter.

---

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

Answer 4

I am handling them correctly, as per Spring guidelines. As I said, it works fine with JTDS driver.

---

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

Answer 5

Hi Feroze Daud,

Based on my test, you may need to use the following setInt method instead.

		cs.setInt(1, jobStatusId);
		cs.setInt(2, queueIdStart);
		cs.setInt(3, batchSize);

---

Sam Zha
TechNet Community Support

Answer 6

Sure, using ordinal instead of name parameters works. But that is not the fix I was looking for.

The correct answer is, ladies and gentlemen, that the MSSQL driver does not like the "@" in front of the parameter. So if you have a sproc parameter "@pFoo INT" you cannot say:

cs.setInt("@pFoo", value);

instead you should use:

cs.setInt("pFoo", value);

---

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

Answer 7

Feroze, does the workaround you give solve the issue? You can mark the reply which helps this issue.