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

EMartin
Member
From: Buenos Aires - Argentina
Registered: 2013-01-09
Posts: 332

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

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.


Esteban

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

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

Are you calling CreateMissingTable as required?

Offline

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

EMartin
Member
From: Buenos Aires - Argentina
Registered: 2013-01-09
Posts: 332

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

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.


Esteban

Offline

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

EMartin
Member
From: Buenos Aires - Argentina
Registered: 2013-01-09
Posts: 332

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

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. big_smile

Thanks.


Esteban

Offline

Board footer

Powered by FluxBB