http://qs1969.pair.com?node_id=256835


in reply to MySQL count with DBI

Yeah I HAD an idea, a combination Hat / Shoe garment, but the backers dropped out and I
lost EVERYTHING. Anyway, does this answer your non-question?
#!perl -w
use strict;
use DBI;

my $dbh = DBI->connect('DBI:mysql:my_database', 'my_username', 'my_pas
+sword')
    or die "Couldn't open database: $DBI::errstr; stopped";

# Prepare the SQL query for execution
my $sth = $dbh->prepare(<<SQL) or die "Couldn't prepare statement: $DB
+I::errstr; stopped";
SELECT COUNT(*) FROM music
SQL

# Execute the query
$sth->execute() or die "Couldn't execute statement: $DBI::errstr; stop
+ped";

#print result
my ($count) = $sth->fetchrow_array();
print "Found $count results\n";

[download]
Smitz

Yeah I copied and pasted from stephens excellent Reading from a database. Sorry, stephen

Replies are listed 'Best First'.
Re: Re: MySQL count with DBI
by Itatsumaki (Friar) on May 09, 2003 at 15:11 UTC

    Not to be a pita, but I think it is vastly clearer and safer to explicitly write SQLs explicitly into a variable, rather than read them from STDIN. In addition, I'm not a huge fan of using ->fetchrow_array() to get back a single method: that is exactly what ->selectrow_array() is meant for, I think. Finally, if the questioner is just starting to use DBI he should probably enable it's error-handling apparatus explicitly until he knows when/why he might not want to use it. Similarly, I would explicitly turn off autocommit to limit accidental damage to the DB.

    No major changes there, smitz, but just seems "safer" this way to me....

    my $dbh = DBI->connect('DBI:mysql:my_db', 'user', 'pass', {RaiseError 
+=> 1, PrintError => 1, AutoCommit => 0}) or die "Error connecting to 
+DB: $DBI::errstr\n";

my $sql = "SELECT COUNT(*) FROM music";
my @row = $dbh->selectrow_array($sql);
print "Found $row[0] results\n";

    [download]
    -Tats
[reply]
[d/l]
      Yeah you are right, I should have been more discriminating when I copied stephen's tut, however, Im not sure why you say:
      > it is vastly clearer and safer to explicitly write SQLs explicitly into a variable, rather than read them from STDIN.
      Where is my snippet doing that?

      Smitz
[reply]

        In fact you are not: I just skimmed your code too quickly and mis-read the <<SQL as <STDIN> for some reason. My bad.

        On the other hand, I *do* think it is much better to avoid allowing an application to receive a SQL statement as a parameter. That opens up lots of scope for problems, but you probably already knew that. :)

        Update in bold. Who knew one little word could make it mean the exact opposite of what I intended? :)

[reply]

In Section Seekers of Perl Wisdom