[DB-SIG] Would like to challenge DBAPI on callproc's return value

[M.-A. Lemburg]

Hi Milosz,

On 01.12.2015 06:40, Milosz Kosmider wrote:
> Hi folks,
> 
> As PostgreSQL has had named parameters in stored procedures since version
> 9.0, I
> have taken to adding support for the feature in the DBAPI-complicant Python
> wrapper, psycopg2: https://github.com/psycopg/psycopg2/pull/16/files.
> 
> The feature allows the "callproc" method to be invoked, in addition to the
> standard, tuple-y way, like this: cur.callproc('some_proc', { some_param:
> 'some_value', ... })
> 
> While implementing the feature I stumbled upon the DBAPI spec for callproc:
> http://legacy.python.org/dev/peps/pep-0249/#callproc. It states that "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." It then goes onto say that "The procedure may also provide a
> result
> set as output."
> 
> I would like to challenge the former requirement, and make the latter
> statement a strict requirement.
> 
> The former requirement of returning the modified input sequence effectively
> forces cur.callproc to examine its result set and copy it over the input
> sequence. This is not only redundant, but in fact undefined for result sets
> with
> more than one row. The requirement's definition is moreover
> self-contradictory,
> because output parameters are not *in* the input sequence.

I think you are mixing up parameters of a stored procedure and
possible result sets that the call to the procedure creates.

It's true that .callproc() has to copy over the input parameters
to create the return value, but this doesn't affect the result
sets.

For pure output parameters, you typically pass in a placeholder value
as input parameter, since it will be overwritten anyway. Using
a placeholder value is often needed to help determine the type of the
output parameter, so e.g. you'd use an empty string as input
if you know that the output parameter is going to be a string.

> The aforementioned result set is a sufficient and intuitive tool for
> extracting
> data from a stored procedure call. As mechanisms for returning data, stored
> procedures are not very different from queries. All queries yield result
> sets.
> All stored procedures yield result sets. Some queries yield empty result
> sets.

That's not universally true. You can easily have stored procedures
which don't generate results sets at all, but only return some
aggregate query value via an output parameter.

I agree that using result sets for passing back output
data from a stored procedure is often a better approach. However
that doesn't help you when you have to use a stored procedure which
you cannot change to implement this :-)

> Some stored procedures yield empty result sets. As such, I would argue that,
> like a query (e.g. cur.execute on a SELECT statement) cur.callproc *must*
> endow
> the cursor with a result set.

See above. That requirement would be too strong and make it
impossible to access database stored procedures which do not
generate result sets.

> I propose changing the DBAPI spec on callproc to be as follows:
> 
> (This method is optional since not all databases provide stored
> procedures.)
> 
> Call a stored database procedure with the given name. The sequence of
> parameters
> must contain one entry for each argument that the procedure expects.
> Overloaded
> procedures are supported. If the underlying database supports named
> parameters
> in stored procedures, the sequence of parameters may be given as a
> dictionary-like object mapping parameter names to values.
> 
> The procedure must provide a result set as output. This is then made
> available
> through the standard fetch* methods.
> 
> Return values are not defined.

You are free to implement this as new separate cursor method,
but as explained above, we cannot change the existing definition
in such an incompatible way.

BTW: If you want to add support for dictionary based parameters
to .callproc(), I'd suggest to use the same approach as for
positional parameters: copy over the input values and replace
and output and in/out parameters with the new values.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Experts (#1, Dec 02 2015)
>>> Python Projects, Coaching and Consulting ...  http://www.egenix.com/
>>> Python Database Interfaces ...           http://products.egenix.com/
>>> Plone/Zope Database Interfaces ...           http://zope.egenix.com/
________________________________________________________________________

::: We implement business ideas - efficiently in both time and costs :::

   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/
                      http://www.malemburg.com/