<!- i use postgres. you should too. --> hi, monks.

i've been using dbi a whole lot lately. im writing usually upwards of 700 lines of code per week. anyone who's coded with dbi knows that its kind of hard to write code that isnt fraught with unmaintainable peril.

i guess the problems come in because of all the references and abstractions used.

here are a few examples of stuff I find difficult to code in dbi in a manner that is clearly readable by novice programmers (many of the people I work with) and also not so cluttered that intermediate programmers can read it without too much pain.

inserts with $sth

my $sth = $dbh -> prepare("insert into table1 (col1, col2, col2) value
+s (?, ?, ?)");
[download]

my beef here is that the line winds up being so long. risacher and i have discussed style and spacing. i used to think that horizontal space wasnt such a premieum, but vertical space was. this was when i was using a 21" monitor. i'm now using a 15" laptop screen. the above is a very simple query, and still comes out at 84 characters. you can see how cramped lines this long can be in this screen sh ot.

This problem is even further complicated by using descriptive variable names. Let's look a little further at this:

# do we need an insert or an update?
my $row_checker = $dbh -> prepare("select count(col1) from table1 wher
+e col1 = ? and col2 = ? and
 col3 = ?");
my $inserter = $dbh -> prepare("insert into table1 (col1, col2, col2) 
+values (?, ?, ?)");my $upda
ter = $dbh -> parepare("update table1 set col1 = ?, col2 = ?, col3 = ?
+ where col1 = ? and col2 = 
? and col3 = ?");
[download]

as you can see, this gets ugly in a hurry. so i've been doing two things when this starts to bother me and set off my ugly-sensors.

my $row_checker = "select count(col1) from table1 where col1 = ? and c
+ol2 = ? and col3 = ?";
$row_checker = $dbh -> prepare($row_checker);

# or ...

my $updater = $dbh -> prepare(qq{ 
        update table1 set col1 = ?, col2 = ?, col3 = ?
                where col1 = ? and col2 = ? and col3 = ?
});
[download]

i still don't really like either of these. the first one is more compact for smaller queries, but still doesnt help for larger queries. the second one is okay, but i dislike having the }); sitting all by itself on another line. but it is much better than the original, imho.

pulling out data from the database

so lets say you want to pull a single tuple out of the database. this is something most of us do quite frequently. even on a huge database like the one at work (terabyte scale), i still do want one tuple pretty frequently. here's what the dbi pod would have you do:

my $rowRef = $dbh -> selectrow_arrayref("select col1 from table1 where
+ col1 = col2 and col2 != '$
someval'");
my @row = @{ $rowRef };
#or ...
my $tuple = shift @{ $rowRef };
#or ...
my $tuple = @{ $rowRef }[0];
[download]

there are some problems with this for people who use warnings and/or strict. if your reference comes back empty (because your query failed), your whole script dies with the ever popular "cant use an undefined value as an arrayref at oops.pl line 100", which sucks. i dont want my program to crash because my query failed, i'd rather trap the error and report it to the user (and/or the developer)! instead, i've been using this:

my ($tuple) = map { @{ $_ } } $dbh -> selectall_arrayref("select col1 
+from table1 where col1 = co
l2 and col2 != '$someval'");
[download]

of course you can use trickery like $query or selectall with a multiline qq{}, but i like the above method because then I can do:

if ($tuple) { # succeeded...
[download]

and i dont have to play around with if ref $tuple ... which I find rather irritating .

now, that having been said, i generally prefer to not use $dbh when I can use a statement handle instead.

my $next_col1 = "select distinct(col1) from table1";
$next_col1 = $dbh -> prepare($next_col1);
$next_col1 -> execute();

my @col1s = map { @{ $_ } } @{ $next_col1 -> fetchall_arrayref() };

foreach my $col1 (@col1s) { ...
[download]

in general, if youre going to be executing a query a lot of times, a $sth is a better bet than just using $dbh. its faster, and you let the DBD module take care of quoting and whatnot. it also gives you more meaningful errors if you call it with too many bind values or not enough.

i'm sure I have more syntactic tricks up my sleeve that i'm not remembering right now. the purpose of this post, however, was to see if anyone was doing anything with dbi's refs of refs of refs of (gack!)... and making it easier to read and/or write.

happy new year and stuff.
brother dep.

--
Laziness, Impatience, Hubris, and Generosity.

---

---

• Are you posting in the right place? Check out Where do I post X? to know for sure.
• Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:

  <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>

• Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
• Want more info? How to link or How to display code and escape characters are good places to start.