Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

Re: Database Insert Depending on Data Type

by jZed (Prior)
on Dec 18, 2007 at 21:47 UTC ( #657751=note: print w/ replies, xml ) Need Help??


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


Comment on Re: Database Insert Depending on Data Type
Download Code
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"; }
    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.
    Do you believe in miracles? Yes!
      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).

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://657751]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (18)
As of 2014-11-24 16:08 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My preferred Perl binaries come from:














    Results (143 votes), past polls