Handling multiple data sets in MySQL stored procedures from Perl

by Giuseppe Maxia

MySQL stored procedures can use SELECT statements in their body, returning a data set to the caller. The manual also explains that multiple data sets can be returned from such a stored procedure.
For example, assume you have this stored procedure:
CREATE PROCEDURE p1(x INT, y INT)
DETERMINISTIC
BEGIN
SELECT
x ;
SELECT
x AS first_param,
y AS second_param;
SELECT
x,
y,
x + y AS sum_xy,
x * y AS prod_xy;
SELECT * FROM t1;
END

There are four SELECT statements, with different headers, and a variable number of rows returned.
Early versions of DBD::mysql could not handle multiple data sets. Actually, prior to version 3.0004 they could not even handle one dataset from a stored procedure. Starting with version 4.001, single and multiple datasets from SPs are handled gracefully.
The trick is to call $sth->more_results after retrieving each dataset.
See the complete example at Multiple data sets in MySQL stored procedures

MySQl Users Conference and Expo
For those interested, the MySQL Users Conference and Expo includes a BoF about DBD::mysql on April 24th at 7:30pm.