Re: Current function does not work!

In article <35bee356.7768718@news.newmedia.no>, Nils Myklebust <Nils.Myklebust@nmdata.com> writes >On Tue, 28 Jul 1998 18:10:53 -0400, "Art S. Kagel" ><kagel@bloomberg.net> wrote: > >>Stefan Weideneder wrote: >>> >>> Hi Wolf, >>> >>> I would recommend to contact your Technical Support, because this >>> behaviour looks like a BUG. >> >>Not a bug. For consistency during SPL execution the value of CURRENT >>is frozen for the session when a Stored Procedure is started until >>you complete the outermost stored procedure. >> >>Art S. Kagel > >As I have understood it this is done so due to a requirement in the >ANSI standard for SQL that the value of current shouldn't change over >the execution time taken by a single SQL statement. Informix have >interpreted this to include execution of stored procedures. If you >execute a simple select statement you wouldn't normaly want current to >return different values if used multiple times. To me it's clear that >both executions of current should return the same value in the >following statement and must do so for every row returned: > >select current, updatetime >from sometable >where updatetime > current> >(Convoluted example perhaps, but you might use it to find errors in an >application.) > >However it's a somewhat different issue in a stored procedure. I am a >little doubtfull to Informix's interpretation of the ANSI standard >here. >Is it realy required by the ANSI standard? >Are there examples where it would be required to work the way it does, >or is it just an example of following a "standard" for the purpose of >beeing compliant while creating more problems than that solves? > >I don't know the answers, so a clarification of the issues involved >would be very welcome. > >While I think about it now you could easily get into the same kind of >problems as would result from the above select statement having >current executed every thime it was encountered if a stored procedure >where used as part of a select statement. May be the only solution is >to provide another statement that could be used inside stored >procedures to return the current date and time as it was executed. >It's a question of how necessary such a statement is however. For >checking performance it would of course be nice, but there are other >ways of doing that. Are there other important uses of such a >statement? > Have a stored procedure x which returns one value and do select x(col1) from tab1... if x usesCURRENT internally then clearly it must use the same value for all rows in your results set... > >Nils Myklebust >NM Data AS >Norway >E-mail: Nils.Myklebust@nmdata.com >FAQ at: http://www.iiug.org/techinfo/faq/faq_top.html >(Now with ODBC info under "Third party products".) -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care