Hello,
I am unable to get the value of an OUTPUT parameter from a SQL
Server stored procedure - but only when accessing it with ADO using
Python and COM. I also can't find any good Google answers on the topic
(mostly, a post by one Steve Holden who had the same problem, and that
was left unresolved).
As it seems to be a problem between PyWin32/COM and ADO, rather than
with SQL or ADO, and I have seen COM/ADO posts in this archive, I
thought I would try here next. Can any of you suggest anything, please?
SQL Stored Procedure:
CREATE PROCEDURE [dbo].[log_item]
@name varchar(150), @row_id int OUTPUT
AS
BEGIN
INSERT INTO my_log VALUES (@name, GETDATE(), NULL)
SELECT @row_id = SCOPE_IDENTITY()
END
This works in the SQL Query Analyzer:
declare @row_id int
exec log_item "abcd", @row_id output
select @row_id
> 1146
This also works from VBScript using code adapted from step 4 here:
http://authors.aspalliance.com/stevesmith/articles/sprocs.asp
I can create a command, add two parameters, execute the command, and get
a valid value returned in the second parameter.
I can't get it to work from Python. Literal translations of the VBScript
return None. The stored procedure is being called, rows are inserted
into the database, but the value isn't getting back to my Python script.
The Simplest example I can create that demonstrates the problem is:
from win32com.client import Dispatch, constants
conn = Dispatch("ADODB.Connection")
conn.ConnectionString = "Provider=SQLOLEDB.1;Data
Source=server;uid=user;pwd=pass;database=test"
conn.Open()
cmd = Dispatch('ADODB.Command')
cmd.ActiveConnection = conn
cmd.CommandText = "log_item"
cmd.CommandType = 4 # =adCmdStoredProc
param1 = cmd.CreateParameter('@name', 200, 1, 150) # adVarChar,
adParamInput, size
param1.Value = "abcd"
cmd.Parameters.Append(param1)
row_param = cmd.CreateParameter('@row_id', 3, 2) # adInteger,
adParamOutput
cmd.Parameters.Append(row_param)
result = cmd.Execute()
print row_param.Name, row_param.Value
row_id = cmd.Parameters.Item("@row_id")
print "row_id: ", row_id, "Value: ", row_id.Value
It just prints "None" afterwards.
I have seen suggestions to try setting cmd.Prepared = True, and this has
no apparent effect.
I have tried changing it to be an InputOutput parameter, and sending a
value in. This time, I just get the same value out again.
I have checked with cmd.Parameters.Refresh, and that tells me the stored
procedure needs the two parameters I am expecting. It doesn't make any
difference to whether it works or not though.
(Oh, and Windows XP, Python 2.4.1, PythonWin build 204, and I have run
MakePy on Microsoft ActiveX Data Objects 2.8)
I'm now quite stumped, and would appreciate any further suggestions
before I give up and resort to a cursor and run "insert into" then
"select max(row_id) from" to get the same effect.
Cheers,
Simon.
This email originates from IT Group UK Limited.
With subsidiary companies:
Rockford IT Limited
Rockford Electronics Limited
Registered Office:
2A New Street
Newport
Shropshire
TF10 7AX
UK
Company Registered in England: 4263503
Tel: 01952 812 290 (Technical Centre)
Tel: 0871 871 2778 (Head Office)
_______________________________________________
Python-win32 mailing list
Python-win32@python.org
http://mail.python.org/mailman/listinfo/python-win32
