Scotty Kedward

Greenhorn
+ Follow
since Feb 01, 2012
Merit badge: grant badges
For More
Cows and Likes
Cows
Total received
0
In last 30 days
0
Total given
0
Likes
Total received
0
Received in last 30 days
0
Total given
0
Given in last 30 days
0
Forums and Threads
Scavenger Hunt
expand Ranch Hand Scavenger Hunt
expand Greenhorn Scavenger Hunt

Recent posts by 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!)
12 years ago
Ooops - got a workaround, so no worries.
12 years ago
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!
12 years ago