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.

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

assuming you have transactions turned on (which you really, really want), and RaiseError as well)

I'm going to take issue with the transactions part of this statement. Transactions can be necessary in many cases. However, one of the benefits of MySQL over, say, Oracle is that you can choose whether or not you want transactions or not. In a majority of cases, you don't actually need transactions because your transactions are all one statement long.

The benefit of ACID-compliant transactions is the ability to rollback multi-statement changes. If your one-statement transaction fails, nothing has actually occurred to change the state of the tables involved.

By using a table type that doesn't have transactions (such as the default MyISAM), you can do many activities (like bulk inserts) 5-10x faster. If you don't need transactions, that's a huge benefit.

------
We are the carpenters and bricklayers of the Information Age.

Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose

I shouldn't have to say this, but any code, unless otherwise stated, is untested

Hey Abigail,

Please enlighten me on the last bit of your code which I reproduced below:

if ($@) {
    $dbh -> rollback;
    die $@;
}
else {
    $dbh -> commit;
}

# Can we leave out the 'else' and just say:

if ($@) {
    $dbh -> rollback;
    die $@;
}
$dbh -> commit;
[download]

Is there any difference?

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

Is it also possible to do this

Instead of just saying "yes" or "no", I give you a counter question: what happened when you tried?

Abigail

Better to get in the habit of specifying your column values:

my $insert = $dbh->prepare("insert into table
(column1, column2, column3) values (?,?,?)");
[download]

OT, but the only time I don't do this is outside of Perl where the language lets me declare record variables like a table:

define some_record like some_table.*

insert into some_table some_record.*
[download]

And even this breaks when columns get out of order between the time you compile the program and run it, so column order becomes one more thing to maintain, and it's better to not have to maintain one more thing. :)