Perl DBD fetchrow_array error:

Question

I got some strange error when reading MySQL, my code is something like this:

my $sql = <<"sqleof";
select t1.name t1_name, t2.name t2_name from t2
    inner join t1 using(Id)
    where t2.Id in (select Id from t3 where t3Id='$id') 
sqleof
#here $dbh had connected correctly and done some query before this; $sql can execute pretty well on MySQL command line and return me some records.
my $execute = $dbh->prepare($sql);
$execute->execute or die "Error: $DBI::errstr\n";
my @client = $execute->fetchrow_array() or die "Error: $DBI::errstr\n";
#here I got error saying: DBD::ODBC::st fetchrow_array failed:     Unable to fetch information about the error

What's the problem?

Hi, all, sorry to bother you. I had found the reason. It's a low level miss, as I had used multiple $dbh and I make a mistake of the execute name.

    my $execute_A = $dbh->prepare($sql);
$execute_A->execute or die "Error: $DBI::errstr\n";
my @client = $execute_B->fetchrow_array()  #$execute_B here when I copied lines and modified.

Your helps are of much importance to me. Thank you all.

You've tagged mysql and error suggests you're using it with ODBC which is very unusual setup. — mpapec
– mpapec, Commented Dec 19, 2013 at 6:29
I use Windows ODBC Data Source administrator created DSN and the DSN use a MySQL ODBC connector to connect the remote database. Your comments is valuable to me. — anaconda_wly
– anaconda_wly, Commented Dec 19, 2013 at 15:16

mpapec · Accepted Answer · 2013-12-18 18:18:47Z

2

Replace

my @client = $execute->fetchrow_array() or die "Error: $DBI::errstr\n";

with

my @client = $execute->fetchrow_array();

You're fetching empty array and this is not suitable for error checking as ..or die .. suggests.

.. or die .. makes sense only for prepare and execute methods.

Side note, you're also lacking proper error checking:

my $execute = $dbh->prepare($sql) or die $dbh->errstr; # not $DBI::errstr
$execute->execute or die $execute->errstr;             # not $DBI::errstr

also, use sql placeholders to prevent sql injection.

edited Dec 18, 2013 at 18:18

answered Dec 18, 2013 at 17:30

mpapec

50.7k8 gold badges71 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ThisSuitIsBlackNot Over a year ago

Not exactly true that you shouldn't do error checking for fetch methods (but you're right about not using or die). From the DBI docs: "If there are no more rows or if an error occurs, then fetchrow_array returns an empty list. You should check $sth->err afterwards (or use the RaiseError attribute) to discover if the empty list returned was due to an error." (emphasis mine)

mpapec Over a year ago

@ysth but makes much less sense when using multiple DBI instances

anaconda_wly Over a year ago

I copied and run the $sql on command line and can return me 5 records. So I didn't know why I'm fetching the empty array.

anaconda_wly Over a year ago

the $sth->err returns "1" and the $DBI::errstr returns Unable to fetch information about the error. Seems no more useful message for me.

mpapec Over a year ago

@anaconda_wly does query returns something on command line? Try $dbh->{RaiseError} = 1; for automated error checking.

nick_v1 · Accepted Answer · 2013-12-18 17:13:26Z

0

Try changing the variable $id in your sql query to ? and then pass the variable through execute. Like $execute->execute($id)

answered Dec 18, 2013 at 17:13

nick_v1

1,6841 gold badge20 silver badges29 bronze badges

Comments

Guntram Blohm · Accepted Answer · 2013-12-18 17:16:29Z

It seems to me that the execute causes your mysql server to crash. This is the only reason i can think of that would explain the "unable to fetch information about the error". If you're using safe_mysqld, you might not even notice since it gets restarted automatically.

If that is on a web site, and you get this error once in a while, then someone is trying to hack you by putting stuff like 3' or 'a'='a in your form (try what happens if you replace id with this string in your question). If they get the format wrong, the server might try to execute anything. To prevent this kind of SQL Injection attack, rewrite your query to

my $sql = <<"sqleof";
select t1.name t1_name, t2.name t2_name from t2
    inner join t1 using(Id)
    where t2.Id in (select Id from t3 where t3Id=?) 
sqleof

and call $execute->execute($id).

Collectives™ on Stack Overflow

Perl DBD fetchrow_array error:

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related