Re: SQL problem

David Williams wrote: > > 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!! > > -- Yes, you could do this if you enjoy a challenge. I would prefer FETCH LAST my_scroll_cursor ... -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 --- If all else fails, read the instructions. All opinions are my own and not those of Bayer plc. My Internet plumbing does not allow me to mail and post news together. Sorry. --- Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/