aspose file tools*
The moose likes Jython/Python and the fly likes MSSQL stored procedure with parameters not working Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Languages » Jython/Python
Bookmark "MSSQL stored procedure with parameters not working" Watch "MSSQL stored procedure with parameters not working" New topic
Author

MSSQL stored procedure with parameters not working

Scotty Kedward
Greenhorn

Joined: Feb 01, 2012
Posts: 3
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
Greenhorn

Joined: Feb 01, 2012
Posts: 3
Ooops - got a workaround, so no worries.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30753
    
156

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!


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Scotty Kedward
Greenhorn

Joined: Feb 01, 2012
Posts: 3
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!)
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: MSSQL stored procedure with parameters not working