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.