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

The short answer is ... No.

The long answer is ...

How could the DBI (or the SQL and/or PL/SQL engine for that matter) know
in advance of getting the data, how much would be retrieved? According
to the concepts manual, part II, chapter 13 for 10gR2:

In a single-user database, the user can modify data in the database
without concern for
other users modifying the same data at the same time. However, in a
multiuser
database, the statements within multiple simultaneous transactions can
update the
same data. Transactions executing at the same time need to produce
meaningful and
consistent results.

Given this, there is no way for the DBI to know how much data will be
retrieved by a SELECT statement.

--
Ron Reidy
Lead DBA
Array BioPharma, Inc.

-----Original Message-----
From: listmail@xxxxxxxxxxxx [mailto:listmail@xxxxxxxxxxxx]
Sent: Wednesday, November 16, 2005 2:27 PM
To: dbi-users@xxxxxxxx
Subject: anyway to determine # rows before fetch loop ends and without
seperate count(*)


#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

This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information is intended
to be for the use of the individual or entity named above. If you are not the
intended recipient, please be aware that any disclosure, copying, distribution
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.

.

Relevant Pages