3

I have a Firebird database (v. 2.5), I'm not allowed to create procedures, views or tables into the database, because of losing support.

My view is too long: 

Too many Contexts of Relation/Procedure/Views. Maximum allowed is 255

I think I can solve this Problem by creating GTT, right?

My question is, this GTT will be stored in the Database? When is the GTT deleted? I tried in a copy of my database and created a GTT, after that I closed my connection and reconnected and the GTT was there already. Does my GTT belong to the main tables in the database?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Sam Enbank
  • 41
  • 1
  • 10

1 Answers1

0

The definition of a global temporary table is persistent (that is why it is called global and not, for example, local). The data in a global temporary table is only visible to the transaction that populated it (on commit delete rows), or to the connection that populated it (on commit preserve rows). When the transaction ends or the connection is closed, the data is deleted

So once created the definition of the global temporary table will exist in the database until it is dropped.

Whether or not that violates your agreement with your software vendor, I can't say. You'll need to ask them.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • I created a GTT, but when I closed the connection and reconnected, the Table was allready there... And I write some datas in the table, first with update, after that with insert into, but even when I select * from GTT, there were no data to be displayed, the table is still empty... How should I fill the table with data? And, maybe the GTT will not be dropped because of permanent connection of the progamm to the database? – Sam Enbank Apr 28 '16 at 07:05
  • Read my answer again. The definition of the GTT is persistent, after creation it will continue to exist; the data however is temporary and only visible to the current connection, either until the end of the transaction, or the end of the connection. If you do inserts and updates in a different transaction or connection (depending on the GTT definition), than you do the select, then you won't see any data (because it is was populated in a different transaction or connection). – Mark Rotteveel Apr 28 '16 at 07:20
  • My Statemant is: create global temporary table t_1 ( personalnr int, nachname varchar (50) ); insert into t_1 (personalnr, nachname) select personalnr, nachname from personal where personalnr = 26; SELECT * FROM t_1; drop table t_1; and the result is always empty... I do not think it is in the wrong session, is it? – Sam Enbank Apr 28 '16 at 07:32
  • @SamEnbank The default of a GTT is `ON COMMIT DELETE ROWS`, so data is only visible to the transaction that inserted the data. If you select in a different transaction (eg using auto-commit, after commit or rollback, or with a different connection), then you don't see the data. Other than that you are really exceeding the scope of the original question: consider asking a new question with more details. – Mark Rotteveel Apr 28 '16 at 07:35
  • Ok, I see... with Commit it works... I added the lines: on commit preserve rows; commit; and it works now... Now I have to find out how to implement it into the webpage ;-/ thank you! – Sam Enbank Apr 28 '16 at 08:03