Re^2: Database Insert Depending on Data Type

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!

Comment on Re^2: Database Insert Depending on Data Type Select or Download Code

Replies are listed 'Best First'.

Re^3: Database Insert Depending on Data Type
by jZed (Prior) on Dec 20, 2007 at 21:12 UTC

my $sqlCmd = $dbh->prepare("
    UPDATE $table SET $column = ? WHERE $column = ?
");
for ($count = 0; $count < $EasySetDataCount; $count++) {
    $sqlCmd->execute(
       $NewEasySetDataNames[$count], 
       $EasySetDataNames[$count],
    );
}
[download]

[reply]
[d/l]


Welcome to the Monastery
	PerlMonks