DBI UPDATE w comma in field value

FryingFinn has asked for the wisdom of the Perl Monks concerning the following question:

I am trying to write a script to update a mySQL table. I create a SELECT cmd with placeholders; I copy the updated field values into a bind array. However, one of the fields include a comma in the value. So the DBI::EXECUTE cmd thinks there is one too many values

#!/usr/bin/perl -w

my $dbh = open_admin();
my $table ="test";
my $year = "2018";

my %updates = ();

my $k =7749;
my $new = q["Boop, Betty"];  ## 'Boop, Betty';

$updates{$k}{name} = $new;
$updates{$k}{age} = 33;
foreach my $id ( keys %updates ){
    my $update = "";
    $update .= "UPDATE $table  SET ";
         my @binds = ();
    my $i = 0; 
         foreach my $fn  ( keys %{$updates{$id}} )   {
             $update .= " $fn=?, ";
             $binds[$i] =  $updates{$id}{$fn};
        $i++;
         }
    push @binds, $k;
        $update .= "WHERE course_id    =? AND year=$year";
        
    my $xbinds = join ", ", @binds;
    print "update [$udate]\n";
    print "binds -->  {$xbinds }\n";
    my $sth = $dbh->prepare($update);
    $sth->execute(@binds, $id);
}

exit;
[download]

Results in the following ...

 
-bash-4.1$ ./bbtest 
update [UPDATE test  SET  name=?,  age=?, WHERE course_id=? AND year=2
+018]
binds -->  {"Boop, Betty", 33, 7749 }
DBD::mysql::st execute failed: called with 4 bind variables when 3 are
+ needed at ./bbtest line 36.
[download]

Comment on DBI UPDATE w comma in field value Select or Download Code

Replies are listed 'Best First'.
Re: DBI UPDATE w comma in field value by Corion (Patriarch) on May 22, 2018 at 19:01 UTC
This prints three values: `my $xbinds = join ", ", @binds; print "update [$udate]\n"; print "binds --> {$xbinds }\n";` [download] But here you pass the three plus one more value, the `$id` to `->execute`: `$sth->execute(@binds, $id);` [download] So, DBI is right, you are passing four values where it only expects three.	[reply] [d/l] [select]
Re: DBI UPDATE w comma in field value by roboticus (Chancellor) on May 22, 2018 at 19:07 UTC
FryingFinn: You're passing the key twice: Line 24 you're pushing $k onto @binds, then you're explicitly adding it to the execute statement. It looks like you should just remove line 24. ...roboticus When your only tool is a hammer, all problems look like your thumb.	[reply]
Re: DBI UPDATE w comma in field value by haj (Vicar) on May 22, 2018 at 19:30 UTC
Actually, you are pushing four parameters to `prepare`: You have two (name and age) added to `@bind`, and then push `$id`, and then you pass `$id` again to the parameter list.	[reply] [d/l] [select]
Re: DBI UPDATE w comma in field value by Anonymous Monk on May 22, 2018 at 20:47 UTC
The `join()` function can be a big help in constructing SQL statements. Push the SET-parts onto one array as you push the bind-values onto the other. Then construct your SQL with something clear like: `$update = "UPDATE $table SET " . join(",", @sets) . " WHERE ...";` This will insert comma between the elements without adding an extra one on the end, and it lets you create the SQL statement itself in just one place. This will prove to be vastly easier to debug, and to modify in the future. (Notice the placement of blank spaces at the beginning/end of the string literals! Print the resulting statement to STDERR to make sure it's right.)	[reply]
Re^2: DBI UPDATE w comma in field value by Your Mother (Archbishop) on May 25, 2018 at 21:26 UTC
I also have to chime in since you're getting upvotes for your vague rehashing of ideas present in the thread already. This will prove to be vastly easier to debug, and to modify in the future String munging and concatting to generate SQL is a poor practice and makes things harder to debug, modify, and test. Any robust and secure code written to make this stuff work will end up looking a lot like SQL::Abstract so that is exactly where one should go from the beginning.	[reply]
Re^2: DBI UPDATE w comma in field value by Anonymous Monk on May 25, 2018 at 17:22 UTC
Ok, you showed how to join an array. Rocket science! Why don't you show exactly what pushing onto the two arrays would look like? And how the placeholders array would then be included in the sql statement?	[reply]