!  finally
!    R.Free;
!  end;
You can define a WHERE clause, as if the back-end where a regular SQL database:
!    R := TSQLORM.CreateAndFillPrepare(Client,'ID=?',[i]);
!    try
!    ...
Current implementation understand one condition over one single field, with {\f1\fs20 = > >= < <= IN} clauses. More advanced queries are possible, but won't be handled as SQL, but via direct access to the {\f1\fs20 TMongoDBCollection}.
To perform a query and retrieve the content of several documents, you can use regular {\f1\fs20 CreateAndFillPrepare} or {\f1\fs20 FillPrepare} methods:
!!  R := TSQLORM.CreateAndFillPrepare(Client,'');
!  try
!    n := 0;
!!    while R.FillOne do begin
!      // here R instance contains all values of one document, excluding BLOBs
!      inc(n);
!    end;
!    assert(n=COLL_COUNT);
!  finally
!    R.Free;
!  end;
A WHERE clause can also be defined for {\f1\fs20 CreateAndFillPrepare} or {\f1\fs20 FillPrepare} methods.





















:   BATCH mode
In addition to individual @*CRUD@ operations, our {\i MongoDB} is able to use BATCH mode for adding or deleting documents.
You can write the exact same code as with any SQL back-end:
!  Client.BatchStart(TSQLORM);
!  R := TSQLORM.Create;
!  try
!    for i := 1 to COLL_COUNT do begin

!  finally
!    R.Free;
!  end;
You can define a WHERE clause, as if the back-end where a regular SQL database:
!    R := TSQLORM.CreateAndFillPrepare(Client,'ID=?',[i]);
!    try
!    ...
:  ODM complex queries
To perform a query and retrieve the content of several documents, you can use regular {\f1\fs20 CreateAndFillPrepare} or {\f1\fs20 FillPrepare} methods:
!!  R := TSQLORM.CreateAndFillPrepare(Client,WHERE_CLAUSE,[WHERE_PARAMETERS]);
!  try
!    n := 0;
!!    while R.FillOne do begin
!      // here R instance contains all values of one document, excluding BLOBs
!      inc(n);
!    end;
!    assert(n=COLL_COUNT);
!  finally
!    R.Free;
!  end;
A WHERE clause can also be defined for {\f1\fs20 CreateAndFillPrepare} or {\f1\fs20 FillPrepare} methods. This WHERE clause could contain several expressions, joined with {\f1\fs20 AND} / {\f1\fs20 OR}.\line Each of those expressions could use:
- The simple comparators {\f1\fs20 = < <= <> > >=},
- An {\f1\fs20 IN (....)} clause,
- {\f1\fs20 IS NULL} / {\f1\fs20 IS NOT NULL} tests,
- Or even a {\f1\fs20 LIKE} operation.
The {\i mORMot} ODM will convert this SQL-like statement into the optimized {\f1\fs20 MongoDB} query expression, using e.g. a regular expression for the {\f1\fs20 LIKE} operator.
Here are some typical WHERE clauses, and the corresponding {\i MongoDB} query document as generated by the ODM:
|%50%50
|\b WHERE clause|{\i MongoDB} Query\b0
|{\f1\fs20 'Name=?',['Name 43']}|{\f1\fs20 \{Name:"Name 43"\}}
|{\f1\fs20 'Age<?',[51]}|{\f1\fs20 \{Age:\{$lt:51\}\}}
|{\f1\fs20 'Age in (1,10,20)'}|{\f1\fs20 \{Age:\{$in:[1,10,20]\}\}}
|{\f1\fs20 'Age in (1,10,20) and ID=?',[10]}|{\f1\fs20 \{Age:\{$in:[1,10,20]\},_id:10\}}
|{\f1\fs20 'Age in (10,20) or ID=?',[30]}|{\f1\fs20 \{$or:[\{Age:\{$in:[10,20]\}\},\{_id:30\}]\}}
|{\f1\fs20 'Name like ?',['name 1%']}|{\f1\fs20 \{Name:/^name 1/i\}}
|{\f1\fs20 'Name like ?',['name 1']}|{\f1\fs20 \{Name:/^name 1$/i\}}
|{\f1\fs20 'Name like ?',['%ame 1%']}|{\f1\fs20 \{Name:/ame 1/i\}}
|{\f1\fs20 'Data is null'}|{\f1\fs20 \{Data:null\}}
|{\f1\fs20 'Data is not null'}|{\f1\fs20 \{Data:\{$ne:null\}\}}
|%
Note that parenthesis and mixed {\f1\fs20 AND} {\f1\fs20 OR} expressions are not handled yet. You could always execute any complex query (e.g. aggregations or {\i Map/Reduce}) by using directly the {\f1\fs20 TMongoCollection} methods.
But for most cases, {\i mORMot} allows to share the same exact code between your regular SQL databases or NoSQL engines. You do not need to learn the {\i MongoDB} query syntax: the ODM would compute the right expression for you, depending on the database engine it runs on.
:   BATCH mode
In addition to individual @*CRUD@ operations, our {\i MongoDB} is able to use BATCH mode for adding or deleting documents.
You can write the exact same code as with any SQL back-end:
!  Client.BatchStart(TSQLORM);
!  R := TSQLORM.Create;
!  try
!    for i := 1 to COLL_COUNT do begin

  end;
  NotifyTestSpeed('rows retrieved',COLL_COUNT,fMongoClient.BytesTransmitted-bytes);
