Re: Is it correct that execute_array does not raise/print an error
Tim,
Thanks for the clarification and untested patch. I've tried it and it now
fails (returns undef) and the HandleError routine is called. The error I'm
getting now is:
executing ARRAY(0x8ce4c08) generated 1
Changing your patch to:
return $sth-set_err(1, executing . join(,, @{$tuple_status}) .
generated $err_count errors)
is every so slightly more useful, since it produces:
executing 1,ARRAY(0x981d698),1,1 generated 1 errors
which makes it obvious which row failed. The error handler gets a
better error message of:
DBD::Oracle::st execute_array failed: executing 1,ARRAY(0x8869c08),1,1
generated 1 errors [for Statement insert into mytest values (?,?) with
ParamValues: :p1=53, :p2='one']
Just one note. This breaks t/15array.t around about line 48 because the test
has RaiseError and PrintError turned on and expects the execute_array to fail.
Turning them off before the test and back on again after fixes.
Thanks again.
Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com
On 04-Jul-2006 Tim Bunce wrote:
On Tue, Jul 04, 2006 at 02:18:33PM +0100, Martin J. Evans wrote:
Thanks for the explanation. You have not however convinced me this behavior
is
right. If RaiseError caused a die on error and someone wanted to ignore
errors
they could just do what they always do - turn RaiseError off and do the
checking themselves.
What I was really after was whether not dying on an error in execute_array
when RaiseError was enabled was by design or a an oversight.
An oversight. Though the oversight is actually in execute_for_fetch()
which execute_array() array calls to do the real work.
It makes a
difference to me since I read the DBI docs and saw nothing which said
RaiseError does not work with execute_array, then discovered it didn't,
worked around this in my DBIx extension but would like to document why I
have this workaround.
Try this (untested):
--- DBI.pm (revision 6604)
+++ DBI.pm (working copy)
@@ -1931,7 +1931,10 @@
push @$tuple_status, [ $err, $errstr_cache{$err} ||=
$sth-errstr, $sth-state ];
}
}
- return ($err_count) ? undef : scalar(@$tuple_status)||0E0;
+my $tuples = @$tuple_status;
+return $sth-set_err(1, executing $tuple_status generated
$err_count errors)
+if $err_count;
+ return scalar(@$tuple_status) || 0E0;
}
Tim.
Re: Is it correct that execute_array does not raise/print an error
On Wed, Jul 05, 2006 at 01:38:04PM +0100, Martin J. Evans wrote:
Tim,
Thanks for the clarification and untested patch. I've tried it and it now
fails (returns undef) and the HandleError routine is called. The error I'm
getting now is:
executing ARRAY(0x8ce4c08) generated 1
D'oh. Thanks.
Changing your patch to:
return $sth-set_err(1, executing . join(,, @{$tuple_status}) .
generated $err_count errors)
is every so slightly more useful, since it produces:
executing 1,ARRAY(0x981d698),1,1 generated 1 errors
which makes it obvious which row failed.
You might not think it more useful if the batch had 10,000 tuples in it!
I think I'll stick with just a count.
The error handler gets a better error message of:
DBD::Oracle::st execute_array failed: executing 1,ARRAY(0x8869c08),1,1
generated 1 errors [for Statement insert into mytest values (?,?) with
ParamValues: :p1=53, :p2='one']
Um, including ParamValues is somewhat misleading in this situation.
Just one note. This breaks t/15array.t around about line 48 because the test
has RaiseError and PrintError turned on and expects the execute_array to fail.
Turning them off before the test and back on again after fixes.
Thanks.
Try the attached patch on top of the previous one.
Tim.
Index: t/15array.t
===
--- t/15array.t (revision 6474)
+++ t/15array.t (working copy)
@@ -2,7 +2,7 @@
use strict;
-use Test::More tests = 50;
+use Test::More tests = 52;
##
## 15array.t
@@ -45,7 +45,7 @@
# ---
-ok(!$sth-execute_array(
+ok(! eval { $sth-execute_array(
{
ArrayTupleStatus = $tuple_status
},
@@ -53,9 +53,11 @@
42, # scalar 42 treated as array
of 42's
undef,# scalar undef treated as array
of undef's
[ qw(A B C) ],# array of strings
-),
- '... execute_array should return false'
+) },
+'... execute_array should return false'
);
+ok $@, 'execute_array failure with RaiseError should have died';
+like $sth-errstr, '/executing 3 generated 1 errors/';
cmp_ok(scalar @{$rows}, '==', 2, '... we should have 2 rows');
cmp_ok(scalar @{$tuple_status}, '==', 3, '... we should have 3 tuple_status');
Index: DBI.pm
===
--- DBI.pm (revision 6616)
+++ DBI.pm (working copy)
@@ -1932,7 +1932,7 @@
}
}
my $tuples = @$tuple_status;
-return $sth-set_err(1, executing $tuple_status generated $err_count
errors)
+return $sth-set_err(1, executing $tuples generated $err_count
errors)
if $err_count;
return scalar(@$tuple_status) || 0E0;
}
Re: Is it correct that execute_array does not raise/print an error
On 05-Jul-2006 Tim Bunce wrote:
On Wed, Jul 05, 2006 at 01:38:04PM +0100, Martin J. Evans wrote:
Tim,
Thanks for the clarification and untested patch. I've tried it and it now
fails (returns undef) and the HandleError routine is called. The error I'm
getting now is:
executing ARRAY(0x8ce4c08) generated 1
D'oh. Thanks.
Changing your patch to:
return $sth-set_err(1, executing . join(,, @{$tuple_status}) .
generated $err_count errors)
is every so slightly more useful, since it produces:
executing 1,ARRAY(0x981d698),1,1 generated 1 errors
which makes it obvious which row failed.
You might not think it more useful if the batch had 10,000 tuples in it!
I think I'll stick with just a count.
Argh, yes, good point.
The error handler gets a better error message of:
DBD::Oracle::st execute_array failed: executing 1,ARRAY(0x8869c08),1,1
generated 1 errors [for Statement insert into mytest values (?,?) with
ParamValues: :p1=53, :p2='one']
Um, including ParamValues is somewhat misleading in this situation.
Yes, I meant to mention that - the params given are the last ones in the array
and not the failing ones which were :p1=1 and :p2='fiftytwo' in this case.
Just one note. This breaks t/15array.t around about line 48 because the test
has RaiseError and PrintError turned on and expects the execute_array to
fail.
Turning them off before the test and back on again after fixes.
Thanks.
Try the attached patch on top of the previous one.
Done - works better:
I know get:
executing 4 generated 1 errors
which is correct.
execute_array still returns undef - good.
the two new tests in 15array.t pass and the original one I mentioned passes
although it still throws
t/15arrayok 1/52DBD::Sponge::st execute_array failed: executing
3 generated 1 errors at t/15array.t line 48.
on stdout (PrintError?).
Thanks.
Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com
Re: Is it correct that execute_array does not raise/print an error
It memory serves me correctly I think any errors that are generated is
stored in the ArrayTupleStatus have a parse through that array to see it the
error is stored there.
- Original Message -
From: Martin J. Evans [EMAIL PROTECTED]
To: dbi-users@perl.org
Sent: Tuesday, July 04, 2006 6:37 AM
Subject: Is it correct that execute_array does not raise/print an error
Hi,
I have some code which uses execute_array but it appears when it fails
my error handler is not called and no error is printed despite setting
PrintError and RaiseError. The script below illustrates. Is it correct
that an
error can occur in execute_array and it not cause a die when RaiseError is
set?
use DBI;
use strict;
use Data::Dumper;
sub fred
{
print Error Handler called\n;
print Dumper([EMAIL PROTECTED]);
}
my $dbh = DBI-connect(
'DBI:mysql:mjetest', 'xxx', 'yyy',
{ RaiseError = 1, PrintError = 1, HandleError = \fred});
$dbh-do(q/drop table if exists mytest/);
$dbh-do(q/create table mytest (a int primary key, b char(20))/);
my $sth = $dbh-prepare(q/insert into mytest values (?,?)/);
$sth-bind_param(1, 1);
$sth-bind_param(2, 'onetwothree');
$sth-execute;
$sth-bind_param_array(1, [51,1,52,53]);
$sth-bind_param_array(2, ['fiftyone', 'fiftytwo', 'fiftythree', 'one']);
my @tuple_status;
my $inserted = $sth-execute_array( { ArrayTupleStatus =
[EMAIL PROTECTED] } );
print Error from execute_array - $DBI::errstr\n if (!$inserted);
which produces:
Error from execute_array -
even though the trace indicates:
- prepare('insert into mytest values (?,?)')= DBI::st=HASH(0x82a1b80)
at
execute_array.pl line 17
- bind_param(1 1)= 1 at execute_array.pl line 18
- bind_param(2 'onetwothree')= 1 at execute_array.pl line 19
- execute= 1 at execute_array.pl line 20
- bind_param_array(1 ARRAY(0x829d4f8))= 1 at execute_array.pl line 22
- bind_param_array(2 ARRAY(0x829d534))= 1 at execute_array.pl line 23
- execute_array(HASH(0x829d5c4))= undef at execute_array.pl line 26
Thanks
Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com
Re: Is it correct that execute_array does not raise/print an error
On 04-Jul-2006 John Scoles wrote:
It memory serves me correctly I think any errors that are generated is
stored in the ArrayTupleStatus have a parse through that array to see it the
error is stored there.
John,
They are stored in the ArrayTupleStatus. I'm not saying I can't get them, but I
rather thought since they were errors that RaiseError would cause a die and
that my HandleError routine would be called - neither seems to occur. Unless
I'm mistaken this seems to make execute_array a special case and thus
RaiseError should say it causes a die for all error in methods except
execute_array (and similarly for HandleError).
Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com
- Original Message -
From: Martin J. Evans [EMAIL PROTECTED]
To: dbi-users@perl.org
Sent: Tuesday, July 04, 2006 6:37 AM
Subject: Is it correct that execute_array does not raise/print an error
Hi,
I have some code which uses execute_array but it appears when it fails
my error handler is not called and no error is printed despite setting
PrintError and RaiseError. The script below illustrates. Is it correct
that an
error can occur in execute_array and it not cause a die when RaiseError is
set?
use DBI;
use strict;
use Data::Dumper;
sub fred
{
print Error Handler called\n;
print Dumper([EMAIL PROTECTED]);
}
my $dbh = DBI-connect(
'DBI:mysql:mjetest', 'xxx', 'yyy',
{ RaiseError = 1, PrintError = 1, HandleError = \fred});
$dbh-do(q/drop table if exists mytest/);
$dbh-do(q/create table mytest (a int primary key, b char(20))/);
my $sth = $dbh-prepare(q/insert into mytest values (?,?)/);
$sth-bind_param(1, 1);
$sth-bind_param(2, 'onetwothree');
$sth-execute;
$sth-bind_param_array(1, [51,1,52,53]);
$sth-bind_param_array(2, ['fiftyone', 'fiftytwo', 'fiftythree', 'one']);
my @tuple_status;
my $inserted = $sth-execute_array( { ArrayTupleStatus =
[EMAIL PROTECTED] } );
print Error from execute_array - $DBI::errstr\n if (!$inserted);
which produces:
Error from execute_array -
even though the trace indicates:
- prepare('insert into mytest values (?,?)')= DBI::st=HASH(0x82a1b80)
at
execute_array.pl line 17
- bind_param(1 1)= 1 at execute_array.pl line 18
- bind_param(2 'onetwothree')= 1 at execute_array.pl line 19
- execute= 1 at execute_array.pl line 20
- bind_param_array(1 ARRAY(0x829d4f8))= 1 at execute_array.pl line 22
- bind_param_array(2 ARRAY(0x829d534))= 1 at execute_array.pl line 23
- execute_array(HASH(0x829d5c4))= undef at execute_array.pl line 26
Thanks
Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com
Re: Is it correct that execute_array does not raise/print an error
Yes execute_array is a special case.
What DBI does is pass the Array of Tuples off to the native array interface
of the database for processing and expects back a Tuple of results.
Not sure what the exact call is in MYSQL but I know for Oracle this is what
OCI (the native Oracle interface) is doing. So it is a case of DBI::DBD
mimicking what the native driver is doing.
As execute_array is normally use to inserts/update of millions of records
and with this volume you would not want your process to choke on just one
bad record so it makes sense to look for errors after the process has run
rather that stopping at each one.
This being said if the DBD driver does not support an array interface DBI
simply mimics this behaviour by iterating though the array. Not real time
saving there it just follows the array interface model.
Cheers
John Scoles
- Original Message -
From: Martin J. Evans [EMAIL PROTECTED]
To: dbi-users@perl.org
Sent: Tuesday, July 04, 2006 8:03 AM
Subject: Re: Is it correct that execute_array does not raise/print an error
On 04-Jul-2006 John Scoles wrote:
It memory serves me correctly I think any errors that are generated is
stored in the ArrayTupleStatus have a parse through that array to see it
the
error is stored there.
John,
They are stored in the ArrayTupleStatus. I'm not saying I can't get them,
but I
rather thought since they were errors that RaiseError would cause a die
and
that my HandleError routine would be called - neither seems to occur.
Unless
I'm mistaken this seems to make execute_array a special case and thus
RaiseError should say it causes a die for all error in methods except
execute_array (and similarly for HandleError).
Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com
- Original Message -
From: Martin J. Evans [EMAIL PROTECTED]
To: dbi-users@perl.org
Sent: Tuesday, July 04, 2006 6:37 AM
Subject: Is it correct that execute_array does not raise/print an error
Hi,
I have some code which uses execute_array but it appears when it fails
my error handler is not called and no error is printed despite setting
PrintError and RaiseError. The script below illustrates. Is it correct
that an
error can occur in execute_array and it not cause a die when RaiseError
is
set?
use DBI;
use strict;
use Data::Dumper;
sub fred
{
print Error Handler called\n;
print Dumper([EMAIL PROTECTED]);
}
my $dbh = DBI-connect(
'DBI:mysql:mjetest', 'xxx', 'yyy',
{ RaiseError = 1, PrintError = 1, HandleError = \fred});
$dbh-do(q/drop table if exists mytest/);
$dbh-do(q/create table mytest (a int primary key, b char(20))/);
my $sth = $dbh-prepare(q/insert into mytest values (?,?)/);
$sth-bind_param(1, 1);
$sth-bind_param(2, 'onetwothree');
$sth-execute;
$sth-bind_param_array(1, [51,1,52,53]);
$sth-bind_param_array(2, ['fiftyone', 'fiftytwo', 'fiftythree',
'one']);
my @tuple_status;
my $inserted = $sth-execute_array( { ArrayTupleStatus =
[EMAIL PROTECTED] } );
print Error from execute_array - $DBI::errstr\n if (!$inserted);
which produces:
Error from execute_array -
even though the trace indicates:
- prepare('insert into mytest values (?,?)')=
DBI::st=HASH(0x82a1b80)
at
execute_array.pl line 17
- bind_param(1 1)= 1 at execute_array.pl line 18
- bind_param(2 'onetwothree')= 1 at execute_array.pl line 19
- execute= 1 at execute_array.pl line 20
- bind_param_array(1 ARRAY(0x829d4f8))= 1 at execute_array.pl line
22
- bind_param_array(2 ARRAY(0x829d534))= 1 at execute_array.pl line
23
- execute_array(HASH(0x829d5c4))= undef at execute_array.pl line
26
Thanks
Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com
Re: Is it correct that execute_array does not raise/print an error
John,
On 04-Jul-2006 John Scoles wrote:
Yes execute_array is a special case.
What DBI does is pass the Array of Tuples off to the native array interface
of the database for processing and expects back a Tuple of results.
As an aside, I'm not sure this is always the case. I'm sure I read in the DBI
docs you could pass a function reference in which would be used to retrieve the
tuples.
Not sure what the exact call is in MYSQL but I know for Oracle this is what
OCI (the native Oracle interface) is doing. So it is a case of DBI::DBD
mimicking what the native driver is doing.
As execute_array is normally use to inserts/update of millions of records
and with this volume you would not want your process to choke on just one
bad record so it makes sense to look for errors after the process has run
rather that stopping at each one.
This being said if the DBD driver does not support an array interface DBI
simply mimics this behaviour by iterating though the array. Not real time
saving there it just follows the array interface model.
Cheers
John Scoles
Thanks for the explanation. You have not however convinced me this behavior is
right. If RaiseError caused a die on error and someone wanted to ignore errors
they could just do what they always do - turn RaiseError off and do the
checking themselves.
What I was really after was whether not dying on an error in execute_array
when RaiseError was enabled was by design or a an oversight. It makes a
difference to me since I read the DBI docs and saw nothing which said
RaiseError does not work with execute_array, then discovered it didn't,
worked around this in my DBIx extension but would like to document why I
have this workaround.
Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com
- Original Message -
From: Martin J. Evans [EMAIL PROTECTED]
To: dbi-users@perl.org
Sent: Tuesday, July 04, 2006 8:03 AM
Subject: Re: Is it correct that execute_array does not raise/print an error
On 04-Jul-2006 John Scoles wrote:
It memory serves me correctly I think any errors that are generated is
stored in the ArrayTupleStatus have a parse through that array to see it
the
error is stored there.
John,
They are stored in the ArrayTupleStatus. I'm not saying I can't get them,
but I
rather thought since they were errors that RaiseError would cause a die
and
that my HandleError routine would be called - neither seems to occur.
Unless
I'm mistaken this seems to make execute_array a special case and thus
RaiseError should say it causes a die for all error in methods except
execute_array (and similarly for HandleError).
Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com
- Original Message -
From: Martin J. Evans [EMAIL PROTECTED]
To: dbi-users@perl.org
Sent: Tuesday, July 04, 2006 6:37 AM
Subject: Is it correct that execute_array does not raise/print an error
Hi,
I have some code which uses execute_array but it appears when it fails
my error handler is not called and no error is printed despite setting
PrintError and RaiseError. The script below illustrates. Is it correct
that an
error can occur in execute_array and it not cause a die when RaiseError
is
set?
use DBI;
use strict;
use Data::Dumper;
sub fred
{
print Error Handler called\n;
print Dumper([EMAIL PROTECTED]);
}
my $dbh = DBI-connect(
'DBI:mysql:mjetest', 'xxx', 'yyy',
{ RaiseError = 1, PrintError = 1, HandleError = \fred});
$dbh-do(q/drop table if exists mytest/);
$dbh-do(q/create table mytest (a int primary key, b char(20))/);
my $sth = $dbh-prepare(q/insert into mytest values (?,?)/);
$sth-bind_param(1, 1);
$sth-bind_param(2, 'onetwothree');
$sth-execute;
$sth-bind_param_array(1, [51,1,52,53]);
$sth-bind_param_array(2, ['fiftyone', 'fiftytwo', 'fiftythree',
'one']);
my @tuple_status;
my $inserted = $sth-execute_array( { ArrayTupleStatus =
[EMAIL PROTECTED] } );
print Error from execute_array - $DBI::errstr\n if (!$inserted);
which produces:
Error from execute_array -
even though the trace indicates:
- prepare('insert into mytest values (?,?)')=
DBI::st=HASH(0x82a1b80)
at
execute_array.pl line 17
- bind_param(1 1)= 1 at execute_array.pl line 18
- bind_param(2 'onetwothree')= 1 at execute_array.pl line 19
- execute= 1 at execute_array.pl line 20
- bind_param_array(1 ARRAY(0x829d4f8))= 1 at execute_array.pl line
22
- bind_param_array(2 ARRAY(0x829d534))= 1 at execute_array.pl line
23
- execute_array(HASH(0x829d5c4))= undef at execute_array.pl line
26
Thanks
Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com
Re: Is it correct that execute_array does not raise/print an error
John Scoles schreef: As execute_array is normally use to inserts/update of millions of records and with this volume you would not want your process to choke on just one bad record Huh? It is fine if you can deliberately turn it off, but by default such a feature should abort and roll back. If non-fatal warnings occur, see perllexwarn about FATAL. -- Affijn, Ruud Gewoon is een tijger.
Re: Is it correct that execute_array does not raise/print an error
On Tue, Jul 04, 2006 at 02:18:33PM +0100, Martin J. Evans wrote:
Thanks for the explanation. You have not however convinced me this behavior is
right. If RaiseError caused a die on error and someone wanted to ignore errors
they could just do what they always do - turn RaiseError off and do the
checking themselves.
What I was really after was whether not dying on an error in execute_array
when RaiseError was enabled was by design or a an oversight.
An oversight. Though the oversight is actually in execute_for_fetch()
which execute_array() array calls to do the real work.
It makes a
difference to me since I read the DBI docs and saw nothing which said
RaiseError does not work with execute_array, then discovered it didn't,
worked around this in my DBIx extension but would like to document why I
have this workaround.
Try this (untested):
--- DBI.pm (revision 6604)
+++ DBI.pm (working copy)
@@ -1931,7 +1931,10 @@
push @$tuple_status, [ $err, $errstr_cache{$err} ||=
$sth-errstr, $sth-state ];
}
}
- return ($err_count) ? undef : scalar(@$tuple_status)||0E0;
+my $tuples = @$tuple_status;
+return $sth-set_err(1, executing $tuple_status generated $err_count
errors)
+if $err_count;
+ return scalar(@$tuple_status) || 0E0;
}
Tim.
