#1 2017-08-05 09:37:29

Bo
Member
From: Melbourne
Registered: 2016-07-04
Posts: 48
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)

Offline

#2 2017-08-05 11:33:14

igors233
Member
Registered: 2012-09-10
Posts: 233

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.

Offline

#3 2017-08-05 12:53:02

Bo
Member
From: Melbourne
Registered: 2016-07-04
Posts: 48
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?

Offline

#4 2017-08-05 13:59:37

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
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.

Offline

#5 2017-08-06 09:53:44

Bo
Member
From: Melbourne
Registered: 2016-07-04
Posts: 48
Website

Re: Different query result of two equivalent queries

ab wrote:

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;

Offline

Board footer

Powered by FluxBB