How to see updated info in SQLite3 record after update without rerunning query?

I am using Delphi 2010 with Tim Anderson's SQLite wrapper (unicode version)

Have a SQLite3 database that is currently around a thousand records.

I am trying implement a feature were the user can walk through the database using Next/Previous buttons, edit a record if they desire then move on to the next record.

When I update one of the records the currently loaded table is acting like a snapshot i.e. the table shows the old data until I rerun the SQL query to reload the table.

Here is the Delphi code that does the update.

procedure TForm1.btnUpdateClick(Sender: TObject);
var
  slDBpath: string;
  sldb: TSQL开发者_JAVA技巧iteDatabase;
  sSQL: String;
  ts: TStringStream;
begin
  slDBpath := ExtractFilepath(application.exename) + 'test.db';
  sldb := TSQLiteDatabase.Create(slDBpath);
  try
    ts := TStringStream.Create(memNotes.Text, TEncoding.UTF8);
    try
      sldb.BeginTransaction;
      sSQL := 'UPDATE testtable SET Name = "' + ebName.Text + '", Number = ' + ebNumber.Text + ' WHERE ID = '+ ebID.text +';';
      sldb.execsql(sSQL);
      sldb.Commit;
    finally
      ts.Free;
    end;
  finally
    sldb.Free;
  end;
end;

Is there a way to refresh the currently loaded table? Or do I have to rerun the original query and walk my way back to the currently used record?

I see two possibilities - which are not SQLite specific (you should always try not to be tied to one database engine):

1. Add a modification TimeStamp to each record, don't forget to create an index on it, and make a query to fetch rows only after the latest retrieved time. Run it in a timer. This will be fast and easy. Much faster than retrieving the whole table content.

2. Another variation. You can add a trigger in the SQLite, then insert the updated record into a separated table. Then you will have to fetch only this small external table. But you'll have to maintain one table per client.

So I guess option # 1 is the easiest.

For instance, in the latest version of our ORM (which is SQLite3 based, but not restricted to), you have two ways of UI refresh:

• By a global stateless protocol and server-side caching (with a dedicated method);
• By a new TModTime published property, which will be updated on any RESTful update of the database, to easily implement option # 1.

I worked around it by using TSQLiteTable to create an array of ID#'s and use TSQLiteUniTable to show or edit the data based on the record's ID#. Very simple and more than fast enough for number of records this application will deal with.