(Fwd) DBI"s method for reading [row x,field y]

----- Forwarded message from Bob Hunter -----

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




____________________________________________________________ ________________________
Be a PS3 game guru.
Get your game face on with the latest PS3 news and previews at Yahoo! Games.
http://videogames.yahoo.com/platform?platform=120121

----- End forwarded message -----

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

Hi Bob,

> ----- Forwarded message from Bob Hunter
-----
> Date: Tue, 27 Feb 2007 08:03:19 -0800 (PST)
> From: Bob Hunter
> Subject: DBI's method for reading [row x,field y]
>=20
> Tim,
>=20
> I am porting an application from Pg to DBI, and make extensive use of
> the following method:
>=20
> Returns the value of the given record and field number:
> $sth->getvalue($rn,$fn)
>=20
> In particular, I use expressions like
> $sth->getvalue($rn+$i,$fn-$j)
>=20
> 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 =3D $dbh->prepare("some sql");
$sth->execute();
my $results =3D $sth->fetchall_arrayref();

my $ntuples =3D @$results;
my $nfields =3D $sth->{NUM_OF_FIELDS};
for (my $i =3D 0; $i < $ntuples; $i++) {
for (my $j =3D 0; $j < $nfields; $j++) {
# replacement for getvalue()
my $value =3D $results->[$i][$j];
print "Value at (i,j): $value\n";
}
}

Regards,
Philip

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

The list is dead. Forwarding the message to the list
is like dropping it in the bin. If you do not care
meeting user's problems, then why should I care using
DBI in the first place? I have your book, it is
verbose, but it does not answer to key questions.
Either you write a better book, or start changing
attitude with mails. After all, you do get royalties
from the book, don't you? So, you are paid, in a
sense, to answer to questions about the book itself.

Bob





____________________________________________________________ ________________________
Be a PS3 game guru.
Get your game face on with the latest PS3 news and previews at Yahoo! Games.
http://videogames.yahoo.com/platform?platform=120121

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

Tim Bunce wrote:

>----- Forwarded message from Bob Hunter -----
>
>Date: Tue, 27 Feb 2007 08:03:19 -0800 (PST)
>From: Bob Hunter
>Subject: DBI's method for reading [row x,field y]
>To: Tim Bunce
>X-Pobox-Pass: catdogbeloved@yahoo.com 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

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

As far as I can see, this feature is not documented,
neither in perldoc DBI nor in Bunce's book. You seem
to know DBI better than the author...

Thank you.

Bob





____________________________________________________________ ________________________
Bored stiff? Loosen up...
Download and play hundreds of games for free on Yahoo! Games.
http://games.yahoo.com/games/front

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

Bob Hunter wrote:
> The list is dead. Forwarding the message to the list
> is like dropping it in the bin. If you do not care
> meeting user's problems, then why should I care using
> DBI in the first place? I have your book, it is
> verbose, but it does not answer to key questions.
> Either you write a better book, or start changing
> attitude with mails. After all, you do get royalties
> from the book, don't you? So, you are paid, in a
> sense, to answer to questions about the book itself.
>
> Bob
>

Bob, please simmer down and give some credit to both Tim and this list.
The list is not dead, it is very active and includes hundreds of DBI
users and developers. Your reasoning about Tim's pay is quite faulty,
he has put in literally years of unpaid work on DBI. (Also, do you
expect Bob Woodward to answer your questions about Watergate simply
because he got royalties from writing a book about it?) Tim regularly
forwards mail to the list when others on the list can answer, that is
how the list has always worked and it has worked well. Another reason
to keep questions on the list rather than in personal mail is that an
issue that impacts one impacts all and the only way for all to know
about it is to see it on the list.

The DBI documentation is very clear:

"The /dbi-users/ mailing list is the primary means of communication
among users of the DBI and its related modules"

So don't take Tim's forwarding your message personally, have some
patience, and someone on the list will answer your question.

--
Jeff

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

Bob Hunter [mailto:catdogbeloved@yahoo.com] wrote:

> The list is dead. Forwarding the message to the list
> is like dropping it in the bin. If you do not care
> meeting user's problems, then why should I care using
> DBI in the first place? I have your book, it is
> verbose, but it does not answer to key questions.
> Either you write a better book, or start changing
> attitude with mails. After all, you do get royalties
> from the book, don't you? So, you are paid, in a
> sense, to answer to questions about the book itself.

The list is most definitely not dead. There were 10 messages sent to the list
yesterday, and 56 sent to the list last week.
Tim does care about solving user's problems. That, in fact, is why he forwarded
your message to the list. Tim is often too busy to answer questions himself,
but he has a whole community of users on this list who are happy to help answer
questions.

