How big is my result set?
Hi, I've a google search showed me this question has been mentioned, but never seems to be asked or anwered directly: I am running SE, and have made a select cursor I traverse using "Next" and "Prev" buttons in my C application. I want my buttons to becomes insesnsitive when I hit either the start or the end of the set ( cant go further, and want the user to know that ). I have a function that figures out if I'm at the start or end, even taking into account any records I've deleted from my set ( but which still have a blank entry ). My application skips over the blank entries. Unfortunately, I need to know what the theoretically last entry in the set is, or, in other words, how big is the set I just selected? At the moment I traverse the list just after selection but before display, counting as I go. For a set of 6500 odd records this takes well over a minute on my hardware. I could use a count(*) but it would have to be a seperate call. I'm worried that if an entry is added or deleted my application may either never think it is at the end, or will miss a record. Is there a way to embed the count(*) into my select statement I use for my cursor, returning the total count of all rows on each row? My select statements are always in the form "SELECT <key value> FROM <table> WHERE ..." with no order by, groups etc. -- Scott Burns Mirrabooka Systems Tel +61 7 3857 7899 Fax +61 7 3857 1368
You can use SQLCA structure - more specific sqlca.sqlerrd[0], but this is only an estimate(!). Documentation says, that actual number of rows in a result set is not guaranteed to be accurate unless you've scrolled through the whole result set. You can get more information on SQLCA record on: http://www.dbcenter.cise.ufl.edu/triggerman/InfoShelf/esqlc/11.fm3.html Two more good sites are: http://www.dbcenter.cise.ufl.edu/triggerman/InfoShelf/index/global.html and http://www.docs.rinet.ru:8080/InforSmes/index.htm Gorazd "Scott Burns" <scott@mirrabooka.com> wrote in message news:woR7b.1205$U74.71670@news.optus.net.au... > > Hi, > I've a google search showed me this question has been mentioned, but > never seems to be asked or anwered directly: > > I am running SE, and have made a select cursor I traverse using "Next" > and "Prev" buttons in my C application. I want my buttons to becomes > insesnsitive when I hit either the start or the end of the set ( cant go > further, and want the user to know that ). > > I have a function that figures out if I'm at the start or end, even > taking into account any records I've deleted from my set ( but which > still have a blank entry ). My application skips over the blank > entries. Unfortunately, I need to know what the theoretically last > entry in the set is, or, in other words, how big is the set I just selected? > > At the moment I traverse the list just after selection but before > display, counting as I go. For a set of 6500 odd records this takes > well over a minute on my hardware. I could use a count(*) but it would > have to be a seperate call. I'm worried that if an entry is added or > deleted my application may either never think it is at the end, or will > miss a record. Is there a way to embed the count(*) into my select > statement I use for my cursor, returning the total count of all rows on > each row? > > My select statements are always in the form "SELECT <key value> FROM > <table> WHERE ..." with no order by, groups etc. > > > -- > Scott Burns > Mirrabooka Systems > > Tel +61 7 3857 7899 > Fax +61 7 3857 1368 >
Gorazd Hribar Rajteric wrote: >You can use SQLCA structure - more specific sqlca.sqlerrd[0], but this is >only an estimate(!). Documentation says, that actual number of rows in a >result set is not guaranteed to be accurate unless you've scrolled through >the whole result set. You can get more information on SQLCA record on: >http://www.dbcenter.cise.ufl.edu/triggerman/InfoShelf/esqlc/11.fm3.html > >Two more good sites are: >http://www.dbcenter.cise.ufl.edu/triggerman/InfoShelf/index/global.html and >http://www.docs.rinet.ru:8080/InforSmes/index.htm > >Gorazd > > > Thanks for the pointers. I'm actually using dead tree manuals from version 4.0, so I missed that feild. My manual says that it is not used at present... Of course, it also has a handwritten note in it to the effect that the structure is wrong, and I should look at the _old_ book :) I'll have to experiment and see how accurate the estimate is. -- Scott Burns Mirrabooka Systems Tel +61 7 3857 7899 Fax +61 7 3857 1368
If you're using IDS 7.3x there is some very useful documentation available at my site: http://www.klimaexpert.com/gorazd/informix/index.html Gorazd "Scott Burns" <scott@mirrabooka.com> wrote in message news:5MV7b.1223$U74.72176@news.optus.net.au... > Gorazd Hribar Rajteric wrote: > > >You can use SQLCA structure - more specific sqlca.sqlerrd[0], but this is > >only an estimate(!). Documentation says, that actual number of rows in a > >result set is not guaranteed to be accurate unless you've scrolled through > >the whole result set. You can get more information on SQLCA record on: > >http://www.dbcenter.cise.ufl.edu/triggerman/InfoShelf/esqlc/11.fm3.html > > > >Two more good sites are: > >http://www.dbcenter.cise.ufl.edu/triggerman/InfoShelf/index/global.html and > >http://www.docs.rinet.ru:8080/InforSmes/index.htm > > > >Gorazd > > > > > > > Thanks for the pointers. I'm actually using dead tree manuals from > version 4.0, so I missed that feild. My manual says that it is not used > at present... > > Of course, it also has a handwritten note in it to the effect that the > structure is wrong, and I should look at the _old_ book :) > > I'll have to experiment and see how accurate the estimate is. > > -- > Scott Burns > Mirrabooka Systems > > Tel +61 7 3857 7899 > Fax +61 7 3857 1368 >