end;

procedure TTestORM.RetrieveOneWithWhereClause;
var R: TSQLORM;
    i,n: integer;
    bytes: Int64;
begin
  bytes := fMongoClient.BytesTransmitted;
  for i := 1 to COLL_COUNT do begin
    R := TSQLORM.CreateAndFillPrepare(fClient,'ID=?',[i]);
    try
      n := 0;
      while R.FillOne do begin
................................................................................
      inc(n);
      TestOne(R,n);
    end;
    Check(n=50);
  finally
    R.Free;
  end;















































































end;

procedure TTestORM.Update;
var R: TSQLORM;
    bytes: Int64;
    n: integer;
begin
................................................................................
  end;
end;

procedure TTestORM.Blobs;
var R: TSQLORM;
    i, n: integer;
    blob,blobRead: TSQLRawBlob;
    bytes: Int64;
begin
  SetLength(blob,8);
  bytes := fMongoClient.BytesTransmitted;
  for i := 1 to COLL_COUNT do begin
    PIntegerArray(blob)[0] := i;
    PIntegerArray(blob)[1] := i*$01020304;
    Check(fClient.UpdateBlob(TSQLORM,i,'Data',blob));
................................................................................
      Check(R.Data=blob);
      R.Data := '';
    end;
    Check(n=COLL_COUNT);
  finally
    R.Free;
  end;




end;

procedure TTestORM.Delete;
var i,n: integer;
    ExpectedCount: integer;
    bytes: Int64;
    temp: string;

  end;
  NotifyTestSpeed('rows retrieved',COLL_COUNT,fMongoClient.BytesTransmitted-bytes);
end;

procedure TTestORM.RetrieveOneWithWhereClause;
var R: TSQLORM;
    i,n: integer;
    bytes,i64: Int64;
begin
  bytes := fMongoClient.BytesTransmitted;
  for i := 1 to COLL_COUNT do begin
    R := TSQLORM.CreateAndFillPrepare(fClient,'ID=?',[i]);
    try
      n := 0;
      while R.FillOne do begin
................................................................................
      inc(n);
      TestOne(R,n);
    end;
    Check(n=50);
  finally
    R.Free;
  end;
  R := TSQLORM.CreateAndFillPrepare(fClient,'Age in (1,10,20)',[51]);
  try
    n := 0;
    while R.FillOne do begin
      inc(n);
      case n of
      1: i := 1;
      2: i := 10;
      3: i := 20;
      else i := 0;
      end;
      TestOne(R,i);
    end;
    Check(n=3);
  finally
    R.Free;
  end;
  R := TSQLORM.CreateAndFillPrepare(fClient,'Age in (1,10,20) and ID=?',[10]);
  try
    n := 0;
    while R.FillOne do begin
      inc(n);
      TestOne(R,10);
    end;
    Check(n=1);
  finally
    R.Free;
  end;
  R := TSQLORM.CreateAndFillPrepare(fClient,'Age in (10,20) or ID=?',[30]);
  try  
    n := 0;
    while R.FillOne do begin
      inc(n);
      TestOne(R,n*10);
    end;
    Check(n=3,'{$or:[{Age:{$in:[10,20]}},{_id:30}]}');
  finally
    R.Free;
  end;
  R := TSQLORM.CreateAndFillPrepare(fClient,'Name like ?',['name 1%']);
  try
    n := 0;
    while R.FillOne do begin
      inc(n);
      Check(IdemPChar(pointer(R.Name),'NAME 1'));
      TestOne(R,R.Age);
    end;
    Check(n>10,'{Name:/^name 1/i}');
  finally
    R.Free;
  end;
  R := TSQLORM.CreateAndFillPrepare(fClient,'Name like ?',['name 1']);
  try
    n := 0;
    while R.FillOne do begin
      inc(n);
      Check(IdemPChar(pointer(R.Name),'NAME 1'));
      TestOne(R,R.Age);
    end;
    Check(n=1,'{Name:/^name 1$/i}');
  finally
    R.Free;
  end;
  R := TSQLORM.CreateAndFillPrepare(fClient,'Name like ?',['%ame 1%']);
  try
    n := 0;
    while R.FillOne do begin
      inc(n);
      Check(IdemPChar(pointer(R.Name),'NAME 1'));
      TestOne(R,R.Age);
    end;
    Check(n>10,'{Name:/ame 1/i}');
  finally
    R.Free;
  end;
  check(fClient.OneFieldValue(TSQLORM,'count(*)','Data is null',[],[],i64));
  check(i64=COLL_COUNT,'{Data:null}');
  check(fClient.OneFieldValue(TSQLORM,'count(*)','Data is not null',[],[],i64));
  check(i64=0,'{Data:{$ne:null}}');
end;

procedure TTestORM.Update;
var R: TSQLORM;
    bytes: Int64;
    n: integer;
begin
................................................................................
  end;
end;

procedure TTestORM.Blobs;
var R: TSQLORM;
    i, n: integer;
    blob,blobRead: TSQLRawBlob;
    bytes,i64: Int64;
