Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask
 
PerlMonks  

comment on

( [id://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":



  • 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?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others taking refuge in the Monastery: (4)
As of 2024-03-29 10:10 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found