Hi Bob,
> ----- Forwarded message from Bob Hunter <[EMAIL PROTECTED]>
-----
> Date: Tue, 27 Feb 2007 08:03:19 -0800 (PST)
> From: Bob Hunter <[EMAIL PROTECTED]>
> Subject: DBI's method for reading [row x,field y]
>
> Tim,
>
> I am porting an application from Pg to DBI, and make extensive use of
> the following method:
>
> Returns the value of the given record and field number:
> $sth->getvalue($rn,$fn)
>
> In particular, I use expressions like
> $sth->getvalue($rn+$i,$fn-$j)
>
> where the the number of record/field are displaced by variables. As
> far as I can see from your book, DBI has a method to work one row at
> the time, in sequence. This is way too simple to handle the case
> above. I looked for a more powerful DBI method, but it does not seem
> to exist. Is it so? Please give me an insider's view of this problem.
> Does DBI have an equivalent to Pg' method "getvalue"? If not, can you
> please copy it from Pg's module, and make it available in DBI?
If you absolutely MUST refer to query results by (x,y) coordinates,
you can use fetchall_arrayref. I don't know your application, but this
is definitely not the most efficient way to do things with large
result sets. You'd probably be better off in the long run by
converting your code to use the "while ($sth->fetch)" idiom.
# pulls entire result set from database to client
my $sth = $dbh->prepare("some sql");
$sth->execute();
my $results = $sth->fetchall_arrayref();
my $ntuples = @$results;
my $nfields = $sth->{NUM_OF_FIELDS};
for (my $i = 0; $i < $ntuples; $i++) {
for (my $j = 0; $j < $nfields; $j++) {
# replacement for getvalue()
my $value = $results->[$i][$j];
print "Value at (i,j): $value\n";
}
}
Regards,
Philip