begin
  SetLength(blob,8);
  bytes := fMongoClient.BytesTransmitted;
  for i := 1 to COLL_COUNT do begin
    PIntegerArray(blob)[0] := i;
    PIntegerArray(blob)[1] := i*$01020304;
    Check(fClient.UpdateBlob(TSQLORM,i,'Data',blob));
................................................................................
      Check(R.Data=blob);
      R.Data := '';
    end;
    Check(n=COLL_COUNT);
  finally
    R.Free;
  end;
  check(fClient.OneFieldValue(TSQLORM,'count(*)','Data is null',[],[],i64));
  check(i64=0,'{Data:null}');
  check(fClient.OneFieldValue(TSQLORM,'count(*)','Data is not null',[],[],i64));
  check(i64=COLL_COUNT,'{Data:{$ne:null}}');
end;

procedure TTestORM.Delete;
var i,n: integer;
    ExpectedCount: integer;
    bytes: Int64;
    temp: string;

var SQL: RawUTF8;
    i: integer;
    T: TSQLTableJSON;
begin
  result := false;
  if (self<>nil) and (Table<>nil) and (length(FieldName)=length(FieldValue)) then
  with Table.RecordProps do begin


    for i := 0 to high(FieldName) do
      if not IsFieldName(FieldName[i]) then
        exit else // prevent SQL error
        if SQL='' then
          SQL := 'SELECT '+FieldName[i] else
          SQL := SQL+','+FieldName[i];
    SQL := SQL+' FROM '+SQLTableName+' WHERE '+WhereClause+' LIMIT 1;';

    T := ExecuteList([Table],SQL);
    if T<>nil then
    try
      if (T.FieldCount<>length(FieldName)) or (T.RowCount<=0) then
        exit;
      // get field values from the first (and unique) row
      for i := 0 to T.FieldCount-1 do
................................................................................
                inc(result);
                if (Stmt.Limit>0) and (result>=Stmt.Limit) then
                  break;
              end;
            end else
              goto err;
    {$endif}
    opIs: // handle IS NULL and IS NOT NULL operators
      if Stmt.Where[0].Field>0 then begin
        Prop := fStoredClassRecordProps.Fields.List[Stmt.Where[0].Field-1];
        if Prop.InheritsFrom(TSQLPropInfoRTTIRawBlob) then begin
          IsNull := IdemPropName(Stmt.Where[0].Value,'NULL');
          for i := 0 to fValue.Count-1 do
          if TSQLPropInfoRTTIRawBlob(Prop).IsNull(fValue.List[i])=IsNull then begin
            TSQLRecord(fValue.List[i]).GetJSONValues(W);
            W.Add(',');
            inc(result);
            if (Stmt.Limit>0) and (result>=Stmt.Limit) then
              break;

var SQL: RawUTF8;
    i: integer;
    T: TSQLTableJSON;
begin
  result := false;
  if (self<>nil) and (Table<>nil) and (length(FieldName)=length(FieldValue)) then
  with Table.RecordProps do begin
    if (length(FieldName)=1) and IdemPChar(pointer(FieldName[0]),'COUNT(*)') then
      SQL := 'SELECT COUNT(*) FROM '+SQLTableName+' WHERE '+WhereClause else begin
      for i := 0 to high(FieldName) do
        if not IsFieldName(FieldName[i]) then
          exit else // prevent SQL error
          if SQL='' then
            SQL := 'SELECT '+FieldName[i] else
            SQL := SQL+','+FieldName[i];
      SQL := SQL+' FROM '+SQLTableName+' WHERE '+WhereClause+' LIMIT 1;';
    end;
    T := ExecuteList([Table],SQL);
    if T<>nil then
    try
      if (T.FieldCount<>length(FieldName)) or (T.RowCount<=0) then
        exit;
      // get field values from the first (and unique) row
      for i := 0 to T.FieldCount-1 do
................................................................................
                inc(result);
                if (Stmt.Limit>0) and (result>=Stmt.Limit) then
                  break;
              end;
            end else
              goto err;
    {$endif}
    opIsNull, opIsNotNull:
      if Stmt.Where[0].Field>0 then begin
        Prop := fStoredClassRecordProps.Fields.List[Stmt.Where[0].Field-1];
        if Prop.InheritsFrom(TSQLPropInfoRTTIRawBlob) then begin
          IsNull := Stmt.Where[0].Operator=opIsNull;
          for i := 0 to fValue.Count-1 do
          if TSQLPropInfoRTTIRawBlob(Prop).IsNull(fValue.List[i])=IsNull then begin
            TSQLRecord(fValue.List[i]).GetJSONValues(W);
            W.Add(',');
            inc(result);
            if (Stmt.Limit>0) and (result>=Stmt.Limit) then
              break;

    function EngineList(const SQL: RawUTF8; ForceAJAX: Boolean=false; ReturnedRowCount: PPtrInt=nil): RawUTF8; override;
    function EngineAdd(TableModelIndex: integer; const SentData: RawUTF8): TID; override;
    function EngineUpdate(TableModelIndex: integer; ID: TID; const SentData: RawUTF8): boolean; override;
    function EngineUpdateField(TableModelIndex: integer;
      const SetFieldName, SetValue, WhereFieldName, WhereValue: RawUTF8): boolean; override;
    function EngineDeleteWhere(TableModelIndex: Integer;const SQLWhere: RawUTF8;
      const IDs: TIDDynArray): boolean; override;
    // BLOBs should be access directly, not through slower JSON Base64 encoding
    function EngineRetrieveBlob(TableModelIndex: integer; aID: TID;
      BlobField: PPropInfo; out BlobData: TSQLRawBlob): boolean; override;
    function EngineUpdateBlob(TableModelIndex: integer; aID: TID;
      BlobField: PPropInfo; const BlobData: TSQLRawBlob): boolean; override;
    // method not implemented: always return false
    function EngineExecute(const aSQL: RawUTF8): boolean; override;
    /// TSQLRestServer.URI use it for Static.EngineList to by-pass virtual table
