Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical

Comment on

( #3333=superdoc: print w/replies, xml ) Need Help??

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 = ''; 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.

 _  _ _  _  
(_|| | |(_|><

In reply to Multiple data sets in MySQL stored procedures by gmax

Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":

  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?

    What's my password?
    Create A New User
    and all is quiet...

    How do I use this? | Other CB clients
    Other Users?
    Others contemplating the Monastery: (11)
    As of 2018-06-25 12:59 GMT
    Find Nodes?
      Voting Booth?
      Should cpanminus be part of the standard Perl release?

      Results (126 votes). Check out past polls.