Re: SQL problem

On Wed, 24 Jun 1998 17:39:02 +0200, Siebe de Klaver <siebe@interedge.nl> wrote: >Hello All, > >I'm using informix-esql/c. > >problem : I want to know the number of rows a select statement returns. > >'source' : EXEC SQL prepare exec from "SELECT FROM WHERE "; > EXEC SQL declare curs for exec; > EXEC SQL open curs; > > for > { > EXEC SQL fetch curs into ... > } > >I use sqlca.sqlerrd[2] to get the number of rows. > >This value is properly filled after all the rows are fetched. >Is it possible to get the number of rows before all the fetch's? No database in the world could on a general basis know the number of rows that will be returned from a select statement before they are actually fetched. >I'd tried fetch last followed by fetch next but that doesn`t work. Have you tried to check the sqlca.sqlerrd[2] value just after the fetch last? I don't know if it's then correct, but it should be possible for Informix to know the number at that time at least. However with cursors that doesn't use a temporary table it may be that a fetch last is also done without reading all rows so the count may not be available. A fetch next after a fetch last is never usefull. Why do you want to know this number in advance? Most of the time a design change can remove the need to know this number until you have done all the fetches. One case where one might want to show the number of rows in the cursor might be if you display the first rows in a list, but want to display the total number of rows as well. If you program in Java it's quite easy to use a separate thread to display the list and find the number of rows. Then the user can continue working (including selections from the list) while the other thread uses whatever time is needed to display and do the count. Of course a lot of background activity like this could severely load your database. Nils Myklebust NM Data AS Norway E-mail: Nils.Myklebust@nmdata.com FAQ at: http://www.iiug.org/techinfo/faq/faq_top.html (Now with ODBC info under "Third party products".)