Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask
 
PerlMonks  

Counting number of rows while working with Oracle

by denzil_cactus (Sexton)
on Mar 18, 2008 at 06:29 UTC ( #674721=perlquestion: print w/ replies, xml ) Need Help??
denzil_cactus has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks, I am working on Perl with Oracle. My query will return zero or 1 row but I need to put a condition to check how many rows my query returned I have tried $sth->{NUM_OF_FIELDS} but its for number of fields in the table then I have tried print $sth->rows but its giving me the following warning $sth->rows count is incomplete before all rows fetched. The above function I am writing after $sth->execute only Please help me to find out the function for counting the number of rows the query returns Thanks

Comment on Counting number of rows while working with Oracle
Select or Download Code
Re: Counting number of rows while working with Oracle
by ikegami (Pope) on Mar 18, 2008 at 07:24 UTC

    As discussed in the CB, databases tend to not tell the client how many rows they will return. The only way for the client to know how many rows will be returned is to count them as they're being read in.

    The simplest solution would to read all the rows into memory before starting to process them. For example, replace

    while (my $row = $sth->fetch_arrayref()) { ... }

    with

    my $rows = $sth->fetchall_arrayref(); my $num_rows = @$rows; foreach my $row (@$rows) { ... }

    If you need a DBI interface, then you can replace the code with

    my $rows = $sth->fetchall_arrayref(); my $num_rows = @$rows; my $sponge = DBI->connect('dbi:Sponge:'); my $sponge_sth = $sponge->prepare( $sth->{Statement}, { rows => $rows, NAME => $sth->{NAME}, behave_like => $sth, } ); while (my $row = $sponge_sth->fetch_arrayref()) { ... }

    It's too bad that DBD::Sponge doesn't override rows.

      The only way for the client to know how many rows will be returned is to count them as they're being read in. The simplest solution would to read all the rows into memory before starting to process them.

      Especially since you say your query will only return at most a single row, using fetchall_arrayref or the even more convenient selectall_arrayref should be no problem.

        Oh, I missed that bit about returning at most one row! He can simply use

        my $row = $dbh->selectrow_arrayref($stmt_or_sth);

        or

        my $row = $sth->fetch_arrayref(); $sth->finish();

        defined($row) will tell him if a row was returned. If the row only contains one field and it can't be NULL, then he could even use

        my ($value) = $sth->fetch_array(); $sth->finish();

        defined($value) will tell him if a value was returned (again, assuming the value can't be NULL).

Re: Counting number of rows while working with Oracle
by DBAugie (Beadle) on Mar 19, 2008 at 03:06 UTC
    In SQL, the easy way to do this is:

     select count(*) from table_a

    r,

    Gus

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://674721]
Approved by ikegami
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (10)
As of 2014-12-18 09:59 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (48 votes), past polls