firebird
firebird copied to clipboard
FirebirdSQL
Local temporary tables [CORE720]
Submitted by: @pcisar
Votes: 32
SFID: 807942# Submitted By: pcisar
Allow for the definition of temporary tables which are only visible within the current connection context and which are deleted upon connection disconnect/lost.
Commented by: Alice F. Bird (firebirds)
Date: 2004-05-24 12:53 Sender: agjedrus Logged In: YES user_id=749589
great feature really usefull but with implementation like is done in MSSQL
Commented by: Dejan Schild (schild)
It's really time to implement this feature, it's very needed for reporting,...
Commented by: Maxim (paratruper)
Pleas see key core -961. May be is better to choose not context connections, and a context of transaction?
Commented by: @hvlad
Maxim, how 'Free external tables' is related to the 'Temporary tables' ? It seems you mixed two different things
Commented by: Mirco Malaguti (mirmalag)
Would be useful to declare the lifetime of the table, either the connection or the transaction in which the temporary table is created, e.g.: CREATE TEMPORARY TABLE FOO ( ... ) DROP ON COMMIT/ROLLBACK | DISCONNECT;
Mirco
Modified by: @dyemanov
summary: Temporary tables => Local temporary tables
Modified by: @pcisar
Workflow: jira [ 10744 ] => Firebird [ 15129 ]
Commented by: Umberto Masotti (umasotti)
Maybe I've missed something, but if Fb 2.1 GTT solve completely this feature request, maybe it's time to close it.
Commented by: @dyemanov
Global and local temporary tables are two different things. This ticket refers to the latter one.
Commented by: Cosmin Apreutesei (cosmin_ap2)
I second Mirco's idea -- otherwise procedures won't be re-entrant in the same connection and there's no easy way to make them so (you'd have to implement DROP IF EXISTS).
Commented by: Cosmin Apreutesei (cosmin_ap2)
On a second thought, this won't be enough for all cases (you can still call the same procedure in the same transaction twice and the CREATE will fail), so the best way is for them to apply the same lifetime rules as for local variables.
Or better yet, let us declare variables in the procedure body and apply full lexical scoping. It's just a feature request so any amount of fantasizing is allowed right?
Commented by: @dyemanov
I don't think you need to apply your fantasy. The SQL committee had already decided everything we would implement.
Commented by: Cosmin Apreutesei (cosmin_ap2)
> The SQL committee had already decided everything we would implement.
No good deed ever goes unpunished.
Modified by: Sean Leyne (seanleyne)
description: SFID: 807942# Submitted By: pcisar
Allow for the definition of temporary tables which are only visible within the current connection context and which are deleted upon connection disconnect/lost.
=>
SFID: 807942# Submitted By: pcisar
Allow for the definition of temporary tables which are only visible within the current connection context and which are deleted upon connection disconnect/lost.
Commented by: robert gilland (robert.gilland)
I have just moved company after working with firebird since 2003, and being a major contributor to it. My new company uses "SQL anywhere". I cannot even begin to recommend Firebird to my new company unless this is implemented An example of this is below:
ie. inside a stored procedure
declare local temporary table Foo( FooCode char(10) null, ) on commit preserve rows;
Commented by: Robert Gilland (robert.gilland_basx.com.au)
You wouldn't believe I moved company again and have exactly the same issue. The critical need for local temporary tables and hash tables
Commented by: robert gilland (robert.gilland)
I propose this solution:
Add a section in the metadata-cache for local temporary tables. So when compiling a stored procedure the dependency tree looks at this cache. When running the stored procedure the metadata-cache is modified according to this cache. The dependency tree should be unaffected by local temporary tables. This cache should be connection specific.
Just an Idea. I might be completely wrong.
Local Temporary Tables is a really critical need for Firebird.
Commented by: @asfernandes
What I think would be a very good thing is kind of "packaged table", a temporary table defined (and upgraded) as part of a package body/spec.