Stupid newbie question?

Hey guys, I'm very green at DBI stuff, so maybe this is a stupid question.
But after going through the DBI man page and searching the web for a
couple hours, I can't find the answer. All I'm looking to do is to walk
the result set from a select statement more than once. Every example I can
find follows the form:

prepare, execute, fetch, fetch, fetch, ..., finish

What I would *like* to be able to do is walk the entire result set, and
then, once I get to the end, reset the statement handle to point the fetch
calls back to the top, and then walk them again. That seems so simple. Is
there really no "reset" command? Do I actually have to execute the select
again?

RE: Stupid newbie question?

Isn't this an ODBC thing? Did you look at the DBD::ODBC docs?

--
Ron Reidy
Lead DBA
Array BioPharma, Inc.

-----Original Message-----
From: Ben [mailto:bench@silentmedia.com]=20
Sent: Friday, June 23, 2006 1:48 PM
To: dbi-users@perl.org
Subject: Stupid newbie question?

Hey guys, I'm very green at DBI stuff, so maybe this is a stupid
question.=20
But after going through the DBI man page and searching the web for a=20
couple hours, I can't find the answer. All I'm looking to do is to walk=20
the result set from a select statement more than once. Every example I
can=20
find follows the form:

prepare, execute, fetch, fetch, fetch, ..., finish

What I would *like* to be able to do is walk the entire result set, and=20
then, once I get to the end, reset the statement handle to point the
fetch=20
calls back to the top, and then walk them again. That seems so simple.
Is=20
there really no "reset" command? Do I actually have to execute the
select=20
again?

This electronic message transmission is a PRIVATE communication which =
contains
information which may be confidential or privileged. The information is =
intended=20
to be for the use of the individual or entity named above. If you are =
not the=20
intended recipient, please be aware that any disclosure, copying, =
distribution=20
or use of the contents of this information is prohibited. Please notify =
the
sender of the delivery error by replying to this message, or notify us =
by
telephone (877-633-2436, ext. 0), and then delete it from your system.

Re: Stupid newbie question?

Ben wrote:
> Hey guys, I'm very green at DBI stuff, so maybe this is a stupid
> question. But after going through the DBI man page and searching the web
> for a couple hours, I can't find the answer. All I'm looking to do is to
> walk the result set from a select statement more than once. Every
> example I can find follows the form:
>
> prepare, execute, fetch, fetch, fetch, ..., finish
>
> What I would *like* to be able to do is walk the entire result set, and
> then, once I get to the end, reset the statement handle to point the
> fetch calls back to the top, and then walk them again. That seems so
> simple. Is there really no "reset" command? Do I actually have to
> execute the select again?
>
>
>
>
So long as you are not bothered about the result-set changing between
the first and second pass do:

$rs = $dbh->selectall_arrayref($sql, undef, $p1, $p2 ...)

or (selectall_hashref).

then:

while (my $row = @{$rs}) {
print $row->[0]; # first column, n'th row
}

Make sure RaiseError is on or check for $dbh->err;

If you are bothered about a change run selectall_arrayref twice.
However, I'd question why you need to process the result-set
twice IF you are not bothered about it changing. If the result-set
can change then you are talking cursors that pick up changes
and that is very db specific.

Martin

Re: Stupid newbie question?

Your best bet (and fastest) would be to use

fetchall_arrayref()

like this

my $sth = $dhh->prepare("select * from my_countries");
$sth->execute();
my $reff_array = $sth->fetchall_arrayref();

then you can use $ref_array anyway you want without going back to the DB.

foreach my $row (@$reff_array )
{
....

}



Like the others said the data in the array will be stale but I do not think
you care to much about that.

The point here to remeber is that DBI result sets forward only reads.

cheers

RE: Stupid newbie question?

Concerning stale data, depending on your application's design, you might
want to set AutoCommit to 0 (off). You can do this as an option in
connect, like this:

my $dbh =3D DBI->connect( ... { AutoCommit =3D> 0 } );

Once you do this, the underlying database will treat all your DB
operations as a single transaction (if the DB supports transactions,
which most do) until you issue either a commit or a rollback.

Within a transaction, all operations are atomic and consistent.

-Will


> -----Original Message-----
> From: John Scoles [mailto:scoles@pythian.com]=20
> Sent: Monday 26 June 2006 07:29
> To: dbi-users@perl.org
> Subject: Re: Stupid newbie question?
>=20
>=20
> Your best bet (and fastest) would be to use
>=20
> fetchall_arrayref()
>=20
> like this
>=20
> my $sth =3D $dhh->prepare("select * from my_countries");
> $sth->execute();
> my $reff_array =3D $sth->fetchall_arrayref();
>=20
> then you can use $ref_array anyway you want without going=20
> back to the DB.
>=20
> foreach my $row (@$reff_array )
> {
> ....
>=20
> }
>=20
>=20
>=20
> Like the others said the data in the array will be stale but=20
> I do not think
> you care to much about that.
>=20
> The point here to remeber is that DBI result sets forward only reads.
>=20
> cheers
>=20
>=20
>=20
>=20


- - - - - Appended by Scientific Atlanta, a Cisco company - - - - -=20
This e-mail and any attachments may contain information which is confidenti=
al, proprietary, privileged or otherwise protected by law. The information =
is solely intended for the named addressee (or a person responsible for del=
ivering it to the addressee). If you are not the intended recipient of this=
message, you are not authorized to read, print, retain, copy or disseminat=
e this message or any part of it. If you have received this e-mail in error=
, please notify the sender immediately by return e-mail and delete it from =
your computer.