mORMot Open Source

yurasek

Member

From: Belarus

Registered: 2011-04-19

Posts: 18

Strange behavior when using the virtual tables (no such column: ID)

I created a small example that demonstrates the bug using the virtual tables. After pressing the Button1 in the back end is the following error:

Exception class ESQLite3Exception with message 'no such column: ID'

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Mormot, SynCommons, mORMotSQLite3, SynSQLite3Static,
  mORMotHttpServer, mORMotHttpClient;

type
  TSQLValue1 = class(TSQLRecord)
  private
    fValue1: Integer;
  published
    property Value1: Integer read fValue1 write fValue1;
  end;

  TSQLValue2 = class(TSQLRecord)
  private
    fValue2: Integer;
  published
    property Value2: Integer read fValue2 write fValue2;
  end;

  TSQLServer = class(TSQLRestServerDB)
  private
    FModel: TSQLModel;
    FServer: TSQLHttpServer;
  public
    constructor Create;
    destructor Destroy;
  end;

  TSQLClient = class(TSQLHttpClient)
  private
    FModel: TSQLModel;
  public
    constructor Create;
    destructor Destroy;
  end;

  TForm1 = class(TForm)
    Button1: TButton;
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure Button1Click(Sender: TObject);
  public
    Client: TSQLClient;
    Server: TSQLServer;
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

function CreateModel: TSQLModel;
begin
  Result:= TSQLModel.Create([TSQLAuthGroup, TSQLAuthUser, TSQLValue2, TSQLValue1]);
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
  Server:= TSQLServer.Create;
  Client:= TSQLClient.Create;
end;

procedure TForm1.FormDestroy(Sender: TObject);
begin
  Client.Free;
  Server.Free;
end;

procedure TForm1.Button1Click(Sender: TObject);
var
  SQLValue1: TSQLValue1;
  SQLValue2: TSQLValue2;
begin
  SQLValue1:= TSQLValue1.CreateAndFillPrepare(Client, '');
  SQLValue1.Free;
  SQLValue2:= TSQLValue2.CreateAndFillPrepare(Client, '');
  SQLValue2.Free;
end;

constructor TSQLServer.Create;
begin
  FModel:= CreateModel;
  FModel.VirtualTableRegister(TSQLValue1, TSQLVirtualTableJSON);
  FModel.VirtualTableRegister(TSQLValue2, TSQLVirtualTableJSON);
  inherited Create(FModel, ChangeFileExt(ParamStr(0), '.db'), True);
  Self.CreateMissingTables(0);
  FServer:= TSQLHttpServer.Create('8080', Self);
end;

destructor TSQLServer.Destroy;
begin
  FServer.Free;
  inherited;
  FModel.Free;
end;

constructor TSQLClient.Create;
begin
  FModel:= CreateModel;
  inherited Create('127.0.0.1', '8080', FModel);
  Self.SetUser('Admin', 'synopse');
end;

destructor TSQLClient.Destroy;
begin
  inherited;
  FModel.Free;
end;

end.

The error appears here:

result ^. Prepare (DB.DB, SQL);

function TSQLRestServerDB.GetAndPrepareStatement(const SQL: RawUTF8): PSQLRequest;
var i, maxParam: integer;
    Types: TSQLParamTypeDynArray;
    Nulls: TSQLFieldBits;
    Values: TRawUTF8DynArray;
    GenericSQL: RawUTF8;
begin
  GenericSQL := ExtractInlineParameters(SQL,Types,Values,maxParam,Nulls);
  if maxParam=0 then begin
    // SQL code with no valid :(...): internal parameters
    if not (IdemPChar(pointer(SQL),'INSERT INTO ') and
            (PosEx(' DEFAULT VALUES;',SQL,13)=Length(SQL)-15)) then begin
      result := @fStaticStatement;
      result^.Prepare(DB.DB,SQL);
      DB.Log.Log(sllSQL,'% is no prepared statement',SQL,self);
      exit;
    end;
  end;
  DB.Log.Log(sllSQL,'% prepared as % with % param',[SQL,GenericSQL,maxParam],self);
  result := fStatementCache.Prepare(GenericSQL);
  // bind parameters
  assert(sqlite3.bind_parameter_count(result^.Request)=maxParam);
  for i := 0 to maxParam-1 do
  if i in Nulls then
    result^.BindNull(i) else
    case Types[i] of
      sptDateTime, // date/time are stored as ISO-8601 TEXT in SQLite3
      sptText:    result^.Bind(i+1,Values[i]);
      sptBlob:    result^.Bind(i+1,pointer(Values[i]),length(Values[i]));
      sptInteger: result^.Bind(i+1,GetInt64(pointer(Values[i])));
      sptFloat:   result^.Bind(i+1,GetExtended(pointer(Values[i])));
    end;
