Re: Problem with Views AND Stored Procedures

Hi, I've clearly offended people by suggesting that the error message gives the accurate answer to the problem -- there are too many values returned from a stored procedure. I apologise for the offense caused, but... I agree that it should be explicitly documented that a stored procedure used in the SELECT-list of a SELECT statement (and probably in the SET clauses of UPDATE statements too) should only return a single value (single row, single column). I have sent this email to the doc@informix.com alias so that the next version of the manuals can either note this restriction or note that it has been lifted, whichever is more appropriate. The version 7.20 manuals (Informix Guide to SQL: Reference and Tutorial) do not mention the restriction. The documentation does not say that it is possible to use a stored procedure which returns more than one value in a single RETURN statement in the SELECT-list, any more than it notes that is is impossible -- it is silent on the subject. I also agree that it might be possible for an extension to the language to allow a stored procedure which returns more than one value to be used in the context of the SELECT-list of a SELECT statement. However, I think the future directions of SQL (eg SQL-3) imply that multiple-return values are more likely to be outlawed altogether than to accepted in the SELECT-list of a SELECT statement. However, I still think that the problem is deducible from the error message, whether the documentation says anything about it or not. The error message is quite explicit: procedure xyz returns too many values. This is a concise way of saying that the stored procedure cannot be used in the context in which it was used because the data it returns doesn't meet whatever criteria are relevant in the context in which it is used. In the case cited, it was the SELECT-list of a SELECT statement. Moreover, I speak from experience. I hadn't encountered that specific problem before, but by looking at the output from finderr, and because I have a reasonably accurate mental model of how Informix things work, I was able to deduce that (a) you were trying to do something that the engine wasn't going to accept, (b) that the expanded error message from finderr gives a hint (not an answer, but at least a hint) about what the problem was, (c) scrutiny of the manuals reveals that they are silent on the issues, and (d) that the appropriate action when there is a question about the content of the documentation is to contact doc@informix.com, as cited in the 'Informix Welcomes Your Comments' sections in current editions of most manuals. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> >Date: Fri, 30 Aug 1996 08:45:34 -0500 (CDT) >From: Rajeev SRK Nimmagadda <rajeevn@lmis.jcdc.doleta.gov> >Subject: Re: Problem with Views AND Stored Procedures >To: Jonathan Leffler <johnl@informix.com> > >On Thu, 29 Aug 1996, Jonathan Leffler wrote: >> If you use a stored procedure in a SELECT statement, it must >> return a single value -- single row AND single column. >> >> Your procedure returns multiple columns, violating the constraint. >> >> Incidentally, finder -684 says: >> >> -684 Procedure procedure-name returns too many values. >> >> The number of returned values from a procedure is more than the number of >> values that the caller expects. >> >> Example of error: >> >> CREATE PROCEDURE testproc (arg INT) >> RETURNING INT, INT;>> RETURN 1,2; >> END PROCEDURE >> SELECT col FROM tab WHERE col = testproc(1); -- error>> >> This gives an example of the constraint, admittedly not in the SELECT-list, >> but still it gives a fairly good hint, don't you think? > >NO! > >I am sorry I am slow. > >In the above Example you are trying to assign to a single Variable >That's why it gave an error. >How is it same with my case? >I am giving a bucket for each value returned by the procedure!!! > >It should have been documented somewhere. > >> Yours, >> Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>