Multiple data sets in MySQL stored procedures

[gmax]

The latest versions of DBD::mysql introduced some enhancements, and especially it has fixed a bug that prevented Perl users from calling stored procedures containing multiple datasets.

As stated in the manual, MySQL stored procedures can include one or more SELECT statements in their body. Each call to SELECT generates a data set. Thus a simple call to a stored procedure may end up with multiple data sets, which need to be handled properly.
Old version of DBD::mysql could simply not deal with this problem. Even when the stored procedure includes only one statement, it generated an error. Versions between 3.0005 and 3.0008 could get one dataset, but failed to get the next ones, and no error was generated.

Starting with version 4.001, the issue has been properly addressed, and now you can get your results from a stored procedure with just a bit of extra care.

The method, shown in the code below, requires a call to $sth->more_results after the you have dealt with the first data set. If this call returns anything different from undef, it means that another data set awaits treatment, and you can handle each one of them as if they were isolated. Each data set has its own metadata.

#!/usr/bin/perl
use strict;
use warnings;
use DBI;

#
# Connection parameters.
# Adjust them to suit your needs.
#
my $HOST            = '127.0.0.1';
my $user            = undef;
my $password        = undef;
my $defaults_file   = "$ENV{HOME}/.my.cnf";

my $dbh = DBI->connect('DBI:mysql:test;'
        . "host=$HOST"
    . ";mysql_read_default_file=$defaults_file",  
    $user,
        $password, 
        {RaiseError => 1, PrintError => 1}) 
    or die "can't connect ($DBI::errstr)\n";

#
# A sample table.
#
my $create_table = qq{
    CREATE TABLE t1 (
        id INT NOT NULL, 
        some_text CHAR(20),
        PRIMARY KEY (id)
    )
};

#
# The sample procedure.
# Notice that it contains 4 SELECT statements
#
my $create_procedure = qq{
    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
};

#
# creating the test table and procedure
#
$dbh->do(qq{DROP TABLE IF EXISTS t1});
$dbh->do(qq{DROP PROCEDURE IF EXISTS p1});
$dbh->do($create_table);
$dbh->do($create_procedure);

#
# inserting a few rows in the table
#
$dbh->do(qq{INSERT INTO t1 VALUES 
        (1, 'aaa'), 
        (2, 'bbb'), 
        (3, 'ccc')});

#
# Calling the procedure
#
my $sth = $dbh->prepare(qq{CALL p1(2,5)});

$sth->execute();

my $more_results;
my $count = 0;
do {
    # 
    # The loop must run at least once
    #
    $count++;
    print "\ndataset $count\n";

    # 
    # The dataset is isolated. Each one has its own column names
    #
    my $names = $sth->{NAME};
    print_line($names); 
    print_line([map {'-' x length $_} @$names]); 

    #
    # Now we can get the data as if we were dealing
    # with a normal dataset,
    #
    # either all records at once
    #
    if ($count % 2 == 0 ) {
        my $rows = $sth->fetchall_arrayref();
        print_line ($_) for @$rows; 
    }
    #
    # or one by one.
    #
    else {
        while (my $row = $sth->fetchrow_arrayref()) {
            print_line ($row);
        }
    }
    #
    # Finally, check if more datasets are available
    #
} while ( $more_results = $sth->more_results);

sub print_line {
    my ($line) = @_;
    print q{  }, join(q{ }, map {sprintf '%-12s', $_} @$line),"\n";
}

There is still work to do, but the recent fix allows a more intensive usage of MySQL stored procedures with Perl code.

[derby]

<rant>Our database developers are notorious for creating stored procs that return multiple result sets ... it makes my skin crawl. Multiple result sets violate cohesion principles (do one thing well). Whenever I bring this up, the answer always seems to be - "well we need to reduce network latency." To me, that's a premature optimization and although that may have been true 10 or more years ago it really isn't a problem for most (for my company) today.</rant>

Okay ... sorry for the rant ... back to your normal viewing.

[mpeppler]

There are situations where a stored proc that returns multiple result sets makes a lot of sense.

Then there is the Sybase way, where not only stored procs, but normal SQL batches including lots of requests can be sent to the dataserver with one single request, followed by a simple loop to fetch the results.

This makes client-side programming elegant, as I can throw a Transact-SQL block with IF and WHILE clauses at the server and not have to add any special code to handle it.

Michael

[Herkum]

Consistently I have found( in general not just IT) that most people are intellectually lazy.

Once they find some way of doing something they will not attempt to find another way to do it. Often they will say or do 'ANYTHING' to keep from changing their habits or their thoughts on particular subjects/processes or methods.

In your case, I bet if you ask them for details so that it can fixed, you will get an amazing amount of information and stories that will keep you running-around on why it is an issue.

[smiffy]

Just providing a cross-reference here. I asked for clarification on this issue, but ended up getting the answers through experimentation.

If one creates a stored procedure that always returns a known number of result sets (probably good practice to do this anyway), the only difference to the "traditional" usage of DBI is that you need to call $sth->more_results() before retrieving each new set (otherwise you don't get the new set.)

So, this means that $sth->more_results() doesn't just mean "are there more results?" but is also the command to retrieve the next result set.

Please see the node I have referenced above for examples.