Additionally, Tim is not even the author of DBD::Pg; perhaps he feels someone
else on the list will be better qualified to answer your PostGres-related
question.

Tim gets royalties for having written the book. He doesn't get paid extra for
answering questions. And yet he answers them anyway, when he is able, or
forwards them to the list for other people to answer.

The only person who needs to change his attitude is you. If you're going to be
so ungrateful, maybe we just shouldn't bother to help you.

Ronald

P.S. BTW, fetchall_arrayref() is most definitely documented in perldoc DBI and
in the Programming the Perl DBI book. If you can't find it you must not be
looking very hard. Speaking of reading the documentation...

perldoc DBI

Please note that Tim Bunce does not maintain the mailing lists or the
web page (generous volunteers do that). So please don't send mail
directly to him; he just doesn't have the time to answer questions per-
sonally. The dbi-users mailing list has lots of experienced people who
should be able to help you if you need it. If you do email Tim he's
very likely to just forward it to the mailing list.

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

The same example, using Pg...

# use Pg;
# my $dbh = Pg::connectdb("dbname=");
# my $sth = $dbh->exec("");
# for (my $i = 0; $i < $sth->ntuples; $i++) {
# for (my $j = 0; $j < $sth->nfields; $j++) {
# print "Value at ($i,$j):
$sth->getvalue($i,$j)\n";
# }}

It is more concise, and more intuitive.
It is a pity that DBI is so cumbersome.
Yes, DBI is faster and independent from specific
databases, but Pg is far more elegant. Too bad one
cannot have both worlds.




____________________________________________________________ ________________________
Get your own web address.
Have a HUGE year through Yahoo! Small Business.
http://smallbusiness.yahoo.com/domains/?p=BESTDEAL

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

Yes, you are right. Sorry.





____________________________________________________________ ________________________
Need Mail bonding?
Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users.
http://answers.yahoo.com/dir/?link=list&sid=396546091

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

------_=_NextPart_001_01C75ABD.3D9005F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

> -----Original Message-----
> From: Bob Hunter [mailto:catdogbeloved@yahoo.com]
> Sent: Tue 2/27/2007 5:07 PM
> To: Garrett, Philip (MAN-Corporate); dbi-users@perl.org
> Cc: catdogbeloved@yahoo.com
> Subject: RE: (Fwd) DBI's method for reading [row x,field y]
>=20
> The same example, using Pg...
>=20
> # use Pg;
> # my $dbh =3D Pg::connectdb("dbname=3D");
> # my $sth =3D $dbh->exec("");
> # for (my $i =3D 0; $i < $sth->ntuples; $i++) {
> # for (my $j =3D 0; $j < $sth->nfields; $j++) {
> # print "Value at ($i,$j):
> $sth->getvalue($i,$j)\n";
> # }}
>=20
> It is more concise, and more intuitive.=20
> It is a pity that DBI is so cumbersome.
> Yes, DBI is faster and independent from specific
> databases, but Pg is far more elegant. Too bad one
> cannot have both worlds.

Beauty is in the eye of the beholder. Your code example is not very
"Perlish" -- it is accessing database records at a low level like
a C-style multidimensional array, rather than as named fields in =
records.

I, for one, think this is more intuitive than your example, since it
treats the data by name rather than by some computer-assigned
numeric index:

my $dbh =3D DBI->connect;
my $sth =3D $dbh->prepare("SELECT * FROM BOOKS");
$sth->execute;
while (my $book =3D $sth->fetchrow_hashref) {
print "$book->{ISBN}: $book->{TITLE}\n";
}

or
my $sth =3D $dbh->prepare("SELECT TITLE, ISBN FROM BOOKS");
$sth->execute;
while (my ($title,$isbn) =3D $sth->fetchrow) {
print "$isbn: $title\n";
}

Regards,
Philip

------_=_NextPart_001_01C75ABD.3D9005F0--

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

Martin J. Evans wrote:
> 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.

PostgreSQL does NOT provide any mechanism to navigate in the way bob
suggests. He is using a dataset driver and this is doing a
fetchall_arrayref (equivalent) under the covers.

The other things I would point out that recent versions of
PostgreSQL will often randomly order result sets that do not have an
explicit order by clause. This is good as it is a significant
performance boost but does catch the unwary (including myself when I had
a missing order by clause!).

If the past I have found people using code similar to bobs without
an order by clause - often random results and perfomance (as system
scales) instigate a complete redesign soon after.

Jacqui