Is it timing of a dataset?

[Sieben]

Saving updates (and inserts as well) consist of several steps at different levels.

1. Editing or inserting a record. Dataset has to be in either dsEdit or dsInsert mode.
2. Posting the changes made with editing or inserting. Dataset goes back to dsBrowse mode and changes become 'updates' on the dataset level.

All of the above is restricted to the dataset level, the DBMS or the Transaction object are NOT involved yet. So when you issue a CancelUpdate there is no need for any rollback of transaction, just like there is no need for it if you do a simple Cancel while editing. Only when you do an

3. ApplyUpdates these internal updates on the dataset level are sent to the DBMS, but only provisionally. Only now, after ApplyUpdates, do you need either a Commit to really save the changes to the DBMS or a Rollback to cancel them.

[CraigC]

Oh, yeah.  All of the query's are set to sqoKeepOpenOnCommit.  I found that out quite early working with the SQLdb components. 

I'll step through again and see if there is something I am missing.  But when it errors out and says it's not in dsEdit and I try to refresh, it says there are pending updates. 

[rvk]

All of the above is restricted to the dataset level, the DBMS or the Transaction object are NOT involved yet. So when you issue a CancelUpdate there is no need for any rollback of transaction, just like there is no need for it if you do a simple Cancel while editing.

Actually, there might be a reason to do Rollback or Commit in case of CancelUpdate.
Because if you don't, you are kept in the last transaction state. So when you do a refresh, you only get a refresh of old records in an old state. Any changes on a different machine of through another transaction, are not visible yet.

Doing Rollback or Commit (or xRetaining), you force the current transaction to close and the subsequent refresh opens a new transaction with a fresh view of the database (and any changed records since the last one can be viewed too).

(above also depends on the isolation level of the transaction)

(PS. A transaction is started on Opening of a dataset not just on start of editing or Applyupdate)

[Sieben]

All of the above is restricted to the dataset level, the DBMS or the Transaction object are NOT involved yet. So when you issue a CancelUpdate there is no need for any rollback of transaction, just like there is no need for it if you do a simple Cancel while editing.

Actually, there might be a reason to do Rollback or Commit in case of CancelUpdate.
Because if you don't, you are kept in the last transaction state. So when you do a refresh, you only get a refresh of old records in an old state. Any changes on a different machine of through another transaction, are not visible yet.

Doing Rollback or Commit (or xRetaining), you force the current transaction to close and the subsequent refresh opens a new transaction with a fresh view of the database (and any changed records since the last one can be viewed too).

(above also depends on the isolation level of the transaction)

(PS. A transaction is started on Opening of a dataset not just on start of editing or Applyupdate)

You're absolutely right and thanks for pointing that out, but as you say for yourself, that's due to the nature of implicit transactions as used by default in SQLdb. It does not affect the logic of updates I layed out. That's why i use explicit transactions like I did when working with Delphi and ADODatasets, starting them only just prior to ApplyUpdates. But I think you have to understand the logic of updates regardless of the type of transaction used.

[rvk]

That's why i use explicit transactions like I did when working with Delphi and ADODatasets, starting them only just prior to ApplyUpdates. But I think you have to understand the logic of updates regardless of the type of transaction used.

If I set stoExplicitStart in the transaction, it doesn't start automatically. So when you do SQLQuery1.Open, you'll get an error that the transaction hasn't started yet.

You can't just start the transaction just before doing ApplyUpdates because the transaction would already have needed to be started on the Opening of the dataset. EVERY read, write etc needs to be in a transactional context. So a transaction would need to be started on all of them (including open).

I'm not sure if ADODatasets work in the same way. Maybe those have a sort of buffering mechanisme. But SQLite, Firebird, IBX etc don't, and they do need to start the transaction on Open.

[Sieben]

I'm afraid you caught me with my pants down - I always wanted to switch to explicit transactions but never really tried so far. So I did a little test just now, and it seems you're right once more. But it still does not affect the logic of updates.

[CraigC]

I added a bit of code on the Datasource to tell me when the transaction goes active.

  if dsInventory.State in [dsEdit, dsInsert] then
    if transLedger.Active then
      ShowMessage('Transaction active!');

As soon as I edit one field I get this.  I then call saveData() and that code again is below.

procedure SaveData(qry: TSQLQuery; bSave: Boolean);
var
  s: string;
