selectall_arrayref/hashref error again

Greetings,

I'm wondering about how to retrieve the error from bad sql passed to
selectall_arrayref or selectall_hashref. In
http://www.mail-archive.com/dbi-users@perl.org/msg26094.html
Tim suggested this:
my $hr = $dbh->selectall_hashref( ... );
die(...) if $DBI::err;

but as far as I can tell, $DBI::err is not set when the sql is bad. For
instance (the last two lines are the important ones):

use DBI;
my $dsn = "DBI:mysql:mydb";
my $user = "...";
my $password = "...";
#
eval { $dbh = DBI->connect ($dsn, $user, $password,
{ RaiseError => 0, AutoCommit => 0 }) };
if (! $dbh || $@) {
die "...";
}
#
my $fields = $dbh->selectall_arrayref ("this is erroneous sql");
warn "DBI::err=$DBI::err, DBI::errstr=$DBI::errstr.\n" if ! defined $fields;

The output is:

DBI::err=0, DBI::errstr=.

.... so $fields is undef, as expected, but $DBI::err is still 0 despite
the syntax error. Am I misunderstanding something basic? I tried
looking into the code, but could not unravel what might be getting set
from the error.

Meanwhile, the doc for selectall_arrayref says:

If "RaiseError" is not set [...] You should check
"$sth->err" afterwards (or use the "RaiseError" attribute) to
discover if the data is complete or was truncated due to an
error.

With selectall_arrayref, there may well be no $sth (that being its
raison d'etre after all). If there is really no alternative to using
RaiseError without a $sth, it would be nice if the doc would just say
that.

Any advice would be much appreciated. I'm using perl 5.8.7 and DBI 1.50
under GNU/Linux.

Thanks,
Karl

RE: selectall_arrayref/hashref error again

Karl,

Seems to work fine for me:

perl -e 'use DBI;
$dbh=DBI->connect("dbi:mysql:hostname=xxx","xxx","yyy",{Rais eError=>0,PrintErro
r=>0});
$r=$dbh->selectall_arrayref("this is eroneous sql");
print "r=", DBI::neat($r), ",",$DBI::err,",",$DBI::errstr,"\n";'

prints:

r=undef,1064,You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'this
is eroneous sql' at line 1

I'm using perl 5.8.7 and DBI 1.50 too (with 1 minor patch posted on this list
to get Statement and ParamArrays).

What driver are you using?

I tried this with DBD::ODBC and DBD::mysql - both were OK (but my DBD::mysql is
massively patched).

Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com


On 06-Apr-2006 Karl Berry wrote:
> Greetings,
>
> I'm wondering about how to retrieve the error from bad sql passed to
> selectall_arrayref or selectall_hashref. In
> http://www.mail-archive.com/dbi-users@perl.org/msg26094.html
> Tim suggested this:
> my $hr = $dbh->selectall_hashref( ... );
> die(...) if $DBI::err;
>
> but as far as I can tell, $DBI::err is not set when the sql is bad. For
> instance (the last two lines are the important ones):
>
> use DBI;
> my $dsn = "DBI:mysql:mydb";
> my $user = "...";
> my $password = "...";
> #
> eval { $dbh = DBI->connect ($dsn, $user, $password,
> { RaiseError => 0, AutoCommit => 0 }) };
> if (! $dbh || $@) {
> die "...";
> }
> #
> my $fields = $dbh->selectall_arrayref ("this is erroneous sql");
> warn "DBI::err=$DBI::err, DBI::errstr=$DBI::errstr.\n" if ! defined
> $fields;
>
> The output is:
>
> DBI::err=0, DBI::errstr=.
>
> ... so $fields is undef, as expected, but $DBI::err is still 0 despite
> the syntax error. Am I misunderstanding something basic? I tried
> looking into the code, but could not unravel what might be getting set
> from the error.
>
> Meanwhile, the doc for selectall_arrayref says:
>
> If "RaiseError" is not set [...] You should check
> "$sth->err" afterwards (or use the "RaiseError" attribute) to
> discover if the data is complete or was truncated due to an
> error.
>
> With selectall_arrayref, there may well be no $sth (that being its
> raison d'etre after all). If there is really no alternative to using
> RaiseError without a $sth, it would be nice if the doc would just say
> that.
>
> Any advice would be much appreciated. I'm using perl 5.8.7 and DBI 1.50
> under GNU/Linux.
>
> Thanks,
> Karl