我正在尝试使用zxJDBC连接到在SQL Server 2008 R2(Express)上运行的数据库并调用存储过程，并向其传递单个参数。我正在使用jython-standalone 2.5.3，理想情况下，不想安装其他模块。

我的测试代码如下所示。

数据库名称为CSM
存储过程:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE dbo.DUMMY 
    -- Add the parameters for the stored procedure here
    @carrierId VARCHAR(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    INSERT INTO dbo.carrier (carrierId, test)
    VALUES (@carrierId, 'Success')
END
GO

from com.ziclix.python.sql import zxJDBC

conn = None
try :
    conn = zxJDBC.connect('jdbc:sqlserver://localhost\SQLEXPRESS', 'sa', 'password', 'com.microsoft.sqlserver.jdbc.SQLServerDriver')
    cur = conn.cursor()
    cur.callproc(('CSM','dbo','DUMMY'), ['carrier1'])
    conn.commit()
except Exception, err :
    print err
    if conn:
        conn.rollback()
finally :
    if conn :
        conn.close()

c.callproc(("northwind", "dbo", "SalesByCategory"), ["Seafood", "1998"], maxrows=2)

Error("Could not find stored procedure 'CSM.DUMMY'. [SQLCode: 2812], [SQLState: S00062]",)

Error('An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name. [SQLCode: 1038], [SQLState: S0004]',)

use []
go

cur.callproc(('CSM', '', 'dbo.DUMMY'), ['carrier1'])

Error("Procedure or function 'DUMMY' expects parameter '@carrierId', which was not supplied. [SQLCode: 201], [SQLState: S0004]",)

cur.execute('EXEC CSM.dbo.DUMMY ?', ['carrier1'])

use CSM
go
exec sp_sproc_columns_100 N'dbo.DUMMY',N'dbo',N'CSM',NULL,N'3'
go

exec sp_sproc_columns_100 N'DUMMY',N'dbo',N'CSM',NULL,N'3'

CREATE TABLE [dbo].[carrier](
    [carrierId] [varchar](50) NOT NULL,
    [test] [varchar](50) NULL
) ON [PRIMARY]

最佳答案

尽管完全不了解此处使用的技术(除非对SQL Server有一些了解)，但我将尝试给出答案(如果我的jython语法不正确，请原谅我。我试图在此处概述不完全正确的代码的可能性)

我的第一种方法(位于this post)是尝试:

cur.execute("use CSM")
cur.callproc(("CSM","dbo","dbo.DUMMY"), ["carrier1"])

cur.callproc("DUMMY", ["carrier1"])
cur.callproc("dbo.DUMMY", ["carrier1"])

>> Program calls a Stored Procedure - master.dbo.xp_fixeddrives on  MS SQL Server

from com.ziclix.python.sql import zxJDBC

def getConnection():
    url = "${DBServer.Url}"
    user= "${DBServer.User}"
    password = "${DBServer.Password}"
    driver = "${DBServer.Driver}"
    con = zxJDBC.connect(url, user, password, driver)
    return con

try:
    conn = getConnection()
    print 'Connection successful'
    cur = conn.cursor()
    cur.execute("use master")
    cur.callproc(("master", "dbo", "dbo.xp_fixeddrives"))
    print cur.description
    for a in cur.fetchall():
        print a
finally:
    cur.close()
    conn.close()
    print 'Connection closed'

关于java - zxjdbc调用存储过程的正确用法是什么？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/18055993/