Re: Updating lots of database fields in a single row
On Tuesday, January 22, 2013, Philip Skinner wrote:
my $qry = $self->dbh->prepare("UPDATE table SET " . join (sort(@fields),
> '=?, ') . " WHERE id=?");
> my $affected = $qry->execute(@hash{sort(keys %hash)}, $id);
I know this is just demoing how to use hash manipulation to get two matched
lists, but I thought it might be worth pointing out to beginners that the
above code neglects error checking.
You should always check that dbh, prepare and execute all return errors and
handle them correctly. Or, alternatively turn on exception throwing when
you make the call to dbh using RaiseError:
my $dbh = DBI->connect(
"DBI:mysql:hostname=127.0.0.1;database=foo",
$username,
$password,
{ RaiseError => 1 },
);
Which is what I do in my subroutine that gets my database connection.
Updating lots of database fields in a single row
On Tuesday, January 22, 2013, Andrew Beverley wrote:
is a better way of writing the following database query
With Perl there's always more than one way to do it. Greg's post rightly
recommended ORMs and pointed out that, indeed, DBIx::Class is commonly
considered the current best of breed for general use. Phil's post pointed
out how you can use Perl's syntax to easily create two lists, one for the
SQL and one for the binds.
A middle approach that isn't as heavyweight as an ORM but isn't as tedious
as rolling your own is to use a module to generate the SQL and Binds and
then pass them to DBI.
A good choice for this would be something like SQL::Abstract.
To create some SQL and binds from a hash you can just do this:
my $sa = SQL::Abstract->new;
my ($sql, @binds) = $sa->insert('table', {
key => $value,
key2 => $value2,
});
my $sth = $dbh->prepare($sql);
$sth->execute(@binds);
See http://goo.gl/Q8Huf for more info.
Mark
(Typed on my iOS doohicky very slowly)
Re: Updating lots of database fields in a single row
In this day and age I'd be looking at an ORM[1] layer for such simple changes,
they are almost foolproof until someone is a fool ;-). And they will probably
avoid stupid SQL mistakes that you and I might both make.
And DBIx::Class[2] is the current best of breed, it can also 'reverse
engineer'[3] your existing schema to Perl modules.
G.
[1] http://en.wikipedia.org/wiki/Object-relational_mapping
[2] http://search.cpan.org/~getty/DBIx-Class-0.08204/lib/DBIx/Class.pm
[3]
http://search.cpan.org/~getty/DBIx-Class-0.08204/lib/DBIx/Class/Manual/Intro.pod#Using_DBIx::Class::Schema::Loader
On 22 Jan 2013, at 22:57, 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.
>
> Thanks,
>
> Andy
>
>
Re: Updating lots of database fields in a single row
Maybe something like:
my $qry = $self->dbh->prepare("UPDATE table SET " . join (sort(@fields),
'=?, ') . " WHERE id=?");
my $affected = $qry->execute(@hash{sort(keys %hash)}, $id);
Though I've had quite a bit to drink.
On 01/22/2013 10:57 PM, 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.
Thanks,
Andy
Updating lots of database fields in a single row
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.
Thanks,
Andy
