anyway to determine # rows before fetch loop ends and without seperatecount(*)

#Here's an example which shows what I am trying to accomplish. If I
can determine the number of rows before pushing the data, this can
simply things for #me when processing the data throught my scripts.
#
use warnings;
use strict;
use DBI;
use DBD::Oracle;

my $sql=q{ select name, location
from mytable
};

my $dbh;

eval {
$dbh = DBI->connect("dbi:Oracle:MYDB",
'dbuser', 'dbpass',
{
RaiseError => 1,
AutoCommit => 0,
ora_session_mode => 0
}
);
};

if ( $@ ) {
outprint('end',"$DBI::errstr\n");
}

my $sth=$dbh->prepare($sql) or die "Couldn't prepare statement: " . DBI-
>errstr;

$sth->execute or die "Couldn't execute statement: " . DBI->errstr;

my $ary;

while ($ary = $sth->fetchrow_array()) {
#I need to determine number of rows as this will affect
whether a matrix is used or not
#a boolean variable $matrix could be returned or a ref
check done so that the data
#processing code can act accordingly
#$sth->rows only shows total rows after the while loop
is processed
#Can I accomplish this without a seperate count(*)
statement?
#
#push @newary,[ @{$ary} ]; # if more than one row
#or
#push @newary, @{$ary} ; # single row
}

$sth->finish;

$dbh->disconnect;
#
#ActivePerl 5.8.7 813
#ppm
#-DBD-Oracle 1.16
#-DBI 1.48

Re: anyway to determine # rows before fetch loop ends and withoutseperate count(*)

listmail@triad.rr.com wrote:

> #Here's an example which shows what I am trying to accomplish. If I
> can determine the number of rows before pushing the data, this can
> simply things for #me when processing the data throught my scripts.
> #
> use warnings;
> use strict;

Good good :)

> use DBI;
> use DBD::Oracle;
>
> my $sql=q{ select name, location
> from mytable
> };
>
> my $dbh;
>
> eval {
> $dbh = DBI->connect("dbi:Oracle:MYDB",
> 'dbuser', 'dbpass',
> {
> RaiseError => 1,
> AutoCommit => 0,
> ora_session_mode => 0
> }
> );
> };
>
> if ( $@ ) {
> outprint('end',"$DBI::errstr\n");
> }

Hmm, perhaps the oracle specific stuff needs it but why are you evaling
that?

my $dbh = DBI->connect(@DBI_CONNECT_ARGS) or outprint('end',
$DBI::errstr); # assumign its die()ing or exit()ing


> my $sth=$dbh->prepare($sql) or die "Couldn't prepare statement: " . DBI-
>
>>errstr;
>
>
> $sth->execute or die "Couldn't execute statement: " . DBI->errstr;
>
> my $ary;
>
> while ($ary = $sth->fetchrow_array()) {
> #I need to determine number of rows as this will affect
> whether a matrix is used or not

Also very convoluted, all of that can be done with:

my $results = $dbh->selectall_arrayref($sql); # if you only want to
process a certain amount just LIMIT in your $sql...

my $count = @{ $results };

$dbh->disconnect;

if($count < 1000) { # or whatever you wanted teh count for...
for my $record(@{ $results }) {
# now use the data:
# $record->[0]
# $record->[1]
}
}

Re: anyway to determine # rows before fetch loop ends and without seperate count(*)

On 11/16/05, Rob Craig wrote:
> I am using DBD::mysql and I can get the number of rows by assigning the
> result of the execute to a scalar:
>
> my $rows =3D $sth->execute or die "Couldn't execute statement: " .
> DBI->errstr;
>
> maybe this doesn't work with DBD::Oracle?
>
> Rob


When you execute a SELECT statement in MySQL, it (by default)
immediately returns all of the rows back to you under the covers,
which is why it is able to give you a row count. Woe betide you if
your SELECT statement returns a million rows.

>
> listmail@triad.rr.com wrote:
> #Here's an example which shows what I am trying to accomplish. If I
> can determine the number of rows before pushing the data, this can
> simply things for #me when processing the data throught my scripts.
> #
> use warnings;
> use strict;
> use DBI;
> use DBD::Oracle;
>
> my $sql=3Dq{ select name, location
> from mytable
> };
>
> my $dbh;
>
> eval {
> $dbh =3D DBI->connect("dbi:Oracle:MYDB",
> 'dbuser', 'dbpass',
> {
> RaiseError =3D> 1,
> AutoCommit =3D> 0,
> ora_session_mode =3D> 0
> }
> );
> };
>
> if ( $@ ) {
> outprint('end',"$DBI::errstr\n");
> }
>
> my $sth=3D$dbh->prepare($sql) or die "Couldn't prepare statement: " . DBI=
-
>
>
> errstr;
>
> $sth->execute or die "Couldn't execute statement: " . DBI->errstr;
>
> my $ary;
>
> while ($ary =3D $sth->fetchrow_array()) {
> #I need to determine number of rows as this will affect
> whether a matrix is used or not
> #a boolean variable $matrix could be returned or a ref
> check done so that the data
> #processing code can act accordingly
> #$sth->rows only shows total rows after the while loop
> is processed
> #Can I accomplish this without a seperate count(*)
> statement?
> #
> #push @newary,[ @{$ary} ]; # if more than one row
> #or
> #push @newary, @{$ary} ; # single row
> }
>
> $sth->finish;
>
> $dbh->disconnect;
> #
> #ActivePerl 5.8.7 813
> #ppm
> #-DBD-Oracle 1.16
> #-DBI 1.48
>
>
>
>
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.1.362 / Virus Database: 267.13.3/173 - Release Date: 11/16/200=
5
>
>
>