possible bug in functions, or I need a work-around
A user on IDS 9.14 found that an SPL function returning CURRENT gave a fresh timestamp each call, but a second function that selected the first function's value always returned the same frozen timestamp (only 'update statistics for function' refreshed it). Suggestions: call via LET rather than SELECT (didn't help him), check USEOSTIME. Jonathan Leffler said CURRENT is fixed for the duration of a statement, including nested sub-statements, and pointed to DBINFO with keywords like utc_current as the workaround. The discrepancy with 7.24/NT was disputed but never explained; no confirmed fix is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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.
i created the following based on yours,
supplying slightly different from , and
also did a call instead of the select you used...
CREATE FUNCTION tst_get_current( )
RETURNING datetime year to fraction(3) ;
DEFINE lCurrent datetime year to fraction(3) ;
SELECT CURRENT
INTO lCurrent
FROM SYSMASTER:SYSTABLES
WHERE TABID = 1 ; RETURN lCurrent ;
end function ;
CREATE FUNCTION tst_get_time( )
RETURNING datetime year to fraction(3) ;
DEFINE lCurrent datetime year to fraction(3) ;
let lCurrent = tst_get_current();
RETURN lCurrent ;
end function ;
execute function tst_get_time will return new values upon each exec.
hth,
edward
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.
Well, I tried your functions (and I like them, btw), but when I run
tst_get_time(), I still get the same time over and over again using the
call execute function tst_get_time() ;
I'm running on IUS 9.14.UC7X4. I don't know if that makes a difference
with this particular problem. What version did you get that to work on?
Edward Rosenthal <edrosenthal@home.com> wrote:
> i created the following based on yours,
> supplying slightly different from , and
> also did a call instead of the select you used...
>
> CREATE FUNCTION tst_get_current( )
> RETURNING datetime year to fraction(3) ;>
> DEFINE lCurrent datetime year to fraction(3) ;
>
> SELECT CURRENT
> INTO lCurrent
> FROM SYSMASTER:SYSTABLES
> WHERE TABID = 1 ;> RETURN lCurrent ;
>
> end function ;
>
> CREATE FUNCTION tst_get_time( )
> RETURNING datetime year to fraction(3) ;>
> DEFINE lCurrent datetime year to fraction(3) ;
>
> let lCurrent = tst_get_current();
>
> RETURN lCurrent ;
> end function ;
>
> execute function tst_get_time will return new values upon each exec.>
> hth,
> edward
>
> 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.
well, im on an nt running a local instance of 9.20.
and i also checked on another machine running 9.12.UC2
with similar results. On that machine i created the
get_current function and executed ok, multiple times,
different times.
in the instance for 9.12 i checked the config file :
USEOSTIME 0 # 0: use internal time(fast), 1: get time from
OS(slow)maybe this has something to do with the problem?
the same entry for the nt.
USEOSTIME 0 # 0: use internal time(fast), 1: get time from OS(slow)are you using internal time or time from the os?
maybe someone else can comment on the internals for the call to current,
and how it could be affected...
Curtis Bennett wrote:
> Well, I tried your functions (and I like them, btw), but when I run
> tst_get_time(), I still get the same time over and over again using the
> call execute function tst_get_time() ;
>
> I'm running on IUS 9.14.UC7X4. I don't know if that makes a difference
> with this particular problem. What version did you get that to work on?
>
> Edward Rosenthal <edrosenthal@home.com> wrote:
>
> > i created the following based on yours,
> > supplying slightly different from , and
> > also did a call instead of the select you used...
> >
> > CREATE FUNCTION tst_get_current( )
> > RETURNING datetime year to fraction(3) ;> >
> > DEFINE lCurrent datetime year to fraction(3) ;
> >
> > SELECT CURRENT
> > INTO lCurrent
> > FROM SYSMASTER:SYSTABLES
> > WHERE TABID = 1 ;> > RETURN lCurrent ;
> >
> > end function ;
> >
> > CREATE FUNCTION tst_get_time( )
> > RETURNING datetime year to fraction(3) ;> >
> > DEFINE lCurrent datetime year to fraction(3) ;
> >
> > let lCurrent = tst_get_current();
> >
> > RETURN lCurrent ;
> > end function ;
> >
> > execute function tst_get_time will return new values upon each exec.> >
> > hth,
> > edward
> >
> > 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.
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>
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?
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.
Curtis Bennett wrote:
> 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?
Which version of 7.24 on which platform? I'm dubious in the extreme
about
the claim. Ditto, but not quite so strongly, for the NT version.
Also, when I go to try and verify my assertion, I find that your state
table
must be unusual in having multiple entries with st_cd = 'KS', and that
the
stored procedures you quote won't work without a FOREACH loop and a
RETURN
WITH RESUME. So, you don't seem to be quoting exactly what you're
using.
Anyway, I tried with this code (using procedure not function because of
a bug
in SQLCMD):
CREATE PROCEDURE tst_get_current ()
RETURNING DATETIME YEAR TO FRACTION(3);
DEFINE lCurrent DATETIME YEAR TO FRACTION(3);
SELECT CURRENT
INTO lCurrent
FROM "informix".systables WHERE tabid = 1; RETURN lCurrent;
END PROCEDURE;
CREATE PROCEDURE tst_get_time ()
RETURNING DATETIME YEAR TO FRACTION(3);
DEFINE lCurrent DATETIME YEAR TO FRACTION(3);
FOREACH
SELECT tst_get_current()
INTO lCurrent
FROM "informix".systables
RETURN lCurrent WITH RESUME;
END FOREACH;
END PROCEDURE;
execute procedure tst_get_time();
execute procedure tst_get_current();
This produces answers consistent with my assertion. I also note that
you
cannot be using CREATE FUNCTION statements in 7.24 (unless there's an
undocumented
feature in there that FUNCTION is a synonym for PROCEDURE).
> 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 ...
--
Yours,
Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h>
Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN
"I don't suffer from insanity; I enjoy every minute of it!"
Jonathan, you missed where the poster states that the multiple outputs are
the result of running the function repeatedly NOT the output of a FOREACH
loop and that there is ONLY one "KS" record. He just presented the outputs
sequentially to save bandwidth.
Art S. Kagel
Jonathan Leffler wrote:
>
> Curtis Bennett wrote:
> > 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?
>
> Which version of 7.24 on which platform? I'm dubious in the extreme
> about
> the claim. Ditto, but not quite so strongly, for the NT version.
>
> Also, when I go to try and verify my assertion, I find that your state
> table
> must be unusual in having multiple entries with st_cd = 'KS', and that
> the
> stored procedures you quote won't work without a FOREACH loop and a
> RETURN
> WITH RESUME. So, you don't seem to be quoting exactly what you're
> using.
>
> Anyway, I tried with this code (using procedure not function because of
> a bug
> in SQLCMD):
>
> CREATE PROCEDURE tst_get_current ()
> RETURNING DATETIME YEAR TO FRACTION(3);>
> DEFINE lCurrent DATETIME YEAR TO FRACTION(3);
>
> SELECT CURRENT
> INTO lCurrent
> FROM "informix".systables WHERE tabid = 1;> RETURN lCurrent;
>
> END PROCEDURE;
>
> CREATE PROCEDURE tst_get_time ()
> RETURNING DATETIME YEAR TO FRACTION(3);>
> DEFINE lCurrent DATETIME YEAR TO FRACTION(3);
>
> FOREACH
> SELECT tst_get_current()
> INTO lCurrent
> FROM "informix".systables
> RETURN lCurrent WITH RESUME;
> END FOREACH;
>
> END PROCEDURE;
>
> execute procedure tst_get_time();
> execute procedure tst_get_current();>
> This produces answers consistent with my assertion. I also note that
> you
> cannot be using CREATE FUNCTION statements in 7.24 (unless there's an
> undocumented
> feature in there that FUNCTION is a synonym for PROCEDURE).
>
> > 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 ...
>
> --
> Yours,
> Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h>
> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN
> "I don't suffer from insanity; I enjoy every minute of it!"
"Art S. Kagel" wrote: > > Jonathan, you missed where the poster states that the multiple outputs are > the result of running the function repeatedly NOT the output of a FOREACH > loop and that there is ONLY one "KS" record. He just presented the outputs > sequentially to save bandwidth. OK, mea culpa. I've not reread the question, but I apologize for castigating someone for what they did not do. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"