Apologies in advance for this long mail. Also, I warn you that I've
only subscribed to db-sig today, so I am blissfully unaware of any
related discussions you might have had on this topic in the past. I
skimmed the last couple months of message subjects and only saw PEP
249 referenced once (apparently incorrectly).
At work, we use the python-sybase adapter in all our Python code:
http://python-sybase.sourceforge.net/
We are in the midst of upgrading our code to use a newer version of
our internal wrapper of that module from a version which relies on
python-sybase 0.36 to a version which relies on python-sybase
0.40pre2. Yesterday, one of the other programmers reported that he
could no longer retrieve stored procedure return values. In 0.38 and
before, the return value was tacked onto the front of the first result
set. In 0.39, an attempt was made to change how return values were
processed, but that change introduced a new bug. I fixed that problem
today.
After this episode, I was motivated to revisit PEP 249's discussion of
stored procedures. I barely use SQL in my own work, relying heavily
on the work of others, and rarely formulating sophisticated queries
myself. Until I had to fix this bug, I didn't even realize that
stored procedures had return statements. I thought they transmitted
values out using some other syntax.
So, now I know that, besides being optional, and being widely
different across different databases, stored procedures have three
ways of returning results:
1. Zero or more result sets
2. Output (or in/out) parameters
3. Return values
Processing result sets is well-defined. Call the cursor's fetch*()
and nextset() methods repeatedly until nextset() returns False. I
think the specification of other two ways of transmitting values out
of a stored procedure could use some work though. The solution to
return values in the python-sybase module is to set a status_result
attribute on the Cursor object. Structurally, it looks like a one-row
one-element result set, e.g.: [(1,)] if the stored procedure executed
"return 1". This seems like a reasonable way to go about this, though
I have no idea how complex return values can be, or how best to
indicate that a stored procedure didn't return a value (None or an
empty list both seem reasonable).
Python-sybase allows you to specify output parameters in parameter
dictionaries using the OUTPUT function. For example, adapted from some
code I maintain:
args = {
"@date1": today,
"@date2": Sybase.OUTPUT(date),
"@symbol": "IBM",
}
conn = db.pool.get_connection()
output = conn.callproc(conn.cursor, "previous_trading_day", args)
In this case, the @date2 key represents an output parameter, and our
wrapper rewrites that value. I don't know if we do this in our
wrapper because the python-sybase authors intended not to do this
rewriting, or if I'm working around an actual bug. The input side of
in/out parameters is (I presume) passed in through the call to
Sybase.OUTPUT(...).
In a separate email thread, Marc-Andre suggested that we could add a
callfunc() method to Cursor objects. I'm not entirely sure that's
necessary, as (at least in the Sybase case) a stored procedure can
return all three types of values in the same call.
So, to draw this exceedingly long mail to a close, I propose:
1. PEP 249 should document how stored procedure return values are made
available to the caller of Cursor.callproc.
2. PEP 249 should more precisely document how output and in/out
parameters are specified and processed.
Skip
_______________________________________________
DB-SIG maillist - DB-SIG@python.org
https://mail.python.org/mailman/listinfo/db-sig
