Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer

Re: Re: Problem with Code

by SamueD2 (Novice)
on Mar 21, 2003 at 13:39 UTC ( [id://244852] : note . print w/replies, xml ) Need Help??

in reply to Re: Problem with Code
in thread Problem with Code

Thanks for your help. Can you explain to me how placeholders work. Also if I set the AutoCommit to 0 where do I place the Commit Thanks

Replies are listed 'Best First'.
Re: Re: Re: Problem with Code
by dga (Hermit) on Mar 21, 2003 at 15:41 UTC

    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: $@"; }

    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™.