--- In firebird-support@yahoogroups.com, Uwe Grauer
<uwemailmeister@...> wrote:

>
> Bhavbhuti Nathwani wrote:
> > Hi all
> >
> > I know Temp Tables are round the corner but I have a requirement to
> > use something similar earlier.
> >
> > I create a regular table using SQL Pass Thru from VFP. I append data
> > to this table using VFP Remote View. I do further SQL Pass Thru using
> > this table now populated with data.
> >
> > Now I try to DROP TABLE this (temp) table and I am not able to because
> > the resource is in use.
> >
> > Is there any way I can use a command like CLOSE RESOURCE <tablename>?
> >
> > Please advise.
> >
> > Regards
> > Bhavbhuti
>
> 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.
>
> Uwe
>

Yes, in fact there is the CURRENT_CONNECTION and CURRENT_TRANSACTION
context variables that may be usable. For example:

TEMPTABLE
(
CURRENTTRANSACTION BIGINT,
SOMEDATA INTEGER
)

SET TERM ^ ;
CREATE TRIGGER TEMPTABLE_BI FOR TEMPTABLE
ACTIVE BEFORE INSERT POSITION 0 AS
BEGIN
NEW.CURRENTTRANSACTION = CURRENT_TRANSACTION;
END
^
SET TERM ; ^

CREATE VIEW VTEMPTABLE
AS
SELECT SOMEDATA
FROM TEMPTABLE
WHERE CURRENTTRANSACTION=CURRENT_TRANSACTION;

---
You may need to index CURRENTTRANSACTION.
You can perform DML operations directly on the view and Firebird will
do the right thing on the underlying table.

There are a few problems/considerations:
These are NOT true temporary tables. The data will remain there
indefinitely if you don't delete what you put in. Furthermore garbage
collection may cause some slowness after a bulk delete. Also,
transaction numbers are reused after a restore, so you will need to
make sure the table is completely emptied after restoring otherwise
you may get wrong results.

In terms of the in use message, you probably just need to commit and
start a new transaction, but I would avoid continuously dropping and
creating real tables. IIRC, there is an internal number that limits
the number of times this can be done.

Adam