http://qs321.pair.com?node_id=657751


in reply to Database Insert Depending on Data Type

No, please don't do that. Either use the $dbh->quote() method, or even better use placeholders. Use placeholders! Did I mention? Use placeholders - more secure, more portable, the right way to do it. Both quote() and placeholders automatically quote strings and leave numbers unquoted. 
my $sth = $dbh->prepare("
    UPDATE tablename SET columname = ?
");
$sth->execute($data);

[download]

Replies are listed 'Best First'.
Re^2: Database Insert Depending on Data Type
by Olaf (Acolyte) on Dec 20, 2007 at 20:45 UTC
    Hmmmm...this seems to be eluding me I have your suggestions in my code below: 
    #---SQL to change the column data
for ($count = 0; $count < $EasySetDataCount; $count++) {
#------Put quotes around data if needed
$QuotedNewEasySetDataName = $dbh->quote($NewEasySetDataNames[$count]);
$QuotedEasySetDataName = $dbh->quote($EasySetDataNames[$count]);
#------Create SQL statement
$sqlStatement = "UPDATE ".$ChosenEasySetTableName." SET ".$ChosenEasyS
+etColumnName." = ".$NewEasySetDataNames[$count]." WHERE ".$ChosenEasy
+SetColumnName." = ".$EasySetDataNames[$count];
#------print it out for debugging
print $sqlStatement;
#------prepare and execute
$sqlCmd = $dbh->prepare( $sqlStatement );
$sqlCmd->execute()
    or die "SQL Error: $DBI::errstr\n";
}

    [download]
    And the run results... 
    Uncaught exception from user code:
        SQL Error: [Microsoft][ODBC SQL Server Driver][SQL Server]Inva
+lid column name 'Other'. (SQL-42S22)
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not 
+be prepared. (SQL-42000)(DBD: st_execute/SQLExecute err=-1)
 at C:\temp\EasySetRenameTool.pl line 155
Issuing rollback() for database handle being DESTROY'd without explici
+t disconnect() at C:\temp\EasySetRenameTool.pl line 155, <STDIN> line
+ 5.
UPDATE AI_ESP SET Race = red WHERE Race = Other

    [download]
    Note the lack of quotes in the Printed SQL at the end around RED and OTHER.
    As I've only submitted a code snippet, line 155 refers to the $sqlCmd->execute(); line
    If I manually put single quotes into the SQL it works. Or if I leave the quotes off and the dataset is numeric it works.
    Do you believe in miracles? Yes!
[reply]
[d/l]
[select]
      It looks like you are not using the same variables in the SQL that you used the quote() method on. I really advise you to use placeholders instead. Something like: 
      my $sqlCmd = $dbh->prepare("
    UPDATE $table SET $column = ? WHERE $column = ?
");
for ($count = 0; $count < $EasySetDataCount; $count++) {
    $sqlCmd->execute(
       $NewEasySetDataNames[$count], 
       $EasySetDataNames[$count],
    );
}

      [download]
      The placeholders mean you don't need to worry about quoting at all, you just say where the value should go (with the question marks in the SQL) and what data should be placed in those places (the items in the call to execute).
[reply]
[d/l]

In Section Seekers of Perl Wisdom