Today’s lesson in Perl is the difference between the DBI fetchrow_array() and fetchrow_hashref() calls. The both work, returning a row from a database, but they differ significantly how that data is stored for later use. Calling fetchrow_array() returns an array full of values from the query. It’s up to you, the developer, to keep track of what was asked for and in what order. When looking at code that is longer than a single screen, this gets to be a nightmare, as everything is referenced numerically: $r[2] selects the 3rd item requested.

fetchrow_hashref() instead packs the data into a hashref, using the column name from the query results as the key. This way, instead of checking to see where ‘user_name’ appeared in the query statement, finding that it was the 3rd field, and then using $r[2], you can just write $r->{'user_name'}. Nothing to look up, nothing to remember, and it sets you up for some much better usage down the line with fetchall_hashref().

fetchall_hashref() allows you to pull the entirety of the query results and pack them into a single hashref, referenced by whatever unique key you like. (You are using unique keys, right?) With fetchall_hashref(), you can then say $r->{$hostname}->{'service_status'}, for any suitable permutation of $hostname.

All of this comes up because I’ve just had to go through a fix a bunch of code I wrote before I discovered the magic of fetchrow_hashref(). Remember: if it’s painful to write, it’s 5 times as painful to fix 2 years later, under pressure.