Re: # of rows in cursor

Sorry guys, but no. SQLCA.SQLERRD[5] gives you the offset within the sql statement where an error occurred, if any. What you are referring to is SQLCA.SQLERRD[3], which returns the number of rows processed. This however doesn't work with selects or cursors, not even if you do a fetch last (Of course, I may be wrong since I'm still using 5 engines). On the other hand, after you open a cursor SQLCA.SQLERRD[1] returns the number of estimated rows affected by the statement, and this is the exact number you would get in the sqexplain.out file, "under estimated cost", so what I do is something along these lines open my_cursor let linecount=SQLCA.SQLERRD[1] let state=-2 while state!=0 fetch absolute linecount mycursor into myvar case when status!=0 and state>0 #forward, found let linecount=linecount-1 let state=0 when status!=0 and linecount=1 #backwards, empty let linecount=0 let state=0 when status!=0 #backwards, continue scanning let linecount=linecount-1 let state=-1 when state=-1 #backwards, found let status=0 otherwise #forward, continue scan let state=1 let linecount=linecount+1 end case end while This, of course, expects to find statistics up to date... HTH, Marco ____________________________________________________________________________ rem radioterapia, which I immeritately manage, seldom agrees with what I say marco greco (Catania, Italy) Work: marcog@ctonline.it rem radioterapia 39 95 447828 fax 446558 (was mar.greco@agora.stm.it) Achea 39 95 503117 --- On Wed, 04 Dec 1996 15:07:03 GMT Nils Myklebust <Nils.Myklebust@idg.no> wrote: Daniel Wright <danwright@bigfoot.com> wrote: :Ramiro Rela wrote: :> :> How can I get the number of rows in a cursor? :> I4GL v4.10 :> TIA. :> Ramiro The whole idea of getting the number of rows in a cursor is often flawed. If you open that same cursor a second later the number may have changed. This also means you can not use select count(*) ... or select count(1) ... with the same where clause as suggested by Daniel.That only works if you are sure nothing changed in the database in the meantime. Of course if you lock the table(s) involved it will work. All this obviously have noting to do with Informix, but is a feature of multi user databases. :Well, if you haven't tried it yet, check out SQLCA.SQLERRD[5] (check :that subscript, I may be wrong) after the OPEN statement. This does not work either. I do however think it gives the right count if you fetch all the rows first (or do a fetch last if it's a scroll cursor). : (If that :doesn't work, which it may not, since it seems too easy, I think you're :stuck with doing a SELECT COUNT(1), which will be terribly inefficient) :("SELECT COUNT(1)" s/b more efficient than SELECT COUNT(*)....In :Informix, you should NEVER select more fields than you actually need.) :The same does not necessarily apply to other SQLs. :-danwright@bigfoot.com The way of nowing the number of rows in a cursor is to count them in a loop while you fetch them. That will allways work. If you for some reason *must* know the number of rows before you start doing something about them, you should allways select them into a temporary table and count the number of rows in that table. That will also allways work. In Informix such a count is also very fast, although the initial creation of the temporary table may not be. I have seen other databases that do such counts by actually fetching every row. Informix doesn't do that when there is no where clause. Remember also that a scroll cursor is implemented via such a temporary table, and may be another option that is as good as selecting into a temporary table. If you need these kinds of stable counts of number of rows you may also need to look into isolation levels to make sure all rows are locked, and don't change underneath you. That can however easily result in an inordinate number of locks. All this isn't simple due to the nature of multi user databases. Sometimes it may be better to reevaluate the original design of your programs. Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org -----------------End of Original Message-----------------