At 10:23 PM 14/11/2007, you wrote:

>Hi Uwe
>
>--- In firebird-support@yahoogroups.com, Uwe Grauer
><uwemailmeister@...> wrote:
>> I you want to mimic temp tables, add a field "jobid" to your table and
>> include your current jobid in all selects/inserts/updates.
>> After your current use of the jobid you can delete all records with this
>> jobid.
>> This way, there is no need to remove your temp-table.
>> Reuse your temp table for all future jobs using a different jobid.
>
>Thanks for the suggestion. But I would want to clear this
>permanent-temp table :) records else it will really grow with data
>that is not at all required.

I don't think you read everything that Uwe wrote:

> After your current use of the jobid you can delete all records with this
> jobid.

In other words, you will have an empty (or nearly empty) table most of the time. In fact, what many of us do, as part of periodic housekeeping when users are excluded, is run a script on our "permanent temporary" tables to delete and recreate them. That way, we avoid building up too much garbages.

In reply to your other question, don't confuse the DB_KEY with a primary key. The DB_Key (actually RDB$DB_KEY) is an internal, transient key that exists for every record in every table. For natural tables, it is calculated from the physical address of the start of the record on the page it is on and the actual page address. For records in views and output from joined sets, it is a more complex algorithm. You can't manipulate it yourself; and it becomes invalid once the transaction ends. (In the case of natural tables, you can configure it to survive for the life of the connection.)

What Uwe is talking about is simply adding a field to the "permanent temporary table" that identifies all the records as belonging to one "job". Whenever you write records to this table, you include this "job ID" that you create for the job before you start pushing out the records. Actually, I just keep a generator for the purpose and fetch a fresh value into my client app. Others use bits and pieces to make the job id meaningful in some way, e.g., current_transaction + job start time, or whatever they want, as long as it cannot cause records from different jobs to get muddled up.

Don't mess around with trying to create and drop tables as part of a user process. You will get tied up in knots.

Global temporary tables are implemented in Firebird 2.1. You might care to download the Beta 2 kit and have a play with them. They are very cool. :-)

./heLen