SQLite : How to refresh the current row to get the last recordID

[swierzbicki]

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]

It's no surprise

Code: [Select]

Showmessage(inttostr(qrStockEntree.fieldbyname('ID).AsInteger)); // ===> 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]

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]

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]

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]

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

Yes, I know about that one

[LacaK]

Did you tried function GetInsertID of TSQLite3Connection ?

[swierzbicki]

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