TSQLquery does not save added records

[dpap]

EDIT2 : ChatGPT gave me the answer

I wrote the code bellow

//create table COUNTERPARTS
.....
SQLTransaction1.CommitRetaining;
     COUNTERPARTS.SQL.Text := 'SELECT * FROM COUNTERPARTS';
     COUNTERPARTS.open;
     COUNTERPARTS.appendRecord(['1111',3,'name-1']);
     COUNTERPARTS.appendRecord(['2222',3,'name-2']);
     COUNTERPARTS.appendRecord(['3333',3,'name-3']);
     COUNTERPARTS.appendRecord(['4444',3,'name-4']);
     SQLTransaction1.CommitRetaining;

In a TBGrid I can see these records but actually they didn't saved at database file although when I close the application I call commit. The same hapens when I append records scrolling down the DBGrid. What I'm missing ?

EDIT:  I tried everything I know but I can't manage to save changes in TSQLquery (appendRecord, Edit etc). AppendRecord seems to work only with memory tables. I can bypass this using SQL.text := 'INSERT...' but I can not manage to save changes that I do in DBedits or DBGrids. Please help me! I'm stuck as I am newby in Lazarus (and SQL). In Delphi where I am comming from has not these issues

[egsuh]

You should understand the relationship between database (a physical server database like SQLite, Firebird, MySQL, etc.) and TDataset (TSQLQuery, TIBDataSet, etc. TMemDataSet or TBufDataSet are also TDataSet descendants).

When you open an SQL within TSQLQuery or whatever, like
          Select * from counterparts;

then what you actually have is a subset copy of the whole table (in this case whole table of counterparts. If there are any "where" clauses then you will copy only a subset of the whole table).

And TDataSet methods like edit, append, appendrecord, etc. operate only on the dataset, not the database file itself. In order to update your changes in the TDataSet to the permanent database file, you have to call

       COUNTERPARTS.ApplyUpdates;

And the transaction is on the permanent database file, not your TDataSet. So the exact order in your example would be:

Code: Pascal  [Select][+][-]

1.      // you can assign this anytime
2.      COUNTERPARTS.SQL.Text := 'SELECT * FROM COUNTERPARTS';
3.  
4.      // following section is downloading records from server database to TDataSet (COUNTERPARTS, in this case)
5.      SQLTransaction1.Active := True;
6.      COUNTERPARTS.open;
7.      SQLTransaction1.CommitRetaining; // Simply opening a dataset, RollbackRetaining may be used as well
8.  
9.      // As Transaction has been committed or rolled back, operations from now on has nothing to do with server database.
10.  
11.      COUNTERPARTS.appendRecord(['1111',3,'name-1']);
12.      COUNTERPARTS.appendRecord(['2222',3,'name-2']);
13.      COUNTERPARTS.appendRecord(['3333',3,'name-3']);
14.      COUNTERPARTS.appendRecord(['4444',3,'name-4']);
15.  
16.      // As you have finished editing TDataSet, now you want to upload the TDataSet content to your permanent database file.      
17.    
18.      SQLTransaction1.Active := True;
19.      CounterParts.ApplyUpdates;
20.      SQLTransaction1.CommitRetaining;
21.  

In order for ApplyUpdates to work correctly, the property "use primary key as key" of counterparts should be set true (which is default), or you have to define InsertSQL, UpdateSQL, and DeleteSQL yourself.

[Zvoni]

FWIW, personally, i don't use "Edit, Append, whatever" of TDataset, and i wouldn't touch those InsertSQL et al of TSQLQuery with a 10 feet long poker.
Use the correct SQL-Statements yourself, and be done with it.
Much finer control over everything

[egsuh]

FWIW, personally, i don't use "Edit, Append, whatever" of TDataset, and i wouldn't touch those InsertSQL et al of TSQLQuery with a 10 feet long poker.
Use the correct SQL-Statements yourself, and be done with it.
Much finer control over everything

This is right. I do not recommend to edit TDataSet and save it to SQL database as well. TDataSet descendants were from Delphi era, when single file tables were the mainstream, like dBase, FoxBase, etc.  The most basic TDataSet was TTable, which can directly connect to a dBase or Foxbase table, and TTable was almost for directly editing the table itself.

Some applications of mine need to edit dataset contents, like editing address, etc. I myself use SQL statements to update any changes in my dataset. But well, easy way is also welcome if available.