Loading ...
Sorry, an error occurred while loading the content.

numRows in the oracle driver

Expand Messages
  • Lukas Smith
    Hi, I am having some trouble getting the numRows() method to work like I want it to in the oracle driver. 1) Right now numRows() works the Metabase way of
    Message 1 of 5 , Mar 10, 2003
    View Source
    • 0 Attachment
      Hi,

      I am having some trouble getting the numRows() method to work like I
      want it to in the oracle driver.

      1) Right now numRows() works the Metabase way of simply fetching all the
      rows and then returning that number.

      2) PEAR works by checking if the last query was the query for which
      numRows() was called and then doing "SELECT COUNT(*) FROM
      (".$this->last_query.")";.

      1) Was the advantage of not sending off yet another query, which is
      pretty ugly. But it makes the current while loops in fetchCol() and
      fetchAll() fail because they rely on the fact that no other method has
      messed around with the result set. I could fix that by specifying
      fetchInto the exact row I want. This will result in mysql_data_seek
      calls in the mysql driver.

      Anyways if all I want is all data and the numbers or rows that is no
      problem as I can just do fetchCol|All and do a count on the array. But
      if I first want to check the number of rows and then do a fetchAll() if
      the number is what I want then I start to run into trouble. FYI: oracle
      does not return the number of fetched rows because it starts to return
      rows before it even finished finding all. So whatever the solution for
      numRows it will go a bit against the spirit of the oracle driver anyways
      (yes I know there is ocifetchstatement() but for several reasons that is
      not a good alternative and certainly no "solution" to be problem)

      2) Gets rid of the issues if 1) but via an ugly hack.

      Anyone who cares about the oracle driver should speak up now or I will
      make a decision alone.

      Regards,
      Lukas Smith
      smith@...
      _______________________________
      BackendMedia
      www.backendmedia.com
      berlin@...

      Linn Zwoch Smith GbR
      Reuchlinstr. 10-11 Raum 4.1.6
      D-10553 Berlin (Tiergarten)

      Tel +49 30 83 22 50 00
      Fax +49 30 83 22 50 07
    • Thomas Duffey
      Hi All, For what it s worth, I ran into something like this when using Oracle with Metabase and after a quick read through the Metabase manual it was clear
      Message 2 of 5 , Mar 10, 2003
      View Source
      • 0 Attachment
        Hi All,

        For what it's worth, I ran into something like this when using Oracle
        with Metabase and after a quick read through the Metabase manual it was
        clear that numRows() should be avoided. Fortunately, it turned out to
        be quite easy to modify my application to avoid using this method at
        all. The increased portability was well worth handling row counting
        inside the application just using SELECT COUNT... as necessary.

        Best Regards,

        Thomas Duffey

        On Mon, 2003-03-10 at 22:37, Manuel Lemos wrote:
        > Hello,
        >
        > On 03/10/2003 06:40 AM, Lukas Smith wrote:
        > > I am having some trouble getting the numRows() method to work like I
        > > want it to in the oracle driver.
        > >
        > > 1) Right now numRows() works the Metabase way of simply fetching all the
        > > rows and then returning that number.
        > >
        > > 2) PEAR works by checking if the last query was the query for which
        > > numRows() was called and then doing "SELECT COUNT(*) FROM
        > > (".$this->last_query.")";.
        > >
        > > 1) Was the advantage of not sending off yet another query, which is
        > > pretty ugly. But it makes the current while loops in fetchCol() and
        > > fetchAll() fail because they rely on the fact that no other method has
        > > messed around with the result set. I could fix that by specifying
        > > fetchInto the exact row I want. This will result in mysql_data_seek
        > > calls in the mysql driver.
        >
        > Metabase handles this circumstance properly. I am not sure what was lost
        > in your MDB port.
        >
        >
        > > Anyways if all I want is all data and the numbers or rows that is no
        > > problem as I can just do fetchCol|All and do a count on the array. But
        > > if I first want to check the number of rows and then do a fetchAll() if
        > > the number is what I want then I start to run into trouble. FYI: oracle
        > > does not return the number of fetched rows because it starts to return
        > > rows before it even finished finding all. So whatever the solution for
        >
        > That is the way most high end databases work, not just Oracle.
        >
        >
        > > numRows it will go a bit against the spirit of the oracle driver anyways
        > > (yes I know there is ocifetchstatement() but for several reasons that is
        > > not a good alternative and certainly no "solution" to be problem)
        >
        > Yes, the use of NumberOfRows call is discouraged in Metabase
        > documentation for large result sets because it is slower.
        >
        >
        > > 2) Gets rid of the issues if 1) but via an ugly hack.
        > >
        > > Anyone who cares about the oracle driver should speak up now or I will
        > > make a decision alone.
        >
        > Why you are so concerned in making it differently than Metabase? Is it
        > some kind of limitation of PEAR-DB API that prevents you to map Metabase
        > behaviour?
        --
        Thomas Duffey <tduffey@...>
      • Manuel Lemos
        Hello, ... Metabase handles this circumstance properly. I am not sure what was lost in your MDB port. ... That is the way most high end databases work, not
        Message 3 of 5 , Mar 10, 2003
        View Source
        • 0 Attachment
          Hello,

          On 03/10/2003 06:40 AM, Lukas Smith wrote:
          > I am having some trouble getting the numRows() method to work like I
          > want it to in the oracle driver.
          >
          > 1) Right now numRows() works the Metabase way of simply fetching all the
          > rows and then returning that number.
          >
          > 2) PEAR works by checking if the last query was the query for which
          > numRows() was called and then doing "SELECT COUNT(*) FROM
          > (".$this->last_query.")";.
          >
          > 1) Was the advantage of not sending off yet another query, which is
          > pretty ugly. But it makes the current while loops in fetchCol() and
          > fetchAll() fail because they rely on the fact that no other method has
          > messed around with the result set. I could fix that by specifying
          > fetchInto the exact row I want. This will result in mysql_data_seek
          > calls in the mysql driver.

          Metabase handles this circumstance properly. I am not sure what was lost
          in your MDB port.


          > Anyways if all I want is all data and the numbers or rows that is no
          > problem as I can just do fetchCol|All and do a count on the array. But
          > if I first want to check the number of rows and then do a fetchAll() if
          > the number is what I want then I start to run into trouble. FYI: oracle
          > does not return the number of fetched rows because it starts to return
          > rows before it even finished finding all. So whatever the solution for

          That is the way most high end databases work, not just Oracle.


          > numRows it will go a bit against the spirit of the oracle driver anyways
          > (yes I know there is ocifetchstatement() but for several reasons that is
          > not a good alternative and certainly no "solution" to be problem)

          Yes, the use of NumberOfRows call is discouraged in Metabase
          documentation for large result sets because it is slower.


          > 2) Gets rid of the issues if 1) but via an ugly hack.
          >
          > Anyone who cares about the oracle driver should speak up now or I will
          > make a decision alone.

          Why you are so concerned in making it differently than Metabase? Is it
          some kind of limitation of PEAR-DB API that prevents you to map Metabase
          behaviour?


          --

          Regards,
          Manuel Lemos
        • Lukas Smith
          ... the ... has ... lost ... Yes I know it does. I also know why it does not work in MDB. This what the above explanation of 1) tries to make clear. I was
          Message 4 of 5 , Mar 11, 2003
          View Source
          • 0 Attachment
            > -----Original Message-----
            > From: Manuel Lemos [mailto:mlemos@...]
            > Sent: Tuesday, March 11, 2003 5:37 AM
            > To: metabase-dev@yahoogroups.com
            > Cc: pear-dev@...; dev@...
            > Subject: [PEAR-DEV] Re: [metabase-dev] numRows in the oracle driver
            >
            > Hello,
            >
            > On 03/10/2003 06:40 AM, Lukas Smith wrote:
            > > I am having some trouble getting the numRows() method to work like I
            > > want it to in the oracle driver.
            > >
            > > 1) Right now numRows() works the Metabase way of simply fetching all
            the
            > > rows and then returning that number.
            > >
            > > 2) PEAR works by checking if the last query was the query for which
            > > numRows() was called and then doing "SELECT COUNT(*) FROM
            > > (".$this->last_query.")";.
            > >
            > > 1) Was the advantage of not sending off yet another query, which is
            > > pretty ugly. But it makes the current while loops in fetchCol() and
            > > fetchAll() fail because they rely on the fact that no other method
            has
            > > messed around with the result set. I could fix that by specifying
            > > fetchInto the exact row I want. This will result in mysql_data_seek
            > > calls in the mysql driver.
            >
            > Metabase handles this circumstance properly. I am not sure what was
            lost
            > in your MDB port.

            Yes I know it does. I also know why it does not work in MDB. This what
            the above explanation of 1) tries to make clear. I was trying to get rid
            of needless row seeks. I don't know expensive those are. Maybe some of
            you can give me some pointers.

            Manuel I know that none of the decisions you have made in Metabase came
            because of lack of thought. But some things were related to PHP3, and
            some were done because you originally did not have bulk fetching etc. So
            since MDB has the possibility of doing some thing a new while keeping BC
            through the wrapper I would like to take that opportunity. I also try to
            poke your brain a bit so that I also get a better understanding of the
            reasons you had when you made your decisions.

            Regards,
            Lukas
          • Manuel Lemos
            Hello, ... Row seeks should not be expensive as they only represent updates in internal pointers that determine from where the data will be fetch next. What is
            Message 5 of 5 , Mar 11, 2003
            View Source
            • 0 Attachment
              Hello,

              On 03/11/2003 05:03 AM, Lukas Smith wrote:
              >>>I am having some trouble getting the numRows() method to work like I
              >>>want it to in the oracle driver.
              >>>
              >>>1) Right now numRows() works the Metabase way of simply fetching all
              >
              > the
              >
              >>>rows and then returning that number.
              >>>
              >>>2) PEAR works by checking if the last query was the query for which
              >>>numRows() was called and then doing "SELECT COUNT(*) FROM
              >>>(".$this->last_query.")";.
              >>>
              >>>1) Was the advantage of not sending off yet another query, which is
              >>>pretty ugly. But it makes the current while loops in fetchCol() and
              >>>fetchAll() fail because they rely on the fact that no other method
              >
              > has
              >
              >>>messed around with the result set. I could fix that by specifying
              >>>fetchInto the exact row I want. This will result in mysql_data_seek
              >>>calls in the mysql driver.
              >>
              >>Metabase handles this circumstance properly. I am not sure what was
              >
              > lost
              >
              >>in your MDB port.
              >
              >
              > Yes I know it does. I also know why it does not work in MDB. This what
              > the above explanation of 1) tries to make clear. I was trying to get rid
              > of needless row seeks. I don't know expensive those are. Maybe some of
              > you can give me some pointers.

              Row seeks should not be expensive as they only represent updates in
              internal pointers that determine from where the data will be fetch next.

              What is expensive is retrieving data and converting into PHP values
              (ZVALs) as that may imply the overhead of many memory allocation calls.


              > Manuel I know that none of the decisions you have made in Metabase came
              > because of lack of thought. But some things were related to PHP3, and
              > some were done because you originally did not have bulk fetching etc. So
              > since MDB has the possibility of doing some thing a new while keeping BC
              > through the wrapper I would like to take that opportunity. I also try to
              > poke your brain a bit so that I also get a better understanding of the
              > reasons you had when you made your decisions.

              I hope it is clean now that this issue has nothing to do with PHP 3
              limitations.

              --

              Regards,
              Manuel Lemos
            Your message has been successfully submitted and would be delivered to recipients shortly.