Re: SQL problem

In article <3591334A.41E2@bayer.co.uk>, Peter Lancashire <Peter.Lancashi re.PL1@bayer.co.uk> writes >Siebe de Klaver 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? >> >> I'd tried fetch last followed by fetch next but that doesn`t work. >> >> Any suggestions ? >> >> Thanks in advance for the responce, >> >> Siebe de Klaver >> >> s.w.deklaver@interedge.nl >No, it isn't. The database may not have actually found all the rows when >you fetch the first row. > >You can find out how many rows there are by forcing the database to put >them in a temporary table and then counting the rows in that. For many >queries, this will be less efficient. > >A scroll cursor also puts the rows in a temporary table (as a side >effect) and you can fetch the last row from that. > >Could you change your program logic so it does not need this information >at the start of the fetch loop? If all you are doing is trying to >prevent an array overflowing, why not test before each fetch? >Alternatively, use a dynamically-allocated linked list, although you'll >still need to test but this time for memory allocation failure. That you >can't do in advance! Why not do a binary search for the number of rows in the cursor. Remember a scroll cursor supports FETCH ABSOLUTE and the status = NOTFOUND if you fetch outside the range of the cursor. You could even log the number of rows in each cursor and then use the audit trail to optimize the sequence of fetches. E.g. if FETCH ABSOLUTE 100 works and 200 works then generally speaking the cursor will have >500 rows so rather than 400 try 500. If that works generally the cursor will have >1500 rows so try curosr position 1500 next!! -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care