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.
mysql perl
asked Nov 16 '18 at 8:06
Sergio D. CaplanSergio D. Caplan
199110
add a comment |
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.
mysql perl
asked Nov 16 '18 at 8:06
Sergio D. CaplanSergio D. Caplan
199110
2
There are no pointers in Perl. Do you mean references?
– simbabque
Nov 16 '18 at 9:41
add a comment |
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.
mysql perl
asked Nov 16 '18 at 8:06
Sergio D. CaplanSergio D. Caplan
199110
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.
mysql perl
mysql perl
asked Nov 16 '18 at 8:06
Sergio D. CaplanSergio D. Caplan
199110
asked Nov 16 '18 at 8:06
Sergio D. CaplanSergio D. Caplan
199110
asked Nov 16 '18 at 8:06
Sergio D. CaplanSergio D. Caplan
199110
asked Nov 16 '18 at 8:06
Sergio D. CaplanSergio D. Caplan
199110
asked Nov 16 '18 at 8:06
Sergio D. CaplanSergio D. Caplan
199110
199110
2
There are no pointers in Perl. Do you mean references?
– simbabque
Nov 16 '18 at 9:41
add a comment |
2
There are no pointers in Perl. Do you mean references?
– simbabque
Nov 16 '18 at 9:41
2
2
There are no pointers in Perl. Do you mean references?
– simbabque
Nov 16 '18 at 9:41
There are no pointers in Perl. Do you mean references?
– simbabque
Nov 16 '18 at 9:41
add a comment |
1 Answer
1
active
oldest
votes
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%'/);
answered Nov 16 '18 at 8:26
ShawnShawn
3,7521613
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"; } }`
– Sergio D. Caplan
Nov 16 '18 at 18:49
@PlasticProgrammer A foreach loop is a lot more idiomatic and easier to read those C-style for loops. Combining a suffix-style loop withjoinis one handy approach:say "----n", join("n", @{$_}) for @rows;
– Shawn
Nov 16 '18 at 19:50
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53333739%2fhow-can-i-grab-multiple-records-from-a-mysql-query-in-perl-using-array-pointers%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%'/);
answered Nov 16 '18 at 8:26
ShawnShawn
3,7521613
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"; } }`
– Sergio D. Caplan
Nov 16 '18 at 18:49
@PlasticProgrammer A foreach loop is a lot more idiomatic and easier to read those C-style for loops. Combining a suffix-style loop withjoinis one handy approach:say "----n", join("n", @{$_}) for @rows;
– Shawn
Nov 16 '18 at 19:50
add a comment |
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%'/);
answered Nov 16 '18 at 8:26
ShawnShawn
3,7521613
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"; } }`
– Sergio D. Caplan
Nov 16 '18 at 18:49
@PlasticProgrammer A foreach loop is a lot more idiomatic and easier to read those C-style for loops. Combining a suffix-style loop withjoinis one handy approach:say "----n", join("n", @{$_}) for @rows;
– Shawn
Nov 16 '18 at 19:50
add a comment |
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%'/);
answered Nov 16 '18 at 8:26
ShawnShawn
3,7521613
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%'/);
answered Nov 16 '18 at 8:26
ShawnShawn
3,7521613
edited Nov 16 '18 at 10:11
answered Nov 16 '18 at 8:26
ShawnShawn
3,7521613
answered Nov 16 '18 at 8:26
ShawnShawn
3,7521613
answered Nov 16 '18 at 8:26
ShawnShawn
3,7521613
3,7521613
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"; } }`
– Sergio D. Caplan
Nov 16 '18 at 18:49
@PlasticProgrammer A foreach loop is a lot more idiomatic and easier to read those C-style for loops. Combining a suffix-style loop withjoinis one handy approach:say "----n", join("n", @{$_}) for @rows;
– Shawn
Nov 16 '18 at 19:50
add a comment |
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"; } }`
– Sergio D. Caplan
Nov 16 '18 at 18:49
@PlasticProgrammer A foreach loop is a lot more idiomatic and easier to read those C-style for loops. Combining a suffix-style loop withjoinis one handy approach:say "----n", join("n", @{$_}) for @rows;
– Shawn
Nov 16 '18 at 19:50
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"; } }`
– Sergio D. Caplan
Nov 16 '18 at 18:49
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"; } }`
– Sergio D. Caplan
Nov 16 '18 at 18:49
@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;– Shawn
Nov 16 '18 at 19:50
@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;– Shawn
Nov 16 '18 at 19:50
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53333739%2fhow-can-i-grab-multiple-records-from-a-mysql-query-in-perl-using-array-pointers%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
There are no pointers in Perl. Do you mean references?
– simbabque
Nov 16 '18 at 9:41