Scenario, in a single connection I do this:

1. Start transaction.
2. Create table.
3. Commit.
4. Start transaction.
5. Insert rows.
6. Select using join with other tables.
7. Commit.
8. Start transaction.
9. Drop table.
 ---> ERROR: Object is in use.

I KNOW the table has been accessed only using this connection and the
transactions listed above. So why is it in use at step 9 and how can I avoid
it?

I need this to temporarily store some data to select data for a report, and
the required set of columns in the temp table varies, so I cannot use a GTT
since it has persistent DDL. Is there any other way to achieve that, i.e. a
local temporary table? For pref I would like to have it local to the
insert/select transaction...

Waiting a while before step 8-9 (and subsequent commit) is not an option
because this is done in application code that runs all the way while the
user is waiting for the report to pop up - I do need to drop it right away.
(Unless I would always just leave it there and instead drop it before step 1
next time I run this kind of report, but it would annoy me to have a useless
temp table lying around.)

Thanks,
Kjell

What sort of transaction are you using? The select may be retaining.. Do you
close the query?

Thank you! Umm... I did find it difficult to determine (docs?) what the various transaction options in the .Net provider really mean for FB, but these are the flags I use for all transactions:

FbTransactionBehavior.ReadCommitted
FbTransactionBehavior.RecVersion
FbTransactionBehavior.Wait

The select is done using this C# construct, which should close and dispose of the query:

using (FbDataReader rd = cmd.ExecuteReader(System.Data.CommandBehavior.SingleRow))
{
    rd.Read();
    for (int colnum = 0; colnum < rd.FieldCount; colnum++)
    {
        string colname = rd.GetName(colnum);
        int count = rd.GetInt32(colnum);
        if (count > 0)
            result.Counts[colname] = count;
    }
}

The FbCommand cmd has default settings except this:

cmd.CommandType = System.Data.CommandType.Text;

Anything wrong with these options etc?

Regards,
Kjell

-- 
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64