Re: possible bug in functions, or I need a work-around

From: Curtis Bennett <die_kluge@hotmail.com> > >I'll research those things as you suggest, but mind explaining to me why >this WORKS in versions 7.24 and the NT version that our DBA tested it >on, but not on version 9.14 that I'm using? If it works in 7.24, it's a bug. >In article <393A72D6.1D12CEDF@earthlink.net>, > Jonathan Leffler <jleffler@earthlink.net> wrote: > > What you are seeing is the expected -- indeed, ANSI mandated -- >behaviour. > > When an SQL statement starts executing, the current time is >established, > > and frozen, for the duration of that statement. And any >sub-statements > > also get the same current time. Even if one of those substatements is >a > > stored procedure which does SLEEP(86400). > > > > This a pain, but it is the way the world works, sometimes. > > > > Investigate the DBINFO function, and keywords like utc_current. > > These have, I'm pretty sure, been discussed in this news group within > > the last year or so. They might, if you're lucky, even be documented. > > > > Curtis Bennett wrote: > > > > > Ok, the functions I'm working with are way complicated, and far too > > > complicated to try to explain here, but I was able to boil them down > > > into simple forms and still reproduce this problem. > > > > > > I created two functions tst_get_current, and tst_get_time. If you >look > > > closely at tst_get_time you'll see that it selects the value of > > > tst_get_current. (hey, functions are neat) I select from the state > > > table where state is Kansas so I'll just get one row back. (is >there a > > > better way to do this without using a distinct?) > > > And when I run them (at the bottom), tst_get_current returns a >different > > > value everytime, but when I run tst_get_time I get a static value >back > > > every time. > > > > > > And I know why; because when I create tst_get_time it caches the >value > > > of tst_get_current and stores it internally. If I add a line to say > > > "update statistics for function tst_get_time ;" I get a new value > > > everytime. But there has got to be a better way. These values >listed > > > at the bottom are the results of running over and over again. Not to > > > imply that these functions are returning multiple rows... > > > > > > Just some insight into what I'm really trying to do. These >functions > > > are trying to calculate the total time a ticket is on hold. And in >my > > > particular case, the example that discoverd this problem is on hold >and > > > has not yet been ended. So the amount of time that this particular > > > ticket is on hold is based on current time, so the amount of time > > > continues to grow as time passes. But then they wanted the current >time > > > to be based on the correct time zone, so I wrote a special function >that > > > returns current +/- 1/2 units hours based on which time zone is >being > > > passed. > > > > > > So, I can only assume that what I'm doing won't work, but I need to > > > figure out a way to make it work. Any suggestions? > > > > > > CREATE FUNCTION tst_get_current ( ) > > > RETURNING datetime year to fraction(3) ;> > > > > > DEFINE lCurrent datetime year to fraction(3) ; > > > > > > SELECT CURRENT > > > INTO lCurrent > > > FROM state > > > WHERE st_cd = 'KS' ;> > > RETURN lCurrent ; > > > > > > end function ; > > > > > > CREATE FUNCTION tst_get_time ( ) > > > RETURNING datetime year to fraction(3) ;> > > > > > DEFINE lCurrent datetime year to fraction(3) ; > > > > > > SELECT tst_get_current() > > > INTO lCurrent > > > FROM state > > > WHERE st_cd = 'KS' ; > > > RETURN lCurrent ; > > > end function ; > > > > > > execute function tst_get_current() ;> > > > > > (expression) > > > > > > 2000-06-01 15:17:17.632 > > > 2000-06-01 15:17:24.118 > > > 2000-06-01 15:17:28.679 > > > etc... > > > > > > execute function tst_get_time() ;> > > > > > (expression) > > > > > > 2000-06-01 15:16:19.163 > > > 2000-06-01 15:16:19.163 > > > 2000-06-01 15:16:19.163 > > > 2000-06-01 15:16:19.163 > > > 2000-06-01 15:16:19.163 > > > etc ... > > > > > > -- > > > Curtis Bennett > > > CIBER, INC > > > Overland Park, KS > > > > > > Sent via Deja.com http://www.deja.com/ > > > Before you buy. > > > > -- > > Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) > > Guardian of DBD::Informix v1.00.PC1 -- see http://www.perl.com/CPAN > > #include <disclaimer.h> > > > > > >-- >Curtis Bennett >CIBER, INC >Overland Park, KS > > >Sent via Deja.com http://www.deja.com/ >Before you buy. ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com