in reply to Problem with Code

I don't know DBD::ODBC directly but one thought that occured to me was "Do you need to pass a statement handle to Sql instead of a scalar containing some sql to execute?"

Alternately this could be done with 2 statement handles along these lines:

#the stuff at the top up to $sth->execute;
my $insh=$dbh->prepare("INSERT INTO TestClearTransferData(AccountNo, T
+ransferFlag, TransferType, RejectType, AdpNumber, Shares, ProcessingD
+ate) VALUES ( ?, ?, ?, ?, ?, ?, ? )");

eval {
  while((@data) = $sth->fetchrow_array) {
    $insh->execute(@data);
  }
};
if($@)
{
  print STDERR "The insert died: $@";
}
...

[download]

Since RaiseError is set the eval will die if theres a problem. Also the placeholders will speed up the inserts. And if you could set AutoCommit to 0 on the database getting the inserts you could do all of them in a transaction so that all are successful or none are successful, thus not leaving a messy table half way through the inserts if there is a problem.

Replies are listed 'Best First'.
Re: Re: Problem with Code
by SamueD2 (Novice) on Mar 21, 2003 at 13:39 UTC
    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
[reply]

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

[reply]
[d/l]

In Section Seekers of Perl Wisdom