Problems with Error Handling

I'm running the Perl DBI module with MySQL under cygwin on a Windows XP
system
and I'm having trouble using the DBI error handling/trapping procedures.

I'm running perl v5.8.7 built for cygwin-thread-multi-64int; DBI v1.50;
MySQL
5.0.18-nt.

Here is a snippet of what my program looks like:

1 $dbHandle->{AutoCommit} = 0;
2 $dbHandle->{PrintError} = 0;
3 $dbHandle->{RaiseError} = 1;
4
5 ... Code to prepare the SQL statements is here
6
7 eval
8 {
9 while ( )
10 {
11 $stmtHandle->execute ( ... );
12 }
13 $dbHandle->commit();
14 };
15
16 if ( $@ )
17 {
18 warn $@;
19 $dbHandle->rollback();
20 }

I've put together a test that tries to insert duplicate keys to test the
error handling logic and it does not work as I expected. I've read all
the manual pages and FAQs I can get hold of and the all say this should
work but I have two questions/problems with the above:

1) According to the manuals, enabling "RaiseError" should cause the
"eval" to
fail the first time it gets an error. In my program, it will print the
error message and continue the inner loop until the input is exhausted.
If I put a "|| die ..." on the "execute" statement, it fails the "eval"
on the first error.

2) Disabling the "PrintError" message is supposed to prevent the error
message from being printed inside the inner loop but it does not. Even
if I add the "|| die ..." clause, it still prints the error message.

If I run the program as coded above with 3 lines of input, I get:

DBD::mysql::st execute failed: Duplicate entry '...' for key 1 at ...
DBD::mysql::st execute failed: Duplicate entry '...' for key 1 at ...
DBD::mysql::st execute failed: Duplicate entry '...' for key 1 at ...

and the commit at line 13 is executed.

If I run the above with the "|| die ..." on line 11, I get:

DBD::mysql::st execute failed: Duplicate entry '...' for key 1 at ...
Duplicate entry '...' for key 1 at ...

where the first line is printed inside the loop and the second line is
printed
by line 18 above.

All I want to do is suppress the error messages and handle the errors myself
at the end of the "eval" block. What am I doing wrong?

TIA


Don

Re: Problems with Error Handling

Please post a *small but complete* example that demonstrates the problem.

Tim.

On Tue, May 30, 2006 at 09:19:25PM -0700, Don Mies wrote:
>
> I'm running the Perl DBI module with MySQL under cygwin on a Windows XP
> system
> and I'm having trouble using the DBI error handling/trapping procedures.
>
> I'm running perl v5.8.7 built for cygwin-thread-multi-64int; DBI v1.50;
> MySQL
> 5.0.18-nt.
>
> Here is a snippet of what my program looks like:
>
> 1 $dbHandle->{AutoCommit} = 0;
> 2 $dbHandle->{PrintError} = 0;
> 3 $dbHandle->{RaiseError} = 1;
> 4
> 5 ... Code to prepare the SQL statements is here
> 6
> 7 eval
> 8 {
> 9 while ( )
> 10 {
> 11 $stmtHandle->execute ( ... );
> 12 }
> 13 $dbHandle->commit();
> 14 };
> 15
> 16 if ( $@ )
> 17 {
> 18 warn $@;
> 19 $dbHandle->rollback();
> 20 }
>
> I've put together a test that tries to insert duplicate keys to test the
> error handling logic and it does not work as I expected. I've read all
> the manual pages and FAQs I can get hold of and the all say this should
> work but I have two questions/problems with the above:
>
> 1) According to the manuals, enabling "RaiseError" should cause the
> "eval" to
> fail the first time it gets an error. In my program, it will print the
> error message and continue the inner loop until the input is exhausted.
> If I put a "|| die ..." on the "execute" statement, it fails the "eval"
> on the first error.
>
> 2) Disabling the "PrintError" message is supposed to prevent the error
> message from being printed inside the inner loop but it does not. Even
> if I add the "|| die ..." clause, it still prints the error message.
>
> If I run the program as coded above with 3 lines of input, I get:
>
> DBD::mysql::st execute failed: Duplicate entry '...' for key 1 at ...
> DBD::mysql::st execute failed: Duplicate entry '...' for key 1 at ...
> DBD::mysql::st execute failed: Duplicate entry '...' for key 1 at ...
>
> and the commit at line 13 is executed.
>
> If I run the above with the "|| die ..." on line 11, I get:
>
> DBD::mysql::st execute failed: Duplicate entry '...' for key 1 at ...
> Duplicate entry '...' for key 1 at ...
>
> where the first line is printed inside the loop and the second line is
> printed
> by line 18 above.
>
> All I want to do is suppress the error messages and handle the errors myself
> at the end of the "eval" block. What am I doing wrong?
>
> TIA
>
>
> Don
>
>
>
>
>

Re: Problems with Error Handling

--------------060502090900070904080904
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Tim, et. al. Following is a small sample program that demonstrates my
problem:

#! /bin/perl -w

use DBI;
# use CGI qw ( :standard );

my $dbHandle = DBI->connect ( "dbi:mysql:commodities:127.0.0.1",
'don', "" )
|| die "Got error $DBI::errstr when connecting to
mysql:commodities\n";

