Subject Re: [Firebird-Architect] RFC: Proposal for the implementation of Temporary Tables.
Author Ann W. Harrison
At 12:18 PM 11/23/2004, Alexandre Benson Smith wrote:

> >
>So I cannot put a "create temporary table" inside my app since it could
>cause an exception ?

The standard is quite clear that temporary table definitions
have the same name scope as other table definitions. Since
Firebird has a single level name space, all tables are visible
to all connections. You could check for duplicates before
creating the table, handle the error and retry, or use a
generator to insure that you always create unique names. All
those are really bad ideas, in fact, because there's a hard
limit of 32K create table statements allowed before you have
to back up and restore the database - with gbak not nbak -
before you can create another table.

>Or I will have to create temporary tables with random names to avoid it ?

You could do that, but see above.

>I just used Temporary Tables on MSSQL, I could create it inside SP's
>(what I think will not occur in FB right now), So different users could
>run the same SP at the same time, but the Temporary Tables are just
>visible for the connection.

When you write your stored procedure, create a temporary table for
it. Every connection will see the table as if it were private although
they would all use the same definition.

>I thought that temporary tables should only be visible inside the
>connection that created it, or if defined as GLOBAL (as Martijn
>suggested) could be seen for every connection...

As I said to Martijn, the standard is quite clear that LOCAL and
GLOBAL refer to module scope, not connections.