Unable to execute stored Procedure using Java and JDBC on SQL server
- by jwmajors81
I have been trying to execute a MS SQL Server stored procedure via JDBC today and have been unsuccessful thus far. The stored procedure has 1 input and 1 output parameter. With every combination I use when setting up the stored procedure call in code I get an error stating that the stored procedure couldn't be found. I have provided the stored procedure I'm executing below (NOTE: this is vendor code, so I cannot change it).
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[spWCoTaskIdGen]
@OutIdentifier int OUTPUT
AS
BEGIN
DECLARE @HoldPolicyId int
DECLARE @PolicyId char(14)
IF NOT EXISTS
(
SELECT *
FROM UniqueIdentifierGen (UPDLOCK)
)
INSERT INTO UniqueIdentifierGen VALUES (0)
UPDATE UniqueIdentifierGen
SET
CurIdentifier = CurIdentifier + 1
SELECT @OutIdentifier =
(SELECT CurIdentifier
FROM UniqueIdentifierGen)
END
The code looks like:
CallableStatement statement = connection
.prepareCall("{call dbo.spWCoTaskIdGen(?)}");
statement.setInt(1, 0);
ResultSet result = statement.executeQuery();
I get the following error: SEVERE: Could not find stored procedure 'dbo.spWCoTaskIdGen'.
I have also tried
CallableStatement statement = connection
.prepareCall("{? = call dbo.spWCoTaskIdGen(?)}");
statement.registerOutParameter(1, java.sql.Types.INTEGER);
statement.registerOutParameter(2, java.sql.Types.INTEGER);
statement.executeQuery();
The above results in: SEVERE: Could not find stored procedure 'dbo.spWCoTaskIdGen'.
I have also tried:
CallableStatement statement = connection
.prepareCall("{? = call spWCoTaskIdGen(?)}");
statement.registerOutParameter(1, java.sql.Types.INTEGER);
statement.registerOutParameter(2, java.sql.Types.INTEGER);
statement.executeQuery();
The code above resulted in the following error: Could not find stored procedure 'spWCoTaskIdGen'.
Finally, I should also point out the following:
I have used the MS SQL Server Management Studio tool and have been able to successfully run the stored procedure. The sql generated to execute the stored procedure is provided below:
GO
DECLARE @return_value int,
@OutIdentifier int
EXEC @return_value = [dbo].[spWCoTaskIdGen]
@OutIdentifier = @OutIdentifier OUTPUT
SELECT @OutIdentifier as N'@OutIdentifier '
SELECT 'Return Value' = @return_value
GO
The code being executed runs with the same user id that was used in point #1 above.
In the code that creates the Connection object I log which database I'm connecting to and the code is connecting to the correct database.
Any ideas?
Thank you very much in advance.
