Re: EXECUTE ... SELECT COUNT(*) ... INTO ?

Hi Jake, }From: Jake Salomon <jsalomon@sprynet.com> }Date: Tue, 10 Dec 1996 23:46:44 -0500 }X-Informix-List-Id: <news.31513> } }Bryan Tonnet wrote: }... }> You are correct because that's the way it works. But when I saw the }> following in the SQL syntax guide V7.1 (Pg 1-251)... }> }> "The INTO clause [of the EXECUTE statement] provides a concise and }> efficient alternative to using more complicated and lengthy syntax... }> ...if you use the INTO clause, you do not have to use the PREPARE, }> DECLARE, OPEN and FETCH sequence of statements to retrieve values }> from a table" }> }> Fairly cut and dried, and to my mind, making a lot of sense for singleton }> SELECTs. But it doesn't work (at least in 4gl). } }Yikes!! Bryan, that piece of documentation is talking about the } EXECUTE <stored procedure> }command. You were talking about the EXECUTE <prepared statement> }command. No, I'm sorry, but p1-251 is on EXECUTE (not EXECUTE IMMEDIATE, which starts on p1-253, nor EXECUTE PROCEDURE, which starts on p1-256). And 7.1x introduces an extension to the pre-7 ESQL/C which means that EXECUTE can have an INTO clause, provided of course that the SELECT or EXECUTE PROCEDURE which is returning data returns just one row of data. }Strangely enough, even version 6.x of 4GL cannot comprehend the EXECUTE }PROCEDURE statement. Not very strange; 6.0x I4GL is I4GL 4.1y with 6.00 connectivity (where y = x + 2). This has always been the case, and, I regret to say, as far as I can tell always will be the case. }In 4GL, even to get the single row returned from a }stored procedure required you to PREPARE the "EXECUTE PROCEDURE" }statement, declare a cursor for it, open cursor and fetch cursor. } }For ESQL/C, you can still } EXEC SQL EXECUTE PROCEDURE yadayada INTO :var1, :var2; }but only for a procedure that returns a single row. If the proc returns }multiple rows you must still use the cursor. } }But this is a digression. The main point is still that prepared }statement that returns ANY value can be used ONLY with a cursor. You need to read the 7.x manuals again. Time passes, and things change. }> >However, I have hated the -4373 message since the first day I saw it! }> >YUCHH! SO exquisitely meaninless!! ;-)) }> }> No argument there. Personally, I'm in favour of just one error number }> that covers all possibilities. Something like "Error -1. You stuffed it." } }Why do I talk to these extremists? ;-) I agree -- error -4373 gives way too much information and should immediately be replaced by error -201 (a syntax error has occurred) :-( Actually, I agree that -4373 is not a very useful message, but it is one better than no message. Interpret it as the compiler saying 'you managed to confuse me enough that I do not know what is the correct error message to give here'. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>