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
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]

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]