lincoln@aut.UUCP (Philip Lincoln) (12/13/89)
Hello world, This may be your basic stupid question, or I may be justified in asking it, but: How can (if it's possible) you tell the number of rows returned by an Informix-ESQL "open" statement that runs a declared select query?? i.e. the number of rows in the active set. The manual has been no help. Thanks, Philip. PS: The bloody weather has warmed up, and now rain (and not snow) is falling!! Sob!!
emuleomo@paul.rutgers.edu (Emuleomo) (12/15/89)
In article <590@aut.UUCP>, lincoln@aut.UUCP (Philip Lincoln) writes: > Hello world, > This may be your basic stupid question, or I may be > justified in asking it, but: > How can (if it's possible) you tell the number of rows > returned by an Informix-ESQL "open" statement that runs a > declared select query?? i.e. the number of rows in the > active set. The manual has been no help. > > Thanks, > Philip. That usually can be found in the sqlca struct that you include as a header file in your source code. I don't have the manual handy, but if you look in the manual, it explains what each fld of that sqlca struct means. Happy digging --Emuleomo O.O. (emuleomo@yes.rutgers.edu) -- ** The ONLY thing we learn from history is that we don't learn from history!
bochner@lange.harvard.edu (Harry Bochner) (12/16/89)
I don't know about ESQL, but from Informix 4gl there really doesn't seem to be a way to get at the number of rows found. If you really have to know you're forced to do a "select count(*) ...", first. That would be OK, except that it's slow. Maybe there's some system variable that contains the answer? Not as far as the manuals tell us ... What really irks me about this is that you can tell that the internals do return the answer: the preprogrammed Query-By-Example processor (perform) always informs you of the number of rows, and clearly doesn't spend any extra time on it. They've got the information, but didn't provide a 4gl interface to it. Harry Bochner bochner@endor.harvard.edu
fst@gtenmc.UUCP (fst) (12/17/89)
In article <BOCHNER.89Dec15170436@lange.harvard.edu> bochner@lange.harvard.edu (Harry Bochner) writes: > >I don't know about ESQL, but from Informix 4gl there really doesn't seem [deleted] >... "select count(*) ...", [deleted] >Harry Bochner >bochner@endor.harvard.edu extern struct sqlca_s sqlca; printf ("Number of rows processed ... %d\n", sqlca.sqlerrd[2]); Page 2-15 of (my) INFORMIX ESQL/C manual for version 2.10.XX ESQL/C Skip -- ---------------------------------------------------------------------------- Fariborz "Skip" Tavakkolian -of- Automated Cellular Engineering Currently consulting -at- GTE Telecom, Inc. Bothell, Wa Mail: tiny1!fst@mcgp1 -or- fst@gtenmc
clh@tacitus.tfic.bc.ca (Chris Hermansen) (12/18/89)
In article <BOCHNER.89Dec15170436@lange.harvard.edu> bochner@lange.harvard.edu (Harry Bochner) writes: > >I don't know about ESQL, but from Informix 4gl there really doesn't seem >to be a way to get at the number of rows found. If you really have to know What about the SQLERRD[3] in 4GL; the manual (2-72 of 4GL Rapid Development System, Volume 1) says: SQLERRD[3] is the number of rows processed ??? I don't know, not having used our 4GL much. Chris Hermansen Timberline Forest Inventory Consultants Voice: 1 604 733 0731 302 - 958 West 8th Avenue FAX: 1 604 733 0634 Vancouver B.C. CANADA uunet!ubc-cs!van-bc!tacitus!clh V5Z 1E5
john@riddle.UUCP (Jonathan Leffler) (12/18/89)
In article <BOCHNER.89Dec15170436@lange.harvard.edu> bochner@lange.harvard.edu (Harry Bochner) writes: >I don't know about ESQL, but from Informix 4gl there really doesn't seem >to be a way to get at the number of rows found. >What really irks me about this is that you can tell that the internals >do return the answer: the preprogrammed Query-By-Example processor (perform) >always informs you of the number of rows, and clearly doesn't spend any >extra time on it. They've got the information, but didn't provide a 4gl >interface to it. In article <590@aut.UUCP> lincoln@aut.UUCP (Philip Lincoln) writes: > How can (if it's possible) you tell the number of rows > returned by an Informix-ESQL "open" statement that runs a > declared select query?? i.e. the number of rows in the > active set. The manual has been no help. First, even though OPEN executes the query, it does not return any rows at all. You cannot tell how many rows will be returned by the select until you have fetched all the rows. I think you will find that Perform essentially fetches the rowids of the rows it will display, and counts them while doing it, and then fetches the first row of data using the rowid. Perform may not actually use rowid -- it should use a set of columns with a unique index on if possible -- but that is about the gist of what it does. Jonathan Leffler (john@sphinx.co.uk) #include <disclaimer.h>
ray@stevie.cs.unlv.edu (Ray Tripamer) (12/19/89)
In article <342@gtenmc.UUCP> fst@gtenmc.UUCP (Fariborz Skip Tavakkolian) writes: >In article <BOCHNER.89Dec15170436@lange.harvard.edu> bochner@lange.harvard.edu (Harry Bochner) writes: >> >>... "select count(*) ...", >>Harry Bochner >>bochner@endor.harvard.edu > >extern struct sqlca_s sqlca; >printf ("Number of rows processed ... %d\n", sqlca.sqlerrd[2]); >Page 2-15 of (my) INFORMIX ESQL/C manual for version 2.10.XX ESQL/C > >Skip This doesn't apply to select statements. sqlca.sqlerrd[2] is only for delete and update statements. The "select count(*) ..." method is the only way I know of to get the number of rows that a select statement will find. -- Ray Tripamer ray@jimi.cs.unlv.edu
aland@infmx.UUCP (Dr. Scump) (12/19/89)
In article <132@tacitus.tfic.bc.ca> clh@tacitus.UUCP (Chris Hermansen) writes: >In article <BOCHNER.89Dec15170436@lange.harvard.edu> bochner@lange.harvard.edu (Harry Bochner) writes: >>I don't know about ESQL, but from Informix 4gl there really doesn't seem >>to be a way to get at the number of rows found. If you really have to know >What about the SQLERRD[3] in 4GL; the manual (2-72 of 4GL Rapid Development >System, Volume 1) says: > >SQLERRD[3] is the number of rows processed > >??? I don't know, not having used our 4GL much. >Chris Hermansen Timberline Forest Inventory Consultants That third value of sqlerrd (sqlerrd[3] in 4GL and ESQL/COBOL, sqlerrd[2] in ESQL/C) is set only after "executable statements", which excludes cursor operations. For example, if you run an UPDATE, INSERT, or DELETE statement (not using WHERE CURRENT OF on an open cursor), that sqlerrd value does reflect the number of rows updated, inserted [always 0 or 1], or deleted. Also, if you run a SELECT ... INTO TEMP, the number of rows selected/inserted *is* reflected in sqlerrd[x] (since no cursors are involved). Since the number of rows in a cursor can change (depending on what isolation level is in effect), the OPEN and FETCH statements *do not* set sqlerrd[x]. If you *have* to know the approximate number of rows in advance, you can run a select count(*) from ... using the same WHERE clause. -- Alan S. Denney @ Informix Software, Inc. {pyramid|uunet}!infmx!aland "I want to live! -------------------------------------------- as an honest man, Disclaimer: These opinions are mine alone. to get all I deserve If I am caught or killed, the secretary and to give all I can." will disavow any knowledge of my actions. - S. Vega
sullivan@aqdata.uucp (Michael T. Sullivan) (12/20/89)
From article <2854@infmx.UUCP>, by aland@infmx.UUCP (Dr. Scump): > > Since the number of rows in a cursor can change (depending on what > isolation level is in effect), the OPEN and FETCH statements *do not* > set sqlerrd[x]. If you *have* to know the approximate number of rows > in advance, you can run a select count(*) from ... using the same > WHERE clause. Somebody else mentioned perform and I am curious about it as well. How does a query in perform know how many rows are returned by a query? -- Michael Sullivan uunet!jarthur.uucp!aqdata!sullivan aQdata, Inc. San Dimas, CA