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

----------------------------------------------------------------------
tatamata · 2016-02-03 08:45

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 · 2016-02-03 10:10

autoinc details at https://www.sqlite.org/autoinc.html. So I guess
something along the lines of
Code: Delphi  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i function
GetLastAutoinc(const aCnn:TSQLConnection):int64;var  vQry :
TSQLQuery;  vTrn :TSQLTransaction;begin  Result := -1; //invalid
result.  vQry := TSQLQuery.Create(Nil);  vTrn :=
TSQLTransaction.Create(Nil);  try    vQry.Transaction := vTrn;   
vTrn.DataBase := aCnn;    vQry.SQL.Text :='SELECT
last_insert_rowid()';    vQry.Open;    vQry.First;    Result :=
vQry.Fields[0].AsLargeInt;  finally    vQry.Free;    vTrn.Free; 
end;end;  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 · 2016-02-03 11:26

This might be easier 
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
MyLastInsertedID := SQLite3Connection1.GetInsertID;
GetInsertID is already declared in TSQLite3Connection as:
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 function
TSQLite3Connection.GetInsertID: int64;begin result:=
sqlite3_last_insert_rowid(fhandle);end;Also see
https://www.sqlite.org/c3ref/last_insert_rowid.html

----------------------------------------------------------------------
tatamata · 2016-02-03 15:01

