CODEWITHSUNDEEP
mysqlperl
Daniel Kaplan
Daniel Kaplan

Reputation: 721

How can I grab multiple records from a MySQL query in Perl using array pointers?

I can do this all as one function, but in trying to port it over to my packages of functions (library) I am missing something.

Here's what I want to do from my main Perl script

my @rows;
$result = Funx::dbcdata($myConnection, 
          "SELECT * FROM Inv where name like \"%DOG%\";", \@rows);

Then in my library package I am attempting this

sub dbcdata
{
    my ($connection, $command, $array) = @_;

    my $query = $connection->prepare($command);
    my $result = $query->execute();

    my $i =0;
    while(my $row = $query->fetchrow_arrayref() )
    {
    @{$array}[$i] = $row;
    $i++;
    }
    $query->finish;
    return $result;
}

I was hoping to get back pointers or references to each row (which was 4in this case) but am not. Every element in @rows is the same:

ARRAY(0x5577a0f77ec0) ARRAY(0x5577a0f77ec0) ARRAY(0x5577a0f77ec0) ARRAY(0x5577a0f77ec0)

Nor do I know how to turn each one into the original separate row. Any help would be appreciated, thanks.

Upvotes: 0

Views: 104

Answers (1)

Shawn
Shawn

Reputation: 52419

From the documentation for fetchrow_arrayref:

Note that the same array reference is returned for each fetch, so don't store the reference and then use it after a later fetch. Also, the elements of the array are also reused for each row, so take care if you want to take a reference to an element.

Sounds like you want fetchall_arrayref:

The fetchall_arrayref method can be used to fetch all the data to be returned from a prepared and executed statement handle. It returns a reference to an array that contains one reference per row.

After executing the statement, you can do something like

@{$array} = $query->fetchall_arrayref->@*;

instead of that ugly loop.

But selectall_array might be even better. Your whole function can be replaced by a call to it:

my @rows =
     $myConnection->selectall_array(q/SELECT * FROM Inv WHERE name LIKE '%DOG%'/);

Upvotes: 3

Related Questions