trouble in Temporary table

[dehorter]

Hi

probably i missed something

i create into FIREBIRD 2.5 a temporary table

Code: Select all

CREATE GLOBAL TEMPORARY TABLE TEMP_RANG (
    RANG     INTEGER NOT NULL,
    ID_DATA  INTEGER NOT NULL
) ON COMMIT DELETE ROWS;

according i understand this table remains full as long as the transaction remains active.

this is a code (from delphi 2010) using IBDAC (3.10.0.15)

Code: Select all

   Query.Transaction := TransactionFB;
   QueryFB.Transaction := TransactionFB;
   IF not TransactionFB.Active THEN
      TransactionFB.Active;
   QueryFB.StoredProcName := '__TEST_TEMPO';
   QueryFB.PrepareSQL(True);

   QueryFB.Execute;
    MessageDlg('nb = ' + QueryFB.FieldByName('i').AsString, mtWarning, [mbOK], 0);  // value = 66 : OK!
   TransactionFB.CommitRetaining;
    MessageDlg('nb = ' + QueryFB.FieldByName('i').AsString, mtWarning, [mbOK], 0);  // value = 66 : OK!
   //QueryFB.close;
   QueryFB.StoredProcName := '';
    QueryFB.SQL.Clear;
    QueryFB.SQL.Add('select count(*) AS Nb from temp_rang');
    QueryFB.Open;
    MessageDlg('nb = ' + QueryFB.Fields[0].AsString, mtWarning, [mbOK], 0);  // value = 0: NOT OK! whereas the transaction is still active !

    Query.SQL.Clear;
    Query.SQL.Add('select count(*) AS Nb from temp_rang');
    Query.Prepare;
    Query.Open;

    MessageDlg('nb = ' + query.Fields[0].AsString, mtWarning, [mbOK], 0);  // value = 0: NOT OK! whereas the transaction is still active ! and the query uses the same transaction

the DefaultCloseAction property is set to : taCommitRetaining

As you can see the transaction remains active. however the temporary table seems empty when the query changes


do you have an idea, where i make something wrong ?


regards

olivier

[dehorter]

Hi again

just information added

i tested the same thing but with "preserve" options

Code: Select all

CREATE GLOBAL TEMPORARY TABLE TEMP_RANG (
    RANG     INTEGER NOT NULL,
    ID_DATA  INTEGER NOT NULL
) ON COMMIT PRESERVE ROWS;

and the behavio is correct. I can access to this temporary table, after disconnection this table become empty.

so where i miss something :oops:

regards

olivier