commit an record
lazarus / 16-Databases

----------------------------------------------------------------------
mangakissa · 2013-11-24 11:48

I used this procedure for every applyupdate
Code: [Select]procedure TDMConnections.Commit;
begin
  if SQLTransaction.Active then
    SQLTransaction.Commit
end;                 Everytime of inserting a record, my table closes.

I have read that TSQLTransaction.action := caCommit is automatically
commits my record. now my table stays open.

But why is my table closing when I manually commit a record and the
TSQLTransaction.action is set to caNone?

----------------------------------------------------------------------
taazz · 2013-11-24 11:52

use CommitRetaining instead it will do what you want.

----------------------------------------------------------------------
janasoft · 2013-11-29 08:40

I think that the property Action of TSQLTransaction is not implemented
in the code, so it doesn´t matter that value you assign to it because
it does nothing.

----------------------------------------------------------------------
BigChimp · 2013-11-29 13:28

What about this (warning: air code, may not compile):
Code: [Select]procedure TDMConnections.Commit;
begin
  if SQLTransaction.Active then
  begin
    SQLTransaction.Commit;
    SQLTransaction.StartTransaction;
  end;
end;                
Does that work for you?

----------------------------------------------------------------------
mangakissa · 2013-11-29 15:05

I'm sorry. I have changed it to SQLTransaction.Commitretaining.
But I still find it strange SQLTransaction.Commit closes a table and
not just save the record permanently to the database.

The commit also closes other open tables as far as I noticed it.

----------------------------------------------------------------------
taazz · 2013-11-29 16:07

To put it as simple as I can, commit usually flags a transaction as
concluded, this will force a transaction destruction after the commit
on the server using commitretaining you instruct the server that the
transaction is not to be destructed and it will be used again.

----------------------------------------------------------------------
janasoft · 2013-11-29 20:15

This is how I do it and it works fine for me (comments are in spanish)
qryLocal is a TSQLQuery and trxLocal is a TSQLTransaction

Code: [Select]  if trxLocal.Active then        
  try
    begin
      qryLocal.ApplyUpdates(0);       
      trxLocal.CommitRetaining;       
    end;
  except                              
    on E: EDatabaseError do
    begin
      MessageDlg('Error', 'Ha ocurrido un error al salvar los datos en
la BD. '
              + 'El mensaje técnico ' + 'es: ' + E.Message, mtError,
[mbOK], 0);
      trxLocal.RollBackRetaining;     
      Abort;     
    end;
  end;

Perhaps, if you put your entire code we could see where is the error.

About Commit behaviour, what you say is correct. Both Commit, and also
Rollback, closes all the open tables attached to the Transaction

----------------------------------------------------------------------
mangakissa · 2013-11-30 11:09

In Delphi I'm working with MYDAC. This is a MySQL datatool to connect 
a MySQL server. I'm working with innoDB tables, so transactions are
available. If want to edit or insert a record, I start a transaction.
Therefore a transaction file will be created. Then after posting the
record I call connection.commit to save the modified record
permanently and the transaction logfile will be erased. But the
dataset (memtable) is still open for using.

SQLdb doesn't work like that. But a commit should not closing the
dataset, but only flushes the transaction logfile and starting with a
new transaction. TConnection.commit should do that.
Now the transaction logfile flushes the modified record to the
database permanently and place a flag in the transactionfile with
CommitRetaining.

But I'm not an expert to SQLdb and maybe I'm wrong with the whole idea
how transactions are working  . Transactions logfiles are not meant to
be growing.

----------------------------------------------------------------------
LacaK · 2013-12-02 08:30

My understanding is, that sqlDB more or less follows sql-92
specification, which expects, that commit or rollback closes any open
cursors.
(and you can look at TSQLQuery as a local representation of any server
cursor)

----------------------------------------------------------------------
taazz · 2013-12-02 08:39

There is an easy way out of this mess, just use 2 transactions one
read only for long leaved transactions that does not lock anything and
one for write only operations for short leaved transactions (very
short) and you are done. I have somewhere a version of MDO that I had
implemented the two transactions I'll see if I can adopt it to
sqlQuery and create a patch.