mORMot Open Source

Bo

Member

From: Melbourne

Registered: 2016-07-04

Posts: 57

Website

Different query result of two equivalent queries

I created a class:

 TSQLIntegerDataTypes = class (TSQLRecord)
  private
    fByte : Byte ;   
  published
    property AByte: Byte read fByte write fByte;    
  end;

Ran the application and  a Sqlite db file created, opened SynDB Explorer and connect it to the db, executed  query "select * from IntegerDataTypes", I got column ID and AByte in the result.

While kept SynDB Explorer running, then I upgraded the class to add one more property as following code:

 TSQLIntegerDataTypes = class (TSQLRecord)
  private
    fByte : Byte ;
    fShortInt :  ShortInt;
  published
    property AByte: Byte read fByte write fByte;
    property AShortInt: ShortInt read fShortInt write fShortInt;
  end;

Compiled and ran the application again, the db was now updated, went to SynDB Explorer and executed the same query again, I got the same result without column AShortInt, but if I executed this equivalent query "select ID,AByte, AShortInt from IntegerDataTypes", I got the column AShortInt!

Is this a bug or a feature?

Last edited by Bo (2017-08-05 09:39:18)

igors233

Member

Registered: 2012-09-10

Posts: 241

Re: Different query result of two equivalent queries

Is this correct setup in this case, you've done DML change in App1 and run query from App2 (SynDBExplorer)?

If so I think SynDBExplorer haven't been notified of the DML change and it returned data from cache, try to disable caching in there.

Bo

Member

From: Melbourne

Registered: 2016-07-04

Posts: 57

Website

Re: Different query result of two equivalent queries

Tested with data change (i.e., inserted records from out of SynDB Explorer), the "select * ..." query did returned new records, so it seems only the columns are cached some where. Where do I disable the caching for this situation?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,669

Website

Re: Different query result of two equivalent queries

The SQlite3 engine is expected to run in exclusive mode.
So if two processes (a mORMot server and SynDBExplorer) do access the very same database at the same time, the cache between the two is inconsistent.
This is as expected.

To consult the database in real time, expose the SQlite3 main mORMot instance using e.g. the mORMotDDD remote administration interface.

Bo

Member

From: Melbourne

Registered: 2016-07-04

Posts: 57

Website

Re: Different query result of two equivalent queries

The SQlite3 engine is expected to run in exclusive mode.
So if two processes (a mORMot server and SynDBExplorer) do access the very same database at the same time, the cache between the two is inconsistent.
This is as expected.

To consult the database in real time, expose the SQlite3 main mORMot instance using e.g. the mORMotDDD remote administration interface.

Don't know the "administration interface" yet, but I have made a little change to the explorer. Instead of generating "SELECT * ..." query when a table on the left list is double clicked, I make the double click to generate the complete list of the column in the SELECT query, like what SQL Studio does.

procedure TDBExplorerFrame.ListTableDblClick(Sender: TObject);
var
  i, j: integer;
  Fields: TSQLDBColumnDefineDynArray;
  LField: TSQLDBProcColumnDefine;
  LSQL : string;
begin
  i := ListTable.ItemIndex;
  if i>=0 then
  begin
    Props.GetFields(S2U(ListTable.Items[i]),Fields);
    LSQL := 'SELECT ' + Fields[0].ColumnName ;
    for j:=1 to Length(Fields) -1 do
    begin
      LSQL := LSQL + ', ' + Fields[j].ColumnName
    end;
    LSQL := LSQL + ' FROM ' + StringToUTF8(ListTable.Items[i]);

    AddSQL(UTF8ToString(LSQL),
      true,ssShift in fListTableShiftState);
  end;
end;