Is it timing of a dataset? lazarus / 16-Databases ---------------------------------------------------------------------- CraigC · 2026-04-26 12:41 I have a table that I have an edit form setup for. When I go back to edit a couple fields and press save and exit, I get dataset not in dsEdit or dsInsert.  If I go and try to do a refresh, I can't because it has pending updates.   Anything I can do to sync the data components? Lazarus 4.6, Linux  SQLdb components. Thanks, Craig ---------------------------------------------------------------------- CraigC · 2026-04-26 12:43 As soon and I press send, I remembered to mention the database.  SQLite 3. ---------------------------------------------------------------------- JanRoza · 2026-04-26 14:24 Show some code and not just an error message. I think the second message says it all, did you do an AplyUdates after posting? ---------------------------------------------------------------------- CraigC · 2026-04-26 14:35 I don't call qry.post.  Never have with SQLite and Lazarus.  Trying to eliminate the error "Not in Edit or Insert mode" Here is my procedure for Saving or Canceling updates.  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       begin         qry.ApplyUpdates;         dmCommonData.transLedger.Commit;       end;     end     else     begin       qry.CancelUpdates;       dmCommonData.transLedger.Commit;     end;   except   on E: EDatabaseError do      begin        dmCommonData.transLedger.Rollback;        MessageDlg('Error', 'DataBase error: '+ E.Message, mtError, [mbOK], 0);      end;   end; end; ---------------------------------------------------------------------- cdbc · 2026-04-26 14:36 Hi Are you using Db-Aware-components? If so -- alas, I can't help you. I'm running SQLite3 in more apps without db-aware and it all goes like stink... Regards Benny ---------------------------------------------------------------------- CraigC · 2026-04-26 15:08 Yes, these are TDBEdits.  I'm finding that in one instance, I use TEdits and found I had issues with TDBEdits and TDBgrid.  I'm getting close to finding something else to update my old code from Delphi 7.  DBedits that were for string fields, would go blank and integers edits would be just fine.  Frustrated beyond belief. ---------------------------------------------------------------------- CraigC · 2026-04-26 15:10 I'm also finding that doing an insert goes great, but updates are really where Lazarus is buggy. ---------------------------------------------------------------------- LemonParty · 2026-04-26 15:16 Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i     begin    qry.CancelUpdates;      dmCommonData.transLedger.Commit;    end;I don't think you have to do Commit after you CancelUpdates. ---------------------------------------------------------------------- cdbc · 2026-04-26 15:21 Hi You could take a look at the attached demo, for inspiration... Regards Benny ---------------------------------------------------------------------- rvk · 2026-04-27 10:18 Quote from: LemonParty on April 26, 2026, 05:16:41 pmCode: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i     begin    qry.CancelUpdates;      dmCommonData.transLedger.Commit;    end;I don't think you have to do Commit after you CancelUpdates. I think you can... but I would do a rollback there. But even so.... before Commit or Rollback I always check the state first. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i     begin    qry.CancelUpdates;      if dmCommonData.transLedger.Active then dmCommonData.transLedger.Rollback;    end; And if you need to edit it directly again I would use CommitRetaining or RollbackRetaining. BTW. Also check the SQL component for the options (AutoApplyUpdates, AutoCommit, ImplicitTransaction, etc). ---------------------------------------------------------------------- LemonParty · 2026-04-27 11:53 Commiting changes whan you don't want them look like mistake in programs logic. Let's say we have 2 tables and we edited the first one then we edited the second table and than we want to discard changes from both tables. Rollback in this situation is reasonable, but if we do Commit then changes from the first table will be saved and from the second one will be lost. This logic is not intuitive for user, so Commit should be avoided. ---------------------------------------------------------------------- CraigC · 2026-04-27 12:11 I modified the code for Rollback. Are you saying I should not use Commit for applying updates?  Most of my issues are getting updates to save and not so much for inserting. ---------------------------------------------------------------------- rvk · 2026-04-27 12:22 Quote from: CraigC on April 27, 2026, 02:11:13 pmAre you saying I should not use Commit for applying updates?  Most of my issues are getting updates to save and not so much for inserting. No, you schouldn't use Commit after a cancel. With cancel you want to rollback any changes made in the transaction. Besides that, we don't see the complete (flow of) code. When is this called? What does DataState() do? What does the local variable s do? etc. You also have "if not dmCommonData.transLedger.Active then dmCommonData.transLedger.StartTransaction;" in there... but... when calling SaveData I assume the dataset is already in edit mode (it switches to edit mode as soon as you begin editing). So starting a transaction there is far too late (I suspect the StartTransaction is never called there because the transaction should already have been started at first edit of the data-aware edit-box). ---------------------------------------------------------------------- CraigC · 2026-04-27 12:33 function DataState(ds: TDataSet): Boolean; begin   if ds.State in [dsEdit, dsInsert] then     Result := True   else     Result := False; end; Yeah, dataset should be in dsEdit or dsInsert.  I don't call post anywhere.   Transaction should be active but I get errors that the dataset is not in dsEdit or dsInsert.  The indicator on the dbgrid, tells a different story.  It shows edit mode. ---------------------------------------------------------------------- rvk · 2026-04-27 12:47 Quote from: CraigC on April 27, 2026, 02:33:38 pmTransaction should be active but I get errors that the dataset is not in dsEdit or dsInsert.  The indicator on the dbgrid, tells a different story.  It shows edit mode. On what line do you get the error? BTW. not sure about SQLite, but it could be that Commit and Rollback also closes the dataset. (unless you have sqoKeepOpenOnCommit in the TSQLQuery.Options) You can trace/debug through the program and see where the qry.Sate flips to dsInactive or dsBrowse. ---------------------------------------------------------------------- Sieben · 2026-04-27 12:53 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 · 2026-04-27 12:54 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 · 2026-04-27 13:10 Quote from: Sieben on April 27, 2026, 02:53:10 pmAll 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 · 2026-04-28 09:24 Quote from: rvk on April 27, 2026, 03:10:57 pmQuote from: Sieben on April 27, 2026, 02:53:10 pmAll 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 · 2026-04-28 09:36 Quote from: Sieben on April 28, 2026, 11:24:42 amThat'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 · 2026-04-28 10:08 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 · 2026-04-28 11:28 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 · 2026-04-28 11:36 Quote from: CraigC on April 28, 2026, 01:28:19 pmI 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). Quote from: CraigC on April 28, 2026, 01:28:19 pmAs soon as I edit one field I get this.I take it you do get the "Transaction is active" message? Quote from: CraigC on April 28, 2026, 01:28:19 pm  I then call saveData() and that code again is below.When exactly do you call that? Quote from: CraigC on April 28, 2026, 01:28:19 pmCode: [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 · 2026-04-28 12:22 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 · 2026-04-29 21:19 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 · 2026-04-29 21:59 Can you check with the debugger what the qry.State is right before you execute qry.ApplyUpdates? ---------------------------------------------------------------------- CraigC · 2026-04-30 19:27 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 · 2026-04-30 20:20 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 · 2026-04-30 21:46 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 · 2026-04-30 21:56 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) ---------------------------------------------------------------------- egsuh · 2026-05-06 06:44 I haven't looked into the codes here in depth, but I'd like to mention basic structure of SQL databases, which I had difficulty when I first entered the SQL world. What are called as TDataSet and its decendants like TSQLQuery, etc. are separate entities in the of SQL DB world. If you are operating with local tables, e.g. csv files, then the TDataSets (TCSVDataSet, TBufDataSet, etc.) are directly linked to the physical DB on HDD. But in SQL, once you open a dataset, like   SQLQuery.Transaction.Active := True;   SQLQuery.SQL.Text := 'select * from table1';   SQLQuery.Open;   (SQLQuery.Transaction as TSQLTransaction).RollbackRetaining; // with this, Server DB file is disconnected. You'd better think that this makes a copy of the subset of the table (the whole table in this case) from physical table on HDD. And following operations on this dataset, like        SQLQuery.Edit;        SQLQuery.FieldByName('f1').AsInteger := 199999;        SQLQuery.Post; operate only on the TSQLQuery, which is "COPY" of original table. Here, you may insert a record and then cancel it. If all options are manual (nothing is done automatically), then you should start transaction yourself again, because this is related with ServerDB, not local TDataSet.   SQLQuery.Transaction.Active := True;   SQLQuery.ApplyUpdates;   (SQLQuery.Transaction as TSQLTransaction).Commit; // with this, Server DB file is disconnected. If you rollback transaction after ApplyUpdates, then the changes will not be saved to the permanent DB. And "commiting" after ApplyUpdates will close SQLQuery. If you call CommitRetaining, then the updates will be applied (changes will be saved to the permanent DB), but the SQLQuery DataSet will still remain open (as dsBrowse). And there are options like AutoApplyUpdates, AutoCommit, etc. autocommit, etc. Possibly AutoApplyUpdates will carry out ApplyUpdate as soon as "post" is done. With AutoCommit, any updates to the permanent DB will be committed automatically, I guess. Using these options you can  alleviate starting and stopping transactions. You may already know on these. Sorry if you do. If not, please check your codes step by step.  ======================== I made mistake. I thank rvk for correcting them (below). ---------------------------------------------------------------------- rvk · 2026-05-06 07:05 Quote from: egsuh on May 06, 2026, 08:44:05 amBut in SQL, once you open a dataset, like   SQLQuery.Transaction.Active := True;   SQLQuery.SQL.Text := 'select * from table1';   SQLQuery.Open;   (SQLQuery.Transaction as TSQLTransaction).RollbackRetaining; // with this, Server DB file is disconnected. You'd better think that this makes a copy of the subset of the table (the whole table in this case) from physical table on HDD. And following operations on this dataset, like        SQLQuery.Edit;        SQLQuery.FieldByName('f1').AsInteger := 199999;        SQLQuery.Post; And you think that when you do SQLQuery.Edit that the transaction doesn't automatically start again? If it doesn't, you get an error that you can't edit a record without a transaction. With RollbackRetaining the transaction is rolled back, but the transaction and table remain active and open. So just above ApplyUpdates the transaction is already active and setting it to true there is useless. Transactions are always from the opening/reading of a table until you close the transaction. You can't read and edit records without being in a transactional state (that's the whole point of transactions). Above is the case with normal databases (like MySQL, SQLite, mssql, firebird, etc). Are you saying that with TBufDataSet you can edit a record without a transaction active?? That might be possible because TBufDataSet doesn't need transaction because it operates completly locally. But when dealing with databases, you do need them. ---------------------------------------------------------------------- egsuh · 2026-05-06 08:24 QuoteWith RollbackRetaining the transaction is rolled back, but the transaction and table remain active and open. So just above ApplyUpdates the transaction is already active and setting it to true there is useless. Transactions are always from the opening/reading of a table until you close the transaction. You can't read and edit records without being in a transactional state (that's the whole point of transactions). You are right. Transaction remains Active after RollbackRetaining or CommitRetaining is called.  So SQLQuery.Transaction.Active := True above ApplyUpdates is not necessary.  I make a copy of TSQLQuery to TBufDataSet because the SQLQuery is from over the Internet, so on my local application there are no transactions. ---------------------------------------------------------------------- CraigC · 2026-05-06 10:25 I put together a sample app and database.  Events for dataset change do not jive. As soon as I edit a field it should fire and it does not.  Can't refresh due to pending updates. etc. Look at the screenshots. Will upload the sample soon. Craig Procedure TForm1.dsDataChange(Sender: TObject; Field: TField); begin   if (field = nil) then exit;   sb.Panels.Items[0].Text := 'Field: '+ Field.FieldName;   if qry.UpdateStatus = usInserted then     sb.Panels.Items[1].Text := 'Data in Insert Mode'   else if qry.UpdateStatus = usModified then     sb.Panels.Items[1].Text := 'Data in Edit Mode'   else if qry.UpdateStatus = usUnModified then     sb.Panels.Items[1].Text := 'Data in Browse Mode'; end; procedure TForm1.dsStateChange(Sender: TObject); begin   if qry.UpdateStatus = usInserted then   Caption := 'Data in Insert Mode'   else if qry.UpdateStatus = usModified then     Caption := 'Data in Edit Mode'   else if qry.UpdateStatus = usUnModified then     Caption := 'Data in Browse Mode'; end; ---------------------------------------------------------------------- rvk · 2026-05-06 10:42 Quote from: CraigC on May 06, 2026, 12:25:13 pmprocedure TForm1.dsStateChange(Sender: TObject); begin   if qry.UpdateStatus = usInserted then   Caption := 'Data in Insert Mode'   else if qry.UpdateStatus = usModified then     Caption := 'Data in Edit Mode'   else if qry.UpdateStatus = usUnModified then     Caption := 'Data in Browse Mode'; end;                                          Can you use the code-tags in the future for code? It makes it a lot more readable. You can use the # above the editor. Anyway... why are you using TSQLQuery.UpdateStatus for the mode? The dsStateChange is connected to a TDatasource. To avoid confusion you might want to use the actual TDataSource for showing its TDataSource.State. Then you are sure you are showing the correct TDatasource with its state. Can you change it in this (so really using the State of the TDataset connected to the TDataSource). (Sender should be a TDatasource automatically because you attached dsStateChange to a TDataSource) Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TForm1.dsStateChange(Sender: TObject);begin  if TDataSource(Sender).State = dsInsert then    Caption := 'Data in Insert Mode'  else if TDataSource(Sender).State = dsEdit then    Caption := 'Data in Edit Mode'  else if TDataSource(Sender).State = dsBrowse then    Caption := 'Data in Browse Mode'  else    Caption := 'Data is in Unknown Mode';end; ---------------------------------------------------------------------- CraigC · 2026-05-06 11:57 TDataSource(Sender).State >>> this is more accurate. Thank you.  The qry.updatestatus was something I found on Google and thought I'd try it.   I'll do more testing.  I want to be able to do an update and be able to do a refresh. Up till now, it would not. Not sure what you are referring to in regards to tags? Craig ---------------------------------------------------------------------- egsuh · 2026-05-06 12:04 QuoteNot sure what you are referring to in regards to tags? Please mark the "codes" part --- your pascal program by Shift + Key_down or Key_up. And then press "#" button that is above the white box into which you are typing in text. ---------------------------------------------------------------------- rvk · 2026-05-06 12:05 Quote from: CraigC on May 06, 2026, 01:57:19 pmNot sure what you are referring to in regards to tags? Look at the code in your post and then look at the code in my post. In my post the code is in a code-block neatly formatted. Your code is proportional and not formatted. The problem with using code outside a code-block, like you did, is that for one the text is proportional (and jumps all over the place). But what is worse is that it can corrupt your code on screen. For example if you use Array[ i ] for referencing an index i in an array (without the spaces around the i) the forum software will change it into "switch to italic" and the [ i ] is not visible. if array = 0 then // <--- this goes wrong and corrupts your code (there is a [ i ] after array which you don't see) !! Code: [Select]if array[i] = 0 then // <-- (this is correct) You can type [ code ] and [/ code ] around your code yourself (without the spaces) or select your code and press the # button at the top of the editor when creating a post. ---------------------------------------------------------------------- CraigC · 2026-05-06 12:40 Thanks, I will give that a try next time. Code: [Select] You can type [ code ] and [/ code ] around your code yourself (without the spaces) or select your code and press the # button at the top of the editor when creating a post. ---------------------------------------------------------------------- CraigC · 2026-05-08 11:27 After a fairly long discussion with Google AI.   I tried what I had already done in the sample app.  Reduce the number of fields in the table and to do a POST before APPLYUPDATES.  I had 24 fields or so and I reduced it to 12 I think.  (I'll have to create another table for the other fields)  It works.  Don't know if this is an issue with Lazarus or SQLite3.  But it works and I can move on after fighting this for too long.  Honestlt, I don't know if doing a POST does help, I'll have to do more testing. thought this might help someone else. Craig ---------------------------------------------------------------------- rvk · 2026-05-08 11:55 Quote from: CraigC on May 08, 2026, 01:27:07 pmI had 24 fields or so and I reduced it to 12 I think.  (I'll have to create another table for the other fields)  It works.  Don't know if this is an issue with Lazarus or SQLite3. It's not a problem with Lazarus or SQLite itself. But it could be that you have some fields named incorrectly. Reserved names or the SQL for UPDATE/INSERT is constructed incorrectly. But 24 fields or more shouldn't be a problem onto itself. (I think SQLITE_MAX_COLUMN is 2000 so 24 shouldn't be a problem) (Maybe you find this out when adding the other 12 fields if they are named incorrectly) ---------------------------------------------------------------------- CraigC · 2026-05-08 12:12 Code: [Select]    MfgProductNo       Char(20),     VendorUID          Integer,     SalesAccount       Char(20),     COSAccount         Char(20),     InventAccount      Char(20),     Allocated          Integer,     OnHand             Integer,     OnOrder            Integer,     MonthlySales       Integer,     YearlySales        Integer,     MinQty             Integer,     MaxQty             Integer,     ReorderQty         Integer, these are the fields removed. ---------------------------------------------------------------------- rvk · 2026-05-08 12:17 Quote from: CraigC on May 08, 2026, 02:12:49 pmCode: [Select]    MfgProductNo       Char(20),     VendorUID          Integer,     SalesAccount       Char(20),     COSAccount         Char(20),     InventAccount      Char(20),     Allocated          Integer,     OnHand             Integer,     OnOrder            Integer,     MonthlySales       Integer,     YearlySales        Integer,     MinQty             Integer,     MaxQty             Integer,     ReorderQty         Integer, these are the fields removed. Allocate or Allocated (not sure which) is a reserved word in some of the SQL variants. But I don't think it is one in SQLite. The others are definitely permitted. ---------------------------------------------------------------------- CraigC · 2026-05-08 12:37 I checked Allocated on SQLite's website, it's not listed.  I may just to be on the safe side, rename that field. ---------------------------------------------------------------------- rvk · 2026-05-08 12:46 Quote from: CraigC on May 08, 2026, 02:37:35 pmI checked Allocated on SQLite's website, it's not listed.  I may just to be on the safe side, rename that field. BTW. Did the caption in that latest change (with the code I gave) still show the dataset was in Edit mode? Further... from your original problem... the insert worked correctly ?? (then it's not a field problem). If only UPDATE and refresh don't work correctly... it could also just be an update problem with the UPDATE SQL. Did you create your own UPDATE statements or are you using the automatically generated ones? Maybe showing the complete SQLs would help. ---------------------------------------------------------------------- CraigC · 2026-05-08 12:51 Insert SQL seem to be working just fine.  UPDATE sql was the problem.  but it was the query error not being in dsEdit was the only error. SQL below is after the fields were removed.  Yes, your suggestion on how to check state worked perfectly.  Thank you. Code: [Select]   SQL.Add('SELECT UID, ProductNo, ProductDescription, UOM, ');     SQL.Add('StdQty, UnitPrice, StdCost, ');     SQL.Add('Suspended, ProductClass, ProductNote, ');     SQL.Add('Taxable1, Taxable2 FROM Inventory ORDER BY ProductNo');     {--------}     UpdateSQL.Clear;     UpdateSQL.Add('UPDATE INVENTORY SET ProductNo=:ProductNo, ProductDescription=:ProductDescription, ');     UpdateSQL.Add('UOM=:UOM, StdQty=:StdQty, UnitPrice=:Unitprice, StdCost=:StdCost, Suspended=:Suspended, ');     UpdateSQL.Add('ProductClass=:ProductClass,ProductNote=:ProductNote, ');     UpdateSQL.Add('Taxable1=:Taxable1, Taxable2=:Taxable2 WHERE UID=:OLD_UID');     {--------}     DeleteSQL.Clear;     DeleteSQL.Add('DELETE FROM Inventory WHERE (UID = :OLD_UID)');     {--------}     InsertSQL.Clear;     InsertSQL.Add('INSERT into Inventory ');     InsertSQL.Add('(ProductNo, ProductDescription, UOM, StdQty, UnitPrice, StdCost, ');     InsertSQL.Add('Suspended, ProductClass, ProductNote, ');     InsertSQL.Add('Taxable1, Taxable2) VALUES ');     InsertSQL.Add('(:ProductNo, :ProductDescription, :UOM, :StdQty, :UnitPrice, :StdCost, ');     InsertSQL.Add(':Suspended, :ProductClass, :ProductNote, :Taxable1, :Taxable2) ');     RefreshSQL.Clear;     RefreshSQL.Add('SELECT UID, ProductNo, ProductDescription, UOM, StdQty, UnitPrice, StdCost, ');     RefreshSQL.Add('Suspended, ProductClass, ProductNote, ');     RefreshSQL.Add('Taxable1, Taxable2 from Inventory WHERE (UID = :UID) '); ---------------------------------------------------------------------- rvk · 2026-05-08 13:10 Ok. That seems to be ok (but it works now). BTW. Did this solve the problem from your original post??? Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i qry.Post;qry.ApplyUpdates;dmCommonData.transLedger.CommitRetaining; If you do that (.Post before ApplyUpdates) does it work then? ---------------------------------------------------------------------- CraigC · 2026-05-08 14:41 yes, qry.post before ApplyUpdates does work.  However, look at the screenshots.  the Lazarus grid doesn't show the two new items entered but the data from the server shows it correctly. In the Lazarus grid, check the bottom record.   The query will need to be refreshed or closed then reopened. ---------------------------------------------------------------------- rvk · 2026-05-08 16:20 Quote from: CraigC on May 08, 2026, 04:41:53 pmyes, qry.post before ApplyUpdates does work.  However, look at the screenshots.  the Lazarus grid doesn't show the two new items entered but the data from the server shows it correctly. In the Lazarus grid, check the bottom record.   The query will need to be refreshed or closed then reopened. I'm starting to see what you mean. Are you saying that when you post, update and commit your changes in a TSQLQuery, that the Grid doesn't show this until it closes and opens the dataset again. If that's the case then I know what you are missing. In that case you need to learn more about transactions. Remember I said that a transaction is always automatically started when you open a dataset? That's because transaction is done in levels. When you open a dataset, a transaction is started, and depending on the sort of transaction, EVERYTHING you do is done in a sort of snapshot of the database at that point. So, if in ANOTHER transaction someone adds a new record, that record is not yet visible in any of the other transaction anyone that has a table open. So if the TDBGrid/TSQLQuery has a different transaction than the one with which you added a record, that record is not visible until you close and open the table and start a new "viewing" transaction. BTW. This includes dbgrids from other programs which might show the database (like a database manager). So, yes, if you do a "Add record" in another transaction, that record is not visible in a TDBGrid/TSQLQuery which might already be open. You do a "save position", close, open, "restore position" on the TDGrid. I even have some extra functions which saves the exact location of the selected record (even to the first record visible in the TDBgrid), to be able to restore it exactly to the same state as before the close/open. https://www.sqlite.org/lang_transaction.html QuoteWhile a read transaction is active, any changes to the database that are implemented by separate database connections will not be seen by the database connection that started the read transaction. If database connection X is holding a read transaction, it is possible that some other database connection Y might change the content of the database while X's transaction is still open, however X will not be able to see those changes until after the transaction ends. While its read transaction is active, X will continue to see an historic snapshot of the database prior to the changes implemented by Y. ---------------------------------------------------------------------- egsuh · 2026-05-09 00:46 QuoteSo, if in ANOTHER transaction someone adds a new record, that record is not yet visible in any of the other transaction anyone that has a table open. So if the TDBGrid/TSQLQuery has a different transaction than the one with which you added a record, that record is not visible until you close and open the table and start a new "viewing" transaction. CraigC does not seem to mean this. With only a single DataSet is open (with single transaction), new records are entered and posted, and then ApplyUpdates is called. And then the new records disappear. This should happen if we assume : 1)  dataset is open with transaction A. 2)  inserting and posting records are done with transaction B, or at least ApplyUpdates is done with transaction B 3)  After Applyupdates, it comes back to the state of transaction A. Is what's happening and possible? ---------------------------------------------------------------------- egsuh · 2026-05-22 07:40 @rvk, QuoteWith RollbackRetaining the transaction is rolled back, but the transaction and table remain active and open. If this means transaction is still active after commitretaining/rollbackretaining, then should it be committed or rolledback afterwards explicitly? I'm afraid that the transaction might remain active even after the TSQLQuery / TSQLTransaction are destroyted, if (as I assume) transaction is maintained by database server? ---------------------------------------------------------------------- rvk · 2026-05-22 08:11 Quote from: egsuh on May 22, 2026, 09:40:50 amQuoteWith RollbackRetaining the transaction is rolled back, but the transaction and table remain active and open. If this means transaction is still active after commitretaining/rollbackretaining, then should it be committed or rolledback afterwards explicitly? I'm afraid that the transaction might remain active even after the TSQLQuery / TSQLTransaction are destroyted, if (as I assume) transaction is maintained by database server? When you do CommitRetaining, the transaction is committed to the database and other clients could then read your committed values. Because of Retaining, the transaction remains active. Essentially you could see it as a Commit & Start transaction in one. And yes, when you close the TSQLQuery, the transaction remains active. If you have uncommitted records, they are still not visible to others (or yourself in a different transaction) unless you did a CommitRetaining. Only when you Commit or Rollback the transaction, the transaction is released. Also note... that freeing a TSQLTransaction has a default action. You could set it to Commit or Rollback. That ensures that when you free the TSQLTransaction (or close the program) the transaction is freed on the DB server. So if you have created a TSQLTransaction with the owner as TSQLQuery, just doing TSQLQuery.Free will also free the TSQLTransaction (because owners also free owned components) and the default action of that TSQLTransaction is automatically executed. I often do sql.Transaction := TSQLTransaction.Create(sql) to make sql the owner (so it's automatically freed). If you have a larger program where you have multiple tables and multiple TSQLQuery's, then you might want an independent TSQLTranaction so it oversees all those TSQLQuery's. In that case you don't want the TSQLTransaction to be closed when one of those queries close and you want to do that manually (or give the user a "Apply all" or "Cancel all" button or something). ---------------------------------------------------------------------- egsuh · 2026-05-22 09:24 Hmmm ... I'm running FastCGI, and am checking the transaction at the "AfterResponse". Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TwmAQs.DataModuleAfterResponse(Sender: TObject; AResponse: TResponse);begin   if aqfb5.trAQ.Active then begin      aqfb5.trAQ.Commit;  // This is Called.      Log('Module s _ Transaction.commit called at DataModuleAfterResponse');   end;end;  And sometimes this log is executed. With FastCGI, this webmodules are created / destroyed for every request, but there is another datamodule, which  contains database and all related methods. All DB operations are done within this datamodule. There are several commitretaining/rollbackretaining calls. at https://www.freepascal.org/docs- html/fcl/sqldb/tsqltransaction.html, TSQLTransaction.Action does not do anything. And https://www.freepascal.org/docs- html/fcl/sqldb/tsqltransaction.options.html says stoUseImplicit means that no explicit transaction start and stop commands will be sent to the server when the Commit or Rollback methods are called (effectively making them a no-op at the DB level). And I experienced that while one transaction is staying active and other operations are done on DB, at some point the DB is slowed down very much. In the previous case, I check with trAQ, but many transactions are created and destroyed temporarily. Not sure all those transactions are stopped at the destruction. ---------------------------------------------------------------------- rvk · 2026-05-22 09:34 Quote from: egsuh on May 22, 2026, 11:24:23 amWith FastCGI, this webmodules are created / destroyed for every request, but there is another datamodule, which  contains database and all related methods. All DB operations are done within this datamodule. There are several commitretaining/rollbackretaining calls. With Retaining the transaction would be active so it would trigger that log. Quote from: egsuh on May 22, 2026, 11:24:23 amat https://www.freepascal.org/docs-html/fcl/sqldb/tsqltransaction.html, TSQLTransaction.Action does not do anything. O wow... property Action: TCommitRollbackAction; [rw] Currently unused in SQLDB That really seems to be an error. From the source: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TSQLTransaction.EndTransaction;begin  Case Action of    caCommit, caCommitRetaining :      Commit;    caNone,    caRollback, caRollbackRetaining :      if not (stoUseImplicit in Options) then      RollBack      else        CloseTrans;  end;end;And EndTransaction is called on freeing TSQLTransaction. So that really is an old wiki. Quote from: egsuh on May 22, 2026, 11:24:23 amAnd I experienced that while one transaction is staying active and other operations are done on DB, at some point the DB is slowed down very much. In the previous case, I check with trAQ, but many transactions are created and destroyed temporarily. Not sure all those transactions are stopped at the destruction. From the source you see that in TSQLTransaction is the transaction is freed when the component is destroyed. So as long as the CGI destroys the component you shouldn't have lingering transactions. What database is used? In some databases you can view the current running transactions. For example... I have this in Firebird (to see the transactions and statements): Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SELECT  ma.mon$attachment_id,  ma.mon$server_pid,  ma.mon$state,  ma.mon$attachment_name,  ma.mon$remote_protocol,  ma.mon$remote_address,  ma.mon$remote_process,  ma.mon$timestamp,  mt.mon$transaction_id,  mt.mon$timestamp,  CAST(ms.mon$sql_text AS CHAR(5000)) AS Sql_statementFROM mon$attachments maLEFT JOIN mon$transactions mt ON mt.mon$attachment_id=ma.mon$attachment_idLEFT  JOIN mon$statements ms ON ms.mon$transaction_id=mt.mon$transaction_idORDER BY 1 ---------------------------------------------------------------------- egsuh · 2026-05-22 09:41 I'm using Firebird. I don't know. All temporary transactions are destroyed. ---------------------------------------------------------------------- rvk · 2026-05-26 09:09 Quote from: egsuh on May 22, 2026, 11:24:23 amAnd I experienced that while one transaction is staying active and other operations are done on DB, at some point the DB is slowed down very much. Quote from: egsuh on May 22, 2026, 11:41:53 amI'm using Firebird. I don't know. All temporary transactions are destroyed. If all transactions are destroyed and there are no long running transaction, the slowness must be caused by something else. ---------------------------------------------------------------------- 1HuntnMan · 2026-08-10 19:41 Try this... With a menu item Save Changes or a bitbtn/spdbtn, execute this procedure below. You'll need to pass it your DataSet and Transaction names... Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure SaveQueryChanges(Q: TSQLQuery; Trans: TSQLTransaction);begin  if Q = nil then Exit;   if Q.State in [dsEdit, dsInsert] then    Q.Post;   //if Q.ChangeCount > 0 then    Q.ApplyUpdates;   if Q.Active then    Q.Close;   if Assigned(Trans) and Trans.Active then  begin    Trans.Commit;    Trans.StartTransaction;  end;end;