It seems that solution is even simpler. 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
RefreshADatasetAfterInsert(pDataSet: TDataSet);//This procedure
refreshes a dataset and positions cursor to last recordvar 
{vBookmark: TBookmark; //This does not work correctly after Refresh} 
vUpdateStatus: string;  vMessage: string;  vID: Integer;begin  try   
try      //Take info about dataset update status BEFORE Refresh!     
case pDataset.UpdateStatus of          usUnmodified:
vUpdateStatus:='usUnmodified';          usModified:
vUpdateStatus:='usModified';          usInserted:
vUpdateStatus:='usInserted';          usDeleted:
vUpdateStatus:='usDeleted';          else
 vUpdateStatus:='SomethingOther' ;      end;     
{vBookmark:=pDataset.GetBookmark; //This does not work correctly after
Refresh!}       (pDataset as TSQLQuery).ApplyUpdates;       //Now come
back to respective row      if vUpdateStatus='usInserted' then begin 
     pDataset.Refresh;       {pDataset.GotoBookmark(vBookmark); //This
does not work correctly after Refresh!}        pDataSet.Last;     
end;     except      on E:Exception do begin       
vMessage:=E.Message;        ShowMessage(vMessage);      end;    end; 
finally    {pDataset.FreeBookmark(vBookmark); //This does not work
correctly after Refresh! }  end;end; procedure
TDataModule1.SQLQuery1AfterPost(DataSet: TDataSet);begin 
RefreshADatasetAfterInsert(Dataset);end;

----------------------------------------------------------------------
rvk · 2016-02-03 15:07

Quote from: tatamata on February 03, 2016, 04:01:34 pmCode: [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 · 2016-02-03 15:14

Quote from: rvk on February 03, 2016, 04:07:36 pmQuote from: tatamata
on February 03, 2016, 04:01:34 pmCode: [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 · 2016-02-03 15:25

Quote from: tatamata on February 03, 2016, 04:01:34 pmIt 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
RefreshADatasetAfterInsert(pDataSet: TDataSet);var  vLastID : Int64; 
vUpdateStatus: string;  vMessage: string;begin  try    try     
(pDataset as TSQLQuery).ApplyUpdates;       vLastIDE := ((pDataSet as
TSQLQuery).DataBase as TSQLite3Connection).GetInsertID;      if
vUpdateStatus='usInserted' then begin        pDataset.Refresh;       
pDataset.Locate(['ID'],[vLastID],[]);      end;     except      on
E:Exception do begin        vMessage:=E.Message;       
ShowMessage(vMessage);      end;    end;  finally   
{pDataset.FreeBookmark(vBookmark); //This does not work correctly
after Refresh! }  end;end; 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 · 2016-02-03 16:18

Ok. good, so we can conclude this discussion with two overloaded
procedures:
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 interface 
 procedure RefreshADatasetAfterInsert(pDataSet: TDataSet);overload;  
procedure RefreshADatasetAfterInsert(pDataSet: TDataSet; pKeyField:
string);overload;    implementation procedure
RefreshADatasetAfterInsert(pDataSet: TDataSet; pKeyField:
string);//This procedure refreshes a dataset and positions cursor to
last record//To be used if Dataset is not guaranteed to be sorted by
an autoincrement primary keyvar  vUpdateStatus: string;  vMessage:
string;  vLastID: Integer;begin  //Take info about dataset update
status BEFORE Refresh!  case pDataset.UpdateStatus of     
usUnmodified: vUpdateStatus:='usUnmodified';      usModified:
vUpdateStatus:='usModified';      usInserted:
vUpdateStatus:='usInserted';      usDeleted:
vUpdateStatus:='usDeleted';      else  vUpdateStatus:='SomethingOther'
;  end;  //Get last inserted ID in the database  vLastID:=((pDataSet
as TSQLQuery).DataBase as TSQLite3Connection).GetInsertID;   (pDataset
as TSQLQuery).ApplyUpdates;   //Now come back to respective row  if
vUpdateStatus='usInserted' then begin    pDataset.Refresh;   
//Refresh and go back to respective row   
pDataset.Locate(pKeyField,vLastID,[]);  end;end; procedure
RefreshADatasetAfterInsert(pDataSet: TDataSet);//This procedure
refreshes a dataset and positions cursor to last record//To be used
only if DataSet is guaranteed to be sorted by an autoincrement primary
keyvar  vUpdateStatus: string;  vMessage: string;  vLastID:
Integer;begin  //Take info about dataset update status BEFORE
Refresh!  case pDataset.UpdateStatus of      usUnmodified:
vUpdateStatus:='usUnmodified';      usModified:
vUpdateStatus:='usModified';      usInserted:
vUpdateStatus:='usInserted';      usDeleted:
vUpdateStatus:='usDeleted';      else  vUpdateStatus:='SomethingOther'
;  end;  vLastID:=((pDataSet as TSQLQuery).DataBase as
TSQLite3Connection).GetInsertID;   (pDataset as
TSQLQuery).ApplyUpdates;   //Refresh and go back to respective row  if
vUpdateStatus='usInserted' then begin    pDataset.Refresh;   
//Dangerous!    pDataSet.Last;  end;end;

----------------------------------------------------------------------
taazz · 2016-02-03 17:49

Quote from: tatamata on February 03, 2016, 05:18:08 pmOk. good, so we
can conclude this discussion with two overloaded procedures:
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 interface 
 procedure RefreshADatasetAfterInsert(pDataSet: TDataSet);overload;  
procedure RefreshADatasetAfterInsert(pDataSet: TDataSet; pKeyField:
string);overload;    implementation procedure
RefreshADatasetAfterInsert(pDataSet: TDataSet; pKeyField:
string);//This procedure refreshes a dataset and positions cursor to
last record//To be used if Dataset is not guaranteed to be sorted by
an autoincrement primary keyvar  vUpdateStatus: string;  vMessage:
string;  vLastID: Integer;begin  //Take info about dataset update
status BEFORE Refresh!  case pDataset.UpdateStatus of     
usUnmodified: vUpdateStatus:='usUnmodified';      usModified:
vUpdateStatus:='usModified';      usInserted:
vUpdateStatus:='usInserted';      usDeleted:
vUpdateStatus:='usDeleted';      else  vUpdateStatus:='SomethingOther'
;  end;  //Get last inserted ID in the database  vLastID:=((pDataSet
as TSQLQuery).DataBase as TSQLite3Connection).GetInsertID;   (pDataset
as TSQLQuery).ApplyUpdates;   //Now come back to respective row  if
vUpdateStatus='usInserted' then begin    pDataset.Refresh;   
//Refresh and go back to respective row   
pDataset.Locate(pKeyField,vLastID,[]);  end;end; procedure
RefreshADatasetAfterInsert(pDataSet: TDataSet);//This procedure
refreshes a dataset and positions cursor to last record//To be used
only if DataSet is guaranteed to be sorted by an autoincrement primary
keyvar  vUpdateStatus: string;  vMessage: string;  vLastID:
Integer;begin  //Take info about dataset update status BEFORE
Refresh!  case pDataset.UpdateStatus of      usUnmodified:
vUpdateStatus:='usUnmodified';      usModified:
vUpdateStatus:='usModified';      usInserted:
vUpdateStatus:='usInserted';      usDeleted:
vUpdateStatus:='usDeleted';      else  vUpdateStatus:='SomethingOther'
;  end;  vLastID:=((pDataSet as TSQLQuery).DataBase as
TSQLite3Connection).GetInsertID;   (pDataset as
TSQLQuery).ApplyUpdates;   //Refresh and go back to respective row  if
vUpdateStatus='usInserted' then begin    pDataset.Refresh;   
//Dangerous!    pDataSet.Last;  end;end;                             
                              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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i interface 
 procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery);overload; 
 procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery; pKeyField:
