Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation

Extra Information

by Revelation (Deacon)
on Mar 26, 2002 at 17:06 UTC ( #154443=note: print w/replies, xml ) Need Help??

in reply to Reading from a database

I'm going to go through some things that I thought were lacking in this tutorial. They aren't necessary, but are helpful.


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.


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.


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

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://154443]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (4)
As of 2018-05-21 15:46 GMT
Find Nodes?
    Voting Booth?