Re: Database Insert Depending on Data Type

[jZed]

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);

[Olaf]

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";
}

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

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.

[jZed]

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],
    );
}

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).