begin
  s := qry.Name;
  try
    if not dmCommonData.transLedger.Active then dmCommonData.transLedger.StartTransaction;
    if bSave then
    begin
      if DataState(qry) then  // checks for dsEdit or dsInsert
      begin
        qry.ApplyUpdates;                                               // error, not in dsEdit or dsInsert.
        dmCommonData.transLedger.Commit;
      end;
    end
    else
    begin
      qry.CancelUpdates;
      if dmCommonData.transLedger.Active then
        dmCommonData.transLedger.RollBack;
    end;
  except
  on E: EDatabaseError do
     begin
       dmCommonData.transLedger.Rollback;
       MessageDlg('Error', 'DataBase error: '+ E.Message, mtError, [mbOK], 0);
     end;
  end;
end;                         

[rvk]

I added a bit of code on the Datasource to tell me when the transaction goes active.

Please use the "code" tags when adding code (you can use the # above the editor for that). It makes it all a lot more readable (and it won't corrupt your code in the editor).

As soon as I edit one field I get this.

I take it you do get the "Transaction is active" message?

  I then call saveData() and that code again is below.

When exactly do you call that?

Code: [Select]

      if DataState(qry) then  // checks for dsEdit or dsInsert
      begin
        qry.ApplyUpdates;                                               /[b][u]/ error, not in dsEdit or dsInsert.[/u][/b]
        dmCommonData.transLedger.Commit;
      end;

So this is really strange. DataState is only true when qry.State is in [dsEdit, dsInsert].
The following line of qry.ApplyUpdates shouldn't give you an error then.

I guess you need to show more code.
Do you have OnBeforePost or any other triggers in your program.

If you can't find the problem you would need to strip your example to a reproducible test-project and post it here.

[LemonParty]

I am suggest to change Commit to CommitRetaining and Rollback to RollbackRetaining and check if this change anything.

By the way, when you opening/closing the query?

[CraigC]

I did change from Commit to CommitRetaining.  But, I still cannot save an update.  I believe the query is opened right after the selection is chosen to edit Inventory items.  No triggers on other events going on.  Pretty straight forward.

[rvk]

Can you check with the debugger what the qry.State is right before you execute qry.ApplyUpdates?

[CraigC]

Saving Data ->
tblInventory_  DS in dsEdit, dsInsert,   transaction Is active.         

Is in a txt file and I check for everything I can.  DS = datasource.

I get an error that the qry is not in dsEdit or dsInsert.

[rvk]

We can't help if you don't show more code (like how and when this procedure is called).

At least if seems very strange that qry.state is dsEdit and qry.ApplyUpdates complains about not being in dsEdit mode.

[CraigC]

Simple process.  I edit the table, maybe only 3 fields. Then press button to call SaveData(qryInventory_, True);

And this procedure is called.  No extra bits of code anywhere.  There is code in here to save state to a text file.


procedure SaveData(qry: TSQLQuery; bSave: Boolean);
var
  sTrans, sQry, sDS: string;
begin
  sQry := qry.Name;
//  sDS  := qry.DataSource.Name;   // exception created.
  if dmCommonData.transLedger.Active then
   sTrans := ' transaction Is active.'
  else
   sTrans := ' transaction is NOT active.';
  try
    if DataState(qry) then
      sDS := 'DS in dsEdit, dsInsert,  ';
    if dmCommonData.transLedger.Active then
     SendToFile('Saving Data -> ',sQry + '  ' + sDS  + sTrans);
    if bSave then
    begin
      if DataState(qry) then // checks for dsEdit or dsInsert, returns true.
      begin
        qry.ApplyUpdates;
        dmCommonData.transLedger.CommitRetaining;
      end;
    end
    else
    begin
      qry.CancelUpdates;
      if dmCommonData.transLedger.Active then
        dmCommonData.transLedger.RollBackRetaining;
    end;
  except
  on E: EDatabaseError do
     begin
       dmCommonData.transLedger.RollbackRetaining;
       MessageDlg('Error', 'DataBase error: '+ E.Message, mtError, [mbOK], 0);
//       InsertIntoLog('Data Rolled back. ',s, qry.UpdateSQL.Text);
     end;
  end;
end;
                                                                                 

[rvk]

Then I would say... strip your program to a small project which reproduces this problem.
In 90% of cases you'll find the problem yourself.
If not, you can post the source here.

(You also didn't use code-tag again which can obscure certain code sometimes, like [ i ] for array index will convert to italic if you don't use proper code-tags)