my $insert = qq/
INSERT INTO rawPrices
( Contract, Date, Open, High, Low, Close, Volume, OpenInterest,
DayNumber, Source, DateAdded )
VALUES
( ?, ?, ?, ?, ?, ?, ?, ?, ?, "CBOE", NOW() )
/;

my $stmtHandle = $dbHandle->prepare ( $insert );

$dbHandle->{AutoCommit} = 0;
$dbHandle->{PrintError} = 0;
$dbHandle->{RaiseError} = 1;

eval
{
$stmtHandle->execute ( "AC06M", "2006-05-19", 1.0, 1.0, 1.0, 1.0,
1.0, 1.0, 1 );
$stmtHandle->execute ( "AC06M", "2006-05-19", 1.0, 1.0, 1.0, 1.0,
1.0, 1.0, 1 );
$stmtHandle->execute ( "AC06M", "2006-05-19", 1.0, 1.0, 1.0, 1.0,
1.0, 1.0, 1 );
};

if ( $@ )
{
$dbHandle->rollback();
print "====> transaction rolled back\n";
}
else
{
$dbHandle->commit();
print "====> transaction committed\n";
}
$stmtHandle->finish();
$dbHandle->disconnect();

When I execute the above, I get:

DBD::mysql::st execute failed: Duplicate entry 'AC06M-2006-05-19'
for key 1 at zz.pl line 25.
DBD::mysql::st execute failed: Duplicate entry 'AC06M-2006-05-19'
for key 1 at zz.pl line 27.
DBD::mysql::st execute failed: Duplicate entry 'AC06M-2006-05-19'
for key 1 at zz.pl line 29.
====> transaction committed

I expect that the program should NOT print any of the error messages,
break out of the eval block after the first error, and NOT commit the
transaction but roll it back.

Thanks,


Don


Tim Bunce wrote:

>Please post a *small but complete* example that demonstrates the problem.
>
>Tim.
>
>On Tue, May 30, 2006 at 09:19:25PM -0700, Don Mies wrote:
>
>
>>I'm running the Perl DBI module with MySQL under cygwin on a Windows XP
>>system
>>and I'm having trouble using the DBI error handling/trapping procedures.
>>
>>I'm running perl v5.8.7 built for cygwin-thread-multi-64int; DBI v1.50;
>>MySQL
>>5.0.18-nt.
>>
>>Here is a snippet of what my program looks like:
>>
>> 1 $dbHandle->{AutoCommit} = 0;
>> 2 $dbHandle->{PrintError} = 0;
>> 3 $dbHandle->{RaiseError} = 1;
>> 4
>> 5 ... Code to prepare the SQL statements is here
>> 6
>> 7 eval
>> 8 {
>> 9 while ( )
>> 10 {
>> 11 $stmtHandle->execute ( ... );
>> 12 }
>> 13 $dbHandle->commit();
>> 14 };
>> 15
>> 16 if ( $@ )
>> 17 {
>> 18 warn $@;
>> 19 $dbHandle->rollback();
>> 20 }
>>
>>I've put together a test that tries to insert duplicate keys to test the
>>error handling logic and it does not work as I expected. I've read all
>>the manual pages and FAQs I can get hold of and the all say this should
>>work but I have two questions/problems with the above:
>>
>>1) According to the manuals, enabling "RaiseError" should cause the
>>"eval" to
>> fail the first time it gets an error. In my program, it will print the
>> error message and continue the inner loop until the input is exhausted.
>> If I put a "|| die ..." on the "execute" statement, it fails the "eval"
>> on the first error.
>>
>>2) Disabling the "PrintError" message is supposed to prevent the error
>> message from being printed inside the inner loop but it does not. Even
>> if I add the "|| die ..." clause, it still prints the error message.
>>
>>If I run the program as coded above with 3 lines of input, I get:
>>
>> DBD::mysql::st execute failed: Duplicate entry '...' for key 1 at ...
>> DBD::mysql::st execute failed: Duplicate entry '...' for key 1 at ...
>> DBD::mysql::st execute failed: Duplicate entry '...' for key 1 at ...
>>
>>and the commit at line 13 is executed.
>>
>>If I run the above with the "|| die ..." on line 11, I get:
>>
>> DBD::mysql::st execute failed: Duplicate entry '...' for key 1 at ...
>> Duplicate entry '...' for key 1 at ...
>>
>>where the first line is printed inside the loop and the second line is
>>printed
>>by line 18 above.
>>
>>All I want to do is suppress the error messages and handle the errors myself
>>at the end of the "eval" block. What am I doing wrong?
>>
>>TIA
>>
>>
>>Don
>>
>>
>>
>>
>>
>>
>>
>
>
>

--------------060502090900070904080904--

Re: Problems with Error Handling

On Wed, May 31, 2006 at 08:57:23PM -0700, Don Mies wrote:
> Tim, et. al. Following is a small sample program that demonstrates my
> problem:
>
> my $stmtHandle = $dbHandle->prepare ( $insert );
>
> $dbHandle->{AutoCommit} = 0;
> $dbHandle->{PrintError} = 0;
> $dbHandle->{RaiseError} = 1;

You're altering the parent handle attributes after you've created a
child handle. The changes won't affect the child. See the DBI docs.

Tim.