MSSQL stored procedure with parameters not working

[Scotty Kedward]

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:

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()


This is calling the following stored procedure on MSSQL 2008:



CREATE PROCEDURE [dbo].[UserCreate]

@UserName varchar(50),
@UserPassword varchar(50),
@FName varchar(50),
@SName varchar(50)
AS
BEGIN

SET NOCOUNT ON;


INSERT INTO Users
(
UserName,
UserPassword,
FName,
SName
)
VALUES
(
@UserName,
@UserPassword,
@FName,
@SName
)
END


This gives me the following error:

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

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) - note there are no parameters passed:

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



A quick VBScript I knocked up to use the same stored procedure via ADO works perfectly.

If you can offer any help at all, I'll be very very grateful!

[Scotty Kedward]

Ooops - got a workaround, so no worries.

[Jeanne Boyarsky]

It's always nice when someone finds a solution so quickly. Can you share what it was for anyone else who has the same problem?

Also, please update your display name to a name that looks like it could be real. And welcome to CodeRanch!

[Scotty Kedward]

Indeed it was nice to find a resolution; I found a post suggesting that the callproc implementation was not yet stable (sorry I've lost the link now), so I used cursor.execute instead. This was giving me problems too until I realised that I needed to explicitly commit the transaction using connection.commit()


So I ended up with the following:

objConnection = zxJDBC.connect(strDataSource, strDBUser, strDBPassword, strDBVersion)
objCursor = objConnection.cursor()
objCursor.execute("exec AutoTest.dbo.LogMasterCreate @LogMasterDescription='TestUser', @LogMasterCreatedBy=" + str(intUserID))
objConnection.commit()


And I've submitted a name change request (that I suspect I'll have to change again having read the rules!)