On Mon, Mar 21, 2011 at 1:10 AM, karolbieniaszewski <
liviuslivius@...> wrote:

> --- In firebird-support@yahoogroups.com, Mike Pomraning <mjp@...>
> wrote:
> >
> > On Sat, Mar 19, 2011 at 12:47 PM, homerjones1941 <homer@...>wrote:
>
> > > Can you declare a variable, and then just assign the value once?
> > >
> >
> > In a stored procedure, yes. IMHO this is suitable for regular, production
> > environments, when you already know all the queries you want to run.
> > However, I find it too restrictive for general or "on the fly" querying.
> >
> > Another possibility is using a table or GTT or RDB$SET_CONTEXT() to store
> > the precomputed value. In this case you'd have to explicitly compute the
> > value before every SELECT, which is still cumbersome but more flexible
> IMHO
> > than using a stored procedure.
> >
> > But, I'm wondering if there is an easier technique than any of the above.
> >
> > -Mike
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
> Yes exists "better" way
> What problem with this i suppose that you write some client application?
>
> Select "your_date_time_utc", x, y, z from table_x ..
>

Thanks, Karol. Client-generated timestamps are susceptible to the vagaries
of every client's clock, or require the client to execute two queries:
SELECT server's time, then issue the query we care about.

Hmm, is there a way in the API to determine the transactional scope of the
currently executing query, finer-grained than CURRENT_TRANSACTION? I
suppose CURRENT_TIMESTAMP effectively serves that purpose. This appears to
do what I want:

CREATE PROCEDURE exact_txn_timestampUTC
RETURNS (now TIMESTAMP) AS
DECLARE VARIABLE k VARCHAR(100);
BEGIN
k = 'utc_now_' || CURRENT_TIMESTAMP;
now = RDB$GET_CONTEXT('USER_TRANSACTION', :k);
IF (now IS NULL) THEN
BEGIN
RDB$SET_CONTEXT('USER_TRANSACTION', :k, getexacttimestampUTC());
now = RDB$GET_CONTEXT('USER_TRANSACTION', :k);
END
SUSPEND;
END;

Not pretty, but it's a start.

-Mike


[Non-text portions of this message have been removed]