http://qs321.pair.com?node_id=135089


in reply to dbi style questions (code, discussion)

I find the messy-ness that you speak of to be a common occurence when other languages are intermixed inside perl. Compared to normal perl, SQL or even HTML breaks up the flow and can cause confusion with novice and experienced programmers alike.

However, I do think it's possible to write clean DBI code, and simplify on some of the methods you use to fetch data from the database handle.

I agree with you splitting up the SQL query onto multiple lines is a better idea. I also capitalize the SQL keywords/commands, and lower case the columns and table names:

my $sth = $dbh->prepare(q{ INSERT INTO table (col1, col2, col3) VALUES (?, ?, ?) });

Style is very subjective, I'd say work what's best for you, and most importantly, be consistent. The novice programmers you work with will learn any style you choose, as long as you/they aren't changing your mind every few weeks =)

Along with SQL formatting conventions, I follow a few simple naming conventions when working with DBI:

All of the above conventions won't really do much good if you have to jump through hoops to get the data out of the "fetch" commands. =) Luckily, there are simpler ways to pull data from the database.

IMHO, the best way to pull a tuple from a data source is with DBI::selectrow_array:

my $tuple = $dbh->selectrow_array(q{ SELECT col1 FROM table1 WHERE col1 = col2 AND col2 = ? LIMIT 1 }, {}, $someval, );

I agree with your position that using statement handles is faster (especialy with some databases that can prepare a query plan, like Oracle) when you are querying over and over, when compared to using DBI's utility methods. But, there is one exception that alot of people may not know: you can prepare a statement and pass that off to a utility method, not losing any speed, and cutting a few lines of code out. Consider the following:

my $sth_get_id = $dbh->prepare(q{ SELECT id FROM contact WHERE email = ? LIMIT 1 }); foreach my $email (@emails) { my $id = $dbh->selectrow_array($sth_get_id, {}, $email); print "$email -> $id\n"; }

All of DBI's utility methods can accept statement handle rather than a straight SQL query as the first parameter.

Also, if you want to fetch multiple rows, but want only the value from the first column of each row, DBI has a built in method called selectcol_arrayref that will do exactly that:

my $emails = $dbh->selectcol_arrayref(q{ SELECT email FROM contact WHERE id BETWEEN ? AND ? }, {}, 1, 100, ); foreach my $email (@$emails) { ...