At 12:47 AM 7/02/2003 +0000, you wrote:

>Hi all
>
>Using FB1.02
>
>I have a group of stored procedures which perform a rather lengthy
>sequence of calculations and subsequent table inserts. These
>operations
>are logged at various intervals in a separate table.
>
>The problem I'm having is that the date and time logged for each
>entry
>(which is assigned using current_date and current_time) is the same
>for
>every log entry with a single calculation run, even though the run
>takes
>a few minutes to complete. I am therefore assuming that the date and
>time are assigned when the transactions are finally commited. Is
>that
>correct? If so, is there some way I can get the transacations
>commited
>at nominated points instead of just when the operation finally
>completes? It seems that 'commit' is not a valid keyword inside
>stored
>procedures.

They *should* be the timestamp when the individual record is
posted. However, if the logic of your program brings the timestamp into a
variable and then uses the variable to post a value when iterating through
the loop, of course the value is "frozen".

So, if your proc does

update aTable
set aTimestamp = CURRENT_TIMESTAMP,
....

or if you have a timestamping BeforeUpdate trigger, you should get the
timestamp when the row was *posted*, regardless of when the COMMIT
happens. And, no, transactions are started and completed by the client,
not the server, so you can't commit the transaction from inside itself.

If your logic isn't unintentionally freezing the timestamp, experiment with
using CAST ('NOW' AS TIMESTAMP) to see whether it makes a difference. I
reported a bug waaaaay back, ex an early beta, regarding the apparent
difference between them, and it got deleted from the bug-tracker, not sure
whether it was because it was fixed or because the person who deleted it
couldn't reproduce it...

heLen