无法在SQL Server上使用Java和JDBC执行存储过程 [英] Unable to execute stored Procedure using Java and JDBC on SQL server

问题描述

我一直在尝试通过JDBC执行MS SQL Server存储过程，到目前为止一直没有成功。存储过程有1个输入和1个输出参数。我在代码中设置存储过程调用时使用的每个组合都会收到一条错误，指出无法找到存储过程。我已经提供了我正在执行的存储过程（注意：这是供应商代码，所以我无法更改它）。

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

代码如下：

 CallableStatement statement = connection
            .prepareCall("{call dbo.spWCoTaskIdGen(?)}");
    statement.setInt(1, 0);
    ResultSet result = statement.executeQuery();

我收到以下错误：SEVERE：找不到存储过程'dbo.spWCoTaskIdGen'。

I get the following error: SEVERE: Could not find stored procedure 'dbo.spWCoTaskIdGen'.

我也试过

    CallableStatement statement = connection
            .prepareCall("{? = call dbo.spWCoTaskIdGen(?)}");
    statement.registerOutParameter(1, java.sql.Types.INTEGER);
    statement.registerOutParameter(2, java.sql.Types.INTEGER);
    statement.executeQuery();

上述结果为：严重：无法找到存储过程'dbo.spWCoTaskIdGen'。

The above results in: SEVERE: Could not find stored procedure 'dbo.spWCoTaskIdGen'.

我也试过：

    CallableStatement statement = connection
            .prepareCall("{? = call spWCoTaskIdGen(?)}");
    statement.registerOutParameter(1, java.sql.Types.INTEGER);
    statement.registerOutParameter(2, java.sql.Types.INTEGER);
    statement.executeQuery();

上面的代码导致以下错误：无法找到存储过程'spWCoTaskIdGen'。

The code above resulted in the following error: Could not find stored procedure 'spWCoTaskIdGen'.

最后，我还应该指出以下内容：

Finally, I should also point out the following:

1. 我用过MS SQL Server Management Studio工具已经能够成功运行存储过程。下面提供了生成执行存储过程的sql：

1. 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;
DECLARE @OutIdentifier int;
EXEC @return_value = [dbo].[spWCoTaskIdGen] @OutIdentifier = @OutIdentifier OUTPUT;
SELECT
    @OutIdentifier [@OutIdentifier],
    @return_value [Return Value];
GO

正在执行的代码运行时使用的用户ID相同在上面的第1点中。

The code being executed runs with the same user id that was used in point #1 above.

任何想法？

非常感谢您提前。

推荐答案

最有可能是......

Most likely one of...

1. 凭据使用没有权限来运行代码。你需要一个 GRANT EXECUTE上面脚本中的

错误的数据库。例如，存储过程在master中创建，但您已连接到MyDB

Wrong database. For example, the stored proc was created in master but you are connected to "MyDB"

这篇关于无法在SQL Server上使用Java和JDBC执行存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！