................................................................................
    Query,Projection: variant;
    Res: TBSONDocument;
    ResCount: PtrInt;
    extFieldNames: TRawUTF8DynArray;
    Stmt: TSynTableStatement;
    bits: TSQLFieldBits;
    withID: boolean;

procedure ComputeQuery;
const // see http://docs.mongodb.org/manual/reference/operator/query
  QUERY_OPS: array[opNotEqualTo..opIn] of RawUTF8 = (
    '$ne','$lt','$lte','$gt','$gte','$in');
var QueryFieldName: RawUTF8;



begin






  if (Length(Stmt.Where)<>1) or (Stmt.Where[0].Field<0) then begin

    SetVariantNull(Query); // only a SINGLE expression is allowed yet
    exit;
  end;




















  QueryFieldName := fStoredClassProps.ExternalDB.FieldNameByIndex(Stmt.Where[0].Field-1);
  case Stmt.Where[0].Operator of
  opEqualTo:
    Query := BSONVariant([QueryFieldName,Stmt.Where[0].ValueVariant]);
  opIs: // http://docs.mongodb.org/manual/faq/developers/#faq-developers-query-for-nulls
    if IdemPropName(Stmt.Where[0].Value,'null') then
      Query := BSONVariant('{%:{$type:10}}',[QueryFieldName],[]) else
      Query := BSONVariant('{%:{$not:{type:10}}}',[QueryFieldName],[])
  else













    Query := BSONVariant([QueryFieldName,
      '{',QUERY_OPS[Stmt.Where[0].Operator],Stmt.Where[0].ValueVariant,'}']);


  end;

