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

Mike Bayer mike_mp at zzzcomputing.com
Fri Dec 4 16:04:46 EST 2015



On 12/01/2015 12:40 AM, 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. 

I believe the term "input/output parameters" refers to OUT parameters
specifically.   If Postgresql's stored procedure system is entirely
result-set based, then "output parameters" just wouldn't apply here;
you'd only need return the same list of inputs that was passed.


> This is not only redundant, but in fact undefined for result
> sets with
> more than one row. 

again, this language refers to OUT parameters, which are not result
rows, they are parameters that are writable, as in:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:325417134618


> The requirement's definition is moreover
> self-contradictory,
> because output parameters are not *in* the input sequence.

In your case of Postgresql, they're not. In the case of Oracle, MS SQL
Server, DB2, and others, they are (or can be). ;)




> All stored procedures yield result sets. 

that is entirely untrue for many databases.

SQL Server stored procedures for example usually return an integer
return code.   They can also use OUT parameters, return multiple result
sets, or a detached cursor object:

https://technet.microsoft.com/en-us/library/aa174792%28v=sql.80%29.aspx



> Some queries yield empty result
> sets.
> 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.
> 
> 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.
> 
> Thank you,
> Milosz
> 
> 
> _______________________________________________
> DB-SIG maillist  -  DB-SIG at python.org
> https://mail.python.org/mailman/listinfo/db-sig
> 


More information about the DB-SIG mailing list