string);overload;    implementation procedure
RefreshADatasetAfterInsert(pDataSet: TSQLQuery; pKeyField:
string);//This procedure refreshes a dataset and positions cursor to
last record//To be used if Dataset is not guaranteed to be sorted by
an autoincrement primary keyvar  vLastID: Integer;  vUpdateStatus :
TUpdateStatus;begin  vUpdateStatus := pDataset.UpdateStatus;  //Get
last inserted ID in the database  vLastID:=(pDataSet.DataBase as
TSQLite3Connection).GetInsertID;  pDataset.ApplyUpdates;  //Now come
back to respective row  if pDataset.UpdateStatus = usInserted then
begin    pDataset.Refresh;    //Refresh and go back to respective row 
  pDataset.Locate(pKeyField,vLastID,[]);  end;end; procedure
RefreshADatasetAfterInsert(pDataSet: TSQLQuery);//This procedure
refreshes a dataset and positions cursor to last record//To be used
only if DataSet is guaranteed to be sorted by an autoincrement primary
keyvar  vLastID: Integer;  vUpdateStatus : TUpdateStatus;begin 
vUpdateStatus := pDataset.UpdateStatus;  vLastID:=((pDataSet as
TSQLQuery).DataBase as TSQLite3Connection).GetInsertID;  (pDataset as
TSQLQuery).ApplyUpdates;  if pDataset.UpdateStatus = usInserted then
begin    pDataset.Refresh;    //Dangerous!    pDataSet.Last;  end;end;

----------------------------------------------------------------------
tatamata · 2016-02-03 18:37

Quote from: taazz on February 03, 2016, 06:49:56 pmQuote from:
tatamata on February 03, 2016, 05:18:08 pmOk. good, so we can conclude
this discussion with two overloaded procedures:
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 interface 
 procedure RefreshADatasetAfterInsert(pDataSet: TDataSet);overload;  
procedure RefreshADatasetAfterInsert(pDataSet: TDataSet; pKeyField:
string);overload;    implementation procedure
RefreshADatasetAfterInsert(pDataSet: TDataSet; pKeyField:
string);//This procedure refreshes a dataset and positions cursor to
last record//To be used if Dataset is not guaranteed to be sorted by
an autoincrement primary keyvar  vUpdateStatus: string;  vMessage:
string;  vLastID: Integer;begin  //Take info about dataset update
status BEFORE Refresh!  case pDataset.UpdateStatus of     
usUnmodified: vUpdateStatus:='usUnmodified';      usModified:
vUpdateStatus:='usModified';      usInserted:
vUpdateStatus:='usInserted';      usDeleted:
vUpdateStatus:='usDeleted';      else  vUpdateStatus:='SomethingOther'
;  end;  //Get last inserted ID in the database  vLastID:=((pDataSet
as TSQLQuery).DataBase as TSQLite3Connection).GetInsertID;   (pDataset
as TSQLQuery).ApplyUpdates;   //Now come back to respective row  if
vUpdateStatus='usInserted' then begin    pDataset.Refresh;   
//Refresh and go back to respective row   
pDataset.Locate(pKeyField,vLastID,[]);  end;end; procedure
RefreshADatasetAfterInsert(pDataSet: TDataSet);//This procedure
refreshes a dataset and positions cursor to last record//To be used
only if DataSet is guaranteed to be sorted by an autoincrement primary
keyvar  vUpdateStatus: string;  vMessage: string;  vLastID:
Integer;begin  //Take info about dataset update status BEFORE
Refresh!  case pDataset.UpdateStatus of      usUnmodified:
vUpdateStatus:='usUnmodified';      usModified:
vUpdateStatus:='usModified';      usInserted:
vUpdateStatus:='usInserted';      usDeleted:
vUpdateStatus:='usDeleted';      else  vUpdateStatus:='SomethingOther'
;  end;  vLastID:=((pDataSet as TSQLQuery).DataBase as
TSQLite3Connection).GetInsertID;   (pDataset as
TSQLQuery).ApplyUpdates;   //Refresh and go back to respective row  if
vUpdateStatus='usInserted' then begin    pDataset.Refresh;   
//Dangerous!    pDataSet.Last;  end;end;                             
                              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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i interface 
 procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery);overload; 
 procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery; pKeyField:
