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

[Roger Upole]

I don't have a Sql Server to work with, but I can get an Oracle stored
procedure to return an output parameter with your code.
Will Sql server let you select a procedure result ?
In other words, could you use something like
'select log_item(?)' to return the result in a record set ?

        hth
             Roger

Simon Bayling wrote:

> 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)