Re: Stored Procs & CURRENT

Neil Stevenson wrote: } } In article <5b08ur$mjg@nntp.idgonline.no>, Nils Myklebust } <Nils.Myklebust@idg.no> writes } >wardr@gusco.com (Rick Ward) wrote: } > } >:I have a user who is running a stored procedure which takes several hours to } >update (which is } >:OK). The last statement that is executed in the stored procedure is an: } > } >:INSERT INTO table VALUES (...,CURRENT,...); } > } >:The value in the column inserted for CURRENT (which is a datetime field) is set } >to the datetime } >:the EXECUTE PROCEDURE statement was executed, NOT the time the INSERT statement } >was executed } >:some 6 hours later. } > } >:This "feature" is documented in the SQL SYNTAX manual, and explains that the } >CURRENT value is } >:calculated when the EXECUTE PROCEDURE statement is started. What it doesn't } >say is whether or } >:not there is a workround. } > } >:The question I would like to pose is: } > } >:Does anyone know of a workround such that this can be kept in the same stored } >procedure ? } > } >:We are running on Online DSA 7.14. } > } >I don't *know* of a workaround, but may be it will work if you call } >another stored procedure that returns it's value of current and use } >that to insert into the table? I haven't ever tried this. } > } > } >Nils.Myklebust@idg.no } >NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway } >My opinions are those of my company } >The Informix FAQ is at http://www.iiug.org } > } } What you could try is to make the column have a default value of current } and insert into that table but not into that column. } } i.e. } } create table test_table } ( } col1 serial, } col2 datetime hour to second default current hour to second } ); } } insert into test_table(col1) values (0); } } HTH } -- } ######################################### } # Neil Stevenson # } # DSW Computer Systems Limited # } # E-mail : neil@dsw2.demon.co.uk # } # WWW : http://www.dsw2.demon.co.uk/ # } # Voice : +44-1236-731330 # } # Fax : +44-1236-731330 # } ######################################### Thanks for your suggestion, we tried that first. Unfortunately the bloody thing does the same thing as if it was inserted from the stored procedure. Currently it looks like the only solution is to insert the row in a different procedure. What a PAIN !!!! Thanks for everyones suggestions, -- Rick Ward Senior DBA Great Universal Stores Home Shopping MANCHESTER, UK