Re: # of rows in cursor

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