Everything

php

PDO proposal: add PDOStatement::nextResult() method to support stored procedures

php.internals

Dan Scott

21 years ago

Wez et al:

I've been writing a chapter on database programming with PHP, using
PDO, and ran across a scenario that has not yet been fulfilled by the
PDO API. Many databases (Apache Derby, DB2, Microsoft SQL Server,
MySQL 5, and PostgreSQL to a certain extent) support stored procedures
that can return multiple result sets. Database APIs typically set the
result set pointer to the first row of the first result set returned,
allow the application to iterate through the rows, and provide some
method to request the second result set from the database server.

In ODBC, for example, you would call SQLFetch() to iterate through the
rows of the first result set, then call SQLNextResult() to request the
next result set.

In the PHP realm, Unified ODBC defines odbc_next_result(); mssql
defines mssql_next_result(); PEAR::DB defines DB_Result:nextResult().

PDO, however, currently provides no means of working with the second
or subsequent result sets returned from a call to a stored procedure.

To build on the established naming practice from existing database
extensions, I propose that the PDO interface define the standard
method:
/* {{{ proto bool PDOStatement::nextResult(void)
Requests the next result set from the database
*/

Thanks,
Dan

Note: somewhat confusingly, mysqli defines mysqli_next_result(), but
this is used to retrieve the results of a multi-query... a very
different thing, and not something I advocate adding to PDO. They
might have trouble disambiguating that from the stored procedure case
when MySQL 5 reaches production status.

Jared Williams

21 years ago

>
> I've been writing a chapter on database programming with PHP,
> using PDO, and ran across a scenario that has not yet been
> fulfilled by the PDO API. Many databases (Apache Derby, DB2,
> Microsoft SQL Server, MySQL 5, and PostgreSQL to a certain
> extent) support stored procedures that can return multiple
> result sets. Database APIs typically set the result set
> pointer to the first row of the first result set returned,
> allow the application to iterate through the rows, and
> provide some method to request the second result set from the
> database server.
>
> In ODBC, for example, you would call SQLFetch() to iterate
> through the rows of the first result set, then call
> SQLNextResult() to request the next result set.
>
> In the PHP realm, Unified ODBC defines odbc_next_result();
> mssql defines mssql_next_result(); PEAR::DB defines
> DB_Result:nextResult().
>
> PDO, however, currently provides no means of working with the
> second or subsequent result sets returned from a call to a
> stored procedure.
>
> To build on the established naming practice from existing
> database extensions, I propose that the PDO interface define
> the standard
> method:
> /* {{{ proto bool PDOStatement::nextResult(void) Requests the
> next result set from the database */
>
> Thanks,
> Dan
>
> Note: somewhat confusingly, mysqli defines
> mysqli_next_result(), but this is used to retrieve the
> results of a multi-query... a very different thing, and not
> something I advocate adding to PDO. They might have trouble
> disambiguating that from the stored procedure case when MySQL
> 5 reaches production status.
>

Doesn't PDOStatement::nextRowset() do this?

Jared

Christopher Kings-Lynne

21 years ago

> PDO API. Many databases (Apache Derby, DB2, Microsoft SQL Server,
> MySQL 5, and PostgreSQL to a certain extent) support stored procedures

PostgreSQL to a certain extent, what the...?

Anyway, why do stored procs need to be treated differently to normal
results?  In PostgreSQL they just return results like normal SELECTs...

Chris

Derick Rethans

21 years ago

On Sat, 9 Apr 2005, Christopher Kings-Lynne wrote:

> > PDO API. Many databases (Apache Derby, DB2, Microsoft SQL Server,
> > MySQL 5, and PostgreSQL to a certain extent) support stored procedures
>
> PostgreSQL to a certain extent, what the...?
>
> Anyway, why do stored procs need to be treated differently to normal results?
> In PostgreSQL they just return results like normal SELECTs...

Because SPs can return multiple result sets...or can't PostGreSQL don't
do this?

Derick

--
Derick Rethans
http://derickrethans.nl | http://ez.no | http://xdebug.org

Dan Scott

21 years ago

On Apr 9, 2005 11:48 AM, Christopher Kings-Lynne
<chriskl@familyhealth.com.au> wrote:

> > PDO API. Many databases (Apache Derby, DB2, Microsoft SQL Server,
> > MySQL 5, and PostgreSQL to a certain extent) support stored procedures
>
> PostgreSQL to a certain extent, what the...?
>
> Anyway, why do stored procs need to be treated differently to normal
> results?  In PostgreSQL they just return results like normal SELECTs...
>
> Chris
>

Sorry Chris, no slight against PostgreSQL intended. I guess when I
think of "normal" SELECT statements, they return a single result set.
But then I'm coming at this from a different set of databases that use
CREATE PROCEDURE / CALL to deal with stored procedures, instead of
CREATE FUNCTION / SELECT in PostgreSQL's case. I do enjoy the learning
curve associated with database abstractions :)

I was reading through the PostgreSQL 8.0 docs and saw how functions
returned multiple result sets, but didn't see the corresponding
client-side API for calling it. Looking deeper at the docs, in pgsql
would it be pg_get_result()?

BTW, I have now added PDOStatement::nextRowset() documentation to the
manual. That's what I get for reading my own (incomplete)
documentation.

Dan

Christopher Kings-Lynne

21 years ago

> BTW, I have now added PDOStatement::nextRowset() documentation to the
> manual. That's what I get for reading my own (incomplete)
> documentation.

Oh multiple result sets per stored procedure...  I'm not sure that is
possible in PostgreSQL...