easier to get data from a database

rsiedl has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks

I've just written a little snippet to allow me to retrieve data from a mysql database without all the usual DBI prepare, execute, fetchrow every time i need to access the db.

I was just after your thoughts and criticism's.

Here it is...

#!/usr/bin/perl

# Modules used
use strict;
use warnings;
use DBI;

# Database Definition
our $dbh = &DB_OPEN('db_name','localhost','3306','db_user','db_passwor
+d');
sub DB_OPEN
{
  my ($db_name,$host_name,$port,$db_user,$db_pass,) = @_;
  my $database = "DBI:mysql:$db_name:$host_name:$port";
  my $dbh = DBI->connect($database,$db_user,$db_pass);
}

# Retreive Database data
sub DB_GET
{
  my ($table,$refkey,$refval) = @_;
  my (%db_data,$sth);
  my $select = "select ";
  $sth = $dbh->prepare("desc " . $table);
  $sth->execute;
  while ( my ( $NAME, $TYPE, $NULL, $KEY, $DEFAULT, $EXTRA ) = $sth->f
+etchrow ) {
    $select .= "'$NAME',$NAME,";
  } # end-while
  $sth->finish;
  chop($select);
  $select .= " from $table";
  if ($refval) { $select .= " where $refkey=?"; }
  $sth = $dbh->prepare( $select );
  if ($refval) { $sth->execute($refval); }
  else { $sth->execute(); }
  while ( my ( @array ) = $sth->fetchrow_array ) {
    my %tmp_hash;
    my $ref = $array[1];
    for (my$i=0;$i<scalar(@array);$i+=2) {
      if ($array[$i] eq "$refkey") { $ref = "$array[$i+1]" }
      $tmp_hash{$array[$i]} = $array[$i+1];
    } # end-for
    $db_data{$ref} = { %tmp_hash };
  } # end-while
  $sth->finish;
  %db_data;
}
[download]

Then I just include the above file and I can use it with something like:

my %db_data = &DB_GET('clients', 'client_id', $client_id);
[download]

Thanks
Reagen

Comment on easier to get data from a database Select or Download Code

Replies are listed 'Best First'.
Re: easier to get data from a database by Joost (Canon) on Jul 12, 2005 at 16:44 UTC
Critisms: You're doing a describe on the table for each query which is inefficient (and not portable), your code doesn't handle any interesting queries (only select by ID specific values), and you should probably use $fetchrow_hashref instead of transforming the result of $fetchrow_array to a hash. This has all been done before, also by me, and I learned a lot from it, but the main thing I learned is that doing it right takes a whole lot of effort. :-) Take a look at Class::DBI and Class::DBI::Autoloader for a framework that tries to do it right (and mostly succeeds in my opinion). There is a list of different implementations here. "What should it profit a man, if he should win a flame war, yet lose his cool?"	[reply]
Re: easier to get data from a database by fmerges (Chaplain) on Jul 12, 2005 at 16:45 UTC
Hi, Read the DBI down because for this kind of selects you can use methods of the DBI to take the data back to the data type you want... For example: `$ary_ref = $dbh->selectall_arrayref($statement); $hash_ref = $dbh->selectall_hashref($statement, $key_field); $ary_ref = $dbh->selectcol_arrayref($statement); $ary_ref = $dbh->selectcol_arrayref($statement, \%attr); @row_ary = $dbh->selectrow_array($statement); $ary_ref = $dbh->selectrow_arrayref($statement); $hash_ref = $dbh->selectrow_hashref($statement);` [download] So I don't think that your approach was worst in time, and the connection stuff is not much work to code it... I wouldn't use something like you post... You can also use some nifty things like Class::DBI or some more simple but also very confortable like DBIx::Abstract Normally you use config files where you set all the database connection data so that you only need the line where you connect/create the DBI object... and the keep on rollin' Regards, \|fire\| at irc.freenode.net I like merlyn's disclaimer	[reply] [d/l]
Re: easier to get data from a database by kirbyk (Friar) on Jul 12, 2005 at 16:47 UTC
That's a neat shortcut. The downside is that it's horribly inefficient. You're slurping in the whole table every time, rather than just what you need, and doing an extra query to get the contents of the table. Don't get too reliant on this shortcut, because it will not be acceptable in applications that have performance demands. But for code where performance doesn't matter much, and you always read in the entire contents of a table (preferably only once!) it's a good trick. -- Kirby, WhitePages.com	[reply]
Re: easier to get data from a database by davidrw (Prior) on Jul 12, 2005 at 16:53 UTC
correct me if i'm wrong (and in that case please explain what you DB_GET function returns), but i believe this is just: `use DBI; my ($db_name,$host_name,$port,$db_user,$db_pass,) = @_; my $database = "DBI:mysql:$db_name:$host_name:$port"; my $dbh = DBI->connect($database,$db_user,$db_pass); my ($table, $col, $value) = ('clients', 'client_id', $client_id); my $sql = "select * from $table"; if( $value ){ $sql .= " where $col = ?"; push @bind, $value; } my $href = $dbh->selectall_hashref($sql, $col, {}, @bind);` [download] I'm not sure exactly though.. it doesn't appear to me that your code works if trying to select out all the rows.. But be sure to read through the DBI docs (`perldoc DBI`) and look at all the `select*` methods available. Other modules to be aware of: SQL::Abstract, Class::DBI, Class::DBI::Autoloader	[reply] [d/l] [select]
Re: easier to get data from a database by Tanktalus (Canon) on Jul 12, 2005 at 19:57 UTC
FYI - I ++'d the OP not because Reagen has a particularly elegant or insightful code snippet. But because it's a SoPW, not a code snippet, and Reagen is about to learn a bunch of stuff. (Describing the table rather than using fetchrow_hashref?) Of course, premature optimisation being the root of all evil and all, I do have to give at least one thumb up on actively seeking Laziness! If/when you find this to be slow, you'll have some lessons learned and be able to make some more leaps of logic to get to the next level. As has been pointed out, Class::DBI may be a good bet for you there.	[reply]