SQLite : How to refresh the current row to get the last recordID
lazarus / 16-Databases

----------------------------------------------------------------------
swierzbicki · 2013-09-10 14:44

Hello,

I'm filling a table with a handled device. Sometimes my users are
doing mistakes and want to delete the record.
My issue is that ID value is always set to 0. The only solution I have
is to refresh the table to get the record with the correct Id.
This is really combersome as my table has hundreds of records.

What can I do to get from the start my record Id ? There is no
refreshRecord function or methods available.... (I'm worjing with
TSQLQuery et TSQLIteConnection) :

Code: [Select]         if not SQliteTransaction.Active then
         SQliteTransaction.StartTransaction;
         try

             qrStockEntree.Insert;
             qrStockEntree.fieldbyname('Date').Value := now;
             qrStockEntree.Post;
             qrStockEntree.ApplyUpdates;

             if SQliteTransaction.Active then
             SQliteTransaction.CommitRetaining;


           
Showmessage(inttostr(qrStockEntree.fieldbyname('ID).AsInteger)); //
===> ALWAYS RETURN 0 (ID IS AUTOINC FIELD)
         except
           on e: exception do
           begin
             if SQliteTransaction.Active then
             SQliteTransaction.RollbackRetaining;
             MessageDlg ('Erreur',e.message, mtError,
             [mbOk],0)  ;
             exit;
           end;                 
Thank you

----------------------------------------------------------------------
BigChimp · 2013-09-10 14:50

It's no surprise
Code: [Select]Showmessage(inttostr(qrStockEntree.fieldbyname('ID).AsIn
teger)); // ===> ALWAYS RETURN 0 (ID IS AUTOINC FIELD)
doesn't work: once you posted/committed the record, all fields are
cleared again.

See
http://www.sqlite.org/faq.html#q1
which says:
"There is a function named sqlite3_last_insert_rowid() which will
return the integer key for the most recent insert operation." and has
a link for that function.

----------------------------------------------------------------------
swierzbicki · 2013-09-10 16:08

As i'm using CommitRetaining all data are still available.
Is it safe to do something like that :

Code: [Select]             qrStockEntree.Insert;
             qrStockEntree.fieldbyname('Date').Value := now;
             qrStockEntree.Post;
             qrStockEntree.ApplyUpdates;

             if SQliteTransaction.Active then
             SQliteTransaction.CommitRetaining;
             //Data is still displayed on the screen

            
             qrLastID.Open; //SQL.Text := select last_insert_rowid()
as Id;
             qrStockEntree.Edit;
             qrStockEntree.fieldbyname('Id').AsInteger :=
qrLastID.FieldByName('Id').AsInteger;
             qrLastID.close;
             qrStockEntree.Post;
             qrStockEntree.ApplyUpdates;
             //DB will not be updated since OLD_Id equal 0 but correct
ID will be displayed
             if SQliteTransaction.Active then
             SQliteTransaction.CommitRetaining;

----------------------------------------------------------------------
BigChimp · 2013-09-10 17:04

No idea. I'm not much of a GUI guy.

Perhaps that's why I'm a bit confused about your commitretaining
remark: isn't data display separate from whether you use
commitretaining or an explicit commit/starttransaction?

----------------------------------------------------------------------
swierzbicki · 2013-09-10 17:56

See this http://www.freepascal.org/docs-
html/fcl/sqldb/tsqltransaction.commitretaining.html

I've modified the SQLUpdate query to take others fields than the
automatic ID

----------------------------------------------------------------------
BigChimp · 2013-09-10 18:16

Quote from: swierzbicki on September 10, 2013, 07:56:21 pmSee this
http://www.freepascal.org/docs-
html/fcl/sqldb/tsqltransaction.commitretaining.html
Yes, I know about that one

----------------------------------------------------------------------
LacaK · 2013-09-13 05:12

Did you tried function GetInsertID of TSQLite3Connection ?

----------------------------------------------------------------------
swierzbicki · 2013-09-13 07:00

@Lakak : Thank you. I'll check this out