How to write a routine for DBI updates with a different number of arguments?

Question

I am writing a routine for DBI updates and have some problems figuring out how to add a placeholder and stuff ...

I have it:

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

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

Any ideas?

I just need a simple update function where I can send x the number of arguments (like a hash).

Thanks in advance!

Answer 1

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

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

Now, as an example, we will 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);

It leads to:

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

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

The best part is that we can pass it directly to the DBI:

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

, , . , :

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);

:

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

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

SQL::Abstract, CPAN. Perl Training Australia Perl, .

,

: Perl Training Australia, , .

Answer 2

- :

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;
}

:

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

, ORM DBIx::Class. , , , .

( where, , . . ., ORM?)

: , DBIx::Simple SQL::Abstract. , ORM, .

Answer 3

" "? ".

, , DBI- > quote , , "" .

1) DBI- > . , :

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

2) jrockway - ORM, . DBIx:: Class Rose:: DB:: Object, .

Answer 4

NUM_OF_PARAMS :

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

, , script SQL , :

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();

, , , .