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

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

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