Fetch 2 values using selectrow_array in Perl

329
March 13, 2017, at 12:58 PM

I recently figured that there is a selectrow_array function to fetch values from databases. I'm getting following error when I'm using it. I wonder what is the issue here and couldn't find an alternative way to do this.

Code is:

my $db_connection = DBI->connect($dsn, $dbuser, $dbpassword ) or die $DBI::errstr;
my $sql_statement = "SELECT customer_id,quota FROM customer_call_quota WHERE quota>=1";
while (my $row = $db_connection->selectrow_array($sql_statement)) {
     my ($cust_id, $quota) = @$row; #<---- error line
 }
my $rc = $db_connection->disconnect ;
return "ok";

Error:

Can't use string ("value") as an ARRAY ref while "strict refs" in use at code.pl line ...
Answer 1

Two problems.

  • selectrow_array doesn't return a reference to an array. That's selectrow_arrayref.
  • selectrow_* only returns the first row.

Solutions:

# Wasteful
my $sth = $dbh->prepare($sql_statement);
$sth->execute();
while (my @row = $sth->fetchrow_array()) {
    my ($cust_id, $quota) = @row;
    ...
}

or

my $sth = $dbh->prepare($sql_statement);
$sth->execute();
while (my ($cust_id, $quota) = $sth->fetchrow_array()) {
    ...
}

or

my $sth = $dbh->prepare($sql_statement);
$sth->execute();
while (my $row = $sth->fetch()) {
    my ($cust_id, $quota) = @$row;
    ...
}
Answer 2

As documented, selectrow_array should be called once for each statement, and it returns a list of all rows returned by the query. If you want to use selectrow_array, try something like

foreach my $row ($db_connection->selectrow_array($sql_statement)) {
    my ($cust_id, $quota) = @$row;
    ...
}
READ ALSO
Beginner sql student in need of assistance

Beginner sql student in need of assistance

I'm very new to sql, and Stack OverflowI'm hoping someone can help assist me in this query

146
Why can&#39;t I insert a NULL value into a column after MySQL Re-installation?

Why can't I insert a NULL value into a column after MySQL Re-installation?

Why can't I insert a NULL value into a column after MySQL Re-installation?

168
Downgrading from python 3.6 to 3.4 on Windows

Downgrading from python 3.6 to 3.4 on Windows

I have python 36 and would like to downgrade to 3

324
Pass variables from servlet to jsp

Pass variables from servlet to jsp

How can I pass variable from servlet to jsp? setAttribute and getAttribute didn't work for me :-(

258