--- In IBObjects@yahoogroups.com, Robert martin <rob@...> wrote:

>
> Hi
>
> We have a screen in our app that creates tables (DDL) to use on a
> temporary basis. As FB doesn't yet support temp tables we are

handling

> the removal of said tables. However we don't do it well. This

leads to

> us having a large number of unused tables in our DB at times (we

do

> eventually remove them). However this is not the main issue, it

just

> compounds it.

AIUI, FB can only create 2 ^16 tables ever without doing a
backup/restore, so creating and dropping tables could leave
you with a database that won't work. (Anybody who actually knows
about this can jump in any time...)

There are also warnings about mixing DDL and DML.

>
> IBO collects a schema of the db and whenever DDL changes take

place the

> schema gets completely regenerated. This is a real problem for us

as it

> adds a large amount of delay after creating a new temp table. I

believe

> this is an issue regardless of whether or not the schema is cached

(we

> don't cache). The large number of tables makes this process quite

slow

> (seconds ?)
>
> In the help it says I might be able to use executeImmediate to do

the

> ddl without forcing a rebuild of the schema. However my guess is

that

> if I do this than IBO won't 'see' the newly created temp table

(that I

> need to use).
>
> Does anyone have a suggestion as to how to get around this?
>

I have 15 or 20 such tables in my application. I added a field named
SESSION_ID to them all, and create them once.

When my application instance starts, I do this in the Data Module:

select gen_id(session_id_gen,1) as SESSION_ID
from RDB$DATABASE

(where session_id_gen has been created, of course) and store the
value.

Whenever I want to add a row to one of these tables, I use various
means to make sure that the SESSION_ID field gets populated, e.g.,
using a parameter, a macro substitution or just generating the
appropriate string directly (depending on circumstances) for an
IBOQuery or an AfterInsert event or just a direct assignment to the
field for an IBOTable.

For such tables, or queries that refer to them, I set the
OnPrepareSQL event to point to this routine (in my Data Module):

procedure TStartupDM.RestrictToSession(Sender: TObject);
begin
(Sender as TIBODataset).SQLWhereItems.Add('SESSION_ID = ' +
IntToStr(FSessionID));
end;

(If you need to have a prepared query that gets executed a lot with
different parameters, then I suspect you would want to do this a
little differently.)

Finally, at the point where the original code dropped the temp table,
I call another routine in the Data Module to clean out the
appropriate rows by executing a query like this:

qry.SQL.ADD('DELETE FROM ' + tablename + ' WHERE SESSION_ID = ' +
IntToStr(FSessionID)');
qry.ExecSQL;

I'm using the TIBO objects, but I suspect this will all work with
the TIB_ objects as well.

Michael D. Spence
Mockingbird Data Systems, Inc.