I'm writing a proof-of-concept CGI script that basically implemented an event viewer page. Since the database on the server isn't ready yet, I grabbed DBD::CSV and started coding.

I got to the part where I would want to update a database entry, and I'm using placeholders in the SQL statement, like this: 
      elsif ( $action eq 'Update' )
      {
         # update the event
         my @bind = (
                      $q->param( 'title' )       || '',
                      $q->param( 'description' ) || '',
                      $q->param( 'eventdate' )   || '',
                      $q->param( 'persistent' )  || '0',
                      # $id,
                    );
                    
         # Hmmm, there appears to be a bug in here somewhere...

#        my $sth = $dbh->prepare( "UPDATE tblEvents SET title=?,descri
+ption=?,eventdate=?,persistent=? where id=?" )
         my $sth = $dbh->prepare( "UPDATE tblEvents SET title=?,descri
+ption=?,eventdate=?,persistent=? where id=$id" )
                     or die "Cannot prepare: " . $dbh->errstr();
         
         $sth->execute(@bind) or die "Cannot execute: " . $sth->errstr
+();
      }

[download]
What I have there works, but I really wanted to use a placeholder for the where clause (i.e. 'where id=?'). (The code that I have commented out would implement this). However, this doesn't work -- it writes the ID to the title field, the title to the description, the description to the eventdate field, the eventdate to the persistent field, and throws out the value provided for the persistent field.

I've noted that this same behavior can be reproduced just by supplying too many parameters to the @bind array in my example (i.e. only uncomment the $id line).

If I'm misusing the DBI interface, I'd like to know. The examples in the DBI manpage show that the where clause can have a placeholder. I suspect a bug somewhere in DBD::CSV or SQL::Statement, but I have no idea where to start looking.

Does anyone have a bit of wisdom for me?

In reply to Frustration with DBD::CSV and UPDATE by dpmott

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.