[Melbourne-pm] Perl DBI reference recommendations

[Daniel Pittman]

"Sam Watkins" <sam at nipl.net> writes:

>> 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);

DBI->connect(..., $password, { AutoCommit => 1, RaiseError => 1 });

AutoCommit == 1 means no transactions by default; without that the default is
randomly selected based on the database driver.  If you want a transaction,
explicitly use one.

RaiseError == 1 means to "die" rather than return undef on an actual error;
this makes it much safer to ignore error checking in your code.


> my $sth = $dbh->prepare("SELECT foo, bar FROM baz WHERE foo = ? AND bar
> = ?");

I prefer to encourage "prepare_cached", which saves computation if the same
statement is used multiple times, and costs little enough if it is used only
once, at the scale being discussed.

Either works, though.

> $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!

$dbh->do("UPDATE table SET foo = ?", undef, "foovalue");


> 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.

*nod*

> DBI has umpteen different fetch methods, your life will be easier if you
> just use one of them.

*nod*  I usually encourage fetchrow_hashref, which is easier to use because it
is order-independent at the cost of extra CPU use, but any single choice
works.

Regards,
        Daniel

-- 
✣ Daniel Pittman            ✉ daniel at rimspace.net            ☎ +61 401 155 707
               ♽ made with 100 percent post-consumer electrons