Current function within a Stored Procedure. 
Author Message
 Current function within a Stored Procedure.

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



Wed, 18 Jun 1902 08:00:00 GMT
 Current function within a Stored Procedure.

Quote:

> 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.

Better to create an UPDATE trigger for the table that sets the end_date
column for you:

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



Wed, 18 Jun 1902 08:00:00 GMT
 Current function within a Stored Procedure.

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.



Wed, 18 Jun 1902 08:00:00 GMT
 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



Quote:

> 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



Wed, 18 Jun 1902 08:00:00 GMT
 Current function within a Stored Procedure.

Quote:

> 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.

I think you'll find that CURRENT is evaluated at the beginning of the first SP
and then inherited by all the other called SPs

--
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           #



Wed, 18 Jun 1902 08:00:00 GMT
 Current function within a Stored Procedure.

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


Quote:

> 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



Wed, 18 Jun 1902 08:00:00 GMT
 Current function within a Stored Procedure.


Quote:
> Would having another stored procedure which only updates the datetime help?

No; the value of CURRENT is 'trapped' when the containing SQL statement
starts execution, maintaining the necessary fiction that the statement
executes instantaneously.

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.

Quote:
> [...inapplicable information omitted...]


> > 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.

Yours,

Guardian of DBD::Informix -- see http://www.perl.com/CPAN


Wed, 18 Jun 1902 08:00:00 GMT
 Current function within a Stored Procedure.

Quote:
>> 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.

I seem to recall another function that did exactly what you need, specified
as different to CURRENT for that fact. Was it NOW or is my memory playing
tricks on me?

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

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



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 8 post ] 

 Relevant Pages 

1. changing the current database within a stored procedure

2. Using the Exec function within a stored procedure

3. Calling a Stored Procedure from within a Stored Procedure

4. Calling a Stored Procedure from within a Stored Procedure

5. executing system stored procedures within a stored procedure

6. Calling a stored procedure from within a stored procedure

7. Calling a stored procedure within a stored procedure.

8. stored procedure within a stored procedure

9. Useing a stored procedure within a stored procedure.

10. Retrieving result from a stored procedure called within another stored procedure

11. Calling Oracle Stored Procedure from within SQL Server Stored Procedure

12. Using stored procedures within stored procedures


 
Powered by phpBB® Forum Software