Devart Forums

Posted: **Tue 06 Jul 2010 08:12**

Hi

probably i missed something

i create into FIREBIRD 2.5 a temporary table

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

Posted: **Wed 07 Jul 2010 07:58**

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

Devart Forums

trouble in Temporary table

trouble in Temporary table