string);overload;    implementation procedure
RefreshADatasetAfterInsert(pDataSet: TSQLQuery; pKeyField:
string);//This procedure refreshes a dataset and positions cursor to
last record//To be used if Dataset is not guaranteed to be sorted by
an autoincrement primary keyvar  vLastID: Integer;  vUpdateStatus :
TUpdateStatus;begin  vUpdateStatus := pDataset.UpdateStatus;  //Get
last inserted ID in the database  vLastID:=(pDataSet.DataBase as
TSQLite3Connection).GetInsertID;  pDataset.ApplyUpdates;  //Now come
back to respective row  if pDataset.UpdateStatus = usInserted then
begin    pDataset.Refresh;    //Refresh and go back to respective row 
  pDataset.Locate(pKeyField,vLastID,[]);  end;end; procedure
RefreshADatasetAfterInsert(pDataSet: TSQLQuery);//This procedure
refreshes a dataset and positions cursor to last record//To be used
only if DataSet is guaranteed to be sorted by an autoincrement primary
keyvar  vLastID: Integer;  vUpdateStatus : TUpdateStatus;begin 
vUpdateStatus := pDataset.UpdateStatus;  vLastID:=((pDataSet as
TSQLQuery).DataBase as TSQLite3Connection).GetInsertID;  (pDataset as
TSQLQuery).ApplyUpdates;  if pDataset.UpdateStatus = usInserted then
begin    pDataset.Refresh;    //Dangerous!    pDataSet.Last;  end;end;
                                                           Nice.
Thanks!

----------------------------------------------------------------------
LacaK · 2016-02-03 19:30

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 · 2016-02-03 20:08

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 · 2016-02-04 16:34

Quote from: tatamata on February 03, 2016, 07:37:32 pmQuote from:
taazz on February 03, 2016, 06:49:56 pmQuote from: tatamata on
February 03, 2016, 05:18:08 pmOk. good, so we can conclude this
discussion with two overloaded procedures:
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 interface 
 procedure RefreshADatasetAfterInsert(pDataSet: TDataSet);overload;  
procedure RefreshADatasetAfterInsert(pDataSet: TDataSet; pKeyField:
string);overload;    implementation procedure
RefreshADatasetAfterInsert(pDataSet: TDataSet; pKeyField:
string);//This procedure refreshes a dataset and positions cursor to
last record//To be used if Dataset is not guaranteed to be sorted by
an autoincrement primary keyvar  vUpdateStatus: string;  vMessage:
string;  vLastID: Integer;begin  //Take info about dataset update
status BEFORE Refresh!  case pDataset.UpdateStatus of     
usUnmodified: vUpdateStatus:='usUnmodified';      usModified:
vUpdateStatus:='usModified';      usInserted:
vUpdateStatus:='usInserted';      usDeleted:
vUpdateStatus:='usDeleted';      else  vUpdateStatus:='SomethingOther'
;  end;  //Get last inserted ID in the database  vLastID:=((pDataSet
as TSQLQuery).DataBase as TSQLite3Connection).GetInsertID;   (pDataset
as TSQLQuery).ApplyUpdates;   //Now come back to respective row  if
vUpdateStatus='usInserted' then begin    pDataset.Refresh;   
//Refresh and go back to respective row   
pDataset.Locate(pKeyField,vLastID,[]);  end;end; procedure
RefreshADatasetAfterInsert(pDataSet: TDataSet);//This procedure
refreshes a dataset and positions cursor to last record//To be used
only if DataSet is guaranteed to be sorted by an autoincrement primary
keyvar  vUpdateStatus: string;  vMessage: string;  vLastID:
Integer;begin  //Take info about dataset update status BEFORE
Refresh!  case pDataset.UpdateStatus of      usUnmodified:
vUpdateStatus:='usUnmodified';      usModified:
vUpdateStatus:='usModified';      usInserted:
vUpdateStatus:='usInserted';      usDeleted:
vUpdateStatus:='usDeleted';      else  vUpdateStatus:='SomethingOther'
;  end;  vLastID:=((pDataSet as TSQLQuery).DataBase as
TSQLite3Connection).GetInsertID;   (pDataset as
TSQLQuery).ApplyUpdates;   //Refresh and go back to respective row  if
vUpdateStatus='usInserted' then begin    pDataset.Refresh;   
//Dangerous!    pDataSet.Last;  end;end;                             
                              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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i interface 
 procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery);overload; 
 procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery; pKeyField:
