Hi Skip,
sorry for the late reply. For some reason I did not see your
email to the list in my inbox.
On 13.03.2014 21:50, Skip Montanaro wrote:
> [...]
>
> 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.
The DB-API syntax goes like this:
result_parameters = cursor.callproc(procname, parameters)
with procname being the procedure name and parameters
the sequence of parameters.
The returned result_parameters is a copy of the parameters
sequence with in/out and output parameters replaced with their
new values.
> 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
That last bullet is usually often to stored functions,
not procedures - even though there are databases which have
procedures return value, just as there are databases which don't
have procedures and instead call everything a function.
Additionally, some databases don't allow in/out and output
parameters for stored functions.
> 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)
Hmm, the method should be defined on the cursor, not the connection
for DB-API 2.0 compatibility. I guess the module still uses
the old and deprecated DB-API 1.0 approach.
> 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.
Agreed, we need to address this in some way.
Since the .callproc() signature is already defined to not return
a procedure/function return value, my proposal was to introduce a
new method .callfunc() which does support this.
There are a few ways this could be done. The most intuitive is
probably this one:
Variant A:
----------
return_value = cursor.callfunc(funcname, parameters)
Unlike the .callproc() method, this call would not support
in/out or output parameters. It would still support creating
result sets, though.
A less intuitive alternative would be this one:
Variant B:
----------
return_value_and_result_parameters = cursor.callfunc(funcname, parameters)
with return_value_and_result_parameters being a sequence of the form
[return_value, parameter0, parameter1, ...], i.e. the return_value
is prepended to the parameters list.
This would also support in/out and output parameters.
More Pythonic:
Variant C:
----------
(return_value, result_parameters) = cursor.callfunc(funcname, parameters)
This would also support in/out and output parameters and allow to
easily separate the return_value from the result_parameters.
> 2. PEP 249 should more precisely document how output and in/out
> parameters are specified and processed.
Agreed as well :-)
At the moment, the DB-API leaves these details to the database
modules to figure out.
--
Marc-Andre Lemburg
eGenix.com
Professional Python Services directly from the Source (#1, Mar 24 2014)
>>> Python Projects, Consulting and Support ... http://www.egenix.com/
>>> mxODBC.Zope/Plone.Database.Adapter ... http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/
________________________________________________________________________
2014-03-29: PythonCamp 2014, Cologne, Germany ... 5 days to go
2014-04-09: PyCon 2014, Montreal, Canada ... 16 days to go
2014-04-29: Python Meeting Duesseldorf ... 36 days to go
eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48
D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
Registered at Amtsgericht Duesseldorf: HRB 46611
http://www.egenix.com/company/contact/
_______________________________________________
DB-SIG maillist - DB-SIG@python.org
https://mail.python.org/mailman/listinfo/db-sig