end;

Last edited by yurasek (2013-05-18 08:34:31)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: Strange behavior when using the virtual tables (no such column: ID)

First of all, some points to be corrected:
1. You should always mark your Destroy destructors with override, otherwise it will leak memory since they will never be called by Free.
2. You should not create such FModel in your own class level: the TSQLRest inherited level has already a FModel - so it is very confusing. The correct way is to create a local TSQLModel instance, and set Owner := self to let it managed by the TSQLRest class.
But this is not the main problem here.
Just take care of those two points.

The documentation clearly states the following:

A more advanced and powerful way of using static tables is to define some classes inheriting from TSQLRecordVirtualTableAutoID, and associate them with some TSQLVirtualTable classes.

You should define your tables as virtual, i.e. descending by TSQLRecordVirtualTableAutoID:

If you do not do that, there was an issue with TSQLRestServerStaticInMemory.AdaptSQLForEngineList() to handle most common RESTful requests from client.
It has been fixed by http://synopse.info/fossil/info/8857333fde

Perhaps some other requests may not work as expected, with TSQLRestServerStaticInMemory, if you define your classes as plain TSQLRecord.

I've just updated the documentation to state the following:

Virtual tables from the client side

For all ORM features to work remotely, you need to notify the Client-side model that a table is defined as a virtual static (in-memory / TObjectList) storage. Otherwise you may encounter some SQL errors when executing requests, like "no such column: ID".

For instance, if you define two in-memory JSON virtual tables on Server side:

constructor TSQLServer.Create;
var aModel: TSQLModel;
begin
  aModel := CreateModel;
  aModel.VirtualTableRegister(TSQLValue1, TSQLVirtualTableJSON);
  aModel.VirtualTableRegister(TSQLValue2, TSQLVirtualTableJSON);
  aModel.Owner := self; // model will be released with TSQLServer instance
  inherited Create(aModel, ChangeFileExt(ParamStr(0), '.db'), True);
  Self.CreateMissingTables(0);
  FHttpServer:= TSQLHttpServer.Create('8080', Self);
end;

You will need to specify also no the client side that those TSQLValue1 and TSQLValue2 tables are virtual.

You have several possibilities:
- Inherit each table not from TSQLRecord, but from TSQLRecordVirtualTableAutoID, as was stated above as standard procedure for virtual tables;
- If your tables are defined as TSQLRecord, ensure that the Client side set the table property of its own model to rCustomAutoID;
- If your tables are defined as TSQLRecord, ensure that both Client and Server set the table property of its own model to rCustomAutoID.

First option could be done as such:

type
  TSQLValue1 = class(TSQLRecordVirtualTableAutoID)
  (...)
  TSQLValue2 = class(TSQLRecordVirtualTableAutoID)
  (...)

Or the client model could be updated as such:

constructor TSQLClient.Create;
var aModel: TSQLModel;
begin
  aModel:= CreateModel;
  aModel.Props[TSQLValue1].Kind := rCustomAutoID;
  aModel.Props[TSQLValue2].Kind := rCustomAutoID;
  aModel.Owner := self; // model will be released within TSQLServer instance
  inherited Create('127.0.0.1', '8080', aModel);
  SetUser('Admin', 'synopse');
end;

Or, perhaps the easiest way of doing it, is to set the property when creating the shared model:

function CreateModel: TSQLModel;
begin
  result:= TSQLModel.Create([TSQLAuthGroup, TSQLAuthUser, TSQLValue2, TSQLValue1]);
  result.Props[TSQLValue1].Kind := rCustomAutoID;
  result.Props[TSQLValue2].Kind := rCustomAutoID;
end;

The easiest is definitively to let your static in-memory tables inherit from TSQLRecordVirtualTableAutoID.

For external databases, the SQL conversion will be done on the fly in a more advanced way, so you should be able to work with such virtual tables from the client side without any specific model notification.