end;
procedure SetCount(aCount: integer);
begin
  result := FormatUTF8('[{"Count(*)":%}]'#$A,[aCount]);
  ResCount := 1;
end;

begin // same logic as in TSQLRestStorageInMemory.EngineList()
  ResCount := 0;
  if self=nil then begin
    result := '';
    exit;
  end;
  StorageLock(false);
................................................................................
        result := '[{"RowID":1}]'#$A;
        ResCount := 1;
      end else begin
      Stmt := TSynTableStatement.Create(SQL,
        fStoredClassRecordProps.Fields.IndexByName,
        fStoredClassRecordProps.SimpleFieldsBits[soSelect]);
      try
        if (Stmt.SQLStatement='') or  // parsing failed
           (length(Stmt.Where)<>1) or // only a SINGLE expression is allowed yet
           not IdemPropNameU(Stmt.TableName,fStoredClassRecordProps.SQLTableName) then begin
          // invalid request -> return '' to mark error
          result := '';
          exit;
        end;
        if Stmt.Where[0].Field=SYNTABLESTATEMENTWHERECOUNT then
          // was "SELECT Count(*) FROM TableName;"
          SetCount(TableRowCount(fStoredClass)) else
        if Stmt.SelectFields=nil then begin
          if Stmt.IsSelectCountWhere then
            // was "SELECT Count(*) FROM TableName WHERE ..."
            if Stmt.Where[0].Field<0 then
              SetCount(TableRowCount(fStoredClass)) else begin
              ComputeQuery;



              SetCount(fCollection.FindCount(Query));
            end;
          exit; // also invalid "SELECT FROM Table"
        end;
        // save rows as JSON, with appropriate search according to Where* arguments
        ComputeQuery;



        Stmt.SelectFieldBits(bits,withID);
        BSONProjectionSet(Projection,withID,bits,@extFieldNames);
        if Stmt.Limit=0 then
          Stmt.Limit := maxInt;
        Res := fCollection.FindBSON(Query,Projection,Stmt.Limit,Stmt.Offset);
        MS := TRawByteStringStream.Create;
        try

    function EngineList(const SQL: RawUTF8; ForceAJAX: Boolean=false; ReturnedRowCount: PPtrInt=nil): RawUTF8; override;
    function EngineAdd(TableModelIndex: integer; const SentData: RawUTF8): TID; override;
    function EngineUpdate(TableModelIndex: integer; ID: TID; const SentData: RawUTF8): boolean; override;
    function EngineUpdateField(TableModelIndex: integer;
      const SetFieldName, SetValue, WhereFieldName, WhereValue: RawUTF8): boolean; override;
    function EngineDeleteWhere(TableModelIndex: Integer;const SQLWhere: RawUTF8;
      const IDs: TIDDynArray): boolean; override;
    // BLOBs should be accessed directly, not through slower JSON Base64 encoding
    function EngineRetrieveBlob(TableModelIndex: integer; aID: TID;
      BlobField: PPropInfo; out BlobData: TSQLRawBlob): boolean; override;
    function EngineUpdateBlob(TableModelIndex: integer; aID: TID;
      BlobField: PPropInfo; const BlobData: TSQLRawBlob): boolean; override;
    // method not implemented: always return false
    function EngineExecute(const aSQL: RawUTF8): boolean; override;
    /// TSQLRestServer.URI use it for Static.EngineList to by-pass virtual table
................................................................................
    Query,Projection: variant;
    Res: TBSONDocument;
    ResCount: PtrInt;
    extFieldNames: TRawUTF8DynArray;
    Stmt: TSynTableStatement;
    bits: TSQLFieldBits;
    withID: boolean;

function ComputeQuery: boolean;



var FieldName: RawUTF8;
    B: TBSONWriter;
    start,startOR,startORItem: cardinal;
    n,w: integer;
begin
  result := false;
  if Stmt.SQLStatement='' then begin
    InternalLog('%.EngineList: Invalid SQL statement "%"',[self,SQL],sllError);
    exit;
  end;
  n := Length(Stmt.Where);
  if (n<1) or (Stmt.Where[0].Field<0) then begin // no WHERE clause or COUNT(*)
    result := true;
    SetVariantNull(Query); // void query -> returns all rows
    exit;
  end;
  B := TBSONWriter.Create(TRawByteStringStream);
  try
    startORItem := 0; // makes compiler happy
    start := B.BSONDocumentBegin;
    if (n>1) and Stmt.Where[1].JoinedOR then begin
      for w := 2 to n-1 do
      if not Stmt.Where[w].JoinedOR then begin
        InternalLog('%.EngineList: Unhandled mixed AND/OR for "%"',[self,SQL],sllError);
        exit;
      end;
      B.BSONWrite('$or',betArray); // e.g. {$or:[{quantity:{$lt:20}},{price:10}]}
      startOR := B.BSONDocumentBegin;
    end else
      startOR := cardinal(-1);
    for w := 0 to n-1 do begin
      if Integer(startOR)>=0 then begin
        B.BSONWrite(UInt32ToUtf8(w),betDoc);
        startORItem := B.BSONDocumentBegin;
      end;
      with Stmt.Where[w] do begin
        FieldName := fStoredClassProps.ExternalDB.FieldNameByIndex(Field-1);








        if not B.BSONWriteQueryOperator(FieldName,Operator,ValueVariant) then begin
          InternalLog('%.EngineList: Unhandled operator % for field "%" in "%"',[
            self,GetEnumName(TypeInfo(TSynTableStatementOperator),ord(Operator))^,
            FieldName,SQL],sllError);
          exit;
        end;
      end;
      if Integer(startOR)>=0 then
        B.BSONDocumentEnd(startORItem);
    end;
    if Integer(startOR)>=0 then
      B.BSONDocumentEnd(startOR);
    B.BSONDocumentEnd(start);
    B.ToBSONVariant(Query);

  finally
    B.Free;
  end;
  result := true; // indicates success
end;
procedure SetCount(aCount: integer);
begin
  result := FormatUTF8('[{"Count(*)":%}]'#$A,[aCount]);
  ResCount := 1;
end;

begin // same logic as in TSQLRestStorageInMemory.EngineList()
  ResCount := 0;
  if self=nil then begin
    result := '';
    exit;
  end;
  StorageLock(false);
................................................................................
        result := '[{"RowID":1}]'#$A;
        ResCount := 1;
      end else begin
      Stmt := TSynTableStatement.Create(SQL,
        fStoredClassRecordProps.Fields.IndexByName,
        fStoredClassRecordProps.SimpleFieldsBits[soSelect]);
      try
        if (Stmt.SQLStatement='') or (length(Stmt.Where)=0) or  // parsing failed

          not IdemPropNameU(Stmt.TableName,fStoredClassRecordProps.SQLTableName) then begin
          // invalid request -> return '' to mark error
          result := '';
          exit;
        end;
        if Stmt.Where[0].Field=SYNTABLESTATEMENTWHERECOUNT then
          // was "SELECT Count(*) FROM TableName;"
          SetCount(TableRowCount(fStoredClass)) else
        if Stmt.SelectFields=nil then begin
          if Stmt.IsSelectCountWhere then
            // was "SELECT Count(*) FROM TableName WHERE ..."
            if Stmt.Where[0].Field<0 then
              SetCount(TableRowCount(fStoredClass)) else begin
              if not ComputeQuery then begin
                result := ''; // indicates error
                exit;
              end;
              SetCount(fCollection.FindCount(Query));
            end;
          exit; // also invalid "SELECT FROM Table"
        end;
        // save rows as JSON, with appropriate search according to Where* arguments
        if not ComputeQuery then begin
          result := ''; // indicates error
          exit;
        end;
        Stmt.SelectFieldBits(bits,withID);
        BSONProjectionSet(Projection,withID,bits,@extFieldNames);
        if Stmt.Limit=0 then
          Stmt.Limit := maxInt;
        Res := fCollection.FindBSON(Query,Projection,Stmt.Limit,Stmt.Offset);
        MS := TRawByteStringStream.Create;
        try

     opEqualTo,
     opNotEqualTo,
     opLessThan,
     opLessThanOrEqualTo,
     opGreaterThan,
     opGreaterThanOrEqualTo,
     opIn,
     opIs,

     opLike);

  TSynTableFieldProperties = class;

  /// one recognized WHERE expression for TSynTableStatement
  TSynTableStatementWhere = record
    /// expressions are evaluated as AND unless this field is set to TRUE
................................................................................
       else
         Where.Operator := opLessThan;
       end;
  'i','I':
    case P[1] of
    's','S': begin
      P := GotoNextNotSpace(P+2);
      {$ifndef NOVARIANTS}
      SetVariantNull(Where.ValueVariant);
      {$endif}
      if IdemPChar(P,'NULL') then begin
        Where.Value := 'null';
        Where.Operator := opIs;



        inc(P,4);
        result := true;
      end else
      if IdemPChar(P,'NOT NULL') then begin
        Where.Value := 'not null';
        Where.Operator := opIs;



        inc(P,8);
        result := true;
      end;
      exit;
    end;
    {$ifndef NOVARIANTS}
    'n','N': begin
       Where.Operator := opIn;
       P := GotoNextNotSpace(P+2);
................................................................................
       inc(P);
       while P^<>')' do
         if P^=#0 then
           exit else
           inc(P);
       inc(P);
       SetString(Where.Value,PAnsiChar(B),P-B);


       Where.Value[1] := '[';
       Where.Value[P-B] := ']';
       TDocVariantData(Where.ValueVariant).InitJSONInPlace(
         pointer(Where.Value),JSON_OPTIONS[true]);
       result := true;
       exit;
    end;

     opEqualTo,
     opNotEqualTo,
     opLessThan,
     opLessThanOrEqualTo,
     opGreaterThan,
     opGreaterThanOrEqualTo,
     opIn,
     opIsNull,
     opIsNotNull,
     opLike);

  TSynTableFieldProperties = class;

  /// one recognized WHERE expression for TSynTableStatement
  TSynTableStatementWhere = record
    /// expressions are evaluated as AND unless this field is set to TRUE
