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

Question

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.

There are no pointers in Perl. Do you mean references?

simbabque
– simbabque

2018-11-16 09:41:02 +00:00
Commented Nov 16, 2018 at 9:41 — simbabque
– simbabque, Commented Nov 16, 2018 at 9:41

Shawn · Accepted Answer · 2018-11-16 10:11:55Z

3

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%'/);

edited Nov 16, 2018 at 10:11

answered Nov 16, 2018 at 8:26

Shawn

53.9k3 gold badges29 silver badges74 bronze badges

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

2 Comments

Daniel Kaplan Over a year ago

Thanks, it works.... here is how I read the reference that is passed back. Please let me know if there's a more proper way to do so. Thanks again. ` my $arrsize = @rows; for (my $x =0; $x < $arrsize; $x++) { my $insize = @{$rows[$x]}; print "-------------\n"; for(my $y = 0; $y < $insize; $y++) { print "$rows[$x][$y]\n"; } }`

Shawn Over a year ago

@PlasticProgrammer A foreach loop is a lot more idiomatic and easier to read those C-style for loops. Combining a suffix-style loop with join is one handy approach: say "----\n", join("\n", @{$_}) for @rows;

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related