Re: (Fwd) DBI's method for reading [row x,field y]

Tim Bunce wrote:

----- Forwarded message from Bob Hunter <catdogbeloved@xxxxxxxxx> -----

Date: Tue, 27 Feb 2007 08:03:19 -0800 (PST)
From: Bob Hunter <catdogbeloved@xxxxxxxxx>
Subject: DBI's method for reading [row x,field y]
To: Tim Bunce <Tim.Bunce@xxxxxxxxx>
X-Pobox-Pass: catdogbeloved@xxxxxxxxx is whitelisted

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?

Regards,
Bob



So if I understand you correctly, getvalue() takes a row number and a column number and returns the value of the column with index $fn from the row with index $rn?

If you are not doing selects with huge result-sets then you can use the selectall_arrayref method (or fetchall_arrayref) which returns all the columns for all the rows in the select into an array reference e.g.

$af = selectall_arrayref('select * from table');

print $af->[0]->[0]; # prints first column from first row

print $af->[1]->[1]; # prints second column from second row

(assuming the default in Perl of array indexes starting at 0)

Of course, selectall/fetchall_arrayref retrieves all the rows in the result-set so this will use that amount of memory up but given you are already doing this I guess that won't bother you (unless Pg uses cursors to navigate to the required row). To my knowledge, DBI does not support fetching only a specific row via a cursor but I'm sure someone will correct me if I'm wrong on that.

Martin
--
Martin J. Evans
Easysoft Limited, UK
http://www.easysoft.com
.

Relevant Pages

  • Re: Problem with dereferencing of $_
    ... > testing the use of DBI. ... We can receive only plain text on this group: coloured highlighting ... You could rewrite the first loop as ... which will work because the fetchrow_arrayref method returns an array reference ...
    (perl.beginners)
  • Re: CURSOR WITH UPDATE
    ... On 7/11/06, Jonathan Leffler wrote: ... > From looking around with Google, it would APPEAR that you can ... > implement a cursor WITH UPDATE in DBI, but I have not been able to ... - the POD has a section CURSORS FOR UPDATE that shows how to do it. ...
    (perl.dbi.users)