(Jython) MSSQL stored procedure with parameters not working - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

(Jython) MSSQL stored procedure with parameters not working

TheGFBTheGFB Posts: 2Member
Hi -

I've been thrown in at the deep end with a Jython project (actually Sikuli X, but I need to use Jython to record test results in an MSSQL2008 DB). This is my first foray into the world of Jython, and I simply can't find anyone else with the same issue on the net....

I'm able to pull data from my MSSQL 2008 DB using a stored proc with no parameters, but if I try to store data using a stored procedure with parameters, I'm getting issues!

I'm running out of time on this project and I'm a bit desperate!

Jython code:

[b]import sys
from com.ziclix.python.sql import zxJDBC


strDataSource, strDBUser, strDBPassword, strDBVersion = "jdbc:sqlserver://192.168.127.130;databaseName=AutoTest", "*******", "******", "com.microsoft.sqlserver.jdbc.SQLServerDriver"
objConnection = zxJDBC.connect(strDataSource, strDBUser, strDBPassword, strDBVersion)
objCursor = objConnection.cursor()


objCursor.callproc(("AutoTest", "dbo", "dbo.UserCreate"), {"@UserName":"UserName", "@UserPassword":"Password", "@FName":"FName", "@SName":"SName"})


objCursor.close()
print "complete"

objConnection.close()
[/b]

This is calling the following stored procedure on MSSQL 2008:



[b]CREATE PROCEDURE [dbo].[UserCreate]
-- Add the parameters for the stored procedure here
@UserName varchar(50),
@UserPassword varchar(50),
@FName varchar(50),
@SName 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 Users
(
UserName,
UserPassword,
FName,
SName
)
VALUES
(
@UserName,
@UserPassword,
@FName,
@SName
)
END
[/b]

This gives me the following error:

[b]zxJDBC.Error: Procedure or function 'UserCreate' expects parameter '@UserName', which was not supplied. [SQLCode: 201], [SQLState: S0004][/b]

I've also tried the following:

objCursor.callproc(("AutoTest", "dbo", "dbo.UserCreate"), ["UserName", "Password", "FName", "SName"])

A SQL Trace shows the following being passed through by Microsoft JDBC(4):

[b]declare @p1 int
set @p1=2
exec sp_prepexec @p1 output,NULL,N'EXEC AutoTest.dbo.UserCreate '
select @p1
[/b]


A quick VBScript I knocked up to use the same stored procedure works perfectly
Sign In or Register to comment.