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) _______________________________________________ Python-win32 mailing list Python-win32@python.org http://mail.python.org/mailman/listinfo/python-win32