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

Question

Hi!

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.

Answer 3

The NUM_OF_PARAMS attribute may be of assistance:

"NUM_OF_PARAMS"  (integer, read-only)
     The number of parameters (placeholders) in the prepared
     statement.

So for instance, I've got a script to run arbitrary SQL from the command line that uses this code:

my $s = $h->prepare($_);

for my $i (1..$s->{NUM_OF_PARAMS}){
  my $param = shift @ARGV;
  $s->bind_param($i, $param);
  print LOG "Bind param $i using $param.\n"
    or die "can't append to $opt{log}: $!";
}

$s->execute();

I can't say I've used the other modules that have been suggested, so they may do a better job.

Answer 4

Others suggested the usual "build a query with the right number of '?'s" approach.

For most queries like this the DBI->quote method is forgotten, but it can keep the code simpler and in most cases it's not any slower than the "proper" placeholder approach.

1) Use DBI->quote instead of place holders to build the query. For example for a simple select:

my $sql = "select foo from bar where baz in ("
           . join(",", map { DBI->quote($_) } @bazs)
           . ")";
my $data = $dbh->selectall_arrayref($sql);

2) As jrockway suggested - use an ORM to do this sort of low level stuff for you. DBIx::Class or Rose::DB::Object, for example.