Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked

comment on

( #3333=superdoc: print w/replies, xml ) Need Help??

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

In reply to (dkubb) Re: (1) dbi style questions (code, discussion) by dkubb
in thread dbi style questions (code, discussion) by deprecated

Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":

  • 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.
Log In?

What's my password?
Create A New User
Domain Nodelet?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (3)
As of 2023-01-31 00:49 GMT
Find Nodes?
    Voting Booth?

    No recent polls found