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]

In reply to DBI UPDATE w comma in field value by FryingFinn

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.