Hi

Yes I had heard there was a limit to the number of tables that could be
created. However we encourage our users to run out DB maintenance
routine weekly. Part of this does a backup and restore.

We cant use tables like your example because the the number of rows and
data type is almost always different. I considered using fixed tables
and altering them for each requirement but their is / was a limit to the
number of modifications to a table before a sweep was required (about
256 if I recall).

The system we have actually works very well. The only issue is the time
it takes to open the newly created table (because of IBO cachine the DB
structure).

Rob Martin
Software Engineer

phone +64 03 377 0495
fax +64 03 377 0496
web www.chreos.com

Wild Software Ltd



mspencewasunavailable wrote:

> --- 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.
>
>
>
>
> ___________________________________________________________________________
> IB Objects - direct, complete, custom connectivity to Firebird or InterBase
> without the need for BDE, ODBC or any other layer.
> ___________________________________________________________________________
> http://www.ibobjects.com - your IBO community resource for Tech Info papers,
> keyword-searchable FAQ, community code contributions and more !
> Yahoo! Groups Links
>
>
>
>
>
>