Results 1 to 4 of 4
  1. #1
    mwilson Guest

    Answered: Oracle8, Perl, and query question

    Hello, I'm helping a co-worker get some legacy cgi scripts written in Perl migrated from MySQL to Oracle8. We used to be able to get the number of rows returned from a query back as the return value when executing a statement handle. Now that we're using Oracle, the value returned is 0E0, $sth->rows returns 0 as well. I've looked and looked and have yet to find any reference to this 'feature'. Can anyone who is currently using DBI and Oracle let me know what's going on? Is there an easy means of getting the number of rows returned from a query?

    Thanks in advance!
    Mark Wilson.

  2. Best Answer
    Posted by Vartan Narinian

    "Hi Mark,

    Have you RTFM'd (Read The Fine Manual) ?

    Pay particular attention to the last paragraph...

    execute

    $rv = $sth->execute || die $sth->errstr; $rv =
    $sth->execute(@bind_values) || die $sth->errstr;

    Perform whatever processing is necessary to execute the prepared statement. An undef is returned if an error occurs, a successful execute always returns true regardless of the number of rows affected (even if it's zero, see below). It is always important to check the return status of execute (and most other DBI methods) for errors.

    For a non-select statement, execute returns the number of rows affected (if known). If no rows were affected then execute returns "0E0" which Perl will treat as 0 but will regard as true. Note that it is not an error for no rows to be affected by a statement. If the number of rows affected is not known then execute returns -1.

    For select statements execute simply 'starts' the query within the Engine. Use one of the fetch methods to retreive the data after calling execute. The execute method does not return the number of rowsthat will be returned by the query (because most Engines can't tell in advance), it simply returns a true value.

    So I guess you don't know until you have fetched them all.

    Regards,
    Vartan
    "


  3. #2
    John D Groenveld Guest
    From DBI::FAQ
    4.5 How do I get the number of rows returned from a SELECT statement?

    Count them. Read the DBI docs for the rows() method.
    John

  4. #3
    Vartan Narinian Guest
    Hi Mark,

    Have you RTFM'd (Read The Fine Manual) ?

    Pay particular attention to the last paragraph...

    execute

    $rv = $sth->execute || die $sth->errstr; $rv =
    $sth->execute(@bind_values) || die $sth->errstr;

    Perform whatever processing is necessary to execute the prepared statement. An undef is returned if an error occurs, a successful execute always returns true regardless of the number of rows affected (even if it's zero, see below). It is always important to check the return status of execute (and most other DBI methods) for errors.

    For a non-select statement, execute returns the number of rows affected (if known). If no rows were affected then execute returns "0E0" which Perl will treat as 0 but will regard as true. Note that it is not an error for no rows to be affected by a statement. If the number of rows affected is not known then execute returns -1.

    For select statements execute simply 'starts' the query within the Engine. Use one of the fetch methods to retreive the data after calling execute. The execute method does not return the number of rowsthat will be returned by the query (because most Engines can't tell in advance), it simply returns a true value.

    So I guess you don't know until you have fetched them all.

    Regards,
    Vartan

  5. #4
    Mark Wilson Guest
    I must have missed that paragraph, sorry to waste your time and bandwidth guys...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •