Re: Advice or best practice for updating multiple rows with a single query
Peter Meszaros <[email protected]> Mon, 28 Aug 2023 12:09:13 +0200
| Newsgroups | gmane.comp.lang.perl.modules.dbi.general |
|---|---|
| Message-ID | <CABMJSUNdRdp9hkvzTrt2ZqDU_QkBU_Tc9bDWG_E=U9Q=FsR5pQ@mail.gmail.com> |
--00000000000092ca690603f8e12b
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
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 =3D shift;
my (@cust_id, @fw_id);
for (values $data->%*) {
push @cust_id, $_->{cust_id};
push @fw_id, $_->{fw_id};
}
my $dbh =3D DBI->connect("dbi:Pg:dbname=3Dtest", '', '', {RaiseError =
=3D> 1});
my $sql =3D 'update condats set fw_id =3D ? where cust_id =3D ?';
my $sth =3D $dbh->prepare($sql);
my $tuples =3D $sth->execute_array(
{ ArrayTupleStatus =3D> \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 =3D $tuple_status[$tuple];
$status =3D [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=E2=80=AFAM Ritter <[email protected]> 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 th=
e
> 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-ro=
ws-one-query-part1.php)
> I tried to map it with the following code.
>
> =E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=
=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=
=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=
code =E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=
=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=
=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=
=94
>
> #!/usr/bin/perl
>
> use strict;
> use warnings;
> use feature qw(say);
> use DBI();
> use Data::Dumper;
>
> use Data::Printer {
> color =3D> {
> 'regex' =3D> 'yellow',
> 'hash' =3D> 'blue',
> 'string' =3D> 'cyan',
> 'array' =3D> 'green'
> },
> };
>
> my $href =3D {
> FOO =3D> {
> Company =3D> "Foo Ltd.",
> fw_id =3D> 11111,
> cust_id =3D> 1001,
> vpn_pri_ipv4 =3D> "192.168.1.1",
> vpn_sec_ipv4 =3D> undef
> },
> BAR =3D> {
> Company =3D> "Bar Ltd.",
> fw_id =3D> 22222,
> cust_id =3D> 1234,
> vpn_pri_ipv4 =3D> "172.16.1.1",
> vpn_sec_ipv4 =3D> undef
> },
> BAZ =3D> {
> Company =3D> "Baz Ltd.",
> fw_id =3D> 33333,
> cust_id =3D> 4321,
> vpn_pri_ipv4 =3D> "10.1.1.1",
> vpn_sec_ipv4 =3D> 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 =3D shift;
>
> my $db_values_to_be_updated;
> foreach (keys %{$data}) {
> $db_values_to_be_updated->{$data->{$_}{cust_id}} =3D $data->{$_}{fw_=
id};
> };
>
> my $when_clause =3D join"\n\t", map { "when ? then ?" } (keys
> %{$db_values_to_be_updated});
> my @placeholders =3D ( 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 =3D "
> UPDATE condats SET
> fw_obj_id =3D
> 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 =3D
> DBI->connect("DBI:mysql:database=3Ddev;host=3D192.168.200.100","User","Pa=
ssword",{'RaiseError'
> =3D> 1});
> $dbh->do("set character set latin1");
> $dbh->do("set names latin1");
>
> my $sql =3D <<"EOF_INPUT";
> UPDATE condats SET
> fw_obj_id =3D
> 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 =3D 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 =3D (keys %{$db_values_to_be_updated});
> print "(2nd) data set passed as 'keys \%{\$db_values_to_be_updated}' t=
o
> \$sth->execute():\n", Dumper(\@y);
>
> my $sth =3D $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();
> }
>
> =E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=
=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=
=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=
=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=
=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=
=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94
>
> What I don't like is the awkward way of dynamically creating the
> placeholders for "$sth =3D $dbh->prepare($sql)" and how the data is passe=
d to
> $sth->execute(). There is one part =E2=80=9Efeeding" the values for CASE =
and
> another part =E2=80=9Efeeding=E2=80=9C the values for "WHERE condats.cust=
_id IN
> ('1001','1234','4321=E2=80=99)=E2=80=9C.
>
> 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 da=
ta
> 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
--00000000000092ca690603f8e12b
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
<div dir=3D"ltr"><div><span style=3D"font-family:arial,sans-serif">Hi,</spa=
n></div><div><span style=3D"font-family:arial,sans-serif"><br></span></div>=
<div><span style=3D"font-family:arial,sans-serif">Another opportunity is to=
use execute_array() instead of execute(). Fore details search for 'exe=
cute_array' in 'perldoc DBI'.</span></div><div><span style=3D"f=
ont-family:arial,sans-serif">I use Postgresql in the example below, because=
I have no MySQL.<br></span></div><div><span style=3D"font-family:arial,san=
s-serif"><br></span></div><div><font size=3D"2"><span style=3D"font-family:=
monospace">sub db_update_with_fw_obj_ids {<br>=C2=A0 =C2=A0 my $data =3D sh=
ift;<br>=C2=A0 =C2=A0 my (@cust_id, @fw_id);<br><br>=C2=A0 =C2=A0 for (valu=
es $data->%*) {<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 push @cust_id, $_->{cu=
st_id};<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 push @fw_id, =C2=A0 $_->{fw_id};<=
br>=C2=A0 =C2=A0 }<br><br>=C2=A0 =C2=A0 my $dbh =3D DBI->connect("d=
bi:Pg:dbname=3Dtest", '', '', {RaiseError =3D> 1});=
<br>=C2=A0 =C2=A0 my $sql =3D 'update condats set fw_id =3D ? where cus=
t_id =3D ?';<br>=C2=A0 =C2=A0 my $sth =3D $dbh->prepare($sql);<br>=
=C2=A0 =C2=A0 my $tuples =3D $sth->execute_array(<br>=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 { ArrayTupleStatus =3D> \my @tuple_status },<br>=C2=A0 =C2=A0=
=C2=A0 =C2=A0 \@fw_id,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 \@cust_id,<br>=C2=A0=
=C2=A0 );<br>=C2=A0 =C2=A0 if ($tuples) {<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 p=
rint "Successfully updated $tuples records\n";<br>=C2=A0 =C2=A0 =
=C2=A0 =C2=A0 #print "@cust_id\n";<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0=
#print "@fw_id\n";<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 #print "@=
tuple_status\n";<br>=C2=A0 =C2=A0 } else {<br>=C2=A0 =C2=A0 =C2=A0 =C2=
=A0 for my $tuple (0..@cust_id-1) {<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 my $status =3D $tuple_status[$tuple];<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0=
=C2=A0 =C2=A0 $status =3D [0, "Skipped"] unless defined $status;=
<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 next unless ref $status;<br>=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 printf "Failed to update (%s=
, %s): %s\n",<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 $cust_id[$t=
uple], $fw_id[$tuple], $status->[1];<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 }<br=
>=C2=A0 =C2=A0 }<br><br>=C2=A0 =C2=A0 $sth->finish();<br>=C2=A0 =C2=A0 $=
dbh->disconnect();<br>}<br></span></font></div><div><br></div><div>Regar=
ds<br></div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"=
gmail_attr">On Mon, Aug 28, 2023 at 8:58=E2=80=AFAM Ritter <<a href=3D"m=
ailto:[email protected]">[email protected]</a>> wrote:<br></div><blo=
ckquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left=
:1px solid rgb(204,204,204);padding-left:1ex">Dear Mongers,<br>
<br>
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 placeh=
olders and the data need to be generated dynamically with each call.<br>
<br>
Based on the following example (<a href=3D"https://www.geeksengine.com/data=
base/data-manipulation/update-multiple-rows-one-query-part1.php" rel=3D"nor=
eferrer" target=3D"_blank">https://www.geeksengine.com/database/data-manipu=
lation/update-multiple-rows-one-query-part1.php</a>) I tried to map it with=
the following code.<br>
<br>
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=
=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=
=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94 co=
de =E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=
=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=
=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=
<br>
<br>
#!/usr/bin/perl<br>
<br>
use strict;<br>
use warnings;<br>
use feature qw(say);<br>
use DBI();<br>
use Data::Dumper;<br>
<br>
use Data::Printer {<br>
=C2=A0 color =3D> {<br>
=C2=A0 =C2=A0 'regex'=C2=A0 =3D> 'yellow',<br>
=C2=A0 =C2=A0 'hash'=C2=A0 =C2=A0=3D> 'blue',<br>
=C2=A0 =C2=A0 'string' =3D> 'cyan',<br>
=C2=A0 =C2=A0 'array'=C2=A0 =3D> 'green'<br>
=C2=A0 },<br>
};<br>
<br>
my $href =3D {<br>
=C2=A0 =C2=A0 FOO =3D>=C2=A0 {<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Company =3D> "Foo Ltd.&quo=
t;,<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0fw_id =3D> 11111,<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0cust_id =3D> 1001,<br>
=C2=A0 =C2=A0 =C2=A0 vpn_pri_ipv4 =3D> "192.168.1.1",<br>
=C2=A0 =C2=A0 =C2=A0 vpn_sec_ipv4 =3D> undef<br>
=C2=A0 =C2=A0 },<br>
=C2=A0 =C2=A0 BAR =3D> {<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Company =3D> "Bar Ltd.&quo=
t;,<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0fw_id =3D> 22222,<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0cust_id =3D> 1234,<br>
=C2=A0 =C2=A0 =C2=A0 vpn_pri_ipv4 =3D> "172.16.1.1",<br>
=C2=A0 =C2=A0 =C2=A0 vpn_sec_ipv4 =3D>=C2=A0 undef<br>
=C2=A0 =C2=A0 },<br>
=C2=A0 =C2=A0 BAZ =3D> {<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Company =3D> "Baz Ltd.&quo=
t;,<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0fw_id =3D> 33333,<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0cust_id =3D> 4321,<br>
=C2=A0 =C2=A0 =C2=A0 vpn_pri_ipv4 =3D> "10.1.1.1",<br>
=C2=A0 =C2=A0 =C2=A0 vpn_sec_ipv4 =3D>=C2=A0 undef<br>
=C2=A0 =C2=A0 }<br>
};<br>
<br>
#delete $href->{BAZ};<br>
#delete @{$href}{'BAR', 'BAZ'};<br>
#delete @{$href}{qw/BAR BAZ/};<br>
<br>
db_update_with_fw_obj_ids($href); <br>
<br>
sub db_update_with_fw_obj_ids {<br>
<br>
=C2=A0 =C2=A0my $data =3D shift;<br>
<br>
=C2=A0 =C2=A0my $db_values_to_be_updated;<br>
=C2=A0 =C2=A0foreach (keys %{$data}) {<br>
=C2=A0 =C2=A0 =C2=A0$db_values_to_be_updated->{$data->{$_}{cust_id}} =
=3D $data->{$_}{fw_id};<br>
=C2=A0 =C2=A0};<br>
<br>
=C2=A0 =C2=A0my $when_clause =3D join"\n\t", map { "when ? t=
hen ?" } (keys %{$db_values_to_be_updated});<br>
=C2=A0 =C2=A0my @placeholders =3D ( join',', ('?') x (keys =
%{$db_values_to_be_updated}));<br>
<br>
=C2=A0 =C2=A0print "\nWhen-clause for \$dbh->prepare(\$sql): \n&quo=
t;, $when_clause, "\n";<br>
=C2=A0 =C2=A0print "#"x80, "\n";<br>
=C2=A0 =C2=A0print "\nPlaceholders for \$dbh->prepare(\$sql): "=
;, @placeholders, "\n";<br>
<br>
=C2=A0 =C2=A0print "#"x80, "\n";<br>
=C2=A0 =C2=A0my $example =3D "<br>
=C2=A0 =C2=A0UPDATE condats SET <br>
=C2=A0 =C2=A0 =C2=A0 fw_obj_id =3D <br>
=C2=A0 =C2=A0 =C2=A0 CASE condats.cust_id<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 when '1001' then '111111'=
;<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 when '1234' then '222222'=
;<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 when '4321' then '333333'=
;<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ELSE fw_obj_id<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0END<br>
=C2=A0 =C2=A0 WHERE condats.cust_id IN ('1001','1234','=
4321');<br>
=C2=A0 =C2=A0 ";<br>
<br>
=C2=A0 =C2=A0my $dbh =3D DBI->connect("DBI:mysql:database=3Ddev;hos=
t=3D192.168.200.100","User","Password",{'Raise=
Error' =3D> 1});<br>
=C2=A0 =C2=A0$dbh->do("set character set latin1");<br>
=C2=A0 =C2=A0$dbh->do("set names latin1");<br>
<br>
=C2=A0 =C2=A0my $sql =3D <<"EOF_INPUT";<br>
UPDATE condats SET <br>
=C2=A0 =C2=A0 fw_obj_id =3D <br>
=C2=A0 =C2=A0 CASE condats.cust_id<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 $when_clause<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 ELSE fw_obj_id<br>
=C2=A0 =C2=A0 END<br>
WHERE condats.cust_id IN (@placeholders);<br>
EOF_INPUT<br>
<br>
=C2=A0 =C2=A0print "\n", "#"x30, " SQL query with =
example data ", "#"x30, "\n";<br>
=C2=A0 =C2=A0p $example;<br>
=C2=A0 =C2=A0print "#"x28, " SQL query with dbi placeholders=
", "#"x28, "\n";<br>
=C2=A0 =C2=A0p $sql;<br>
<br>
=C2=A0 =C2=A0print "#"x125, "\n";<br>
=C2=A0 =C2=A0my @x =3D map { $_, $db_values_to_be_updated->{$_} } keys %=
{$db_values_to_be_updated}; <br>
=C2=A0 =C2=A0print "(1st) data set passed as 'map { \$_, \$db_valu=
es_to_be_updated->{\$_} } keys \%{\$db_values_to_be_updated}' to \$s=
th->execute():\n", Dumper(\@x); <br>
<br>
=C2=A0 =C2=A0print "#"x125, "\n";<br>
=C2=A0 =C2=A0my @y =3D (keys %{$db_values_to_be_updated});<br>
=C2=A0 =C2=A0print "(2nd) data set passed as 'keys \%{\$db_values_=
to_be_updated}' to \$sth->execute():\n", Dumper(\@y);<br>
<br>
=C2=A0 =C2=A0my $sth =3D $dbh->prepare($sql);<br>
=C2=A0 =C2=A0$sth->execute((map { $_, $db_values_to_be_updated->{$_} =
} (keys %{$db_values_to_be_updated})), keys %{$db_values_to_be_updated}) or=
die $DBI::errstr;<br>
=C2=A0 =C2=A0print "Number of rows updated :", $sth->rows, &qu=
ot;\n";<br>
=C2=A0 =C2=A0$sth->finish();<br>
=C2=A0 =C2=A0$dbh->disconnect();<br>
}<br>
<br>
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=
=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=
=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=
=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=
=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=
=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=
=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=
=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94<br>
<br>
What I don't like is the awkward way of dynamically creating the placeh=
olders for "$sth =3D $dbh->prepare($sql)" and how the data is =
passed to $sth->execute(). There is one part =E2=80=9Efeeding" the =
values for CASE and another part =E2=80=9Efeeding=E2=80=9C the values for &=
quot;WHERE condats.cust_id IN ('1001','1234','4321=E2=
=80=99)=E2=80=9C.<br>
<br>
Possibly only a "smarter" data structure is needed, from which DB=
I on the one hand takes the data for the CASE part and from another part of=
the data structure data for the WHERE clause?<br>
<br>
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.<br>
<br>
Any recommendations/best practices?<br>
<br>
Any advice would be be highly appreciated.<br>
<br>
Cheers,<br>
<br>
Ritter</blockquote></div>
--00000000000092ca690603f8e12b--