SQL Server stored prcedures with output parameters
steve at holdenweb.com
Thu Nov 18 15:00:46 CET 2004
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
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:
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
Holden Web LLC +1 800 494 3119
More information about the Python-list