RE: Stored procs: detecting when SELECT returns no records

I have been wrestling with this as well. If you check TFM, Guide to SQl: Syntax, version 7.1, pg 1-568 lower part of the page. This is a discussion on the use of 'DBINFO', and that using DBINFO to get 'sqlca.sqlerrd2', will tell you how many rows were processed. I quote: "The 'sqlca.sqlerrd2' option returns a single integer that provides the number of rows processed by SELECT, INSERT, DELETE, UPDATE, and EXECUTE PROCEDURE statements. You can use this option anywhere within SQL statements and stored procedures. This option also applies to all SQL APIs." Now, according to the documentation, this addresses my, and a lot of other peoples problems. However, I've discovered that it doesn't quite work for everything, in particular, reporting the number of rows returned from a select statement in a stored procedure. I found that the engine returned invalid and inconsistent results. I opened a call with Informix support, and proved that the engine does NOT perform according to the documented functionality. This problem is being fixed, but is in the later releases. The PTS number is 41579, and the fix will be available in OnLine-DSA ver. 7.11.UC2 (atleast for Sequent). Jon ------------------------------ Jon C. Vemo jvemo@cyberspace.com Bothell, WA U.S.A. ---------- From: CRAIG@CHEMISTRY.CHEM.UTAH.EDU[SMTP:CRAIG@CHEMISTRY.CHEM.UTAH.EDU] Sent: Tuesday, November 07, 1995 4:14 AM To: andrew@infi.net Cc: informix-list@rmy.emory.edu Subject: RE: Stored procs: detecting when SELECT returns no records Andrew, I agree that the stored procedure documentation is a bit sparce. But, I don't believe I've ever found anything missing. Just a bit thin on examples. I've used stored procedures quite a bit and I can't recall ever having used an undocumented feature or behavior. Consider SPL a programming language like any other. In any language, there's typically several ways to solve the same problem. Take the example of your "no rows found" questions. You could use my idea of SELECT COUNT(*). Or, you could use Billy's idea of handling the insert-unique violation. You could (maybe) use the NULL values you found. Which is the "True Way"? As with any flexible language, it depends upon your needs. (Although I am quite partial to ~my~ way :) Good luck with your learning. If you come up with any questions the documentation doesn't answer, I wouldn't hesitate to post them. Syntax is best from the books. Experience is what you tap with the news groups. --Craig (craig@chemistry.utah.edu) > > Thanks for the advice, Craig. The main thing I've learned so far from > having posted is that doing this seemingly fundamental operation > (inserting new or updating old) is not as cut-and-dry as I'd figured it > would probably be. I was expecting everyone to bombard me with the One > True Way to perform this trivial task. Hardly. > > > If your documentation is not out-of-date, you should be able to > > find a chapter on stored procedures in the SQL Tutorial. If it's not > > I have the 7.1 docs. Both the Tutorial and Syntax manuals seem to me to > be very incomplete on the subject of stored procs. I'm having to learn a > lot by just trying things and observing what happens. What bothers me > about that is that undocumented behavior is subject to change in future > releases. >