Here's a synopsis of the problem I'm trying to solve:

I have a table (table_a) with two datetime columns (start_time,
end_time).

At the beginning of a stored procedure, I want to insert a row into
table_a setting start_time to the current datetime.  The stored
procedure then executes a bunch of other statements that take up to
several hours to execute.  At the end of the procedure, I want to update
table_a end_time to the current datetime (different than the start
datetime).  Because the Informix 'current' function in a stored
procedure returns only one value, I'm having trouble registering the two
different times.

Do you know of any work-arounds.

Thanks for your help.

        Pete

CREATE TRIGGER FOR UPDATE OF (start_time) ON mytable
        REFERENCING OLD AS finish FOR EACH ROW
                UPDATE mytable SET end_time = CURRENT
                        WHERE mytable.start_time = finish.start_time;

Then you can just:

UPDATE mytable SET start_time = start_time WHERE start_time = CURRENT;

in the procedure.

Art S. Kagel

Hi John,
I had the same problem and contacted INFORMIX support for that. The
behaviour you noticed is correct, because a stored procedure is an
elementary function in SQL and for that reason the CURRENT value has to
remain unchanged within that function.
Compare to an UPDATE stock SET delivery_date=CURRENT with a number of 10000
records. Each record has to have the same timestamp and should not be
evaluated record by record. This was implemented by INFORMIX for stored
procedures too.

Would having another stored procedure which only updates the datetime help?
 You'd need two: one for start time and one for end time.  At the beginning
of your main proc, call update_starttime and at the end, call
update_endtime.  These are two separate procedures so I assume they won't
be affected by Informix' synchronization issue.

                                        -- dbs
--

 NDS Technologies Israel Ltd.                 +972 2-589-4529
 PO Box 23012                                 Fax: +972 2-589-4578
 Jerusalem, Israel                            Cellular: +972 51 287 481

--
Paul Watson                    #
WF Software Ltd                # I don't suffer from stress -
Tel: +44 1436 674729           # I'm a carrier.
Fax: +44 1436 678693           #
www.wfsoftware.co.uk           #

Call an external script which does the insert/update for you.

cheers
j.

cc:    (bcc: Jack Parker/Boston50/Epsilon)
Subject:  Re: Current function within a Stored Procedure.

Would having another stored procedure which only updates the datetime help?
 You'd need two: one for start time and one for end time.  At the beginning
of your main proc, call update_starttime and at the end, call
update_endtime.  These are two separate procedures so I assume they won't
be affected by Informix' synchronization issue.
                         -- dbs
--

 NDS Technologies Israel Ltd.                 +972 2-589-4529
 PO Box 23012                                 Fax: +972 2-589-4578
 Jerusalem, Israel                     Cellular: +972 51 287 481

What is needed is a user defined function which uses the gettimeofday()
system call and returns the result as a DATETIME YEAR TO FRACTION(5) value.
This could be done in IUS (IDS/UDO); not in any other version of the
engines.

K e n  G i l l e t t
-------------------------------------------------------------------------
                             I N F O T A I N
     Fern House, Stonehill Road, Ottershaw, KT16 0EW, United Kingdom
              tel +44(0)1932-879900, fax +44(0)1932-879909

-------------------------------------------------------------------------