................................................................................
       else
         Where.Operator := opLessThan;
       end;
  'i','I':
    case P[1] of
    's','S': begin
      P := GotoNextNotSpace(P+2);



      if IdemPChar(P,'NULL') then begin
        Where.Value := 'null';
        Where.Operator := opIsNull;
        Where.ValueSQL := P;
        Where.ValueSQLLen := 4;
        TVarData(Where.ValueVariant).VType := varNull;
        inc(P,4);
        result := true;
      end else
      if IdemPChar(P,'NOT NULL') then begin
        Where.Value := 'not null';
        Where.Operator := opIsNotNull;
        Where.ValueSQL := P;
        Where.ValueSQLLen := 8;
        TVarData(Where.ValueVariant).VType := varNull;
        inc(P,8);
        result := true; // leave ValueVariant=unassigned
      end;
      exit;
    end;
    {$ifndef NOVARIANTS}
    'n','N': begin
       Where.Operator := opIn;
       P := GotoNextNotSpace(P+2);
................................................................................
       inc(P);
       while P^<>')' do
         if P^=#0 then
           exit else
           inc(P);
       inc(P);
       SetString(Where.Value,PAnsiChar(B),P-B);
       Where.ValueSQL := B;
       Where.ValueSQLLen := P-B;
       Where.Value[1] := '[';
       Where.Value[P-B] := ']';
       TDocVariantData(Where.ValueVariant).InitJSONInPlace(
         pointer(Where.Value),JSON_OPTIONS[true]);
       result := true;
       exit;
    end;

    /// write a string (UTF-8) value from a memory buffer
    procedure BSONWriteString(const name: RawUTF8; value: PUTF8Char; valueLen: integer); 
    /// write a binary (BLOB) value
    procedure BSONWrite(const name: RawUTF8; Data: pointer; DataLen: integer); overload;
    /// write an ObjectID value
    procedure BSONWrite(const name: RawUTF8; const value: TBSONObjectID); overload;
    /// write a RegEx value
    procedure BSONWriteRegEx(const name: RawUTF8; const RegEx: RawByteString);
    /// write a data/time value
    procedure BSONWriteDateTime(const name: RawUTF8; const value: TDateTime);
    /// write an element with no value
    // - elemType can be either betNull, betMinKey or betMaxKey
    procedure BSONWrite(const name: RawUTF8; elemtype: TBSONElementType); overload;
    /// write an element with no value
    procedure BSONWrite(const name: RawUTF8; const elem: TBSONElement); overload;
................................................................................
    /// write an object specified as name/value pairs as a BSON document
    // - data must be supplied two by two, as Name,Value pairs, e.g.
    // ! aBSONWriter.BSONWriteObject(['name','John','year',1972]);
    // - this method wil be faster than using a BSONWriteDoc(_ObjFast(...))
    procedure BSONWriteObject(const NameValuePairs: array of const);
    /// write a projection specified as fieldname:1 pairs as a BSON document
    procedure BSONWriteProjection(const FieldNamesCSV: RawUTF8);






    /// write an array specified as a list of items as a BSON document
    // - data must be supplied as a list of values e.g.
    // ! aBSONWriter.BSONWriteArray(['John',1972]);
    // - this method wil be faster than using a BSONWriteDoc(_ArrFast(...))
    procedure BSONWriteArray(const Items: array of const);
    /// write an array of integers as a BSON Document
    procedure BSONWriteArrayOfInteger(const Integers: array of integer);
................................................................................

procedure TBSONWriter.BSONWrite(const name: RawUTF8; const value: TBSONObjectID);
begin
  BSONWrite(name,betObjectID);
  Write(@value,sizeof(value));
end;

procedure TBSONWriter.BSONWriteRegEx(const name: RawUTF8; const RegEx: RawByteString);
begin
  BSONWrite(name,betRegEx);
  Write(pointer(RegEx),length(RegEx));



end;

procedure TBSONWriter.BSONWrite(const name: RawUTF8; const value: RawUTF8;
  isJavaScript: boolean=false);
const TYP: array[boolean] of TBSONElementType = (betString,betJS);
var L: integer;
begin
................................................................................
begin
  CSVToRawUTF8DynArray(pointer(FieldNamesCSV),FieldNames);
  Start := BSONDocumentBegin;
  for i := 0 to high(FieldNames) do
    BSONWrite(FieldNames[i],1);
  BSONDocumentEnd(Start);
end;






















































procedure TBSONWriter.BSONWriteObject(const NameValuePairs: array of const);
var Start: cardinal;
    Name: RawUTF8;
    i: integer;
begin
  Start := BSONDocumentBegin;

    /// write a string (UTF-8) value from a memory buffer
    procedure BSONWriteString(const name: RawUTF8; value: PUTF8Char; valueLen: integer); 
    /// write a binary (BLOB) value
    procedure BSONWrite(const name: RawUTF8; Data: pointer; DataLen: integer); overload;
    /// write an ObjectID value
    procedure BSONWrite(const name: RawUTF8; const value: TBSONObjectID); overload;
    /// write a RegEx value
    procedure BSONWriteRegEx(const name: RawUTF8; const RegEx,Options: RawByteString);
    /// write a data/time value
    procedure BSONWriteDateTime(const name: RawUTF8; const value: TDateTime);
    /// write an element with no value
    // - elemType can be either betNull, betMinKey or betMaxKey
    procedure BSONWrite(const name: RawUTF8; elemtype: TBSONElementType); overload;
    /// write an element with no value
    procedure BSONWrite(const name: RawUTF8; const elem: TBSONElement); overload;
................................................................................
    /// write an object specified as name/value pairs as a BSON document
    // - data must be supplied two by two, as Name,Value pairs, e.g.
    // ! aBSONWriter.BSONWriteObject(['name','John','year',1972]);
    // - this method wil be faster than using a BSONWriteDoc(_ObjFast(...))
    procedure BSONWriteObject(const NameValuePairs: array of const);
    /// write a projection specified as fieldname:1 pairs as a BSON document
    procedure BSONWriteProjection(const FieldNamesCSV: RawUTF8);
    /// write an object as query parameter
    // - will handle all SQL operators, including IN (), IS NULL or LIKE 
    // - see @http://docs.mongodb.org/manual/reference/operator/query
    // - returns TRUE on success, FALSE if the operator is not implemented yet
    function BSONWriteQueryOperator(const name: RawUTF8; operator: TSynTableStatementOperator;
      const Value: variant): boolean;
    /// write an array specified as a list of items as a BSON document
    // - data must be supplied as a list of values e.g.
    // ! aBSONWriter.BSONWriteArray(['John',1972]);
    // - this method wil be faster than using a BSONWriteDoc(_ArrFast(...))
    procedure BSONWriteArray(const Items: array of const);
    /// write an array of integers as a BSON Document
    procedure BSONWriteArrayOfInteger(const Integers: array of integer);
................................................................................

procedure TBSONWriter.BSONWrite(const name: RawUTF8; const value: TBSONObjectID);
begin
  BSONWrite(name,betObjectID);
  Write(@value,sizeof(value));
end;

procedure TBSONWriter.BSONWriteRegEx(const name: RawUTF8; const RegEx,Options: RawByteString);
begin
  BSONWrite(name,betRegEx); // cstring cstring
  Write(pointer(RegEx),length(RegEx));
  Write1(0);
  Write(pointer(Options),length(Options));
  Write1(0);
end;

procedure TBSONWriter.BSONWrite(const name: RawUTF8; const value: RawUTF8;
  isJavaScript: boolean=false);
const TYP: array[boolean] of TBSONElementType = (betString,betJS);
var L: integer;
begin
................................................................................
begin
  CSVToRawUTF8DynArray(pointer(FieldNamesCSV),FieldNames);
  Start := BSONDocumentBegin;
  for i := 0 to high(FieldNames) do
    BSONWrite(FieldNames[i],1);
  BSONDocumentEnd(Start);
end;

function TBSONWriter.BSONWriteQueryOperator(const name: RawUTF8;
  operator: TSynTableStatementOperator; const Value: variant): boolean;
const
  QUERY_OPS: array[opNotEqualTo..opIn] of RawUTF8 = (
    '$ne','$lt','$lte','$gt','$gte','$in');
var start: cardinal;
    wasString: boolean;
    like: RawUTF8;
    len: integer;
begin
  result := false; // error on premature exit
  case Operator of
  // http://docs.mongodb.org/manual/faq/developers/#faq-developers-query-for-nulls
  // {$type:10} would return only existing fields, but our ODM do not insert
  // blobs by default -> do not use {$type:10} trick but plain {field:null}
  opIsNull:
    operator := opEqualTo;     // here Value=null
  opIsNotNull:
    operator := opNotEqualTo;  // here Value=null
  end;
  case Operator of
  opEqualTo:
    BSONWriteVariant(name,Value);
  opNotEqualTo..opIn: begin
    BSONWrite(name,betDoc);
    start := BSONDocumentBegin;
    BSONWriteVariant(QUERY_OPS[operator],Value);
    BSONDocumentEnd(start);
  end;
  opLike: begin
    VariantToUTF8(Value,like,wasString);
    len := length(like);
    if (len=0) or not wasString then
      exit;
    if like[1]='%' then
      if len=1 then // LIKE '%' is invalid 
        exit else
        if like[len]='%' then
          if len=2 then
            exit else // LIKE '%%' is invalid
            like := copy(like,2,len-2) else    // LIKE '%a%' -> /a/
          like := copy(like,2,len-1)+'$' else  // LIKE '%a'  -> /a$/
      if like[len]='%' then
        like := '^'+copy(like,1,len-1) else    // LIKE 'a%'  -> /^a/
        like := '^'+like+'$';                  // LIKE 'a'   -> /^a$/
    BSONWriteRegEx(name,like,'i'); // /like/i for case-insensitivity
  end;
  else
    exit; // unhandled operator
  end;
  result := true;
end;

procedure TBSONWriter.BSONWriteObject(const NameValuePairs: array of const);
var Start: cardinal;
    Name: RawUTF8;
    i: integer;
begin
  Start := BSONDocumentBegin;

  Check(Stmt.TableName='tab');
  Check(length(Stmt.Where)=2);
  Check(Stmt.Where[0].Field=0);
  Check(Stmt.Where[0].Operator=opNotEqualTo);
  Check(Stmt.Where[0].ValueInteger=100);
  Check(Stmt.Where[1].JoinedOR);
  Check(Props.Fields.List[Stmt.Where[1].Field-1].Name='Data');
  Check(Stmt.Where[1].Operator=opIs);
  Check(Stmt.Where[1].Value='not null');
  Check(Stmt.Limit=20);
  Check(Stmt.Offset=10);
  Check((length(Stmt.SelectFields)=2)and(Stmt.SelectFields[1]=0)and
    (Props.Fields.List[Stmt.SelectFields[0]-1].Name='Data'));
  Check(Stmt.OrderByField=nil);
  New('select data,iD from tab where firstname like "monet" or data is null limit 20 offset 10');
  Check(Stmt.TableName='tab');
  Check(length(Stmt.Where)=2);
  Check(Props.Fields.List[Stmt.Where[0].Field-1].Name='FirstName');
  Check(Stmt.Where[0].Operator=opLike);
  Check(Stmt.Where[0].Value='monet');
  Check(Stmt.Where[1].JoinedOR);
  Check(Props.Fields.List[Stmt.Where[1].Field-1].Name='Data');
  Check(Stmt.Where[1].Operator=opIs);
  Check(Stmt.Where[1].Value='null');
  Check(Stmt.Limit=20);
  Check(Stmt.Offset=10);
  Check((length(Stmt.SelectFields)=2)and(Stmt.SelectFields[1]=0)and
    (Props.Fields.List[Stmt.SelectFields[0]-1].Name='Data'));
  Check(Stmt.OrderByField=nil);
  Stmt.Free;
end;

  Check(Stmt.TableName='tab');
  Check(length(Stmt.Where)=2);
  Check(Stmt.Where[0].Field=0);
  Check(Stmt.Where[0].Operator=opNotEqualTo);
  Check(Stmt.Where[0].ValueInteger=100);
  Check(Stmt.Where[1].JoinedOR);
  Check(Props.Fields.List[Stmt.Where[1].Field-1].Name='Data');
  Check(Stmt.Where[1].Operator=opIsNotNull);

  Check(Stmt.Limit=20);
  Check(Stmt.Offset=10);
  Check((length(Stmt.SelectFields)=2)and(Stmt.SelectFields[1]=0)and
    (Props.Fields.List[Stmt.SelectFields[0]-1].Name='Data'));
  Check(Stmt.OrderByField=nil);
  New('select data,iD from tab where firstname like "monet" or data is null limit 20 offset 10');
  Check(Stmt.TableName='tab');
  Check(length(Stmt.Where)=2);
  Check(Props.Fields.List[Stmt.Where[0].Field-1].Name='FirstName');
  Check(Stmt.Where[0].Operator=opLike);
  Check(Stmt.Where[0].Value='monet');
  Check(Stmt.Where[1].JoinedOR);
  Check(Props.Fields.List[Stmt.Where[1].Field-1].Name='Data');
  Check(Stmt.Where[1].Operator=opIsNull);

  Check(Stmt.Limit=20);
  Check(Stmt.Offset=10);
  Check((length(Stmt.SelectFields)=2)and(Stmt.SelectFields[1]=0)and
    (Props.Fields.List[Stmt.SelectFields[0]-1].Name='Data'));
  Check(Stmt.OrderByField=nil);
  Stmt.Free;
end;

'1.18.588'

'1.18.589'