Virtual table is created in first call but not returning field ID

EMartin · 2017-06-14 21:29:51

Hi @ab, I have a problem with ID field in an external virtual table in the first call, the GET request http://host:port/root/tablename?select=* the answer is with error "400 bad request", in the next call the response is ok, when debugging I detected that with the first call the mORMotSQLite3.vt_Create function is invoked and then mORMot.TSQLVirtualTable.Create and this register the table in aServer.fStaticVirtualTable, with this allow the correct response in next calls.

Thanks.

ab · 2017-06-15 06:27:27

Are you calling CreateMissingTable as required?

EMartin · 2017-06-15 10:48:35

Yes of course. I have registered virtual external tables using SetOptions with NotCreateMissingFields (a database script make this), I can see the virtual external tables in the SQLite3 db. When the application start, the first time, the GET collection data request, the column ID is not sent, the next time yes. With non external tables I haven't this problem.

Thanks.

EMartin · 2017-06-15 15:58:13

Hi @ab, this is the sequence call between first call and next:

procedure TSQLRestServerURIContext.ExecuteORMGet;
...
                // use the first static table (poorman's JOIN)
                Static := Server.InternalAdaptSQL(TableIndexes[0],SQL); //--> always executed
              end;
            end;
            if Static<>nil then  begin
              TableEngine := Static;
              Call.OutBody := TableEngine.EngineList(SQL); //--> always executed after first time
            end else
              Call.OutBody := Server.MainEngineList(SQL,false,nil); //--> executed ONLY the firs time
            // security note: only first statement is run by EngineList()
            if Call.OutBody<>'' then begin // got JSON list '[{...}]' ?
...
end;

procedure TSQLRestServerDB.PrepareStatement(Cached: boolean);
var wasPrepared: boolean;
    timer: PPPrecisionTimer;
begin
  fStaticStatementTimer.Start;
  if not Cached then begin
    fStaticStatement.Prepare(DB.DB,fStatementGenericSQL); //--> executed the first time (called from PrepareStatement->GetAndPrepareStatement->MainEngineList)
    fStatementGenericSQL := '';			          //    then this call to TSQLRequest.Prepare->mORMotSQLite3.vt_create->TSQLVirtualTable.Create
  ...
end;

The first time the error is like this (varying the table and the field names):

20170615 12425630 ( EXC ESQLite3Exception {"ErrorCode":1,"SQLite3ErrorCode":"secERROR","Message":"Error SQLITE_ERROR (1) [SELECT ID,IDMDService,Code,Statement,CRC,LastAccess,MDServiceType,Description,BinData,Internal,Statement_U,Description_U FROM MDServices] using 3.19.2 - no such column: ID, extended_errcode=1"} at 006FED8A SynSQLite3.sqlite3_check (5319) stack trace API

The virtual table registration this table is:

VirtualTableExternalMap(fModel, TSQLMDServices, fConnPropSQLAdmin, 'TV_MDSERVICES').
MapFields(['ID','IDMDSERVICE']).
SetOptions([rpmAutoMapKeywordFields, rpmNoCreateMissingTable, rpmNoCreateMissingField]);

With the next calls all works fine.

My workaround is dirty and bad performance:

  for I := Low(fModel.Tables) to High(fModel.Tables) do begin
    if (fModel.Props[fModel.Tables[I]].Kind in [rCustomForcedID, rCustomAutoID]) then begin // only external tables
      lMappingVersion := fModel.Props[fModel.Tables[I]].ExternalDB.MappingVersion;
      if lMappingVersion>1 then // only with mapped fields
        fServerRest.TableHasRows(fModel.Tables[I]);
    end;
  end;

For me this a bug but you have the last word.

Thanks.

mORMot Open Source

#1 2017-06-14 21:29:51

Virtual table is created in first call but not returning field ID

#2 2017-06-15 06:27:27

Re: Virtual table is created in first call but not returning field ID

#3 2017-06-15 10:48:35

Re: Virtual table is created in first call but not returning field ID

#4 2017-06-15 15:58:13

Re: Virtual table is created in first call but not returning field ID

Board footer