|
Re: use of DBI perl function fetchall_arrayref
by imp (Priest) on Jan 25, 2007 at 22:18 UTC
|
# Create an anonymous arrayref
my $arrayref = [1,2,3];
# Create a reference to an existing array
my @array = (1,2,3);
my $arrayref2 = \@array;
# Get a single value from the arrayref:
my $one = $arrayref2->[0];
# Get all the values from the arrayref:
my ($one,$two,$three) = @$arrayref2;
In the code you posted you wrote:
[$sql_return]
Which is putting the arrayref provided by DBI inside another arrayref. Instead do this:
@$sql_return
It might help you visualize the data structure if you use Data::Dumper, like this:
use Data::Dumper;
#...
my $sql_return = $sth_tss->fetchall_arrayref;
print Dumper $sql_return;
| [reply]
[d/l]
[select] |
|
|
and if you want to be explicit in dereferencing, enclose the references in braces. this sometimes helps increase readability:
# dereferencing an array
print @$reference;
# EXPLICITLY dereferencing an array
print @{$reference};
__________
The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it.
- Terry Pratchett
| [reply]
[d/l] |
Re: use of DBI perl function fetchall_arrayref
by ysth (Canon) on Jan 25, 2007 at 22:19 UTC
|
[$sql_return] creates another reference to an array containing the array of arrays. You want @$sql_return instead. See References quick reference.
| [reply]
[d/l]
[select] |
Re: use of DBI perl function fetchall_arrayref
by chakram88 (Pilgrim) on Jan 25, 2007 at 23:28 UTC
|
Take a look at the perlref tutorial. Specifically Use Rule 1 applies directly to your question "how to access them"
Whatever you want to do with a reference, Use Rule 1 tells you how to do it. You just write the Perl code that you would have written for doing the same thing to a regular array or hash, and then replace the array or hash name with {$reference} .
| [reply]
[d/l] |
Re: use of DBI perl function fetchall_arrayref
by bradcathey (Prior) on Jan 26, 2007 at 02:54 UTC
|
Good questions.
You absolutely have to read gmax's DBI Recipes (plus anything else you can find of his). Anyway, his recipes outlined in that node have been woven into the fabric of all our code that interacts with MySQL, especially in conjunction with HTML::Template. You will not regret it.
—Brad
"The important work of moving the world forward does not wait to be done by perfect men." George Eliot
| [reply] |
Re: use of DBI perl function fetchall_arrayref
by graff (Chancellor) on Jan 26, 2007 at 13:39 UTC
|
My standard idiom for this sort of thing goes like this:
my $rows = $sth->fetchall_arrayref;
for my $row ( @$rows ) {
my @fields = @$row;
# do something with @fields...
}
# or, if the per-field activity is pretty dense:
for my $row ( @$rows ) {
for my $field ( @$row ) {
# do something with $field...
}
}
No doubt there are other approaches that some would consider more "elegant" or "clever", but usually when dealing with basic query activity like this, the plain and simple loops with descriptive variable names is just the easiest way. | [reply]
[d/l] |
|
|
Ok, Here is what I've got:
my $rows = $sth_tss->fetchall_arrayref;
$count = 0;
print $CGI->p("rows is ", @$rows);
for my $row ( @$rows ) {
$CGI->p("Row", ++$count);
my @fields = @$row;
for my $field ( $fields ) {
print $CGI->p($field);
}
}
and the code it generates is:
ARRAY(0x40285608) ARRAY(0x402856a4) ARRAY(0x40285740) ARRAY(0x402
RRAY(0x40285878) ARRAY(0x402860e0) ARRAY(0x4028617c) ARRAY(0x40286218)
+ A
02862b4) ARRAY(0x40286350) ARRAY(0x402863ec) ARRAY(0x40286488) ARRAY(0
+x4
ARRAY(0x40287104) ARRAY(0x402871a0) ARRAY(0x4028723c) ARRAY(0x402872d
+8)
x40287374) ARRAY(0x40287fbc) ARRAY(0x40288058) ARRAY(0x402880f4) ARRAY
+(0
0) ARRAY(0x4028822c) ARRAY(0x402882c8) ARRAY(0x40288364) ARRAY(0x40288
+f3
(0x40288fd8) ARRAY(0x40289074) ARRAY(0x40289110) ARRAY(0x402891ac) ARR
+AY
248) ARRAY(0x40289e90) ARRAY(0x40289f2c) ARRAY(0x40289fc8) ARRAY(0x402
+8a
AY(0x4028a100) ARRAY(0x4028a19c) ARRAY(0x4028ae14) ARRAY(0x4028aeb0) A
+RR
8af4c) ARRAY(0x4028afe8) ARRAY(0x4028b084) ARRAY(0x4028b120) ARRAY(0x4
+02
RRAY(0x4028bddc) ARRAY(0x4028be78) ARRAY(0x4028bf14) ARRAY(0x4028bfb0)
+ A
028c04c) ARRAY(0x4028c0e8) ARRAY(0x4028d100) ARRAY(0x4028d19c)
and so on. What am I doing wrong? | [reply]
[d/l]
[select] |
|
|
for my $field ( $fields ) {
That should probably be:
for my $field ( @fields ) {
use strict would catch typos like that.
| [reply]
[d/l]
[select] |
|
|
|
|
|
|
|
|
You said that you ran this piece of code., Looking at it you have three print statements that do not appear to be printing out anything similiar to what you posted.
For example each ARRAY reference should be between p() tags yet it is one long wrapped string.
Are you sure you looking at the right location for this code?
| [reply] |
|
|
print $CGI->p("rows is ", @$rows);
is telling perl to stringify a list of array references and print them out as strings to your web page. And that is exactly what you are seeing. Those "ARRAY(0x....)" things are perl's normal way of converting references into strings.
Others have pointed other problems with the code, which you might have fixed. Do pay special attention to every "@" and "$", and to the (absence of) spaces around them -- perl is especially picky about those details, because it needs to be.
| [reply]
[d/l] |
Re: use of DBI perl function fetchall_arrayref
by ptum (Priest) on Jan 26, 2007 at 17:53 UTC
|
It sounds like others have already pointed you to some good resources for derefencing, etc., but there are two things which are conspicuous in their absence from your code:
- You don't check if the fetchall_arrayref was successful, in terms of displaying $DBI::errstr.
- You don't check if the array reference is really an array reference.
I usually do something like this:
my $ary_ref = $sth->fetchall_arrayref();
if ($DBI::errstr) {
print "Error detected: $DBI::errstr\n";
return;
}
else {
if (ref($ary_ref) eq 'ARRAY') {
# go on to process the array reference.
}
else {
# do something with Data::Dumper
}
}
| [reply]
[d/l] |
|
|
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.
| [reply] |
