StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POperl/dbi/sql stumped by "operation must use an updatable query" error
text
Body
copied!<p>I've a Perl script that builds up an sql cmd to set certain fields to NULL in a certain table in an MS Access db ( sorry). Here's a simplified mockup.</p> <pre><code>my $nonKeyFields_hashref = { "country" => "ZZZ", "address3" => "FOO" }; my $keyFields_hashref = { "address1" => "1212 O'Mally Street", # embedded single quote here is causing the problem "client ID" => "1234567" }; my $sqlCmd = "UPDATE myTable SET "; $sqlCmd .= join( ", " , map{ "[?} = NULL "} keys $nonKeyFields_hashref; $sqlCmd .= " WHERE "; $sqlCmd .= join( " AND " , map{ "[?} = ? "} keys $keyFields_hashref; # sqlCmd contains "UPDATE myTable SET [?] = NULL, [?} = NULL WHERE [?] = ? AND [?] = ?" $sth = $dbh->prepare( $sqlCmd); if( !defined( $sth)) { _pushErrorMsg("sth failed to define - ".$DBI::errstr); $errorHit = 1; } else { my @cmd_arry = (); push( @cmd_arry, $_ ) for keys $nonKeyFields_hashref; push( @cmd_arry, $_ , $keyFields_hashref->{$_} ) for keys $keyFields_hashref; print Dumper( @cmd_arry); # dumper shows @cmd_arry contains ("country", "address3", "address1", "1212 O'Mally Street", "client ID", "1234567") # which is six elements, which jibes with the query's question-marks $sth->execute( @cmd_arry); # errors here with the given message .... } </code></pre> <p>this code works great when the data does NOT contain nasty embedded single-quotes. I'd hoped the binding would solve this problem but no such luck.</p> <p>Anyone have a solution to this single-quote issue?</p> <p>Thanks in advance,</p> <p>Still-learning Steve.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload