SQLite, master/detail, inserting new records, auto-increment primary key

[tatamata]

How to handle master/detail entering of new data with SQLite if both master and detail tables have autoincrement primary keys and primary key of master is foreign key of detail table?

When a new record is entered into master dataset, the primary key is not yet queried from SQLite, thus is not available to slave detail dataset and foreign key violation happens.
If master dataset is refreshed, then the primary key value becomes available, but the master dataset goes to the first record, not to the one that has been edited or inserted, which is annoying and not user-freiendly behavior.

How to solve this?

[taazz]

autoinc details at https://www.sqlite.org/autoinc.html. So I guess something along the lines of

Code: Delphi  [Select][+][-]

1. function GetLastAutoinc(const aCnn:TSQLConnection):int64;
2. var
3.   vQry : TSQLQuery;
4.   vTrn :TSQLTransaction;
5. begin
6.   Result := -1; //invalid result.
7.   vQry := TSQLQuery.Create(Nil);
8.   vTrn := TSQLTransaction.Create(Nil);
9.   try
10.     vQry.Transaction := vTrn;
11.     vTrn.DataBase := aCnn;
12.     vQry.SQL.Text :='SELECT last_insert_rowid()';
13.     vQry.Open;
14.     vQry.First;
15.     Result := vQry.Fields[0].AsLargeInt;
16.   finally
17.     vQry.Free;
18.     vTrn.Free;
19.   end;
20. end;
21.  

just call it in the after insert event of your dataset or something along those lines. Make sure that no other insert is executed between calls otherwise it will report the wrong id.

PS. No compilation, no execution no testing what so ever. Use at your own risk.

[rvk]

This might be easier 

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

1. MyLastInsertedID := SQLite3Connection1.GetInsertID;

GetInsertID is already declared in TSQLite3Connection as:

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

1. function TSQLite3Connection.GetInsertID: int64;
2. begin
3.  result:= sqlite3_last_insert_rowid(fhandle);
4. end;

Also see https://www.sqlite.org/c3ref/last_insert_rowid.html

[tatamata]

It seems that solution is even simpler.

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

1. procedure RefreshADatasetAfterInsert(pDataSet: TDataSet);
2. //This procedure refreshes a dataset and positions cursor to last record
3. var
4.   {vBookmark: TBookmark; //This does not work correctly after Refresh}
5.   vUpdateStatus: string;
6.   vMessage: string;
7.   vID: Integer;
8. begin
9.   try
10.     try
11.       //Take info about dataset update status BEFORE Refresh!
12.       case pDataset.UpdateStatus of
13.           usUnmodified: vUpdateStatus:='usUnmodified';
14.           usModified: vUpdateStatus:='usModified';
15.           usInserted: vUpdateStatus:='usInserted';
16.           usDeleted: vUpdateStatus:='usDeleted';
17.           else  vUpdateStatus:='SomethingOther' ;
18.       end;
19.       {vBookmark:=pDataset.GetBookmark; //This does not work correctly after Refresh!}
20.  
21.       (pDataset as TSQLQuery).ApplyUpdates;
22.  
23.       //Now come back to respective row
24.       if vUpdateStatus='usInserted' then begin
25.         pDataset.Refresh;
26.        {pDataset.GotoBookmark(vBookmark); //This does not work correctly after Refresh!}
27.         pDataSet.Last;
28.       end;
29.  
30.     except
31.       on E:Exception do begin
32.         vMessage:=E.Message;
33.         ShowMessage(vMessage);
34.       end;
35.     end;
36.   finally
37.     {pDataset.FreeBookmark(vBookmark); //This does not work correctly after Refresh! }
38.   end;
39. end;
40.  
41. procedure TDataModule1.SQLQuery1AfterPost(DataSet: TDataSet);
42. begin
43.   RefreshADatasetAfterInsert(Dataset);
44. end;  
45.  
46.  

[rvk]

Code: [Select]

        pDataSet.Last;


Be very very careful here. Because you might have a select statement with a different order than the ID, the "Last" record in the dataset might NOT be the last record you inserted !!

Only sure way to do is to get the last inserted ID (with GetInsertID) and do a Lookup for that record. In that case, even when the dataset is not ordered on ID, you end up on the correct record.

[tatamata]

Code: [Select]

        pDataSet.Last;


Be very very careful here. Because you might have a select statement with a different order than the ID, the "Last" record in the dataset might NOT be the last record you inserted !!

Only sure way to do is to get the last inserted ID (with GetInsertID) and do a Lookup for that record. In that case, even when the dataset is not ordered on ID, you end up on the correct record.

Thanks, I understand your point, but in my case, the datasets are always sorted by primary key, so no dilema.
On other hand, your solution is SQLite specific...what would be universal solution (PostgreSQL, Firebird etc.)?

[taazz]

It seems that solution is even simpler.

Not really. Although it works if there is no "order by" in the sql command it will fail the moment an order by clause is added or under any other condition that will change the default order for example if an other record is inserted between applyupdates and refresh. The logical think to do is to save the GetInsertID in to a local variable and use the dataset.locate method to place your cursor to the correct record. eg     

Code: Delphi  [Select][+][-]

1. procedure RefreshADatasetAfterInsert(pDataSet: TDataSet);
2. var
3.   vLastID : Int64;
4.   vUpdateStatus: string;
5.   vMessage: string;
6. begin
7.   try
8.     try
9.       (pDataset as TSQLQuery).ApplyUpdates;
10.  
11.       vLastIDE := ((pDataSet as TSQLQuery).DataBase as TSQLite3Connection).GetInsertID;
12.       if vUpdateStatus='usInserted' then begin
13.         pDataset.Refresh;
14.         pDataset.Locate(['ID'],[vLastID],[]);
15.       end;
16.  
17.     except
18.       on E:Exception do begin
19.         vMessage:=E.Message;
20.         ShowMessage(vMessage);
21.       end;
22.     end;
23.   finally
24.     {pDataset.FreeBookmark(vBookmark); //This does not work correctly after Refresh! }
25.   end;
26. end;
27.  

Just a FYI, you should never handle exception in any helper procedures. Only events and top level code is allowed to handle exception or show messages the libraries and helpers must raise or let the exception go through to higher levels, as a rule of thumb if it is not part of a form or frame do not handle exceptions.

[tatamata]

Ok. good, so we can conclude this discussion with two overloaded procedures:

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

1. interface
2.     procedure RefreshADatasetAfterInsert(pDataSet: TDataSet);overload;
3.     procedure RefreshADatasetAfterInsert(pDataSet: TDataSet; pKeyField: string);overload;  
4.  
5. implementation
6.  
7. procedure RefreshADatasetAfterInsert(pDataSet: TDataSet; pKeyField: string);
8. //This procedure refreshes a dataset and positions cursor to last record
9. //To be used if Dataset is not guaranteed to be sorted by an autoincrement primary key
10. var
11.   vUpdateStatus: string;
12.   vMessage: string;
13.   vLastID: Integer;
14. begin
15.   //Take info about dataset update status BEFORE Refresh!
16.   case pDataset.UpdateStatus of
17.       usUnmodified: vUpdateStatus:='usUnmodified';
18.       usModified: vUpdateStatus:='usModified';
19.       usInserted: vUpdateStatus:='usInserted';
20.       usDeleted: vUpdateStatus:='usDeleted';
21.       else  vUpdateStatus:='SomethingOther' ;
22.   end;
23.   //Get last inserted ID in the database
24.   vLastID:=((pDataSet as TSQLQuery).DataBase as TSQLite3Connection).GetInsertID;
25.  
26.   (pDataset as TSQLQuery).ApplyUpdates;
27.  
28.   //Now come back to respective row
29.   if vUpdateStatus='usInserted' then begin
30.     pDataset.Refresh;
31.     //Refresh and go back to respective row
32.     pDataset.Locate(pKeyField,vLastID,[]);
33.   end;
34. end;
35.  
36. procedure RefreshADatasetAfterInsert(pDataSet: TDataSet);
37. //This procedure refreshes a dataset and positions cursor to last record
38. //To be used only if DataSet is guaranteed to be sorted by an autoincrement primary key
39. var
40.   vUpdateStatus: string;
41.   vMessage: string;
42.   vLastID: Integer;
43. begin
44.   //Take info about dataset update status BEFORE Refresh!
45.   case pDataset.UpdateStatus of
46.       usUnmodified: vUpdateStatus:='usUnmodified';
47.       usModified: vUpdateStatus:='usModified';
48.       usInserted: vUpdateStatus:='usInserted';
49.       usDeleted: vUpdateStatus:='usDeleted';
50.       else  vUpdateStatus:='SomethingOther' ;
51.   end;
52.   vLastID:=((pDataSet as TSQLQuery).DataBase as TSQLite3Connection).GetInsertID;
53.  
54.   (pDataset as TSQLQuery).ApplyUpdates;
55.  
56.   //Refresh and go back to respective row
57.   if vUpdateStatus='usInserted' then begin
58.     pDataset.Refresh;
59.     //Dangerous!
60.     pDataSet.Last;
61.   end;
62. end;                                                          
63.  
64.  

[taazz]

Ok. good, so we can conclude this discussion with two overloaded procedures:

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

1. interface
2.     procedure RefreshADatasetAfterInsert(pDataSet: TDataSet);overload;
3.     procedure RefreshADatasetAfterInsert(pDataSet: TDataSet; pKeyField: string);overload;  
4.  
5. implementation
6.  
7. procedure RefreshADatasetAfterInsert(pDataSet: TDataSet; pKeyField: string);
8. //This procedure refreshes a dataset and positions cursor to last record
9. //To be used if Dataset is not guaranteed to be sorted by an autoincrement primary key
10. var
11.   vUpdateStatus: string;
12.   vMessage: string;
13.   vLastID: Integer;
14. begin
15.   //Take info about dataset update status BEFORE Refresh!
16.   case pDataset.UpdateStatus of
17.       usUnmodified: vUpdateStatus:='usUnmodified';
18.       usModified: vUpdateStatus:='usModified';
19.       usInserted: vUpdateStatus:='usInserted';
20.       usDeleted: vUpdateStatus:='usDeleted';
21.       else  vUpdateStatus:='SomethingOther' ;
22.   end;
23.   //Get last inserted ID in the database
24.   vLastID:=((pDataSet as TSQLQuery).DataBase as TSQLite3Connection).GetInsertID;
25.  
26.   (pDataset as TSQLQuery).ApplyUpdates;
27.  
28.   //Now come back to respective row
29.   if vUpdateStatus='usInserted' then begin
30.     pDataset.Refresh;
31.     //Refresh and go back to respective row
32.     pDataset.Locate(pKeyField,vLastID,[]);
33.   end;
34. end;
35.  
36. procedure RefreshADatasetAfterInsert(pDataSet: TDataSet);
37. //This procedure refreshes a dataset and positions cursor to last record
38. //To be used only if DataSet is guaranteed to be sorted by an autoincrement primary key
39. var
40.   vUpdateStatus: string;
41.   vMessage: string;
42.   vLastID: Integer;
43. begin
44.   //Take info about dataset update status BEFORE Refresh!
45.   case pDataset.UpdateStatus of
46.       usUnmodified: vUpdateStatus:='usUnmodified';
47.       usModified: vUpdateStatus:='usModified';
48.       usInserted: vUpdateStatus:='usInserted';
49.       usDeleted: vUpdateStatus:='usDeleted';
50.       else  vUpdateStatus:='SomethingOther' ;
51.   end;
52.   vLastID:=((pDataSet as TSQLQuery).DataBase as TSQLite3Connection).GetInsertID;
53.  
54.   (pDataset as TSQLQuery).ApplyUpdates;
55.  
56.   //Refresh and go back to respective row
57.   if vUpdateStatus='usInserted' then begin
58.     pDataset.Refresh;
59.     //Dangerous!
60.     pDataSet.Last;
61.   end;
62. end;                                                          
63.  
64.  

multiple flags.
1) string comparison again (copy paste I know)
2) If the pdataset is required to be a TSQLQuery as is the case with this code then make it a visible requirement.
3) GetlastID is an SQLite3 feature and probably will not be available in other databases you could make it an external inline function that would make it easier to change in the future if the need arises

3 is a little bit on the caution side of things not so much on the error side.

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

1. interface
2.     procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery);overload;
3.     procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery; pKeyField: string);overload;  
4.  
5. implementation
6.  
7. procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery; pKeyField: string);
8. //This procedure refreshes a dataset and positions cursor to last record
9. //To be used if Dataset is not guaranteed to be sorted by an autoincrement primary key
10. var
11.   vLastID: Integer;
12.   vUpdateStatus : TUpdateStatus;
13. begin
14.   vUpdateStatus := pDataset.UpdateStatus;
15.   //Get last inserted ID in the database
16.   vLastID:=(pDataSet.DataBase as TSQLite3Connection).GetInsertID;
17.   pDataset.ApplyUpdates;
18.   //Now come back to respective row
19.   if pDataset.UpdateStatus = usInserted then begin
20.     pDataset.Refresh;
21.     //Refresh and go back to respective row
22.     pDataset.Locate(pKeyField,vLastID,[]);
23.   end;
24. end;
25.  
26. procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery);
27. //This procedure refreshes a dataset and positions cursor to last record
28. //To be used only if DataSet is guaranteed to be sorted by an autoincrement primary key
29. var
30.   vLastID: Integer;
31.   vUpdateStatus : TUpdateStatus;
32. begin
33.   vUpdateStatus := pDataset.UpdateStatus;
34.   vLastID:=((pDataSet as TSQLQuery).DataBase as TSQLite3Connection).GetInsertID;
35.   (pDataset as TSQLQuery).ApplyUpdates;
36.   if pDataset.UpdateStatus = usInserted then begin
37.     pDataset.Refresh;
38.     //Dangerous!
39.     pDataSet.Last;
40.   end;
41. end;                                                          
42.  

[tatamata]

Ok. good, so we can conclude this discussion with two overloaded procedures:

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

1. interface
2.     procedure RefreshADatasetAfterInsert(pDataSet: TDataSet);overload;
3.     procedure RefreshADatasetAfterInsert(pDataSet: TDataSet; pKeyField: string);overload;  
4.  
5. implementation
6.  
7. procedure RefreshADatasetAfterInsert(pDataSet: TDataSet; pKeyField: string);
8. //This procedure refreshes a dataset and positions cursor to last record
9. //To be used if Dataset is not guaranteed to be sorted by an autoincrement primary key
10. var
11.   vUpdateStatus: string;
12.   vMessage: string;
13.   vLastID: Integer;
14. begin
15.   //Take info about dataset update status BEFORE Refresh!
16.   case pDataset.UpdateStatus of
17.       usUnmodified: vUpdateStatus:='usUnmodified';
18.       usModified: vUpdateStatus:='usModified';
19.       usInserted: vUpdateStatus:='usInserted';
20.       usDeleted: vUpdateStatus:='usDeleted';
21.       else  vUpdateStatus:='SomethingOther' ;
22.   end;
23.   //Get last inserted ID in the database
24.   vLastID:=((pDataSet as TSQLQuery).DataBase as TSQLite3Connection).GetInsertID;
25.  
26.   (pDataset as TSQLQuery).ApplyUpdates;
27.  
28.   //Now come back to respective row
29.   if vUpdateStatus='usInserted' then begin
30.     pDataset.Refresh;
31.     //Refresh and go back to respective row
32.     pDataset.Locate(pKeyField,vLastID,[]);
33.   end;
34. end;
35.  
36. procedure RefreshADatasetAfterInsert(pDataSet: TDataSet);
37. //This procedure refreshes a dataset and positions cursor to last record
38. //To be used only if DataSet is guaranteed to be sorted by an autoincrement primary key
39. var
40.   vUpdateStatus: string;
41.   vMessage: string;
42.   vLastID: Integer;
43. begin
44.   //Take info about dataset update status BEFORE Refresh!
45.   case pDataset.UpdateStatus of
46.       usUnmodified: vUpdateStatus:='usUnmodified';
47.       usModified: vUpdateStatus:='usModified';
48.       usInserted: vUpdateStatus:='usInserted';
49.       usDeleted: vUpdateStatus:='usDeleted';
50.       else  vUpdateStatus:='SomethingOther' ;
51.   end;
52.   vLastID:=((pDataSet as TSQLQuery).DataBase as TSQLite3Connection).GetInsertID;
53.  
54.   (pDataset as TSQLQuery).ApplyUpdates;
55.  
56.   //Refresh and go back to respective row
57.   if vUpdateStatus='usInserted' then begin
58.     pDataset.Refresh;
59.     //Dangerous!
60.     pDataSet.Last;
61.   end;
62. end;                                                          
63.  
64.  

multiple flags.
1) string comparison again (copy paste I know)
2) If the pdataset is required to be a TSQLQuery as is the case with this code then make it a visible requirement.
3) GetlastID is an SQLite3 feature and probably will not be available in other databases you could make it an external inline function that would make it easier to change in the future if the need arises

3 is a little bit on the caution side of things not so much on the error side.

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

1. interface
2.     procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery);overload;
3.     procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery; pKeyField: string);overload;  
4.  
5. implementation
6.  
7. procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery; pKeyField: string);
8. //This procedure refreshes a dataset and positions cursor to last record
9. //To be used if Dataset is not guaranteed to be sorted by an autoincrement primary key
10. var
11.   vLastID: Integer;
12.   vUpdateStatus : TUpdateStatus;
13. begin
14.   vUpdateStatus := pDataset.UpdateStatus;
15.   //Get last inserted ID in the database
16.   vLastID:=(pDataSet.DataBase as TSQLite3Connection).GetInsertID;
17.   pDataset.ApplyUpdates;
18.   //Now come back to respective row
19.   if pDataset.UpdateStatus = usInserted then begin
20.     pDataset.Refresh;
21.     //Refresh and go back to respective row
22.     pDataset.Locate(pKeyField,vLastID,[]);
23.   end;
24. end;
25.  
26. procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery);
27. //This procedure refreshes a dataset and positions cursor to last record
28. //To be used only if DataSet is guaranteed to be sorted by an autoincrement primary key
29. var
30.   vLastID: Integer;
31.   vUpdateStatus : TUpdateStatus;
32. begin
33.   vUpdateStatus := pDataset.UpdateStatus;
34.   vLastID:=((pDataSet as TSQLQuery).DataBase as TSQLite3Connection).GetInsertID;
35.   (pDataset as TSQLQuery).ApplyUpdates;
36.   if pDataset.UpdateStatus = usInserted then begin
37.     pDataset.Refresh;
38.     //Dangerous!
39.     pDataSet.Last;
40.   end;
41. end;                                                          
42.  

Nice. Thanks!

[LacaK]

FPC 3.0 has introduced auto refresh capability of ftAutoInc fields.
So when record update is applied, value of autoinc field is automaticaly fetched.
(So there is no more need to explicitly call GetInsertId etc.)

[tatamata]

Lacak, I use CodeTyphon that has fpc 3.1.1.
After establishing connection to SQLIte database, in fielddefs of all SQLite underling tables the primary keys have been automatically recognized as ftInteger fields, not ftAutoInc. I thought it is OK.
Do you suggest that I manually change fielddefs to ftAutoInc in order to enable automatic refreshing?

[tatamata]

Ok. good, so we can conclude this discussion with two overloaded procedures:

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

1. interface
2.     procedure RefreshADatasetAfterInsert(pDataSet: TDataSet);overload;
3.     procedure RefreshADatasetAfterInsert(pDataSet: TDataSet; pKeyField: string);overload;  
4.  
5. implementation
6.  
7. procedure RefreshADatasetAfterInsert(pDataSet: TDataSet; pKeyField: string);
8. //This procedure refreshes a dataset and positions cursor to last record
9. //To be used if Dataset is not guaranteed to be sorted by an autoincrement primary key
10. var
11.   vUpdateStatus: string;
12.   vMessage: string;
13.   vLastID: Integer;
14. begin
15.   //Take info about dataset update status BEFORE Refresh!
16.   case pDataset.UpdateStatus of
17.       usUnmodified: vUpdateStatus:='usUnmodified';
18.       usModified: vUpdateStatus:='usModified';
19.       usInserted: vUpdateStatus:='usInserted';
20.       usDeleted: vUpdateStatus:='usDeleted';
21.       else  vUpdateStatus:='SomethingOther' ;
22.   end;
23.   //Get last inserted ID in the database
24.   vLastID:=((pDataSet as TSQLQuery).DataBase as TSQLite3Connection).GetInsertID;
25.  
26.   (pDataset as TSQLQuery).ApplyUpdates;
27.  
28.   //Now come back to respective row
29.   if vUpdateStatus='usInserted' then begin
30.     pDataset.Refresh;
31.     //Refresh and go back to respective row
32.     pDataset.Locate(pKeyField,vLastID,[]);
33.   end;
34. end;
35.  
36. procedure RefreshADatasetAfterInsert(pDataSet: TDataSet);
37. //This procedure refreshes a dataset and positions cursor to last record
38. //To be used only if DataSet is guaranteed to be sorted by an autoincrement primary key
39. var
40.   vUpdateStatus: string;
41.   vMessage: string;
42.   vLastID: Integer;
43. begin
44.   //Take info about dataset update status BEFORE Refresh!
45.   case pDataset.UpdateStatus of
46.       usUnmodified: vUpdateStatus:='usUnmodified';
47.       usModified: vUpdateStatus:='usModified';
48.       usInserted: vUpdateStatus:='usInserted';
49.       usDeleted: vUpdateStatus:='usDeleted';
50.       else  vUpdateStatus:='SomethingOther' ;
51.   end;
52.   vLastID:=((pDataSet as TSQLQuery).DataBase as TSQLite3Connection).GetInsertID;
53.  
54.   (pDataset as TSQLQuery).ApplyUpdates;
55.  
56.   //Refresh and go back to respective row
57.   if vUpdateStatus='usInserted' then begin
58.     pDataset.Refresh;
59.     //Dangerous!
60.     pDataSet.Last;
61.   end;
62. end;                                                          
63.  
64.  

multiple flags.
1) string comparison again (copy paste I know)
2) If the pdataset is required to be a TSQLQuery as is the case with this code then make it a visible requirement.
3) GetlastID is an SQLite3 feature and probably will not be available in other databases you could make it an external inline function that would make it easier to change in the future if the need arises

3 is a little bit on the caution side of things not so much on the error side.

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

1. interface
2.     procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery);overload;
3.     procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery; pKeyField: string);overload;  
4.  
5. implementation
6.  
7. procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery; pKeyField: string);
8. //This procedure refreshes a dataset and positions cursor to last record
9. //To be used if Dataset is not guaranteed to be sorted by an autoincrement primary key
10. var
11.   vLastID: Integer;
12.   vUpdateStatus : TUpdateStatus;
13. begin
14.   vUpdateStatus := pDataset.UpdateStatus;
15.   //Get last inserted ID in the database
16.   vLastID:=(pDataSet.DataBase as TSQLite3Connection).GetInsertID;
17.   pDataset.ApplyUpdates;
18.   //Now come back to respective row
19.   if pDataset.UpdateStatus = usInserted then begin
20.     pDataset.Refresh;
21.     //Refresh and go back to respective row
22.     pDataset.Locate(pKeyField,vLastID,[]);
23.   end;
24. end;
25.  
26. procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery);
27. //This procedure refreshes a dataset and positions cursor to last record
28. //To be used only if DataSet is guaranteed to be sorted by an autoincrement primary key
29. var
30.   vLastID: Integer;
31.   vUpdateStatus : TUpdateStatus;
32. begin
33.   vUpdateStatus := pDataset.UpdateStatus;
34.   vLastID:=((pDataSet as TSQLQuery).DataBase as TSQLite3Connection).GetInsertID;
35.   (pDataset as TSQLQuery).ApplyUpdates;
36.   if pDataset.UpdateStatus = usInserted then begin
37.     pDataset.Refresh;
38.     //Dangerous!
39.     pDataSet.Last;
40.   end;
41. end;                                                          
42.  

Nice. Thanks!

taaz, you had small mistake in code - instead of "if pDataset.UpdateStatus = usInserted then" there should be "if vUpdateStatus = usInserted then" because UpdateStatus is valid between Post and ApplyUpdates. So, the code is:

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

1. interface
2.     procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery);overload;
3.     procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery; pKeyField: string);overload;  
4.  
5. implementation
6.  
7. procedure TDataModule1.RefreshADatasetAfterInsert(pDataSet: TSQLQuery; pKeyField: string);
8. //This procedure refreshes a dataset and positions cursor to last record
9. //To be used if Dataset is not guaranteed to be sorted by an autoincrement primary key
10. var
11.   vLastID: Integer;
12.   vUpdateStatus : TUpdateStatus;
13. begin
14.   vUpdateStatus := pDataset.UpdateStatus;
15.   //Get last inserted ID in the database
16.   vLastID:=(pDataSet.DataBase as TSQLite3Connection).GetInsertID;
17.   pDataset.ApplyUpdates;
18.   //Now come back to respective row
19.   if vUpdateStatus = usInserted then begin
20.     pDataset.Refresh;
21.     //Refresh and go back to respective row
22.     pDataset.Locate(pKeyField,vLastID,[]);
23.   end;
24. end;
25.  
26. procedure TDataModule1.RefreshADatasetAfterInsert(pDataSet: TSQLQuery);
27. //This procedure refreshes a dataset and positions cursor to last record
28. //To be used only if DataSet is guaranteed to be sorted by an autoincrement primary key
29. var
30.   vLastID: Integer;
31.   vUpdateStatus : TUpdateStatus;
32. begin
33.   vUpdateStatus := pDataset.UpdateStatus;
34.   vLastID:=((pDataSet as TSQLQuery).DataBase as TSQLite3Connection).GetInsertID;
35.   (pDataset as TSQLQuery).ApplyUpdates;
36.   if vUpdateStatus = usInserted then begin
37.     pDataset.Refresh;
38.     //Dangerous!
39.     pDataSet.Last;
40.   end;
41. end;                                                                  
42.  

[tatamata]

Lacak, I use CodeTyphon that has fpc 3.1.1.
After establishing connection to SQLIte database, in fielddefs of all SQLite underling tables the primary keys have been automatically recognized as ftInteger fields, not ftAutoInc. I thought it is OK.
Do you suggest that I manually change fielddefs to ftAutoInc in order to enable automatic refreshing?

Anyway, I changed ftInteger to ftAutoInc and removed AfterPost event. Unfortunately, it does not work!
Lacak, could you explain your statement and how is it supposed to work? Thanks.

[LacaK]

If you have SQLite table created like:
  create table mytable (id integer primary key, f varchar(5))

Then after you insert new record and ApplyUpdates, new value for "id" column should be fetched automatically. So in your opened dataset you will see new value.