How can I write a subroutine for DBI updates with varying number of arguments?

Question

I'm writing a subroutine for DBI updates, and are having some trouble figuring out how to add placeholder and stuff...

I have this:

sub row_update {
  my $table = shift;
  my %updates = @_;
  my $placeholders = ...

  $dbh->do("UPDATE $table SET (foo) WHERE (bar)") etc...
}

Any ideas?

I just want a simple update function where I can send in x number of arguments (as a hash).

Thanks in advance!

Answer 1

Something like this might be Good Enough:

sub update {
    my ($dbh, $args) = @_;

    my $table   = $args->{table}   || die 'need table';
    my $updates = $args->{updates} || die 'need updates';

    my @cols = keys %$updates;

    my $query = 'UPDATE $table SET '.
      (join ', ', map { '$_ = ?' } @cols)
      ($args->{where} ? ' WHERE '. $args->{where} : '');

    my $sth = $dbh->prepare($query);

    $sth->execute(map { $updates->{$_} } @cols);

    return $sth;
}

Use it like:

my $sth = update $dbh, {
    table   => 'foo',
    updates => {
        col1 => 'new_value',
        col2 => 'another_value',
    },
    where => 'id=42',
};

Really, though, you want to look into using an ORM like DBIx::Class. It will do a much better job of building queries than string manipulation like this will.

(Rewriting the where clause to be parameterized is left as an exercise to the reader. You also need to quote the update keys and table name. See why people use ORMs?)

Edit: Thinking about this a bit more, you might like DBIx::Simple combined with SQL::Abstract. This will take less configuration effort than an ORM, but still give you many of the benefits.

Answer 2

If I understand the question correctly, it sounds like you're after SQL::Abstract. First, we create an SQL::Abstract object:

use SQL::Abstract;
my $sql = SQL::Abstract->new;

Now, as an example, we'll use it to insert some data into a table:

my %record = (
    FirstName  => 'Buffy',
    LastName   => 'Summers',
    Address    => '1630 Revello Drive',
    City       => 'Sunnydale',
    State      => 'California',
    Occupation => 'Student',
    Health     => 'Alive',
);

my ($stmt, @bind) = $sql->insert(’staff’,\%record);

This results in:

$stmt = "INSERT INTO staff
                (FirstName, LastName, Address, City,
                 State, Occupation, Health)
                 VALUES (?, ?, ?, ?, ?, ?, ?)";

@bind = ('Buffy','Summers','1630 Revello Drive',
         'Sunnydale',’California','Student','Alive');

The nice thing about this is we can pass it directly to DBI:

 $dbh->do($stmt, undef, @bind);

Of course, you want to be updating records, not just inserting them. Luckily, this is also quite easy:

my $table = 'People';

my %new_fields = (
    Occupation => 'Slayer',
    Health     => 'Dead',
);

my %where = (
    FirstName => 'Buffy',
    LastName  => 'Summers',
);

my ($stmt, @bind) = $sql->update($table, \%new_fields, \%where);

$dbh->do($stmt, undef, @bind);

This produces:

$stmt = 'UPDATE People SET Health = ?, Occupation = ? 
         WHERE ( FirstName = ? AND LastName = ? )';

@bind = ('Dead', 'Slayer', 'Buffy', 'Summers');

If you're after more information about SQL::Abstract, I recommend you look at its CPAN page. There's also a chapter in Perl Training Australia's Database Programming with Perl manual, which are freely available from our course notes page.

All the best,

Paul

Disclaimer: I'm managing director of Perl Training Australia, and therefore think that our course notes are pretty good.