This ought to work (left in debug print statement)
(its more limited than the insert since where clauses can include more
than just 'and' conditionals):
use strict;
sub update_hash {
my $dbh = shift;
my $table = shift;
my $upd_ref = shift;
my $where_ref = shift;
my @upd_fields = sort keys %$upd_ref;
my @where_fields = sort keys %$where_ref;
my $upd_str = join ",", map { "$_=?" } @upd_fields;
my $where_str = join " and ", map { "$_=?" } @where_fields;
my $sql = "update $table set $upd_str where $where_str";
print $sql;
my $sth = $dbh->prepare_cached($sql_str);
$sth->execute(@upd_ref{@upd_fields},@where_ref{@where_fields});
}
update_hash(undef, 'mytable', {qw(this that here there)}, {qw(field1 one
field2
two)});
-----Original Message-----
From: Hardy Merrill [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, June 06, 2001 7:21 AM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Generic DBI "insert_hash" subroutine
Just thought I would send a bug fix on a very nice piece of code
sent by Doug Wilson - Doug's generic subroutine "insert_hash":
sub insert_hash {
my $table = shift;
my $ref = shift;
#Sort fields so we can effectively cache the statement
my @fields = sort keys %$ref;
my @values = @{$ref}{@fields};
my $sql = "insert into $table (", join(",", @fields).
") values (". join(",", ("?") x @fields).")";
my $sth = $dbh->prepare_cached($sql);
$sth->execute(@values);
}
The line that contains the "insert into $table" contains one
small but significant bug - you need to change the 1st comma
to a period(for concatenation):
From:
my $sql = "insert into $table (", join(",", @fields).
To:
my $sql = "insert into $table (". join(",", @fields).
Doug, do you have a similar "update_hash" subroutine?
Thanks.
--
Hardy Merrill
Mission Critical Linux, Inc.
http://www.missioncriticallinux.com
