On Tue, Jan 22, 2013 at 10:57:29PM +0000, Andrew Beverley wrote: > I've not been developing with Perl for long, so I'd like to know if > there is a better way of writing the following database query (or is > there a better place to ask?): > > > my @fields = qw(field1 field2 field3 field4 field5 field6 field7 ... ); > my @updates; > foreach my $field (@fields) > { > push @updates, "$field = '$hash->{$field}'" if $hash->{$field}; > } > my $values = join ',', @updates; > my $sth = $self->dbh->prepare("UPDATE table SET $values WHERE id = ?"); > $sth->execute($opdefs_id); > > > Basically, I'd like to update lots of fields in a single database row. > Obviously I could write out each updated field individually, but I > wondered whether the above is considered tidier, or whether there is a > better way altogether? The problem with the above code is that I'm not > using placeholders and bind values (which I assume is to be preferred) > so I'll also need to escape values as required. >
I'd write something like (untested): my @fields = grep {$$hash {$_}} qw [field1 field2 field3 ...]; if (@fields) { my $query = do {local $" = ", ", <<" --"}; UPDATE table SET @{[map {"$_ = ?"} @fields]} WHERE id = ? -- my $dbh = $sth -> dbh; eval { local $dbh -> {AutoCommit} = 0; local $dbh -> {RaiseError} = 1; local $dbh -> {PrintError} = 0; my $sth = $dbh -> prepare ($query); $sth -> execute (@{$hash} {@fields}, $opdefs_id); $dbh -> commit; 1; } or do { my $error = $@; eval { $dbh -> rollback; 1; } or die "Rollback failed: $@ (after error: $error)\n"; die $error, "\n"; } } Abigail