Howdy, Stranger!

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

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

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