string);overload;    implementation procedure
RefreshADatasetAfterInsert(pDataSet: TSQLQuery; pKeyField:
string);//This procedure refreshes a dataset and positions cursor to
last record//To be used if Dataset is not guaranteed to be sorted by
an autoincrement primary keyvar  vLastID: Integer;  vUpdateStatus :
TUpdateStatus;begin  vUpdateStatus := pDataset.UpdateStatus;  //Get
last inserted ID in the database  vLastID:=(pDataSet.DataBase as
TSQLite3Connection).GetInsertID;  pDataset.ApplyUpdates;  //Now come
back to respective row  if pDataset.UpdateStatus = usInserted then
begin    pDataset.Refresh;    //Refresh and go back to respective row 
  pDataset.Locate(pKeyField,vLastID,[]);  end;end; procedure
RefreshADatasetAfterInsert(pDataSet: TSQLQuery);//This procedure
refreshes a dataset and positions cursor to last record//To be used
only if DataSet is guaranteed to be sorted by an autoincrement primary
keyvar  vLastID: Integer;  vUpdateStatus : TUpdateStatus;begin 
vUpdateStatus := pDataset.UpdateStatus;  vLastID:=((pDataSet as
TSQLQuery).DataBase as TSQLite3Connection).GetInsertID;  (pDataset as
TSQLQuery).ApplyUpdates;  if pDataset.UpdateStatus = usInserted then
begin    pDataset.Refresh;    //Dangerous!    pDataSet.Last;  end;end;
                                                           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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i interface 
 procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery);overload; 
 procedure RefreshADatasetAfterInsert(pDataSet: TSQLQuery; pKeyField:
string);overload;    implementation procedure
TDataModule1.RefreshADatasetAfterInsert(pDataSet: TSQLQuery;
pKeyField: string);//This procedure refreshes a dataset and positions
cursor to last record//To be used if Dataset is not guaranteed to be
sorted by an autoincrement primary keyvar  vLastID: Integer; 
vUpdateStatus : TUpdateStatus;begin  vUpdateStatus :=
pDataset.UpdateStatus;  //Get last inserted ID in the database 
vLastID:=(pDataSet.DataBase as TSQLite3Connection).GetInsertID; 
pDataset.ApplyUpdates;  //Now come back to respective row  if
vUpdateStatus = usInserted then begin    pDataset.Refresh;   
//Refresh and go back to respective row   
pDataset.Locate(pKeyField,vLastID,[]);  end;end; procedure
TDataModule1.RefreshADatasetAfterInsert(pDataSet: TSQLQuery);//This
procedure refreshes a dataset and positions cursor to last record//To
be used only if DataSet is guaranteed to be sorted by an autoincrement
primary keyvar  vLastID: Integer;  vUpdateStatus :
TUpdateStatus;begin  vUpdateStatus := pDataset.UpdateStatus; 
vLastID:=((pDataSet as TSQLQuery).DataBase as
TSQLite3Connection).GetInsertID;  (pDataset as
TSQLQuery).ApplyUpdates;  if vUpdateStatus = usInserted then begin   
pDataset.Refresh;    //Dangerous!    pDataSet.Last;  end;end;

----------------------------------------------------------------------
tatamata · 2016-02-04 16:37

Quote from: tatamata on February 03, 2016, 09:08:26 pmLacak, 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 · 2016-02-05 06:36

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.

----------------------------------------------------------------------
tatamata · 2016-02-06 11:12

Hm, it would be nice, but unfortunately, I just don't see that kind of
behavior...
My SQLite3Connection has scoApplyUpdatesChecksRowsAffected=True,
SQLTransaction.Action=caCommitRetaining, all SQLQuery components have:
sqoAutoApplyUpdates=True, sqoAutoCommit=True,
sqoKeepOpenOnCommit=True, sqoRefreshUsingSelect=True,
Upda]"]>Blockedde=upWhereKeyOnly, UsePrimaryKeyAsKey=True.

----------------------------------------------------------------------
LacaK · 2016-02-06 15:41

May be that your auto-inc column is not recognized as ftAutoInc field
type.
In this case no auto refresh is applied.