Placeholders tell DBI where in a stamement to place values passed in via the execute method. Here is an example which hopefully will demonstrate both things.

... # code to get things set up and vars defined etc.
my $dbh=DBI->connect($datasource_with_RaiseError_1_and_AutoCommit_0);
eval {
  $dbh->rollback;  #this sets the time on the transaction to now
  my $sth=$dbh->prepare('INSERT INTO table ( name, email ) VALUES ( ?,
+ ? )');
  while(my($name,$email)=each %emaillist)
  {
    $sth->execute($name, $email);
  }
  $dbh->commit;
];

if($@)
{
  $dbh->rollback;
  print STDERR "Something went wrong in insert: $@";
}
[download]

With later DBI's you can get the actual statement tried after placeholder substitution for your error messages also. $@ has the message from DBI when it called die. You would have to declare the sth outside the eval so it would be in scope for the error handler block. Also with current DBI there is a begin method which turns off autocommit for one transaction so you can normally have it on for selects and just use multi statement transaction mode when desired.

With this code, if anything goes wrong inserting values the while will be cancelled and the eval is exited with $@ set so the if will catch it. If everything goes ok the while will end and the commit will be called making the changes to the database durable and the if block gets skipped. The rollback at the top gets any timestamps etc set to the start of the eval. Of course if you call this from somewhere where there is a transaction underway it will be cancelled before this one starts so be careful to coordinate your rollbacks so as not to undo things you wanted saved.

The placeholders ?, ? will be replaced by DBI on each call to the execute by the variables passed in order. It will also take an array and use them in order. The advantages are that the Database backend makes up a generic statement for use and sets up the query plan then the variables are just dropped into the existing statement and the database has only to run the previously prepared statement without generating a new plan. Also by doing things in a transaction the overhead of adding rows is smaller since with autocommit on you effectively do a commit, which is an expensive operation, on every row instead of once for a group of rows. In summary, in your loop you don't make query plans or commit changes, just add rows quickly, and getting expensive operations out of loops is a Good Thing™.

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.