[python-win32] Problem with COM/ADO using SQL Stored Procedure with OUTPUT parameter

[Simon Bayling]

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)