kiat has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks,

What is the normal way to execute multiple sql queries? Suppose I've an sql query to describe a table and a second one to select all the elements of a table, how do I execute both queries and print out the data?

Here is how I do it:

# Store sql queries in @sqls
push (@sqls, qq{ DESCRIBE $table{'profile'} });
push (@sqls, qq{ SELECT * FROM $table{'profile'} });

html_start();
print qq~<table>\n~;
foreach (@sqls) {
  $sth = $dbh->prepare($_);
  $sth->execute()
  or bail_out("Cannot execute query.");

  while (my @ary = $sth->fetchrow_array()) {
    print qq~<tr valign="top">\n~;
      foreach (@ary) {
        print qq~<td>$_</td>\n~ if $_;
      }
    print qq~</tr>\n~;
  }
}
print qq~</table>\n~;
html_end();

[download]
Is that how it's normally done?

Thanks in anticipation :)

20031204 Edit by Corion: Changed title from 'multiple perl-myslq queries...'

Replies are listed 'Best First'.
Re: multiple perl-mysql queries...
by jeffa (Bishop) on Dec 04, 2003 at 14:30 UTC

    From the results you want, i'd just use DBIx::XHTML_Table and be done with it. But since that doesn't answer the question, here's more.

    First, you have two completely different queries that produce two completely different results ... how can you expect the same loop to process both? Drop the for loop and process the first query first, the second query second. Also, you may want to experiment with a HandleError since you have your function bail_out: 
    my $dbh = DBI->connect(
   'connect string','user','pass',
   {RaiseError=>1, HandleError => \&bail_out},
};

    [download]
    Then bail_out will be called for you when errors occur. Much nicer.

    UPDATE:
    I really wanted you to refer to the source for DBIx::XHTML_Table, but that is an awful lot to digest for such a simple task. If you prepare the query first, then you can access the names of the columns without having to issue 2 queries — just one. Here is a quicky that uses CGI.pm to generate all HTML: 

    my $sth = $dbh->prepare("select * from foo");
$sth->execute;

print table(
   Tr [
      th($sth->{NAME}),
      map {map td($_), @$_} $sth->fetchall_arrayref
      
   ]
);

    [download]

    jeffa

    L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)
[reply]
[d/l]
[select]
      Thanks, jeffa!

      Does HandleError work with multiple error message types (e.g. Error connecting to database, Error retrieving records, etc)? In other words, for each error type, I want print out the appropriate error message when an error is encountered.

[reply]
        Just check the docs! :) 
        "HandleError" (code ref, inherited)
   The "HandleError" attribute can be used to provide
   your own alternative behaviour in case of errors. If
   set to a reference to a subroutine then that
   subroutine is called when an error is detected (at the
   same point that "RaiseError" and "PrintError" are
   handled).

   The subroutine is called with three parameters: the
   error message string that "RaiseError" and
   "PrintError" would use, the DBI handle being used, and
   the first value being returned by the method that
   failed (typically undef).

   If the subroutine returns a false value then the
   "RaiseError" and/or "PrintError" attributes are
   checked and acted upon as normal.

        jeffa

        L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)
[reply]
      Thanks for your update, jeffa :)

      I experimented with your code but I find it rather difficult to understand especially with the double map.

      I would like to print out the names of the columns, just like your code does (names of columns, followed by columns' values). How can I do that with my code using fetcharray_row()?

[reply]
        Using your original code, it would look something like this: 
        my $sth = $dbh->prepare("select * from foo");
$sth->execute;

print qq~<table>\n~;
print qq~<tr>\n~;
foreach (@{$sth->{NAME}}) {
   print qq~<th>$_</th>\n~;
}
print qq~</tr>\n~;

while (my @ary = $sth->fetchrow_array) {
   print qq~<tr valign="top">\n~;
   foreach (@ary) {
      $_ = defined($_) ? $_ : '&nbsp;';
      print qq~<td>$_</td>\n~;
   }
   print qq~</tr>\n~;
}

print qq~</table>\n~;

        [download]
        But if you haven't tried DBIx::XHTML_Table yet, please do.

        jeffa

        L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)
[reply]
[d/l]
Re: multiple perl-mysql queries...
by hardburn (Abbot) on Dec 04, 2003 at 14:38 UTC

    Instead of the unportable DESCRIBE table, use DBI's table_info. Then use the results to get rid of that ugly SELECT *.

    ----
    I wanted to explore how Perl's closures can be manipulated, and ended up creating an object system by accident.
    -- Schemer

    : () { :|:& };:

    Note: All code is untested, unless otherwise stated

[reply]
[d/l]
[select]
Re: multiple perl-mysql queries...
by Abigail-II (Bishop) on Dec 04, 2003 at 14:32 UTC
    Your way of doing it seems perfectly fine. The typical way to do selects with DBI is:
    • prepare
    • execute
    • fetch
    The fetch is typically done in a loop (either in the program, or by the DBI), and you can have multiple execute/fetch cycles for a prepare (another advantage of placeholders).

    Since the second query doesn't depend on the results of the first, doing all the actions of the first before starting with the second is fine.

    No doubt I would write it differently than you, but in essence, it would still do the same, in the same order.

    Abigail

[reply]

Back to Seekers of Perl Wisdom