RE: 4gl question

Doing the same in a stored procedure: create procedure my_proc() returning char(2); define ws_sec_type char(2); let ws_sec_type = 'OA'; select desc into ws_sec_type from code_desc where code = "999" and type = 'SECTYP'; return ws_sec_type; end procedure; execute procedure my_proc(); returns nothing. Shouldn't both have the same outcome? Mark > -----Original Message----- > From: malcolm.iiug [mailto:maliiug@btopenworld.com] > Sent: Friday, February 01, 2008 12:57 PM > To: Denham, Mark > Cc: informix-list@iiug.org > Subject: RE: 4gl question > > > A Null value could lead you to infer there was one row in the > table, which > is not the case, SQL and 4GL have got it right, as usual. > > > Malcolm > -----Original Message----- > From: informix-list-bounces@iiug.org > [mailto:informix-list-bounces@iiug.org] > On Behalf Of Denham, Mark > Sent: 01 February 2008 17:54 > To: informix-list@iiug.org > Subject: 4gl question > > IBM INFORMIX-4GL Version 7.32.FC2X5 > Pcode Version 732 > > This test sample prints "OA" when executed which surprised > me. The code_desc > table is empty. I would have expected a null value. > > main > > define ws_input char(2), > ws_sec_type char(2) > > let ws_sec_type = 'OA' > > select desc > into ws_sec_type > from code_desc > where code = 1 > and type = 'SECTYP'> > display ws_sec_type > > end main > > Or am I losing it? > > Mark Denham > Senior Technical Developer > Lincoln Financial > 603-226-5483 > > > > > Notice of Confidentiality: **This E-mail and any of its > attachments may > contain > Lincoln National Corporation proprietary information, which > is privileged, > confidential, > or subject to copyright belonging to the Lincoln National > Corporation family > of > companies. This E-mail is intended solely for the use of the > individual or > entity to > which it is addressed. If you are not the intended recipient > of this E-mail, > you are > hereby notified that any dissemination, distribution, > copying, or action > taken in > relation to the contents of and attachments to this E-mail is strictly > prohibited > and may be unlawful. If you have received this E-mail in error, please > notify the > sender immediately and permanently delete the original and > any copy of this > E-mail > and any printout. Thank You.** > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > Notice of Confidentiality: **This E-mail and any of its attachments may contain Lincoln National Corporation proprietary information, which is privileged, confidential, or subject to copyright belonging to the Lincoln National Corporation family of companies. This E-mail is intended solely for the use of the individual or entity to which it is addressed. If you are not the intended recipient of this E-mail, you are hereby notified that any dissemination, distribution, copying, or action taken in relation to the contents of and attachments to this E-mail is strictly prohibited and may be unlawful. If you have received this E-mail in error, please notify the sender immediately and permanently delete the original and any copy of this E-mail and any printout. Thank You.**

Denham, Mark wrote: > Doing the same in a stored procedure: > > create procedure my_proc() > returning char(2);> > define ws_sec_type char(2); > > let ws_sec_type = 'OA'; > > select desc > into ws_sec_type > from code_desc > where code = "999" > and type = 'SECTYP';> > return ws_sec_type; > > end procedure; > > execute procedure my_proc();> > returns nothing. Shouldn't both have the same outcome? > > Mark > >> -----Original Message----- >> From: malcolm.iiug [mailto:maliiug@btopenworld.com] >> Sent: Friday, February 01, 2008 12:57 PM >> To: Denham, Mark >> >> A Null value could lead you to infer there was one row in the >> table, which >> is not the case, SQL and 4GL have got it right, as usual. >> >> >> Malcolm >> -----Original Message----- >> From: [...] On Behalf Of Denham, Mark >> Sent: 01 February 2008 17:54 >> >> IBM INFORMIX-4GL Version 7.32.FC2X5 >> Pcode Version 732 >> >> This test sample prints "OA" when executed which surprised >> me. The code_desc >> table is empty. I would have expected a null value. >> >> main >> >> define ws_input char(2), >> ws_sec_type char(2) >> >> let ws_sec_type = 'OA' >> >> select desc >> into ws_sec_type >> from code_desc >> where code = 1 >> and type = 'SECTYP'>> >> display ws_sec_type >> >> end main >> >> Or am I losing it? I think that in both cases, you are treading into murky water. Yes, I would prefer them to behave the same, and I think the I4GL behaviour is reasonable -- better, if you prefer -- but can anyone point me to the documented behaviour where is says what happens if a singleton SELECT such as the one shown fails to return any data (do the host variables designated to receive the values get overwritten or not)? The opposite problem could also be faced - if the SELECT statement is not a singleton after all, are the values of the host variables clobbered or not? It is probably best to consider the result undefined - until you've checked that you actually got a row returned, you don't know what the value in the host vars will be. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2007.0914 -- http://dbi.perl.org/ publictimestamp.org/ptb/PTB-2422 tiger2 2008-02-02 06:00:04 05037CB63076D01ABBC03D4068120DB272C692E3CE8854DF

Jonathan Leffler wrote: > Denham, Mark wrote: > > > >> Doing the same in a stored procedure: >> >> create procedure my_proc() >> returning char(2);>> >> define ws_sec_type char(2); >> >> let ws_sec_type = 'OA'; >> >> select desc >> into ws_sec_type >> from code_desc >> where code = "999" >> and type = 'SECTYP';>> >> return ws_sec_type; >> >> end procedure; >> >> execute procedure my_proc();>> >> returns nothing. Shouldn't both have the same outcome? >> >> Mark >> >> >>> -----Original Message----- >>> From: malcolm.iiug [mailto:maliiug@btopenworld.com] >>> Sent: Friday, February 01, 2008 12:57 PM >>> To: Denham, Mark >>> >>> A Null value could lead you to infer there was one row in the >>> table, which >>> is not the case, SQL and 4GL have got it right, as usual. >>> >>> >>> Malcolm >>> -----Original Message----- >>> From: [...] On Behalf Of Denham, Mark >>> Sent: 01 February 2008 17:54 >>> >>> IBM INFORMIX-4GL Version 7.32.FC2X5 >>> Pcode Version 732 >>> >>> This test sample prints "OA" when executed which surprised >>> me. The code_desc >>> table is empty. I would have expected a null value. >>> >>> main >>> >>> define ws_input char(2), >>> ws_sec_type char(2) >>> >>> let ws_sec_type = 'OA' >>> >>> select desc >>> into ws_sec_type >>> from code_desc >>> where code = 1 >>> and type = 'SECTYP'>>> >>> display ws_sec_type >>> >>> end main >>> >>> Or am I losing it? >>> > > I think that in both cases, you are treading into murky water. Yes, I > would prefer them to behave the same, and I think the I4GL behaviour is > reasonable -- better, if you prefer -- but can anyone point me to the > documented behaviour where is says what happens if a singleton SELECT > such as the one shown fails to return any data (do the host variables > designated to receive the values get overwritten or not)? The opposite > problem could also be faced - if the SELECT statement is not a singleton > after all, are the values of the host variables clobbered or not? > > It is probably best to consider the result undefined - until you've > checked that you actually got a row returned, you don't know what the > value in the host vars will be. IB the underlying ESQL/C states that the contents of host variables is 'undefined' if a select returns no rows. That would apply equally to compiled 4GL. Behavior for the RDS version and for any 3rd party versions may be different. Art S. Kagel Oninit =========================================================================================== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ===========================================================================================