Hi,
Another opportunity is to use execute_array() instead of execute(). Fore
details search for 'execute_array' in 'perldoc DBI'.
I use Postgresql in the example below, because I have no MySQL.
sub db_update_with_fw_obj_ids {
my $data = shift;
my (@cust_id, @fw_id);
for (values $data->%*) {
push @cust_id, $_->{cust_id};
push @fw_id, $_->{fw_id};
}
my $dbh = DBI->connect("dbi:Pg:dbname=test", '', '', {RaiseError => 1});
my $sql = 'update condats set fw_id = ? where cust_id = ?';
my $sth = $dbh->prepare($sql);
my $tuples = $sth->execute_array(
{ ArrayTupleStatus => \my @tuple_status },
\@fw_id,
\@cust_id,
);
if ($tuples) {
print "Successfully updated $tuples records\n";
#print "@cust_id\n";
#print "@fw_id\n";
#print "@tuple_status\n";
} else {
for my $tuple (0..@cust_id-1) {
my $status = $tuple_status[$tuple];
$status = [0, "Skipped"] unless defined $status;
next unless ref $status;
printf "Failed to update (%s, %s): %s\n",
$cust_id[$tuple], $fw_id[$tuple], $status->[1];
}
}
$sth->finish();
$dbh->disconnect();
}
Regards
On Mon, Aug 28, 2023 at 8:58 AM Ritter <li...@netzritter.de> wrote:
> Dear Mongers,
>
> here is my example code trying to update multiple rows with a single
> query. Since the number of lines to be updated varies constantly, both the
> placeholders and the data need to be generated dynamically with each call.
>
> Based on the following example (
> https://www.geeksengine.com/database/data-manipulation/update-multiple-rows-one-query-part1.php)
> I tried to map it with the following code.
>
> ————————————————————————————————— code —————————————————————————————————
>
> #!/usr/bin/perl
>
> use strict;
> use warnings;
> use feature qw(say);
> use DBI();
> use Data::Dumper;
>
> use Data::Printer {
> color => {
> 'regex' => 'yellow',
> 'hash' => 'blue',
> 'string' => 'cyan',
> 'array' => 'green'
> },
> };
>
> my $href = {
> FOO => {
> Company => "Foo Ltd.",
> fw_id => 11111,
> cust_id => 1001,
> vpn_pri_ipv4 => "192.168.1.1",
> vpn_sec_ipv4 => undef
> },
> BAR => {
> Company => "Bar Ltd.",
> fw_id => 22222,
> cust_id => 1234,
> vpn_pri_ipv4 => "172.16.1.1",
> vpn_sec_ipv4 => undef
> },
> BAZ => {
> Company => "Baz Ltd.",
> fw_id => 33333,
> cust_id => 4321,
> vpn_pri_ipv4 => "10.1.1.1",
> vpn_sec_ipv4 => undef
> }
> };
>
> #delete $href->{BAZ};
> #delete @{$href}{'BAR', 'BAZ'};
> #delete @{$href}{qw/BAR BAZ/};
>
> db_update_with_fw_obj_ids($href);
>
> sub db_update_with_fw_obj_ids {
>
> my $data = shift;
>
> my $db_values_to_be_updated;
> foreach (keys %{$data}) {
> $db_values_to_be_updated->{$data->{$_}{cust_id}} = $data->{$_}{fw_id};
> };
>
> my $when_clause = join"\n\t", map { "when ? then ?" } (keys
> %{$db_values_to_be_updated});
> my @placeholders = ( join',', ('?') x (keys
> %{$db_values_to_be_updated}));
>
> print "\nWhen-clause for \$dbh->prepare(\$sql): \n", $when_clause, "\n";
> print "#"x80, "\n";
> print "\nPlaceholders for \$dbh->prepare(\$sql): ", @placeholders, "\n";
>
> print "#"x80, "\n";
> my $example = "
> UPDATE condats SET
> fw_obj_id =
> CASE condats.cust_id
> when '1001' then '111111'
> when '1234' then '222222'
> when '4321' then '333333'
> ELSE fw_obj_id
> END
> WHERE condats.cust_id IN ('1001','1234','4321');
> ";
>
> my $dbh =
> DBI->connect("DBI:mysql:database=dev;host=192.168.200.100","User","Password",{'RaiseError'
> => 1});
> $dbh->do("set character set latin1");
> $dbh->do("set names latin1");
>
> my $sql = <<"EOF_INPUT";
> UPDATE condats SET
> fw_obj_id =
> CASE condats.cust_id
> $when_clause
> ELSE fw_obj_id
> END
> WHERE condats.cust_id IN (@placeholders);
> EOF_INPUT
>
> print "\n", "#"x30, " SQL query with example data ", "#"x30, "\n";
> p $example;
> print "#"x28, " SQL query with dbi placeholders ", "#"x28, "\n";
> p $sql;
>
> print "#"x125, "\n";
> my @x = map { $_, $db_values_to_be_updated->{$_} } keys
> %{$db_values_to_be_updated};
> print "(1st) data set passed as 'map { \$_,
> \$db_values_to_be_updated->{\$_} } keys \%{\$db_values_to_be_updated}' to
> \$sth->execute():\n", Dumper(\@x);
>
> print "#"x125, "\n";
> my @y = (keys %{$db_values_to_be_updated});
> print "(2nd) data set passed as 'keys \%{\$db_values_to_be_updated}' to
> \$sth->execute():\n", Dumper(\@y);
>
> my $sth = $dbh->prepare($sql);
> $sth->execute((map { $_, $db_values_to_be_updated->{$_} } (keys
> %{$db_values_to_be_updated})), keys %{$db_values_to_be_updated}) or die
> $DBI::errstr;
> print "Number of rows updated :", $sth->rows, "\n";
> $sth->finish();
> $dbh->disconnect();
> }
>
> ———————————————————————————————————————————————————————————————————————
>
> What I don't like is the awkward way of dynamically creating the
> placeholders for "$sth = $dbh->prepare($sql)" and how the data is passed to
> $sth->execute(). There is one part „feeding" the values for CASE and
> another part „feeding“ the values for "WHERE condats.cust_id IN
> ('1001','1234','4321’)“.
>
> Possibly only a "smarter" data structure is needed, from which DBI on the
> one hand takes the data for the CASE part and from another part of the data
> structure data for the WHERE clause?
>
> I wonder if there is a smarter way to create the DBI-placeholder and how
> to hand over the data to $sth->execute() in one shot.
>
> Any recommendations/best practices?
>
> Any advice would be be highly appreciated.
>
> Cheers,
>
> Ritter
