Some things about DBI I did not know before

[fu]

Hmm, I've been thinking about this a bit, and I'd like to start noting down what I'm working on, informally.

What this means is that I'll be posting more: sometimes I'll be posting more technical stuff, other times I'll be posting less technical stuff. More technical entries like this one, which include code, will be tagged with "development", so you can either filter that out or read only that. We'll see how it turns out.

Today, while reviewing a patch to allow support requests to be mass-closed, I stumbled upon code that was completely unfamiliar to me:

$dbh->selectall_arrayref( "...", { Slice => {} } )

Usually the first argument to one of the database calls is undef; I've never bothered to figure out why, just chalked it up to something that was over my head. Tonight, it was obviously time to figure out what that first parameter was for!

Discovery number one: the first parameter is not magic. It's actually a hashref of additional attributes, in case you need to override the default settings (such as whether to commit, what to do when the database errors, what format to return the results in). Usually we just pass in undef because we don't need to override the defaults.

Discovery number two: perldoc DBI is useful for seeing the entire API of DBI, but it's very technical (I skipped sections that I thought were irrelevant; it turns out that one of those irrelevant portions was actually relevant)

Discovery number three: unrelated to previous, but taken from perldoc DBI, you can look up the last SQL statement that your program used, using warn $dbh->{Statement} or warn $sth->{Statement}. That prints out the last SQL statement into your logs, including the values of any variables -- useful when you're deep into debugging something.

Discovery number four: perlmonks.org has a page for DBI recipes, which was what made me realize that the whole thing with Slice? Was so you could return a list of hashrefs.

Comments

[exor674]

oo shiny!

( i kinda wonder what is more efficient...

$dbh->selectall_arrayref( "...", { Slice => {} } )

or

$dbh->prepare; while (my $row = $sth->fetchrow_hashref) {}

when one is just gonna shove the contents into an array.

[fu]

Not sure how the two compare, but from perlmonks:

"You can either build the loh manually, fetching a hash and pushing it to an array, but the DBI can do it for you quite efficiently."

So I'm guessing it doesn't matter much either way (maybe leaning toward letting the DBI do it).

[exor674]

ah anyway, the thing I was thinking of is a hash of hashes, not a list of hashes.

[kareila]

My understanding is that slices are always more efficient than iterative loops, which is why Perl included them. (Since they lose out in the ease-of-readability department, they have to have something going for them.)

[kareila]

Yeah, I find myself loading perldoc DBI in my browser every time I code SQL stuff, and I had seen the Slice syntax described there. I may have even used it with vgifts, but I can't remember for sure.

I didn't see the statement reference though, that might be useful!

[sophie]

I've used the functionality of Slice before, but not via the attribute - I've done it by using $sth->fetchall_arrayref({}), as I normally execute() in a separate step anyway.

Good to know how to do it in one step!