SQL Server stored prcedures with output parameters

[Steve Holden]

Tim Golden wrote:

> [Steve Holden]
> 
> | However, the stored procedure below appears to consistently 
> | return zero for the link_id when called with:
> | 
> | columnist_id, url, title, description, link_id = \
> | 	curs.callproc("sp_InsertArticle",
> | 		(columnist_id, url, title, description, 0))
> | 
> | so maybe there really *is* something wrong.
> 
> Ummm... Well, I don't know how you'd do it from ado, but
> if I were writing this in T-SQL, I'd be saying:
> 
> DECLARE @new_link_id
> EXECUTE sp_InsertArticle @columnist_id... , @new_link_id OUTPUT
> 
Thanks very much for your reply, I'm getting a bit desperate here. The 
T-SQL you quote is indeed what one would do, but an adequate translation 
into Python is defying me.

> In other words, you have to tell SQL that the param you're
> passing in is an output param. Frankly, I don't know why, since
> the data dictionary already knows that the formal parameter to
> which it corresponds is flagged as output, but there it is.
> I don't know (and I don't have the time to look just at the 
> moment) how ADO handles that.
> 
Well, in ADO you'd normally create a command object and then do 
something like

objCmnd.Parameters.Append.CreateParameter("@link_id", &
		adInteger, adParamOutput, , 0)

Unfortunately as far as I can see the DB API docs are silent on how to 
distinguish the output parameters to a callproc(), contenting itself 
with saying """The result of the call is returned as modified copy of 
the input sequence. Input parameters are left untouched, output and 
input/output parameters replaced with possibly new values."""

The adodbapi callproc method uses an executeHelper() function that 
attempts different strategies. It does indeed create a command object, 
then calls its .Parameters.Refresh() method to query the interface to 
the stored procedure. This implies an extra server round trip, but I'm 
not about to start getting picky here.

http://support.microsoft.com/kb/174223/EN-US/ helpfully remarks 
"""Parameters.refresh will fail in some situations or return information 
that is not entirely correct. Parameters.refresh is particularly 
vulnerable when used on ASP pages. Please query on "parameters.refresh" 
to find other articles that talk about some of the problems associated 
with Parameter.refresh.""" which gave me hope briefly.

I've managed, by inserting debugging prints, to determine the following 
information about the call I am making:

Trying strategy 1
Parameter count 6 : 5 parameters
Return value index 0
Ignoring return value parameter 0
Parameter 1 had direction input
Parameter 2 had direction input
Parameter 3 had direction input
Parameter 4 had direction input
Parameter 5 had direction input/output

So it looks as though that's not happening here. The return value 
parameter was a bit of a surprise, but the code seems to treat it 
correctly. Internally the callproc() method completes with

         if isStoredProcedureCall and parameters != None:
             return self._returnADOCommandParameters(self.cmd)

which iterates over the parameters, appending the value of each variant, 
converted to the correct Python type, to the returned list. Further 
debug prints show me:

Return value None stored and ignored
Appending parameter value 23
Appending parameter value http://www.holdenweb.com/
Appending parameter value Stored Procedures in Python
Appending parameter value How to handle databases with stored procedures
Appending parameter value [whatever was provided in the call].

So I'm forced to the conclusion that the SQL Server driver is for some 
reason not setting that output parameter (but I am by no means an ADO 
expert, having found out more about it in the last 24 hours than I 
really wanted :-).

If anyone can shed any further light on this I'll really be grateful.

scratching-my-head-ly y'rs  - steve
-- 
http://www.holdenweb.com
http://pydish.holdenweb.com
Holden Web LLC +1 800 494 3119