[Melbourne-pm] Perl DBI reference recommendations

[matt]

On Thu, Jul 16, 2009 at 5:38 PM, Sam Watkins<sam at nipl.net> wrote:
>> I want a handy 'desk' reference with
>>
>> a) Lots of examples
>> b) Easy to read and complete reference material
>>
>> The reason is that I can't hold the details in my head (because I do
>> not DBI regularly) , but I need to keep knocking off dirty that little
>> scripts that run sql queries. I figure the easiest way for that is SQL
>> via DBI rather than an abstraction module.
>
> Here is a little "handy desk reference" for you, covering all of DBI
> that you should use.  It is shorter than the manpage or a book ;)
>
> my $dbh =
> DBI->connect("DBI:mysql:database=$database;host=$hostname;port=$port",
> $username, $password);
> my $sth = $dbh->prepare("SELECT foo, bar FROM baz WHERE foo = ? AND bar
> = ?");
> $sth->execute("foovalue", $barvalue);
> while (my $row = $sth->fetchrow_arrayref) {
>  my ($foo, $bar) = @$row;
>  print "$foo  $bar\n";
> }
> $sth->finish;
>
>
> You should rarely need to use anything other than the above from DBI.
> keep it simple!
>
> I highly recommend using the "bind values" stuff with the ?s in the SQL
> and corresponding parameters to execute.  DBI will quote and escape
> values for you correctly so you don't need to worry about SQL injection
> attacks, etc.
>
> DBI has umpteen different fetch methods, your life will be easier if you
> just use one of them.
>
> regards,
>
> Sam
> _______________________________________________
> Melbourne-pm mailing list
> Melbourne-pm at pm.org
> http://mail.pm.org/mailman/listinfo/melbourne-pm
>

It's not clearly documented but the empty-hashref-arg trick is also quite neat:

my $select = $dbh->prepare("SELECT 1 as one, 2 as two, 3 as three");
$select->execute;
foreach my $row ( $select->fetchall_arrayref({}) ) {
 print Dumper($row); # { one => 1, two => 2, three => 3 }
}