mORMot Open Source

 Props := TSqlDBOleDBMSSQL2018ConnectionProperties.Create('MyAnotherPC','Training','sa','123');
 SQLite3Log.Enter(self);
 Model := CreateSampleModel;
 VirtualTableExternalRegister(Model,[TOrmSample],Props);
 SampleServer := TSampleServer.Create(Model, ':memory:'); 

20240512 11100250  " DB         mormot.db.sql.oledb.TSqlDBOleDBStatement(027c6f70) Prepare t=1.08s q=select max(ID) from dbo.Sample
20240512 11100250  " SQL        mormot.db.sql.oledb.TSqlDBOleDBStatement(027c6f70) Execute t=1.09s q=select max(ID) from dbo.Sample
20240512 11100251  " DB         mormot.db.sql.oledb.TSqlDBOleDBStatement(027c7090) Prepare t=38us q=insert into dbo.Sample (ID,Name,Question,Time) values (?,?,?,?)
20240512 11100251  " SQL        mormot.db.sql.oledb.TSqlDBOleDBStatement(027c7090) Execute t=11.49ms wr=1 q=insert into dbo.Sample (ID,Name,Question,Time) values (3,'','',135846605441)
20240512 11100252  " srvr         Write POST root/Sample=201 out=0 B in 1.12s
20240512 11100252  "  -    01.128.548
20240512 11122835  #  +    server.TSampleServer(02ac9b20).URI POST root/Sample in=54 B
20240512 11122835  #  +         mormot.db.sql.oledb.TSqlDBOleDBConnection(0283bb70).Create
20240512 11122835  #  -         00.002.040
20240512 11122835  #  +         mormot.db.sql.oledb.TSqlDBOleDBConnection(0283bb70).Connect
20240512 11123143  #  -         03.122.602
20240512 11123143  # DB         mormot.db.sql.oledb.TSqlDBOleDBStatement(027c71b0) Prepare t=3.12s q=insert into dbo.Sample (ID,Name,Question,Time) values (?,?,?,?)
20240512 11123144  # SQL        mormot.db.sql.oledb.TSqlDBOleDBStatement(027c71b0) Execute t=3.13s wr=1 q=insert into dbo.Sample (ID,Name,Question,Time) values (4,'','',135846605596)
20240512 11123144  # srvr         Write POST root/Sample=201 out=0 B in 3.15s
20240512 11123144  #  -    03.153.490

20240512 11202701  % DB         mormot.db.sql.oledb.TSqlDBOleDBStatement(027c73f0) Prepare t=1.14s q=select top(1) ID,Name,Question,Time from dbo.Sample where Name=?
20240512 11202702  % SQL        mormot.db.sql.oledb.TSqlDBOleDBStatement(027c73f0) Execute t=1.14s q=select top(1) ID,Name,Question,Time from dbo.Sample where Name=''
20240512 11202702  % srvr         Read GET root=200 out=62 B in 1.17s
20240512 11202703  % ret        mormot.rest.server.TRestServerRoutingRest(02a478f0) [{"ID":5,"Name":"m2","Question":"tst 1","Time":135846606086}]
20240512 11202703  %  -    01.179.860
20240512 11203207  "  +    server.TSampleServer(02ac9b20).URI GET root in=68 B
20240512 11203207  " DB         mormot.db.sql.oledb.TSqlDBOleDBStatement(027c7510) Prepare t=119us q=select top(1) ID,Name,Question,Time from dbo.Sample where Name=?
20240512 11203208  " SQL        mormot.db.sql.oledb.TSqlDBOleDBStatement(027c7510) Execute t=8.94ms q=select top(1) ID,Name,Question,Time from dbo.Sample where Name=''
20240512 11203208  " srvr         Read GET root=200 out=62 B in 21.97ms
20240512 11203209  " ret        mormot.rest.server.TRestServerRoutingRest(02a478f0) [{"ID":5,"Name":"m2","Question":"tst 1","Time":135846606086}]
20240512 11203210  "  -    00.056.932
20240512 11204361  #  +    server.TSampleServer(02ac9b20).URI POST root/Sample in=52 B
20240512 11204361  # SQL        mormot.db.sql.oledb.TSqlDBOleDBStatement(027c71b0) Execute t=8.03ms wr=1 q=insert into dbo.Sample (ID,Name,Question,Time) values (6,'','',135846606123)
20240512 11204361  # srvr         Write POST root/Sample=201 out=0 B in 20.70ms
20240512 11204361  #  -    00.021.199

var
  v : Variant;
begin
  v := _Json('{"abc":123,"b":"c", "c": {"d":"e"}, "e": [1,2,3]}');

$.abc:123
$.b:c
$.c.d:e
$.e[0]:1
$.e[1]:2
$.e[2]:3

function GetPathAndValue( JDOC: TDocVariantData; AParentPath: string ='$'): TList<TSQLResourceIndex>;
var
  LPathValues: TList<TSQLResourceIndex>;
  LDoc : TDocVariantData;
  i, j: Integer;
  LVal : TDocVariantData;
  LIdx, LNewIdx : TSQLResourceIndex;
  LList : TList<TSQLResourceIndex>;
begin
  LDoc := JDOC;
  LPathValues := TList<TSQLResourceIndex>.Create;
  for i := 0 to LDoc.Count -1 do
  begin
    try
      LVal := TDocVariantData(LDoc.Values[i]); //conversion is failed when the value is an integer
    except
      LIdx := TSQLResourceIndex.Create;
      LIdx.JPath := AParentPath + '.' + LDoc.Names[i];
      LIdx.JValue := LDoc.Values[i];
      LPathValues.Add(LIdx);
      Continue;
    end;
    if LVal.Kind = dvObject then
    begin
      LList := GetPathAndValue(LVal, AParentPath+'.'+LDoc.Names[i]) ;
      for LIdx in LList do
      begin
        LNewIdx := TSQLResourceIndex.Create;
        LNewIdx.JPath := LIdx.JPath;
        LNewIdx.JValue := LIdx.JValue;
        LPathValues.Add(LNewIdx);
      end;
      LList.Free;
    end
    else if (Lval.Kind = dvArray) then
    begin
      for j := 0 to LVal.Count -1 do
      begin
        LList := GetPathAndValue(TDocVariantData(LVal.Values[j]),AParentPath+'.'+LDoc.Names[i]);
        for LIdx in LList do
        begin
          LNewIdx := TSQLResourceIndex.Create;
          LNewIdx.JPath := LIdx.JPath;
          LNewIdx.JValue := LIdx.JValue;
          LPathValues.Add(LNewIdx);
        end;
        LList.Free;
      end;
    end
    else begin
      LIdx := TSQLResourceIndex.Create;
      LIdx.JPath := AParentPath + '.' + LDoc.Names[i];
      LIdx.JValue := LDoc.Values[i];
      LPathValues.Add(LIdx);
    end;
  end;
  Result := LPathValues;
end;

GetPathAndValue(TDocVariantData(v));

    try
      LVal := TDocVariantData(LDoc.Values[i]); //conversion is failed when the value is an integer
    except
    ...
    end;

select
  lastuid.*
from
 lastuid

select  lastuid.* from lastuid

Check(isSelect('select'+#13#10+ ' * from toto'));