Get a hash object from a database with DBI for use with HTML::Template

[hiseldl]

Description:

I needed a way to get name/value pairs from a table using another column as the "object ID." This allowed me to have access to the values in a hash instead of having to do multiple selects.

Warning: this reads the entire data set into memory, so be careful not to read huge amounts of data.

I'm using &get_hash(...) to create a hash filled with values to fill a template created with HTML::Template, I'm also fetching the template from another table in my real script; I simplified the snippet by putting the template in with the attributes with a lookup of "page". With this method, I can use two select statements with one connect statement to create a dynamic web page instead of a file load and several select statements. :)

Update: added {RaiseError=>1}, put parameter binding in get_hash(...), and added array slice in $sth->fetchall_arrayref().

--
hiseldl

#!/usr/bin/perl -w
use strict;
use DBI;
use HTML::Template;

####################################################
# Example usage
####################################################

# connect to the database
my $dbh = DBI->connect("dbi:mysql:mydb", "user", "pass",
                       {RaiseError=>1}  );

# create the hash from the name/value pairs in the db
# using object_id="1"
my %params = &get_hash($dbh, "hashobjects", 1, 2, "object_id", "1" );

# create the html template with the page value from the DB
my $template = HTML::Template->new(scalarref => \$params{page},
                   die_on_bad_params => 0,
                   );

# substitute the params in the template
$template->param(\%params);

# print everything out
print "Content-Type: text/html\n\n";
print $template->output;

# disconnect so we don't get the warning,
# "Database handle destroyed without explicit disconnect."
$dbh->disconnect();
exit 0;

####################################################
# END Example usage
####################################################


####################################################
####################################################
# get_hash
# params:
#    $dbhandle = database handle created with DBI->connect()
#    $name_col = array index of column in the record set
#                used for the key of the hash
#    $val_col  = array index of column in the record set
#                used for the value of the hash
#    $key_col  = column name in the record set
#                used for the key of the object id
#                (this is the column name used in the WHERE clause)
#    $key_val  = column name in the record set
#                used for the value of the object id
#                (this is the column value used in the WHERE clause)
#
sub get_hash {
    my ($dbhandle,$table,$name_col,$val_col,$key_col,$key_val)=@_;
    my $stmt = "SELECT * FROM $table WHERE $key_col = ?";
    my $sth = $dbhandle->prepare( $stmt );
    $sth->execute($key_val);

    # Here's where we read in the entire data set into
    # an array ref containing refs to record arrays.
    #my $ref=$sth->fetchall_arrayref();
    my $ref = $sth->fetchall_arrayref([$name_col,$val_col]);
    $sth->finish();

    # use map to turn the array ref of array refs
    # into a hash and then return it anonymously
    #return map { $_->[$name_col]=>$_->[$val_col] } @$ref;
    return map {@$_} @$ref;
}

####################################################
####################################################

__END__

# Here is a simplified version of the table
# along with some data to make the example 
# run.  This should work in MySQL.


# hashobjects.sql
#
# 1. save this data to a file
# 2. to create the table and view the data, 
#    at the mysql prompt type:
#        mysql> use mydb;
#        mysql> \. hashobjects.sql
#        mysql> describe hashobjects;
#        mysql> select * from hashobjects;
#
# Table structure for table `hashobjects`
#
CREATE TABLE hashobjects (
  object_id int(11) NOT NULL,
  name varchar(255) NOT NULL,
  value text NOT NULL
);

insert into hashobjects values (1, 'firstname', 'Jack');
insert into hashobjects values (1, 'bgcolor', '#FFFFFF');
insert into hashobjects values (1, 'page', '<HTML><HEAD></HEAD>
<BODY BGCOLOR="<TMPL_VAR NAME="bgcolor">">
Welcome, <TMPL_VAR NAME="firstname">!<P>
</BODY></HTML>');

[Cine]

A couple of comments on your code:

• You should use placeholders for values where possible for many reasons, including security, problems with quoting etc. That is you should change

      my $stmt = "SELECT * FROM $table WHERE $key_col = '$key_val'";
      my $sth = $dbhandle->prepare( $stmt );
      $sth->execute();
  

  into

      my $stmt = "SELECT * FROM $table WHERE $key_col = ?";
      my $sth = $dbhandle->prepare( $stmt );
      $sth->execute($key_val);
  

• You should errorcheck your database stuff. Add a || die $DBI::errstr to all your DBI->connect, $dbh->prepare and $sth->execute.
• You only need two row, so only get those. That is change:

      my $ref = $sth->fetchall_arrayref();
      $sth->finish();
      return map { $_->[$name_col] => $_->[$val_col] } @$ref;
  

  into

      my $ref = $sth->fetchall_hashref([$name_col,$val_col]);
      $sth->finish();
      return map {@$_} @$ref;
  

• print "Content-Type: text/html\n\n"; is wrong. The HTTP standard says it should be \r\n\r\n. However most browsers dont care ;)
• Dont use a & in a sub call without needing it. It overrides any prototype you may add at a later time to make perl check the number/type of params to get_hash.
• Dont overgeneralize. If you only use get_hash for this specific purpose, then fill in the tablename/colname and select only the two rows you need instead of *.


T I M T O W T D I

[jeffa]

"Add a || die $DBI::errstr to all ... "

Too much typing, especially after the code has already been typed. Just add {RaiseError => 1} to connect():

my $dbh = DBI->connect(
   "dbi:mysql:mydb", "user", "pass",
   {RaiseError => 1},
);

"Dont use a & in a sub call without needing it. It overrides any prototype you may add ..."

Perl prototypes really are not very good, IMHO. I prefer to avoid them, opting for good documentation instead. Check out (tye)Re: A question of style for some good info.

jeffa

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)

[dug]

  Warning: this reads the entire data set into memory, so be careful not to read huge amounts of data.

You can avoid this by creating other problems :)
Inside of get_hash(),

my $sth = $dbhandle->prepare( $stmt );

can become

my $sth = $dbhandle->prepare( $stmt, 
                              {"mysql_use_result" => 1} 
                             );

This will block other processes, and you will need to roll throught the complete dataset with fetchall_* or fetchrow_* and friends to avoid future errors on the handle unless you want to set up a brand new database for each query. It does seem to make sense some places, however.

  dug