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
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
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.
Re: Updating lots of database fields in a single row
On 22/01/2013 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.
You should never[1] insert variable values into the query. Instead you
should always use placeholders and binding.
Re-writing your code snippet to use placeholders and binding (and fixing
the edge case where no fields need updating) gives:
my @fields = qw(field1 field2 field3 field4 field5 field6 field7 ... );
my @fields_to_update = grep { $hash->{$_} } @fields;
if (@fields_to_update) {
my $query =
'UPDATE table SET '
. join(',', map { "$_ = ?" } @fields_to_update)
. ' WHERE id = ?';
my $sth = $self->dbh->prepare($query);
# Be sure to have opened your DBI with RaiseError => 1 earlier
$sth->execute( @$hash{ @fields_to_update }, $opdefs_id );
}
However the fashionable way to do database operations in Perl is to use
an ORM such as DBIx::Class.
ORMs have some advantages, and a lot of people swear by them, though
they are not without their disadvantages.
This sort of question could be asked on stackoverflow.com.
Regards,
Bill
[1] (There are occasional cases where there is merit in inserting a
value directly into the query. But this is something of an advanced
technique to be used in those cases where the benefits outweigh the
risks. Beginners would be best advised to stick firmly to placeholders
and binding.)
Re: Updating lots of database fields in a single row
On 2013-01-23 10:27, William Blunn wrote:
my @fields_to_update = grep { $hash->{$_} } @fields;
Be aware that it skips any false value, like undef, '', '0', 0.
--
Ruud
Re: Updating lots of database fields in a single row
* Ruud H.G. van Tol (rv...@isolution.nl) [130123 09:45]:
> On 2013-01-23 10:27, William Blunn wrote:
>
> >my @fields_to_update = grep { $hash->{$_} } @fields;
>
> Be aware that it skips any false value, like undef, '', '0', 0.
my @fields_to_update = grep defined $hash->{$_}, @fields;
But that's is probably not true either: after the $hash was read
from the database, logic may have cleared some of these fields.
The clearing itself usually needs to be moved into the database
as well... undef fields need to be set to NULL.
--
Regards,
MarkOv
Mark Overmeer MScMARKOV Solutions
m...@overmeer.net soluti...@overmeer.net
http://Mark.Overmeer.net http://solutions.overmeer.net
Re: Updating lots of database fields in a single row
On 23/01/2013 09:40, Ruud H.G. van Tol wrote:
On 2013-01-23 10:27, William Blunn wrote:
my @fields_to_update = grep { $hash->{$_} } @fields;
Be aware that it skips any false value, like undef, '', '0', 0.
Absolutely.
I wouldn't usually do a straight truth test on a scalar value myself,
unless the preceding code logic meant that it contained a boolean value.
I only coded it this way to match that part of the original semantics.
I think the OP would be well advised to consider using a more explicit
test such as:
exists $hash->{$_}
Regards,
Bill
Re: Updating lots of database fields in a single row
On 23/01/2013 09:52, Mark Overmeer wrote:
* Ruud H.G. van Tol (rv...@isolution.nl) [130123 09:45]:
On 2013-01-23 10:27, William Blunn wrote:
my @fields_to_update = grep { $hash->{$_} } @fields;
Be aware that it skips any false value, like undef, '', '0', 0.
my @fields_to_update = grep defined $hash->{$_}, @fields;
But that's is probably not true either: after the $hash was read from
the database, logic may have cleared some of these fields. The
clearing itself usually needs to be moved into the database as well...
undef fields need to be set to NULL.
Using
exists $hash->{$_}
would mean that undef values in %$hash were included, which would result
in NULL being set in the corresponding database columns, which could be
a useful approach for the OP.
Regards,
Bill
Re: Updating lots of database fields in a single row
On Tue, Jan 22, 2013 at 10:57:29PM +, 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
Re: Updating lots of database fields in a single row
On 22 January 2013 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
> 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);
>
ORM solutions set aside, yep it's faster to update the whole row in
one go (unless your query becomes so big it exceeds your db query size
limit, specially if some of your fields are very long).
Something critical is missing in your code though: quoting:
Replace $field = '$hash->{$field}' with " $field =".$dbh->quote($hash->{$field})
The DBI quote method will 'do the right thing to avoid screwing up
your queries'. http://search.cpan.org/dist/DBI/DBI.pm#quote
I'm quite surprised no one mentionned it (except indirectly via
bindings). Probably an effect on over reliance on ORMs :)
J.
--
Jerome Eteve.
jerome.et...@gmail.com
Re: Updating lots of database fields in a single row
On 23/01/2013 10:21, Jérôme Étévé wrote:
Something critical is missing in your code though: quoting:
Replace $field = '$hash->{$field}' with " $field
=".$dbh->quote($hash->{$field})
The DBI quote method will 'do the right thing to avoid screwing up
your queries'. http://search.cpan.org/dist/DBI/DBI.pm#quote
We shouldn't be doing anything to encourage people to include variable
values directly into queries.
If we feel we must mention quoting helper methods, this should be
clearly qualified with words to the effect that including variable
values directly into queries is considered poor practice, and best
practice is to use placeholders and bindings.
Regards,
Bill
Re: Updating lots of database fields in a single row
On Wed, Jan 23, 2013 at 10:53:16AM +, William Blunn wrote:
> On 23/01/2013 10:21, Jérôme Étévé wrote:
>> Something critical is missing in your code though: quoting:
>>
>> Replace $field = '$hash->{$field}' with " $field
>> =".$dbh->quote($hash->{$field})
>>
>> The DBI quote method will 'do the right thing to avoid screwing up
>> your queries'. http://search.cpan.org/dist/DBI/DBI.pm#quote
>
> We shouldn't be doing anything to encourage people to include variable
> values directly into queries.
>
> If we feel we must mention quoting helper methods, this should be
> clearly qualified with words to the effect that including variable
> values directly into queries is considered poor practice, and best
> practice is to use placeholders and bindings.
I'd say that dogmas are poor practise.
Good practise is actually *knowing* when you should use placeholders,
and when there's no need.
Because someone who knows can actually be trusted to do variable
interpolation in places where placeholders cannot be used. Unlike
someone who goes "variable interpolation is baaad".
Abigail
Re: Updating lots of database fields in a single row
On 23/01/2013 11:09, Abigail wrote:
On Wed, Jan 23, 2013 at 10:53:16AM +, William Blunn wrote:
On 23/01/2013 10:21, Jérôme Étévé wrote:
Something critical is missing in your code though: quoting:
Replace $field = '$hash->{$field}' with " $field
=".$dbh->quote($hash->{$field})
The DBI quote method will 'do the right thing to avoid screwing up
your queries'. http://search.cpan.org/dist/DBI/DBI.pm#quote
We shouldn't be doing anything to encourage people to include variable
values directly into queries.
If we feel we must mention quoting helper methods, this should be
clearly qualified with words to the effect that including variable
values directly into queries is considered poor practice, and best
practice is to use placeholders and bindings.
I'd say that dogmas are poor practise.
Good practise is actually *knowing* when you should use placeholders,
and when there's no need.
Because someone who knows can actually be trusted to do variable
interpolation in places where placeholders cannot be used. Unlike
someone who goes "variable interpolation is baaad".
A person asks a question. We give an answer. We can only say so much. A
person can only take on board so much. So we have to make that answer as
good as possible in the limited scope available.
Including values in the SQL is prone to error in a way which
placeholders and binding isn't, especially for beginners.
You can't make an experienced Perl developer overnight. People need to
learn things a bit at a time.
If people are to learn about the two approaches of (a) placeholders and
binding, and (b) including values directly into the SQL; then I think
it's best to teach placeholders and binding first.
If people never get around to learning the other approach, then perhaps
their code won't be as ideal as it might otherwise have been. But at
least their software won't suffer from the problems of interpolation.
So I say we should actively encourage placeholders and binding for
people new to Perl and/or DBI programming. If people need to use
interpolation, then they'll figure that out for themselves as they get
more experience.
(Even if some approach might be "best" in some sense in a particular
piece of code, there is still the question of how that code may be read
by more junior developers and be used as a template for other pieces of
code.)
Regards,
Bill
Re: Updating lots of database fields in a single row
> On 3//1//013 0::1,, J?r?me ?t?v? wrote:
> > Something critical is missing in your code though: quoting:
> >
> > Replace $field = '$hash->{$field}' with " $field
> > =".$dbh->quote($hash->{$field})
This would assume all fields were strings. To do it properly, you would need to
have the metadata available and do:
$dbh->quote($hash->{$field}, $data_type)
You may also have to worry about $hash->{$field} containing SQL injection
stuff. So bind parameters are potentially safer.
For Oracle, bind variable sometimes also offer performance benefits as the
query plan is more cacheable. But be aware, this is at the price of losing
specific statistical information about values in the where clause which will
mean the optimiser has less information to look at it's histograms with. For
specific queries, it may be faster to use actual values. e.g.:
select * from sometable where column_A = ?
If column_A is indexed and has 90% of rows with value 1 and has 1000 other well
distributed values for the other 10% of rows: the best query plan will be
different for value 1 versus other values.
The other reason I tend not to use bind parameters is it makes abstracting code
harder to do. If you want to write a function that does something like:
populate_excel_tab_with_sql($excel_handle, $sql)
It's easier if you don't have to worry about bind variables. Obviously there
are ways around this but the way I generate some SQL makes it easier to go with
using values.
Chris
Re: Updating lots of database fields in a single row
On Tue, 2013-01-22 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?): Thanks for the many excellent replies. I'll have a play around with them at the weekend and make my decision... It's certainly true that TMTOWTDI! Andy
Re: Updating lots of database fields in a single row
On 23 Jan 2013, at 11:09, Abigail wrote:
> I'd say that dogmas are poor practise.
>
>
> Good practise is actually *knowing* when you should use placeholders,
> and when there's no need.
>
> Because someone who knows can actually be trusted to do variable
> interpolation in places where placeholders cannot be used. Unlike
> someone who goes "variable interpolation is baaad".
If you work in an environment where some of the developers aren't rock stars, a
general rule of "doesn't use placeholders in database queries == code smell"
may well be useful. My inclination would be to say that using placeholders in
database queries is at a similar level to mandating use strict in all but
one-liner or terrifyingly ancient code.
More interesting, though: aren't bind variables also more convenient in many
circumstances?
I mean, sure, this is safe:
if ($status eq 'foo') {
$dbh->do("UPDATE table SET status='$status' WHERE id=$id");
}
But I think I'd often find myself using them even if I didn't need to, just
because of the printf-type convenience.
For the same reason that I'll shift to printf or sprintf for complex log
messages:
warning(sprintf('Found grommet %s instead of expected grommet %s: %s was %s
(%.2f)',
$grommet, $expected_grommet, $category_thing, $category_value,
$some_other_diagnostic
));
rather than
warning("Found grommet $grommet instead of expected grommet $expected_grommet"
." $category_thing was $category_value (" .
printable_value($some_other_diagnostic) . ")");
I think the first version is more readable than the second, especially so when
you start involving more complex variables.
Similarly, saying
$dbh->do('UPDATE table SET foo=?, bar=?, baz=? WHERE toto=? AND tata=?',
...);
is IMO more readable that intermingling SQL and Perl variables.
Sam
--
Website: http://www.illuminated.co.uk/
Re: Updating lots of database fields in a single row
On 24/01/2013 03:01, Sam Kington wrote:
I mean, sure, this is safe:
if ($status eq 'foo') {
$dbh->do("UPDATE table SET status='$status' WHERE id=$id");
}
Only if you're certain you know what $status and $id contain.
http://xkcd.com/327/
Re: Updating lots of database fields in a single row
You do know what $status contains in the example (so you could hardcore it in
the SQL anyway). $id is up for grabs though.
Simon Wilcox wrote:
>On 24/01/2013 03:01, Sam Kington wrote:
>> I mean, sure, this is safe:
>>
>> if ($status eq 'foo') {
>> $dbh->do("UPDATE table SET status='$status' WHERE id=$id");
>> }
>
>Only if you're certain you know what $status and $id contain.
>
>http://xkcd.com/327/
--
Sent from my mobile phone. Please excuse terseness, typos and top-posting.
Re: Updating lots of database fields in a single row
Tied variables ;-)
G.
On 24 Jan 2013, at 13:57, Denny wrote:
> You do know what $status contains in the example (so you could hardcore it in
> the SQL anyway). $id is up for grabs though.
>
>
>
> Simon Wilcox wrote:
>
>> On 24/01/2013 03:01, Sam Kington wrote:
>>> I mean, sure, this is safe:
>>>
>>> if ($status eq 'foo') {
>>> $dbh->do("UPDATE table SET status='$status' WHERE id=$id");
>>> }
>>
>> Only if you're certain you know what $status and $id contain.
>>
>> http://xkcd.com/327/
>
> --
> Sent from my mobile phone. Please excuse terseness, typos and top-posting.
Re: Updating lots of database fields in a single row
On Thu, Jan 24, 2013 at 03:38:08PM +, Greg McCarroll wrote: > > Tied variables ;-) Overloaded constants, and not even your place holders are safe. Abigail
Re: Updating lots of database fields in a single row
On Thu, Jan 24, 2013 at 04:49:17PM +0100, Abigail wrote: > On Thu, Jan 24, 2013 at 03:38:08PM +, Greg McCarroll wrote: > > Tied variables ;-) > Overloaded constants, and not even your place holders are safe. Stupid language. Let's all use C instead. -- David Cantrell | Cake Smuggler Extraordinaire Did you know that shotguns taste like candy canes? Put the barrel in your mouth and pull the trigger for an extra blast of minty goodness!
Re: Updating lots of database fields in a single row
Why C? Assembly is so much more concise. On Thu, Jan 24, 2013 at 11:00 AM, David Cantrell wrote: > On Thu, Jan 24, 2013 at 04:49:17PM +0100, Abigail wrote: >> On Thu, Jan 24, 2013 at 03:38:08PM +, Greg McCarroll wrote: >> > Tied variables ;-) >> Overloaded constants, and not even your place holders are safe. > > Stupid language. Let's all use C instead. > > -- > David Cantrell | Cake Smuggler Extraordinaire > > Did you know that shotguns taste like candy canes? Put the barrel in > your mouth and pull the trigger for an extra blast of minty goodness!
Re: Updating lots of database fields in a single row
On 24/01/13 16:00, David Cantrell wrote: On Thu, Jan 24, 2013 at 04:49:17PM +0100, Abigail wrote: On Thu, Jan 24, 2013 at 03:38:08PM +, Greg McCarroll wrote: Tied variables ;-) Overloaded constants, and not even your place holders are safe. Stupid language. Let's all use C instead. No, no. Let's use PLZ. Dirk
Re: Updating lots of database fields in a single row
No no no, lets just use any language that our process analyst consultant decides - they can come up with a long winded approach to software development that will ensure the lack of any possible security holes by providing long winded documentation to auditors and by selecting whatever language/toolset was cool about 10 years ago. G. On 24 Jan 2013, at 16:00, David Cantrell wrote: > On Thu, Jan 24, 2013 at 04:49:17PM +0100, Abigail wrote: >> On Thu, Jan 24, 2013 at 03:38:08PM +, Greg McCarroll wrote: >>> Tied variables ;-) >> Overloaded constants, and not even your place holders are safe. > > Stupid language. Let's all use C instead. > > -- > David Cantrell | Cake Smuggler Extraordinaire > > Did you know that shotguns taste like candy canes? Put the barrel in > your mouth and pull the trigger for an extra blast of minty goodness!
Re: Updating lots of database fields in a single row
On Jan 24, 2013 8:48 AM, "Greg McCarroll" wrote: > > > > No no no, lets just use any language that our process analyst consultant decides - they can come up with a long winded approach to software development that will ensure the lack of any possible security holes by providing long winded documentation to auditors and by selecting whatever language/toolset was cool about 10 years ago. So we're using Perl still then? :D P > > G. >
Re: Updating lots of database fields in a single row
On 24 Jan 2013, at 17:15, Paul Makepeace wrote: So we're using Perl still then? :D Perl was never cool, it was the kid that liked wearing a sports coat to school with leather patches sewn on the elbows as they took style tips from their 'cool' maths teacher. G.
Re: Updating lots of database fields in a single row
On Jan 24, 2013 7:32 PM, "Greg McCarroll" wrote: > Perl was never cool, it was the kid that liked wearing a sports coat > to school with leather patches sewn on the elbows > as they took style tips from their 'cool' maths teacher. Oi, I resemble that remark! /me wonders what happened to that coat jonathan
Re: Updating lots of database fields in a single row
On 22/01/2013 22:57, Andrew Beverley wrote: Basically, I'd like to update lots of fields in a single database row. From the subject line I thought you were going to ask how to update lots of fields in lots of rows in a single query... Considering a table "table" with primary key columns "pkc1" and "pkc2", and non-key columns "f1" and "f2", in PostgreSQL you can update multiple rows with different values in a single query using: UPDATE table SET f1 = data.f1, f2 = data.f2 FROM ( VALUES ($r1pkc1, $r1pkc2, $r1f1, $r1f2), ($r2pkc1, $r2pkc2, $r2f1, $r2f2), ($r3pkc1, $r3pkc2, $r3f1, $r3f2), ... ($rnpkc1, $rnpkc2, $rnf1, $rnf2) ) data (pkc1, pkc2, f1, f2) WHERE table.pkc1 = data.pkc1 AND table.pkc2 = data.pkc2; Regards, Bill
Etiquette [was: Re: Updating lots of database fields in a single row]
Be nice as this list has more to offer than silly pounding on people that release things onto the CPAN or send unclear emails. A person asks a question. We give an answer. We can only say so much. A person can only take on board so much. So we have to make that answer as good as possible in the limited scope available. I'm proposing Gordon's law, somewhat akin to Godwin's: As a discussion in the London.pm list grows longer, the probability of it turning into a meta-discussion of what is (not) an acceptable posting approaches 1. It's great that we're all so keen not to put off newbies, not to allow time-wasting, etc, but my word is this list self-conscious! ;-) Gordon
