Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

(dkubb) Re: (1) dbi style questions (code, discussion)

by dkubb (Deacon)
on Dec 29, 2001 at 11:34 UTC ( #135089=note: print w/replies, xml ) Need Help??


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 statement handles are prefixed with $sth_, as in $sth_get_customers if I am using more than one, otherwise I just use $sth.
  • If I am using more than one database handle, then I prefix all of them with $dbh_, as in $dbh_accounting. Otherwise I just use $dbh.

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

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://135089]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (1)
As of 2022-11-26 23:19 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Notices?