# Do the updating...
foreach (keys (%Data)) {
  $dbh->do(" update new_table set newData=\"$Data{$_}\" where newID=$_
+ ");
} # end-foreach
[download]

preparing your "updating" statement with placeholders
[download]

Yes, in that section, it should be more like this:

my $sth = $dbh->prepare("update new_table set newData=? where newID=?)
+;
foreach ( keys %Data ) {
    execute( $Data{$_}, $_ );
}
[download]

The idea is that you're preparing the update statement only once, so the database's SQL parser only has to examine it one time. Then as you execute it, the same statement is executed over and over again with the appropriate values inserted via the placeholders.

That snippet is untested, and just from memory, so do test it and check the docs. There's the DBI quote() method that does proper quoting for you.

See DBI for more info.




Dave

Well, what you really want is something like this (assuming you have transactions turned on (which you really, really want), and RaiseError as well):

eval {
    my $sth = $dbh -> prepare (<<'    --');
               update  new_table 
                  set  newData = ? 
                where  newID   = ?
    --
    while (my ($id, $data) = each %Data) {
        $sth -> execute ($data, $id)
    }
};
if ($@) {
    $dbh -> rollback;
    die $@;
}
else {
    $dbh -> commit;
}
[download]

Note that the data inserted doesn't get quotes tacked on - that's why you are using placeholders, quoting is being taken care of.

Abigail

Also, check the docs to learn the better way of quoting data that's getting inserted into the database. There's a DBI method that does it for you

Well, Abigail already told you what to do here, but I thought some of you might find this funny. I just started a new job, and the first task I imposed upon myself was removing all the instances of the wonderful subroutine sql_quote (AKA quote_for_db, db_quote). Actual code follows:


sub sql_quote {
  my ($entry) = @_;
  $entry =~ s/'/''/g;
  return "'" . $entry . "'";
}
[download]

All indented with tabs, too, and I'm not even going to mention the formatting of the SQL queries.

A useful (IMO) thing for placeholders, not in the DBI docs (at least I don't think it is), is when using  foo LIKE ?. Then you have to put your % or ? in the parameter passed in the execute:

$st->execute("%$bar");
[download]

or whatever.

Thanks dave.
I follow it now.
Mysql look out, here i come :)

Cheers,
Reagen

Hey Dave,

I think I understand placeholders now. Is it also possible to do this:


my $insert = $dbh->prepare("insert into table values (?,?,?)");
while (something) {
  $insert->execute($var1,$var2,$var3);
} # end-while
[download]

Cheers,
Reagen