Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much
 
PerlMonks  

Comment on

( #3333=superdoc: print w/ replies, xml ) Need Help??
I'm going to go through some things that I thought were lacking in this tutorial. They aren't necessary, but are helpful.

Selectrow_array:

One thing many perl coders try to do is make their code as short as possible. One way to shorten the code is to use selectrow_array, instead of fetcrow_array. This is helpful if you are only executing a query once, and only getting one row of data.
my @row = $dbh->selectrow_array(q{SELECT my_data1, my_data2 WHERE furn +iture_type=?},undef, $furniture_type);

This is an extremely simple, and elegant way of selecting, and will be as optimized as any selectrow, that is executed without binding, as $furniture_type is the data executed.

RaiseError:

I find RaiseError to be extremely helpful for mission critical queries, and the majority of queries are mission critical to a script. Therefore it may be easyer to just set RaiseError, and stop using or die.
$dbh = DBI->connect($DBDSN, $DBUser, $DBPassword, {RaiseError => 1});

To unset RaiseError on a query in which you want to specify something else to do, if the query doesn't work just add the code: $dbh->{RaiseError} = 0;, and set raiserror back to 1, after you are done.

Bind_columns:

my ($furniture_name, $price); chomp($furniture_type); $sth->execute($furniture_type) or die "Couldn't execute: '$DBI::er +rstr'"; $sth->bind_col(1, \$furniture_name); $sth->bind_col(2, \$price);
You don't need to bind a specific column here. Instead you can just bind all the columns in order, with bind_columns.
So instead of all that code you can write:
chomp($furniture_type); $sth->execute($furniture_type) or die "Couldn't execute: '$DBI::errstr +'"; $sth->bind_columns(\my ($furniture_name, $price));

This gets rid of predeclaring the variables, and using bind_col twice.

Update: Did some research yesterday and found that bind_columns is faster for *all* queries! Strange, but it seems that bind_columns is faster than a fetchrow_arrayref, even if only one row is being caught. This renders fetchrow_array(ref) obselete in a sense, without using bind_columns, as a few lines of code makes a query much faster, regardless of how many rows are being returned.

You may wish to check out this to see the benchmarking, and query analysis that shows that.

Finishing Queries:

One thing that is rather interesting is the code: $sth->finish! Most tutorials don't teach users to finish their queries, as it's not really necessary, in the majoiry of situation. I would advise reading the finish section of perldoc DBI, as finish is pertinant in some situations. A link to that is here, as there's no reason for me to describe what has so aptly been described by Carlos Ramirez. It's really code optimization, but since you went into bind_col, I figured some more optimization would be helpful.

Would like to commend the writer of this tutorial on the vast amount of information! Great job.
Gyan Kapur
gyan.kapur@rhhllp.com

In reply to Extra Information by Revelation
in thread Reading from a database by stephen

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



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • Outside of code tags, you may need to use entities for some characters:
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?
    Username:
    Password:

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

    How do I use this? | Other CB clients
    Other Users?
    Others making s'mores by the fire in the courtyard of the Monastery: (5)
    As of 2014-07-26 15:52 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      My favorite superfluous repetitious redundant duplicative phrase is:









      Results (178 votes), past polls