fProps: TSQLModelRecordProperties;
    /// storage of main read-only properties
    fConnectionProperties: TObject;
    fTableName: RawUTF8;
    fRowIDFieldName: RawUTF8;
    fFieldNames: TRawUTF8DynArray;
    fSQL: TSQLModelRecordPropertiesSQL;

    /// fill fRowIDFieldName/fSQL with the current information
    procedure ComputeSQL;
  public
    /// add a custom field mapping
    // - will re-compute all needed SQL statements as needed, and initialize
    // fSortedFieldsName[] and fSortedFieldsIndex[] internal sorted arrays
    // - can be used e.g. as
................................................................................
    // database engines (e.g. Oracle)
    // - can be customized e.g. via
    // ! aModel.Props[TSQLMyExternal].ExternalDB.MapField('ID','ExternalID');
    property RowIDFieldName: RawUTF8 read fRowIDFieldName;
    /// the external field names, following fProps.Props.Field[] order
    // - excluding ID/RowID field, which is stored in RowIDFieldName
    property FieldNames: TRawUTF8DynArray read fFieldNames;




  end;

  /// ORM properties associated to a TSQLRecord within a given model
  // - "stable" / common properties derivated from RTTI are shared in the
  // TSQLRecordProperties instance
  // - since the same TSQLRecord can be defined in several models, with diverse
  // implementation patterns (e.g. internal in one, external in another),
................................................................................

  /// describe a service provider method arguments
  TServiceMethodArgumentDynArray = array of TServiceMethodArgument;

  /// possible service provider method options, e.g. about logging or execution
  // - see TServiceMethodOptions for a description of each available option
  TServiceMethodOption = (
    optExecLockedPerInterface

    {$ifndef LVCL},
    optExecInMainThread, optFreeInMainThread,
    optExecInPerInterfaceThread, optFreeInPerInterfaceThread,
    optVariantCopiedByReference
    {$endif}
  );

  /// set of per-method execution options for an interface-based service provider
  // - by default, mehthod executions are concurrent, for better server
  // responsiveness; if you set optExecLockedPerInterface, all methods of
................................................................................
    /// this virtual constructor will be called at instance creation
    constructor Create; virtual;
  end;

  TInterfacedObjectWithCustomCreateClass = class of TInterfacedObjectWithCustomCreate;
  TPersistentWithCustomCreateClass = class of TPersistentWithCustomCreate;

{$ifndef LVCL}
  /// a procedure-based background thread associated to a TSQLRestServer instance
  // - in addition to standard TSynBackgroundThreadProcedure behavior, this
  // inherited class will also notify the Server of this thread, calling
  // BeginCurrentThread and EndCurrentThread methods as required
  // - used e.g. when opt*InPerInterfaceThread is defined in
  // TServiceFactoryServer options
  TSynBackgroundThreadSQLRestServerProcedure = class(TSynBackgroundThreadProcedure)
................................................................................
    fServer: TSQLRestServer;
    // will call BeginCurrentThread / EndCurrentThread
    procedure Execute; override;
  public
    /// create the thread, ready to execute background process
    constructor Create(aServer: TSQLRestServer);
  end;
{$endif}

  /// a service provider implemented on the server side
  // - each registered interface has its own TServiceFactoryServer instance,
  // available as one TSQLServiceContainerServer item from TSQLRest.Services property
  // - will handle the implementation class instances of a given interface
  // - by default, all methods are allowed to execution: you can call AllowAll,
  // DenyAll, Allow or Deny in order to specify your exact security policy
................................................................................
    fInstanceLock: TRTLCriticalSection;
    fImplementationClass: TInterfacedClass;
    fImplementationClassWithCustomCreate: Boolean;
    fImplementationClassInterfaceEntry: PInterfaceEntry;
    fSharedInterface: IInterface;
    fByPassAuthentication: boolean;
    fResultAsJSONObject: boolean;

    /// union of all fExecution[].Options
    {$ifndef LVCL}
    fAnyOptions: TServiceMethodOptions;
    fBackgroundThread: TSynBackgroundThreadProcedure;
    {$endif}
    procedure SetTimeoutSecInt(value: cardinal);
    function GetTimeoutSec: cardinal;
    /// get an implementation Inst.Instance for the given Inst.InstanceID
    // - is called by ExecuteMethod() in sicClientDrive mode
    // - returns true for successfull {"method":"_free_".. call (aMethodIndex=-1)
    // - otherwise, fill Inst.Instance with the matching implementation (or nil)
    function InternalInstanceRetrieve(var Inst: TServiceFactoryServerInstance;
................................................................................
    TContent = (TableSimpleFields, UpdateSimple, UpdateSetAll, InsertAll);
  procedure SetSQL(W: TTextWriter;
    withID, withTableName: boolean; var result: RawUTF8;
    content: TContent=TableSimpleFields);
  var f: integer;
  begin
    W.CancelAll;
    if withID and (content=TableSimpleFields) then
      if withTableName then
        W.Add('%.%,',[TableName,RowIDFieldName]) else
        W.AddStrings([RowIDFieldName,',']);




    with fProps.Props do
    for f := 0 to Fields.Count-1 do
    with Fields.List[f] do
    if SQLFieldType in COPIABLE_FIELDS then // sftMany fields do not exist
      case content of
      TableSimpleFields:
        if f in SimpleFieldsBits[soSelect] then begin
          if withTableName then
            W.AddStrings([TableName,'.']);
          W.AddString(fFieldNames[f]);




          W.Add(',');
        end;
      UpdateSimple:
        if f in SimpleFieldsBits[soSelect] then
          W.AddStrings([fFieldNames[f],'=?,']);
      UpdateSetAll:
        W.AddStrings([fFieldNames[f],'=?,']);
      InsertAll:
        W.AddStrings([fFieldNames[f],',']);
      end;
    W.CancelLastComma;
    W.SetText(result);
  end;
var W: TTextWriter;
begin
  W := TTextWriter.CreateOwnedStream(1024);
................................................................................
  if ExternalTableName='' then
    fTableName := Props.Props.SQLTableName else
    fTableName := ExternalTableName;
  fConnectionProperties := ExternalDataBase;
  fProps := Props;
  fRowIDFieldName := 'ID';
  Props.Props.Fields.NamesToRawUTF8DynArray(fFieldNames);

  ComputeSQL;
end;

function TSQLModelRecordPropertiesExternal.MapField(
  const InternalName, ExternalName: RawUTF8): PSQLModelRecordPropertiesExternal;
var int: integer;
begin
  int := fProps.Props.Fields.IndexByNameOrExcept(InternalName);
  if int<0 then
    fRowIDFieldName := ExternalName else
    fFieldNames[int] := ExternalName;
  ComputeSQL;

  result := @self;
end;

procedure TSQLModelRecordPropertiesExternal.MapFields(
  const InternalExternalPairs: array of RawUTF8);
var i,int: Integer;
begin
  for i := 0 to (length(InternalExternalPairs) shr 1)-1 do begin
    int := fProps.Props.Fields.IndexByNameOrExcept(InternalExternalPairs[i*2]);
    if int<0 then
      fRowIDFieldName := InternalExternalPairs[i*2+1] else




      fFieldNames[int] := InternalExternalPairs[i*2+1];




  end;
  ComputeSQL;
end;

function TSQLModelRecordPropertiesExternal.InternalToExternal(const FieldName: RawUTF8): RawUTF8;
var int: integer;
begin
................................................................................
function TSQLModelRecordPropertiesExternal.AppendFieldName(
  FieldIndex: Integer; var Text: RawUTF8): boolean;
begin
  result := false; // success
  if FieldIndex=VIRTUAL_TABLE_ROWID_COLUMN then
    Text := Text+RowIDFieldName else
  if cardinal(FieldIndex)>=cardinal(Length(FieldNames)) then
    result := true else
    Text := Text+FieldNames[FieldIndex];
end;


{ TSQLModelRecordProperties }

constructor TSQLModelRecordProperties.Create(aModel: TSQLModel;
................................................................................
    Session := CONST_AUTHENTICATION_NOT_USED;
  result := true;
end;

{$ifndef LVCL}
type
  TThreadHook = class(TThread);



  TCallMethodSynchro = record
    Action: (syncCallMethod, syncInstanceRelease);
    CallMethodArgs: pointer;
    InstanceToRelease: TInterfacedObjectWithCustomCreate;
  end;
{$endif}

procedure TSQLRestServerURIContext.Execute(Command: TSQLRestServerURIContextCommand);
procedure TimeOut;
begin
  {$ifdef WITHLOG}
  Log.Log(sllServer,'TimeOut %.Execute(%) after % ms',[ClassName,
    GetEnumName(TypeInfo(TSQLRestServerURIContextCommand),ord(Command))^,
................................................................................
  if self=nil then
    result := '' else
    result := Contract; // just return the current value
end;

procedure TServiceFactoryServerInstance.SafeFreeInstance(Factory: TServiceFactoryServer);
var Obj: TInterfacedObject;
{$ifndef LVCL}
    Synch: TCallMethodSynchro;
{$endif}
begin
  InstanceID := 0;
  Obj := Instance;
  Instance := nil;
  {$ifndef LVCL}
  if (optFreeInMainThread in Factory.fAnyOptions) and
     (GetCurrentThreadID<>MainThreadID) then
    {$ifdef DELPHI6OROLDER}TThreadHook(nil).Synchronize(
    {$else}                TThread.Synchronize(nil,
    {$endif}  TInterfacedObjectWithCustomCreate(Obj).InternalRelease) else

  if (optFreeInPerInterfaceThread in Factory.fAnyOptions) and
     Assigned(Factory.fBackgroundThread) then begin
    Synch.Action := syncInstanceRelease;
    Synch.InstanceToRelease := TInterfacedObjectWithCustomCreate(Obj);
    Factory.fBackgroundThread.RunAndWait(@Synch);
  end else
  {$endif}
    IInterface(Obj)._Release;
end;

function TServiceFactoryServer.InternalInstanceRetrieve(
  var Inst: TServiceFactoryServerInstance; aMethodIndex: integer): boolean;
procedure AddNew;
var i: integer;
................................................................................
  end;
  Ctxt.ServiceInstanceID := Inst.InstanceID;
  // 2. call method implementation
  try
    entry := Inst.Instance.GetInterfaceEntry(fInterface.fInterfaceIID);
    if entry=nil then
      exit;
    {$ifndef LVCL}
    if optExecInPerInterfaceThread in fExecution[Ctxt.ServiceMethodIndex].Options then
      if fBackgroundThread=nil then
        fBackgroundThread := TSynBackgroundThreadSQLRestServerProcedure.Create(RestServer);
    {$endif}
    ThreadServer := @ServiceContext;
    WR := TJSONSerializer.CreateOwnedStream;
    try
      with ThreadServer^ do begin
        Factory := self;
        Request := Ctxt;
      end; // RunningThread is already set at thread initialization
................................................................................
var m,i: integer;
begin
  if self<>nil then begin
    if (fInstanceCreation=sicPerThread) and (optExecLockedPerInterface in aOptions) then
      raise EServiceException.CreateFmt(
        'optExecLockedPerInterface option not compatible with sicPerThread for I%s interface',
        [fInterfaceURI]);
    {$ifndef LVCL}
    if (fInstanceCreation=sicPerThread) and
       ([optExecInMainThread,optFreeInMainThread,
         optExecInPerInterfaceThread,optFreeInPerInterfaceThread]*aOptions<>[]) then
      raise EServiceException.CreateFmt(
        'opt*In*Thread option not compatible with sicPerThread for I%s interface',
        [fInterfaceURI]);

    if (optExecLockedPerInterface in aOptions) and
       ([optExecInMainThread,optFreeInMainThread,
         optExecInPerInterfaceThread,optFreeInPerInterfaceThread]*aOptions<>[]) then
      raise EServiceException.CreateFmt(
        'optExecLockedPerInterface with opt*In*Thread options for I%s interface',
        [fInterfaceURI]);
    {$endif}
    if high(aMethod)<0 then
      for i := 0 to fInterface.fMethodsCount-1 do
        fExecution[i].Options := aOptions else
    for m := 0 to high(aMethod) do
      fExecution[fInterface.CheckMethodIndex(aMethod[m])].Options := aOptions;
    {$ifndef LVCL}
    fAnyOptions := [];
    for i := 0 to fInterface.fMethodsCount-1 do
      fAnyOptions := fAnyOptions+fExecution[i].Options;
    if (optFreeInPerInterfaceThread in fAnyOptions) and
       not (optExecInPerInterfaceThread in fAnyOptions) then
      raise EServiceException.CreateFmt(
        'optFreeInPerInterfaceThread without optExecInPerInterfaceThread for I%s interface',
        [fInterfaceURI]);

    if ([optExecInMainThread,optFreeInMainThread]*fAnyOptions<>[]) and
       ([optExecInPerInterfaceThread,optFreeInPerInterfaceThread]*fAnyOptions<>[]) then
      raise EServiceException.CreateFmt(
        'Concurrent opt*InMainThread and opt*InPerInterfaceThread for I%s interface',
        [fInterfaceURI]);
    {$endif}
  end;
................................................................................
    mov [esi].TCallMethodArgs.res64.Hi,edx
@e: mov esp,ebp
    pop ebp
    pop esi
end;
{$endif CPU64}

{$ifndef LVCL}

procedure BackgroundExecuteProc(Call: Pointer);
var Synch: ^TCallMethodSynchro absolute Call;
    ThreadServer: PServiceRunningContext;
    backup: TServiceRunningContext;
begin
  case Synch.Action of
  syncCallMethod: begin
................................................................................
procedure CallMethodSynch(Args: pointer);
var Synch: TCallMethodSynchro;
begin
  Synch.Action := syncCallMethod;
  Synch.CallMethodArgs := Args;
  BackgroundExecuteProc(@Synch);
end;



type
  TCollectionClass = class of TInterfacedCollection;

{$endif LVCL}

function TServiceMethod.ArgResultIndex(ArgName: PUTF8Char; ArgNameLen: integer): integer;
................................................................................
    WideStrings: TWideStringDynArray;
    Records: array of TBytes;
    Value: pointer;
    i,a: integer;
    wasString, valid: boolean;
    Val: PUTF8Char;
    r: TCallMethodArgs;
{$ifndef LVCL}
    SyncMethod: TMethod;
    Synch: TCallMethodSynchro;
{$endif}

    Stack: array[0..MAX_EXECSTACK-1] of byte;
    Int64s: array[0..MAX_METHOD_ARGS-1] of Int64;
    Objects: array[0..MAX_METHOD_ARGS-1] of TObject;
    DynArrays: array[0..MAX_METHOD_ARGS-1] of TDynArrayFake;
    Values: array[0..MAX_METHOD_ARGS-1] of PPointer;
begin
  result := false;
................................................................................
        r.callContext := @ServiceContext; // to be copied into main threadvar
        SyncMethod.Code := @CallMethodSynch;
        SyncMethod.Data := @r; // fake call: PCallMethodArgs(self)^=Params
        {$ifdef DELPHI6OROLDER}TThreadHook(r.callContext^.RunningThread).Synchronize(
        {$else}                TThread.Synchronize(r.callContext^.RunningThread,
        {$endif}  TThreadMethod(SyncMethod));
      end else

      if optExecInPerInterfaceThread in Options then
        if not Assigned(BackgroundExecutionThread) then
          raise EInterfaceFactoryException.Create(
            'optExecInPerInterfaceThread with BackgroundExecutionThread=nil') else begin
        r.callContext := @ServiceContext; // to be copied into background threadvar
        Synch.Action := syncCallMethod;
        Synch.CallMethodArgs := @r;
        BackgroundExecutionThread.RunAndWait(@Synch);
      end else
      {$endif}
        CallMethod(r);
    end;
    // 4. send back any result
    if Res<>nil then begin
      // 4.1 handle custom content (not JSON object answer)
      if (r.resKind=smvRecord) and ArgsResultIsServiceCustomAnswer then
        with PServiceCustomAnswer(Values[ArgsResultIndex])^ do
................................................................................
        end;
        {$endif}
        end;
    end;
  end;
end;

{$ifndef LVCL}

{ TSynBackgroundThreadSQLRestServerProcedure }

constructor TSynBackgroundThreadSQLRestServerProcedure.Create(aServer: TSQLRestServer);
begin
  inherited Create(BackgroundExecuteProc,nil);
  fServer := aServer;
................................................................................
  try
    inherited Execute;
  finally
    fServer.EndCurrentThread(self);
  end;
end;

{$endif LVCL}


{ TServiceContainerClient }

function TServiceContainerClient.Info(aTypeInfo: PTypeInfo): TServiceFactory;
begin
  result := inherited Info(aTypeInfo);
  if result=nil then

    fProps: TSQLModelRecordProperties;
    /// storage of main read-only properties
    fConnectionProperties: TObject;
    fTableName: RawUTF8;
    fRowIDFieldName: RawUTF8;
    fFieldNames: TRawUTF8DynArray;
    fSQL: TSQLModelRecordPropertiesSQL;
    fFieldNamesMatchInternal: TSQLFieldBits;
    /// fill fRowIDFieldName/fSQL with the current information
    procedure ComputeSQL;
  public
    /// add a custom field mapping
    // - will re-compute all needed SQL statements as needed, and initialize
    // fSortedFieldsName[] and fSortedFieldsIndex[] internal sorted arrays
    // - can be used e.g. as
................................................................................
    // database engines (e.g. Oracle)
    // - can be customized e.g. via
    // ! aModel.Props[TSQLMyExternal].ExternalDB.MapField('ID','ExternalID');
    property RowIDFieldName: RawUTF8 read fRowIDFieldName;
    /// the external field names, following fProps.Props.Field[] order
    // - excluding ID/RowID field, which is stored in RowIDFieldName
    property FieldNames: TRawUTF8DynArray read fFieldNames;
    /// each bit set, following fProps.Props.Field[]+1 order (i.e. 0=ID,
    // 1=Field[0], ...), indicates that this external field name
    // has not been mapped
    property FieldNamesMatchInternal: TSQLFieldBits read fFieldNamesMatchInternal;
  end;

  /// ORM properties associated to a TSQLRecord within a given model
  // - "stable" / common properties derivated from RTTI are shared in the
  // TSQLRecordProperties instance
  // - since the same TSQLRecord can be defined in several models, with diverse
  // implementation patterns (e.g. internal in one, external in another),
................................................................................

  /// describe a service provider method arguments
  TServiceMethodArgumentDynArray = array of TServiceMethodArgument;

  /// possible service provider method options, e.g. about logging or execution
  // - see TServiceMethodOptions for a description of each available option
  TServiceMethodOption = (
    optExecLockedPerInterface,
    optExecInPerInterfaceThread, optFreeInPerInterfaceThread
    {$ifndef LVCL},
    optExecInMainThread, optFreeInMainThread,

    optVariantCopiedByReference
    {$endif}
  );

  /// set of per-method execution options for an interface-based service provider
  // - by default, mehthod executions are concurrent, for better server
  // responsiveness; if you set optExecLockedPerInterface, all methods of
................................................................................
    /// this virtual constructor will be called at instance creation
    constructor Create; virtual;
  end;

  TInterfacedObjectWithCustomCreateClass = class of TInterfacedObjectWithCustomCreate;
  TPersistentWithCustomCreateClass = class of TPersistentWithCustomCreate;


  /// a procedure-based background thread associated to a TSQLRestServer instance
  // - in addition to standard TSynBackgroundThreadProcedure behavior, this
  // inherited class will also notify the Server of this thread, calling
  // BeginCurrentThread and EndCurrentThread methods as required
  // - used e.g. when opt*InPerInterfaceThread is defined in
  // TServiceFactoryServer options
  TSynBackgroundThreadSQLRestServerProcedure = class(TSynBackgroundThreadProcedure)
................................................................................
    fServer: TSQLRestServer;
    // will call BeginCurrentThread / EndCurrentThread
    procedure Execute; override;
  public
    /// create the thread, ready to execute background process
    constructor Create(aServer: TSQLRestServer);
  end;


  /// a service provider implemented on the server side
  // - each registered interface has its own TServiceFactoryServer instance,
  // available as one TSQLServiceContainerServer item from TSQLRest.Services property
  // - will handle the implementation class instances of a given interface
  // - by default, all methods are allowed to execution: you can call AllowAll,
  // DenyAll, Allow or Deny in order to specify your exact security policy
................................................................................
    fInstanceLock: TRTLCriticalSection;
    fImplementationClass: TInterfacedClass;
    fImplementationClassWithCustomCreate: Boolean;
    fImplementationClassInterfaceEntry: PInterfaceEntry;
    fSharedInterface: IInterface;
    fByPassAuthentication: boolean;
    fResultAsJSONObject: boolean;
    fBackgroundThread: TSynBackgroundThreadProcedure;
    /// union of all fExecution[].Options

    fAnyOptions: TServiceMethodOptions;


    procedure SetTimeoutSecInt(value: cardinal);
    function GetTimeoutSec: cardinal;
    /// get an implementation Inst.Instance for the given Inst.InstanceID
    // - is called by ExecuteMethod() in sicClientDrive mode
    // - returns true for successfull {"method":"_free_".. call (aMethodIndex=-1)
    // - otherwise, fill Inst.Instance with the matching implementation (or nil)
    function InternalInstanceRetrieve(var Inst: TServiceFactoryServerInstance;
................................................................................
    TContent = (TableSimpleFields, UpdateSimple, UpdateSetAll, InsertAll);
  procedure SetSQL(W: TTextWriter;
    withID, withTableName: boolean; var result: RawUTF8;
    content: TContent=TableSimpleFields);
  var f: integer;
  begin
    W.CancelAll;
    if withID and (content=TableSimpleFields) then begin
      if withTableName then
        W.Add('%.%',[TableName,RowIDFieldName]) else
        W.AddString(RowIDFieldName);
      if 0 in FieldNamesMatchInternal then
        W.Add(',') else
        W.AddShort(' as ID,');
    end;
    with fProps.Props do
    for f := 0 to Fields.Count-1 do
    with Fields.List[f] do
    if SQLFieldType in COPIABLE_FIELDS then // sftMany fields do not exist
      case content of
      TableSimpleFields:
        if f in SimpleFieldsBits[soSelect] then begin
          if withTableName then
            W.AddStrings([TableName,'.']);
          W.AddString(FieldNames[f]);
          if not(f+1 in FieldNamesMatchInternal) then begin
            W.AddShort(' as ');
            W.AddString(Name); // AS [InternalName] to get expected JSON column
          end;
          W.Add(',');
        end;
      UpdateSimple:
        if f in SimpleFieldsBits[soSelect] then
          W.AddStrings([FieldNames[f],'=?,']);
      UpdateSetAll:
        W.AddStrings([FieldNames[f],'=?,']);
      InsertAll:
        W.AddStrings([FieldNames[f],',']);
      end;
    W.CancelLastComma;
    W.SetText(result);
  end;
var W: TTextWriter;
begin
  W := TTextWriter.CreateOwnedStream(1024);
................................................................................
  if ExternalTableName='' then
    fTableName := Props.Props.SQLTableName else
    fTableName := ExternalTableName;
  fConnectionProperties := ExternalDataBase;
  fProps := Props;
  fRowIDFieldName := 'ID';
  Props.Props.Fields.NamesToRawUTF8DynArray(fFieldNames);
  FillChar(fFieldNamesMatchInternal,sizeof(fFieldNamesMatchInternal),255);
  ComputeSQL;
end;

function TSQLModelRecordPropertiesExternal.MapField(
  const InternalName, ExternalName: RawUTF8): PSQLModelRecordPropertiesExternal;

begin





  MapFields([InternalName,ExternalName]);
  result := @self;
end;

procedure TSQLModelRecordPropertiesExternal.MapFields(
  const InternalExternalPairs: array of RawUTF8);
var i,int: Integer;
begin
  for i := 0 to (length(InternalExternalPairs) shr 1)-1 do begin
    int := fProps.Props.Fields.IndexByNameOrExcept(InternalExternalPairs[i*2]);
    if int<0 then begin
      fRowIDFieldName := InternalExternalPairs[i*2+1];
      if IdemPropNameU(fRowIDFieldName,'ID') then
        include(fFieldNamesMatchInternal,0) else     // [0]=ID
        exclude(fFieldNamesMatchInternal,0);
    end else begin
      fFieldNames[int] := InternalExternalPairs[i*2+1];
      if IdemPropNameU(fFieldNames[int],fProps.Props.Fields.List[int].Name) then
        include(fFieldNamesMatchInternal,int+1) else // [0]=ID
        exclude(fFieldNamesMatchInternal,int+1);
    end;
  end;
  ComputeSQL;
end;

function TSQLModelRecordPropertiesExternal.InternalToExternal(const FieldName: RawUTF8): RawUTF8;
var int: integer;
begin
................................................................................
function TSQLModelRecordPropertiesExternal.AppendFieldName(
  FieldIndex: Integer; var Text: RawUTF8): boolean;
begin
  result := false; // success
  if FieldIndex=VIRTUAL_TABLE_ROWID_COLUMN then
    Text := Text+RowIDFieldName else
  if cardinal(FieldIndex)>=cardinal(Length(FieldNames)) then
    result := true else // FieldIndex out of range
    Text := Text+FieldNames[FieldIndex];
end;


{ TSQLModelRecordProperties }

constructor TSQLModelRecordProperties.Create(aModel: TSQLModel;
................................................................................
    Session := CONST_AUTHENTICATION_NOT_USED;
  result := true;
end;

{$ifndef LVCL}
type
  TThreadHook = class(TThread);
{$endif}

type
  TCallMethodSynchro = record
    Action: (syncCallMethod, syncInstanceRelease);
    CallMethodArgs: pointer;
    InstanceToRelease: TInterfacedObjectWithCustomCreate;
  end;


procedure TSQLRestServerURIContext.Execute(Command: TSQLRestServerURIContextCommand);
procedure TimeOut;
begin
  {$ifdef WITHLOG}
  Log.Log(sllServer,'TimeOut %.Execute(%) after % ms',[ClassName,
    GetEnumName(TypeInfo(TSQLRestServerURIContextCommand),ord(Command))^,
................................................................................
  if self=nil then
    result := '' else
    result := Contract; // just return the current value
end;

procedure TServiceFactoryServerInstance.SafeFreeInstance(Factory: TServiceFactoryServer);
var Obj: TInterfacedObject;

    Synch: TCallMethodSynchro;

begin
  InstanceID := 0;
  Obj := Instance;
  Instance := nil;
  {$ifndef LVCL}
  if (optFreeInMainThread in Factory.fAnyOptions) and
     (GetCurrentThreadID<>MainThreadID) then
    {$ifdef DELPHI6OROLDER}TThreadHook(nil).Synchronize(
    {$else}                TThread.Synchronize(nil,
    {$endif}  TInterfacedObjectWithCustomCreate(Obj).InternalRelease) else
  {$endif}
  if (optFreeInPerInterfaceThread in Factory.fAnyOptions) and
     Assigned(Factory.fBackgroundThread) then begin
    Synch.Action := syncInstanceRelease;
    Synch.InstanceToRelease := TInterfacedObjectWithCustomCreate(Obj);
    Factory.fBackgroundThread.RunAndWait(@Synch);
  end else

    IInterface(Obj)._Release;
end;

function TServiceFactoryServer.InternalInstanceRetrieve(
  var Inst: TServiceFactoryServerInstance; aMethodIndex: integer): boolean;
procedure AddNew;
var i: integer;
................................................................................
  end;
  Ctxt.ServiceInstanceID := Inst.InstanceID;
  // 2. call method implementation
  try
    entry := Inst.Instance.GetInterfaceEntry(fInterface.fInterfaceIID);
    if entry=nil then
      exit;

    if optExecInPerInterfaceThread in fExecution[Ctxt.ServiceMethodIndex].Options then
      if fBackgroundThread=nil then
        fBackgroundThread := TSynBackgroundThreadSQLRestServerProcedure.Create(RestServer);

    ThreadServer := @ServiceContext;
    WR := TJSONSerializer.CreateOwnedStream;
    try
      with ThreadServer^ do begin
        Factory := self;
        Request := Ctxt;
      end; // RunningThread is already set at thread initialization
................................................................................
var m,i: integer;
begin
  if self<>nil then begin
    if (fInstanceCreation=sicPerThread) and (optExecLockedPerInterface in aOptions) then
      raise EServiceException.CreateFmt(
        'optExecLockedPerInterface option not compatible with sicPerThread for I%s interface',
        [fInterfaceURI]);

    if (fInstanceCreation=sicPerThread) and
       ([{$ifndef LVCL}optExecInMainThread,optFreeInMainThread,{$endif}
         optExecInPerInterfaceThread,optFreeInPerInterfaceThread]*aOptions<>[]) then
      raise EServiceException.CreateFmt(
        'opt*In*Thread option not compatible with sicPerThread for I%s interface',
        [fInterfaceURI]);
    {$ifndef LVCL}
    if (optExecLockedPerInterface in aOptions) and
       ([optExecInMainThread,optFreeInMainThread,
         optExecInPerInterfaceThread,optFreeInPerInterfaceThread]*aOptions<>[]) then
      raise EServiceException.CreateFmt(
        'optExecLockedPerInterface with opt*In*Thread options for I%s interface',
        [fInterfaceURI]);
    {$endif}
    if high(aMethod)<0 then
      for i := 0 to fInterface.fMethodsCount-1 do
        fExecution[i].Options := aOptions else
    for m := 0 to high(aMethod) do
      fExecution[fInterface.CheckMethodIndex(aMethod[m])].Options := aOptions;

    fAnyOptions := [];
    for i := 0 to fInterface.fMethodsCount-1 do
      fAnyOptions := fAnyOptions+fExecution[i].Options;
    if (optFreeInPerInterfaceThread in fAnyOptions) and
       not (optExecInPerInterfaceThread in fAnyOptions) then
      raise EServiceException.CreateFmt(
        'optFreeInPerInterfaceThread without optExecInPerInterfaceThread for I%s interface',
        [fInterfaceURI]);
    {$ifndef LVCL}
    if ([optExecInMainThread,optFreeInMainThread]*fAnyOptions<>[]) and
       ([optExecInPerInterfaceThread,optFreeInPerInterfaceThread]*fAnyOptions<>[]) then
      raise EServiceException.CreateFmt(
        'Concurrent opt*InMainThread and opt*InPerInterfaceThread for I%s interface',
        [fInterfaceURI]);
    {$endif}
  end;
................................................................................
    mov [esi].TCallMethodArgs.res64.Hi,edx
@e: mov esp,ebp
    pop ebp
    pop esi
end;
{$endif CPU64}



procedure BackgroundExecuteProc(Call: Pointer);
var Synch: ^TCallMethodSynchro absolute Call;
    ThreadServer: PServiceRunningContext;
    backup: TServiceRunningContext;
begin
  case Synch.Action of
  syncCallMethod: begin
................................................................................
procedure CallMethodSynch(Args: pointer);
var Synch: TCallMethodSynchro;
begin
  Synch.Action := syncCallMethod;
  Synch.CallMethodArgs := Args;
  BackgroundExecuteProc(@Synch);
end;

{$ifndef LVCL}

type
  TCollectionClass = class of TInterfacedCollection;

{$endif LVCL}

function TServiceMethod.ArgResultIndex(ArgName: PUTF8Char; ArgNameLen: integer): integer;
................................................................................
    WideStrings: TWideStringDynArray;
    Records: array of TBytes;
    Value: pointer;
    i,a: integer;
    wasString, valid: boolean;
    Val: PUTF8Char;
    r: TCallMethodArgs;
    {$ifndef LVCL}
    SyncMethod: TMethod;

    {$endif}
    Synch: TCallMethodSynchro;
    Stack: array[0..MAX_EXECSTACK-1] of byte;
    Int64s: array[0..MAX_METHOD_ARGS-1] of Int64;
    Objects: array[0..MAX_METHOD_ARGS-1] of TObject;
    DynArrays: array[0..MAX_METHOD_ARGS-1] of TDynArrayFake;
    Values: array[0..MAX_METHOD_ARGS-1] of PPointer;
begin
  result := false;
................................................................................
        r.callContext := @ServiceContext; // to be copied into main threadvar
        SyncMethod.Code := @CallMethodSynch;
        SyncMethod.Data := @r; // fake call: PCallMethodArgs(self)^=Params
        {$ifdef DELPHI6OROLDER}TThreadHook(r.callContext^.RunningThread).Synchronize(
        {$else}                TThread.Synchronize(r.callContext^.RunningThread,
        {$endif}  TThreadMethod(SyncMethod));
      end else
      {$endif}
      if optExecInPerInterfaceThread in Options then
        if not Assigned(BackgroundExecutionThread) then
          raise EInterfaceFactoryException.Create(
            'optExecInPerInterfaceThread with BackgroundExecutionThread=nil') else begin
        r.callContext := @ServiceContext; // to be copied into background threadvar
        Synch.Action := syncCallMethod;
        Synch.CallMethodArgs := @r;
        BackgroundExecutionThread.RunAndWait(@Synch);
      end else

        CallMethod(r);
    end;
    // 4. send back any result
    if Res<>nil then begin
      // 4.1 handle custom content (not JSON object answer)
      if (r.resKind=smvRecord) and ArgsResultIsServiceCustomAnswer then
        with PServiceCustomAnswer(Values[ArgsResultIndex])^ do
................................................................................
        end;
        {$endif}
        end;
    end;
  end;
end;



{ TSynBackgroundThreadSQLRestServerProcedure }

constructor TSynBackgroundThreadSQLRestServerProcedure.Create(aServer: TSQLRestServer);
begin
  inherited Create(BackgroundExecuteProc,nil);
  fServer := aServer;
................................................................................
  try
    inherited Execute;
  finally
    fServer.EndCurrentThread(self);
  end;
end;




{ TServiceContainerClient }

function TServiceContainerClient.Info(aTypeInfo: PTypeInfo): TServiceFactory;
begin
  result := inherited Info(aTypeInfo);
  if result=nil then

    // to remote database engine (e.g. Oracle bound arrays or MS SQL Bulk insert)
    procedure InternalBatchStop; override;
    /// called internally by EngineAdd/EngineUpdate/EngineDelete in batch mode
    function InternalBatchAdd(const aValue: RawUTF8; aID: integer): integer;
    /// TSQLRestServer.URI use it for Static.EngineList to by-pass virtual table
    // - overriden method to handle most potential simple queries, e.g. like
    // $ SELECT Field1,RowID FROM table WHERE RowID=... AND/OR/NOT Field2=
    // - change 'RowID' into 'ID' column name, and SQLTableName into fTableName


    // - any 'LIMIT #' clause will be changed into the appropriate SQL statement
    // - handle statements to avoid slow virtual table loop over all rows, like
    // $ SELECT count(*) FROM table
    function AdaptSQLForEngineList(var SQL: RawUTF8): boolean; override;
    /// run INSERT of UPDATE from the corresponding JSON object
    // - Occasion parameter shall be only either soInsert or soUpate
    // - each JSON field will be bound with the proper SQL type corresponding to
................................................................................
    CreateColumns: TSQLDBColumnPropertyDynArray;
begin
  inherited Create(aClass,aServer,aFileName,aBinaryFile);
  // initialize external DB properties
  if fStoredClassProps=nil then
    raise EBusinessLayerException.CreateFmt(
      'StoredClassProps needed for %s',[StoredClassRecordProps.SQLTableName]);
  fTableName := fStoredClassProps.ExternalDB.TableName;
  fProperties := fStoredClassProps.ExternalDB.ConnectionProperties as TSQLDBConnectionProperties;
  if fProperties=nil then
    raise EBusinessLayerException.CreateFmt(
      'No external DB defined for %s',[StoredClassRecordProps.SQLTableName]);
  if Owner<>nil then
    try
      Owner.ServerTimeStamp := fProperties.ThreadSafeConnection.ServerTimeStamp;
    except
................................................................................
      on E: Exception do ; // ignore any error here
    end;
  // create corresponding external table if necessary, and retrieve its fields info
  fProperties.GetFields(fTableName,fFieldsExternal);
  if fFieldsExternal=nil then begin
    // table is not yet existing -> try to create it
    with aClass.RecordProps do begin
      SetLength(CreateColumns,Fields.Count);



      f := 0;
      for i := 0 to Fields.Count-1 do
        if PropInfoToExternalField(Fields.List[i],CreateColumns[f]) then
          inc(f);
      if f<>Fields.Count then
        SetLength(CreateColumns,f); // just ignore non handled field types
    end;
    SQL := fProperties.SQLCreate(fTableName,CreateColumns);
    if SQL<>'' then
      if ExecuteDirect(pointer(SQL),[],[],false)<>nil then begin
        fProperties.GetFields(fTableName,fFieldsExternal); // fields from DB after create
        if fFieldsExternal=nil then
          raise EORMException.CreateFmt(
            '%s: external table creation %s failed: GetFields() returned nil - SQL="%s"',
            [fStoredClass.ClassName,fTableName,SQL]);
................................................................................
end;

function TSQLRestServerStaticExternal.AdaptSQLForEngineList(var SQL: RawUTF8): boolean;
var Prop: ShortString; // to avoid any temporary memory allocation
    P: PUTF8Char;
    W: TTextWriter;

  function PropHandleField: boolean;
  var int: integer;
  begin
    result := true;
    if IsRowIDShort(Prop) then begin
      W.AddString(StoredClassProps.ExternalDB.RowIDFieldName);



      exit;
    end;
    Prop[ord(Prop[0])+1] := #0; // make ASCIIZ
    int := StoredClassRecordProps.Fields.IndexByName(@Prop[1]);
    if int<0 then
      result := false else
      W.AddString(StoredClassProps.ExternalDB.FieldNames[int]);




  end;
  procedure GetFieldProp;
  var i,L: integer;
      B: PUTF8Char;
  begin
    Prop[0] := #0;
    if P^=#0 then
................................................................................
    W.AddShort(Prop);
    W.Add(' ');
    GetFieldProp;
  end;
  function NextPropHandleField: boolean;
  begin
    GetFieldProp;
    result := PropHandleField;
  end;
  function NextPropHandleInternalTable: boolean;
  begin
    GetFieldProp;
    with StoredClassRecordProps do
      if IdemPropName(Prop,pointer(SQLTableName),length(SQLTableName)) then begin
        W.AddString(fTableName);
        result := true;
      end else
        result := false;
  end;

label Order,Limit;
var Pos: record AfterSelect, WhereClause, Limit, LimitRowCount: integer; end;
    B: PUTF8Char;
    err: integer;
    NewSQL: RawUTF8;
................................................................................
        W.AddShort('count(*)');
        if P^ in [#0,';'] then begin
          result := NextPropHandleInternalTable;
          exit;
        end;
        break; // will process 'select count(*) from tablename where ...'
      end else
      if not PropHandleField then
        exit; // unknown field name
      if P^=',' then begin
        W.Add(',');
        inc(P);
      end else begin
        GetFieldProp;
        if Prop<>'FROM' then
................................................................................
        if Prop<>'' then
          exit; // unexpected clause 
      if not (GotoNextNotSpace(P)^ in [#0,';']) then
        exit; // allow only one column name or one LIMIT ### expression
    end else
    if Prop='WHERE' then
    repeat
      WritePropAndGetFieldProp;
      Pos.WhereClause := W.TextLength+1;
      if Prop='NOT' then
        WritePropAndGetFieldProp; // allow  field1=456 AND NOT field2='Toto'
      if (Prop='') or not PropHandleField then
        exit; // unknown field name or 'LIMIT' / 'ORDER BY' clause
      B := P;
      if P^='=' then
        inc(P) else
      if P^ in ['>','<'] then
        if P[1] in ['=','>'] then
          inc(P,2) else
................................................................................
end;

function TSQLRestServerStaticExternal.EngineList(const SQL: RawUTF8;
  ForceAJAX: Boolean; ReturnedRowCount: PPtrInt): RawUTF8;
var Stmt: ISQLDBStatement;
begin
  if ReturnedRowCount<>nil then
    raise ESQLDBException.Create('TSQLRestServerStaticExternal.EngineList(ReturnedRowCount<>nil)');
  Stmt := PrepareInlinedForRows(SQL);
  if Stmt=nil then
    result := '' else
    Stmt.ExecutePreparedAndFetchAllAsJSON(ForceAJAX or (not NoAJAXJSON),result);
end;

function TSQLRestServerStaticExternal.EngineRetrieve(TableModelIndex, ID: integer): RawUTF8;
................................................................................
end;

function TSQLRestServerStaticExternal.TableRowCount(Table: TSQLRecordClass): integer;
var Rows: ISQLDBRows;
begin
  if (self=nil) or (Table<>fStoredClass) then
    result := 0 else begin
    Rows := ExecuteDirect('SELECT count(*) FROM %',[fTableName],[],true);
    if (Rows=nil) or not Rows.Step then
      result := 0 else
      result := Rows.ColumnInt(0);
  end;
end;

function TSQLRestServerStaticExternal.EngineRetrieveBlob(
................................................................................

function TSQLRestServerStaticExternal.ExecuteInlined(SQLFormat: PUTF8Char;
  const Args: array of const; ExpectResults: Boolean): ISQLDBRows;
begin
  result := ExecuteInlined(FormatUTF8(SQLFormat,Args),ExpectResults);
end;

function TSQLRestServerStaticExternal.PrepareDirectForRows(SQLFormat: PUTF8Char; const Args, Params: array of const): ISQLDBStatement;

var Query: ISQLDBStatement;
begin
  result := nil;
  if self=nil then
    exit;
  Query := fProperties.NewThreadSafeStatementPrepared(SQLFormat,Args,true);
  if Query<>nil then
................................................................................
  const SentData: RawUTF8; Occasion: TSQLOccasion; UpdatedID: integer): integer;
var Decoder: TJSONObjectDecoder;
    SQL: RawUTF8;
    Types: TSQLDBFieldTypeArray;
    ExternalFields: TRawUTF8DynArray;
    InsertedID, F: integer;
    Query: ISQLDBStatement;
    P: PUTF8Char;
begin
  result := 0;
  Lock(false); // avoid race condition against max(ID)
  try
    case Occasion of
    soInsert: begin
      InsertedID := JSONRetrieveIDField(pointer(SentData));
................................................................................
    // execute statement
    Query := fProperties.NewThreadSafeStatementPrepared(SQL,false);
    if Query=nil then
      exit;
    try
      for F := 0 to Decoder.FieldCount-1 do
      if F in Decoder.FieldNull then
        Query.BindNull(F+1) else begin
        P := pointer(Decoder.FieldValues[F]);
        case Types[F] of
        ftInt64:    Query.Bind(F+1,GetInt64(P));
        ftDouble:   Query.Bind(F+1,GetExtended(P));
        ftDate:     Query.BindDateTime(F+1,Iso8601ToDateTimePUTF8Char(P));
        ftCurrency: Query.BindCurrency(F+1,StrToCurrency(P));
        ftBlob:     Query.BindBlob(F+1,Decoder.FieldValues[F]);
        ftUTF8:     Query.BindTextU(F+1,Decoder.FieldValues[F]);
        else raise ESQLDBException.CreateFmt(
          'ExecuteFromJSON: Invalid Types[%d]=%d',[F,ord(result)]);
        end;
      end;
      Query.ExecutePrepared;
    except
      exit; // leave result=0
    end;
    // mark success
    if UpdatedID=0 then
      result := InsertedID else
................................................................................
  if (self<>nil) and (Static<>nil) and
     (oldRowID=newRowID) and (newRowID>0) then // don't allow ID change
    with Static as TSQLRestServerStaticExternal, StoredClassProps.ExternalDB do
      result := ExecuteDirectSQLVar('update % set % where %=?',
        [fTableName,SQL.UpdateSetAll,RowIDFieldName],Values,oldRowID,true) else
    result := false;
end;



initialization
  // all our SynDB related functions shall log to main TSQLLog
  SynDBLog := TSQLLog;
end.

    // to remote database engine (e.g. Oracle bound arrays or MS SQL Bulk insert)
    procedure InternalBatchStop; override;
    /// called internally by EngineAdd/EngineUpdate/EngineDelete in batch mode
    function InternalBatchAdd(const aValue: RawUTF8; aID: integer): integer;
    /// TSQLRestServer.URI use it for Static.EngineList to by-pass virtual table
    // - overriden method to handle most potential simple queries, e.g. like
    // $ SELECT Field1,RowID FROM table WHERE RowID=... AND/OR/NOT Field2=
    // - change 'RowID' into 'ID' column name, internal field names into
    // mapped external field names ('AS [InternalFieldName]' if needed), and
    // SQLTableName into fTableName
    // - any 'LIMIT #' clause will be changed into the appropriate SQL statement
    // - handle statements to avoid slow virtual table loop over all rows, like
    // $ SELECT count(*) FROM table
    function AdaptSQLForEngineList(var SQL: RawUTF8): boolean; override;
    /// run INSERT of UPDATE from the corresponding JSON object
    // - Occasion parameter shall be only either soInsert or soUpate
    // - each JSON field will be bound with the proper SQL type corresponding to
................................................................................
    CreateColumns: TSQLDBColumnPropertyDynArray;
begin
  inherited Create(aClass,aServer,aFileName,aBinaryFile);
  // initialize external DB properties
  if fStoredClassProps=nil then
    raise EBusinessLayerException.CreateFmt(
      'StoredClassProps needed for %s',[StoredClassRecordProps.SQLTableName]);
  fTableName := StoredClassProps.ExternalDB.TableName;
  fProperties := StoredClassProps.ExternalDB.ConnectionProperties as TSQLDBConnectionProperties;
  if fProperties=nil then
    raise EBusinessLayerException.CreateFmt(
      'No external DB defined for %s',[StoredClassRecordProps.SQLTableName]);
  if Owner<>nil then
    try
      Owner.ServerTimeStamp := fProperties.ThreadSafeConnection.ServerTimeStamp;
    except
................................................................................
      on E: Exception do ; // ignore any error here
    end;
  // create corresponding external table if necessary, and retrieve its fields info
  fProperties.GetFields(fTableName,fFieldsExternal);
  if fFieldsExternal=nil then begin
    // table is not yet existing -> try to create it
    with aClass.RecordProps do begin
      SetLength(CreateColumns,Fields.Count+1);
      CreateColumns[0].ColumnName := StoredClassProps.ExternalDB.RowIDFieldName;
      CreateColumns[0].ColumnType := ftUnknown;
      CreateColumns[0].ColumnUnique := true;
      f := 1;
      for i := 0 to Fields.Count-1 do
        if PropInfoToExternalField(Fields.List[i],CreateColumns[f]) then
          inc(f);
      if f<>Length(CreateColumns) then
        SetLength(CreateColumns,f); // just ignore non handled field types
    end;
    SQL := fProperties.SQLCreate(fTableName,CreateColumns,false);
    if SQL<>'' then
      if ExecuteDirect(pointer(SQL),[],[],false)<>nil then begin
        fProperties.GetFields(fTableName,fFieldsExternal); // fields from DB after create
        if fFieldsExternal=nil then
          raise EORMException.CreateFmt(
            '%s: external table creation %s failed: GetFields() returned nil - SQL="%s"',
            [fStoredClass.ClassName,fTableName,SQL]);
................................................................................
end;

function TSQLRestServerStaticExternal.AdaptSQLForEngineList(var SQL: RawUTF8): boolean;
var Prop: ShortString; // to avoid any temporary memory allocation
    P: PUTF8Char;
    W: TTextWriter;

  function PropHandleField(WithAliasIfNeeded: boolean): boolean;
  var int: integer;
  begin
    result := true;
    if IsRowIDShort(Prop) then
    with StoredClassProps.ExternalDB do begin
      W.AddString(RowIDFieldName);
      if WithAliasIfNeeded and not(0 in FieldNamesMatchInternal) then
        W.AddShort(' as ID');
      exit;
    end;
    Prop[ord(Prop[0])+1] := #0; // make ASCIIZ
    int := StoredClassRecordProps.Fields.IndexByName(@Prop[1]);
    if int<0 then
      result := false else
      with StoredClassProps.ExternalDB do begin
        W.AddString(FieldNames[int]);
        if WithAliasIfNeeded and not(int+1 in FieldNamesMatchInternal) then
          W.AddStrings([' as ',StoredClassRecordProps.Fields.List[int].Name]);
      end;
  end;
  procedure GetFieldProp;
  var i,L: integer;
      B: PUTF8Char;
  begin
    Prop[0] := #0;
    if P^=#0 then
................................................................................
    W.AddShort(Prop);
    W.Add(' ');
    GetFieldProp;
  end;
  function NextPropHandleField: boolean;
  begin
    GetFieldProp;
    result := PropHandleField(false);
  end;
  function NextPropHandleInternalTable: boolean;
  begin
    GetFieldProp;
    with StoredClassRecordProps do
    if IdemPropName(Prop,pointer(SQLTableName),length(SQLTableName)) then begin
      W.AddString(fTableName);
      result := true;
    end else
      result := false;
  end;

label Order,Limit;
var Pos: record AfterSelect, WhereClause, Limit, LimitRowCount: integer; end;
    B: PUTF8Char;
    err: integer;
    NewSQL: RawUTF8;
................................................................................
        W.AddShort('count(*)');
        if P^ in [#0,';'] then begin
          result := NextPropHandleInternalTable;
          exit;
        end;
        break; // will process 'select count(*) from tablename where ...'
      end else
      if not PropHandleField(true) then
        exit; // unknown field name
      if P^=',' then begin
        W.Add(',');
        inc(P);
      end else begin
        GetFieldProp;
        if Prop<>'FROM' then
................................................................................
        if Prop<>'' then
          exit; // unexpected clause 
      if not (GotoNextNotSpace(P)^ in [#0,';']) then
        exit; // allow only one column name or one LIMIT ### expression
    end else
    if Prop='WHERE' then
    repeat
      WritePropAndGetFieldProp; // write as 'where' 'and' 'or'
      Pos.WhereClause := W.TextLength+1;
      if Prop='NOT' then
        WritePropAndGetFieldProp; // allow  field1=456 AND NOT field2='Toto'
      if (Prop='') or not PropHandleField(false) then
        exit; // unknown field name or 'LIMIT' / 'ORDER BY' clause
      B := P;
      if P^='=' then
        inc(P) else
      if P^ in ['>','<'] then
        if P[1] in ['=','>'] then
          inc(P,2) else
................................................................................
end;

function TSQLRestServerStaticExternal.EngineList(const SQL: RawUTF8;
  ForceAJAX: Boolean; ReturnedRowCount: PPtrInt): RawUTF8;
var Stmt: ISQLDBStatement;
begin
  if ReturnedRowCount<>nil then
    raise ESQLDBException.CreateFmt('%s.EngineList(ReturnedRowCount<>nil)',[ClassName]);
  Stmt := PrepareInlinedForRows(SQL);
  if Stmt=nil then
    result := '' else
    Stmt.ExecutePreparedAndFetchAllAsJSON(ForceAJAX or (not NoAJAXJSON),result);
end;

function TSQLRestServerStaticExternal.EngineRetrieve(TableModelIndex, ID: integer): RawUTF8;
................................................................................
end;

function TSQLRestServerStaticExternal.TableRowCount(Table: TSQLRecordClass): integer;
var Rows: ISQLDBRows;
begin
  if (self=nil) or (Table<>fStoredClass) then
    result := 0 else begin
    Rows := ExecuteDirect('select count(*) from %',[fTableName],[],true);
    if (Rows=nil) or not Rows.Step then
      result := 0 else
      result := Rows.ColumnInt(0);
  end;
end;

function TSQLRestServerStaticExternal.EngineRetrieveBlob(
................................................................................

function TSQLRestServerStaticExternal.ExecuteInlined(SQLFormat: PUTF8Char;
  const Args: array of const; ExpectResults: Boolean): ISQLDBRows;
begin
  result := ExecuteInlined(FormatUTF8(SQLFormat,Args),ExpectResults);
end;

function TSQLRestServerStaticExternal.PrepareDirectForRows(SQLFormat: PUTF8Char;
  const Args, Params: array of const): ISQLDBStatement;
var Query: ISQLDBStatement;
begin
  result := nil;
  if self=nil then
    exit;
  Query := fProperties.NewThreadSafeStatementPrepared(SQLFormat,Args,true);
  if Query<>nil then
................................................................................
  const SentData: RawUTF8; Occasion: TSQLOccasion; UpdatedID: integer): integer;
var Decoder: TJSONObjectDecoder;
    SQL: RawUTF8;
    Types: TSQLDBFieldTypeArray;
    ExternalFields: TRawUTF8DynArray;
    InsertedID, F: integer;
    Query: ISQLDBStatement;

begin
  result := 0;
  Lock(false); // avoid race condition against max(ID)
  try
    case Occasion of
    soInsert: begin
      InsertedID := JSONRetrieveIDField(pointer(SentData));
................................................................................
    // execute statement
    Query := fProperties.NewThreadSafeStatementPrepared(SQL,false);
    if Query=nil then
      exit;
    try
      for F := 0 to Decoder.FieldCount-1 do
      if F in Decoder.FieldNull then
        Query.BindNull(F+1) else
        Query.Bind(F+1,Types[F],Decoder.FieldValues[F],true);











      Query.ExecutePrepared;
    except
      exit; // leave result=0
    end;
    // mark success
    if UpdatedID=0 then
      result := InsertedID else
................................................................................
  if (self<>nil) and (Static<>nil) and
     (oldRowID=newRowID) and (newRowID>0) then // don't allow ID change
    with Static as TSQLRestServerStaticExternal, StoredClassProps.ExternalDB do
      result := ExecuteDirectSQLVar('update % set % where %=?',
        [fTableName,SQL.UpdateSetAll,RowIDFieldName],Values,oldRowID,true) else
    result := false;
end;



initialization
  // all our SynDB related functions shall log to main TSQLLog
  SynDBLog := TSQLLog;
end.

    procedure Bind(Param: Integer; const Data: TSQLVar;
      IO: TSQLDBParamInOutType=paramIn); overload;
    /// bind one RawUTF8 encoded value
    // - the leftmost SQL parameter has an index of 1
    // - the value should match the BindArray() format, i.e. be stored as in SQL
    // (i.e. number, 'quoted string', 'YYYY-MM-DD hh:mm:ss', null)
    procedure Bind(Param: Integer; ParamType: TSQLDBFieldType; const Value: RawUTF8;
      IO: TSQLDBParamInOutType=paramIn); overload;
    {/ bind an array of const values
     - parameters marked as ? should be specified as method parameter in Params[]
     - BLOB parameters can be bound with this method, when set after encoding
       via BinToBase64WithMagic() call
     - TDateTime parameters can be bound with this method, when encoded via
       a DateToSQL() or DateTimeToSQL() call }
    procedure Bind(const Params: array of const;
................................................................................
    // - this default implementation just returns nothing
    function SQLGetTableNames: RawUTF8; virtual;
    /// should initialize fForeignKeys content with all foreign keys of this
    // database
    // - used by GetForeignKey method
    procedure GetForeignKeys; virtual; abstract;
    /// will use fSQLCreateField[Max] to create the SQL column definition
    // - this default virtual implementation will handle properly
    // SQLite3/MSSQL/Oracle/Jet syntax (but not MySQL due to UNIQUE Constraint)
    function SQLFieldCreate(const aField: TSQLDBColumnProperty): RawUTF8; virtual;
    /// wrapper around GetIndexes() + set Fields[].ColumnIndexed in consequence
    // - used by some overriden versions of GetFields() method
    procedure GetIndexesAndSetFieldsColumnIndexed(const aTableName: RawUTF8;
      var Fields: TSQLDBColumnDefineDynArray);
    /// check if the exception or its error message is about DB connection error
    // - will be used by TSQLDBConnection.LastErrorWasAboutConnection method
................................................................................
    /// convert a textual column data type, as retrieved e.g. from SQLGetField,
    // into our internal primitive types
    // - default implementation will always return ftUTF8
    function ColumnTypeNativeToDB(const aNativeType: RawUTF8; aScale: integer): TSQLDBFieldType; virtual;
    /// returns the SQL statement used to create a Table
    // - should return the SQL "CREATE" statement needed to create a table with
    // the specified field/column names and types
    // - a "ID Int64 PRIMARY KEY" column is always added at first position,
    // and will expect the ORM to create an unique RowID value sent at INSERT
    // (could use "select max(ID) from table" to retrieve the last value) -
    // note that 'ID' is used instead of 'RowID' since it fails on Oracle e.g.
    // - this default implementation will use internal fSQLCreateField and
    // fSQLCreateFieldMax protected values, which contains by default the
    // ANSI SQL Data Types and maximum 1000 inlined WideChars: inherited classes
    // may change the default fSQLCreateField* content or override this method
    function SQLCreate(const aTableName: RawUTF8;
      const aFields: TSQLDBColumnPropertyDynArray): RawUTF8; virtual;
    /// returns the SQL statement used to add a column to a Table
    // - should return the SQL "ALTER TABLE" statement needed to add a column to
    // an existing table
    // - this default implementation will use internal fSQLCreateField and
    // fSQLCreateFieldMax protected values, which contains by default the
    // ANSI SQL Data Types and maximum 1000 inlined WideChars: inherited classes
    // may change the default fSQLCreateField* content or override this method
................................................................................
      IO: TSQLDBParamInOutType=paramIn); overload; virtual;
    /// bind one RawUTF8 encoded value
    // - the leftmost SQL parameter has an index of 1
    // - the value should match the BindArray() format, i.e. be stored as in SQL
    // (i.e. number, 'quoted string', 'YYYY-MM-DD hh:mm:ss', null) - e.g. as
    // computed by TJSONObjectDecoder.Decode()
    procedure Bind(Param: Integer; ParamType: TSQLDBFieldType; const Value: RawUTF8;
      IO: TSQLDBParamInOutType=paramIn); overload; virtual;
    {/ bind an array of const values
     - parameters marked as ? should be specified as method parameter in Params[]
     - BLOB parameters can be bound with this method, when set after encoding
       via BinToBase64WithMagic() call
     - TDateTime parameters can be bound with this method, when encoded via
       a DateToSQL() or DateTimeToSQL() call
     - this default implementation will call corresponding Bind*() method }
................................................................................
      while Rows.Step do begin
        // init when first row of data is available
        if Ins=nil then begin
          SQL := Rows.ColumnsToSQLInsert(aTableName,Fields);
          Properties.GetTableNames(Tables);
          if FindRawUTF8(Tables,TableName,false)<0 then
            with Properties do
              ExecuteNoResult(SQLCreate(aTableName,Fields),[]);
          Ins := NewStatement;
          Ins.Prepare(SQL,false);
        end;
        // write row data
        Ins.BindFromRows(Fields,Rows);
        Ins.ExecutePrepared;
        Ins.Reset;
................................................................................
  if byte(fBatchSendingAbilities)=0 then // if not already handled by driver
    case aDBMS of
    dSQlite,dMySQL,dPostgreSQL,dNexusDB,dMSSQL,dDB2, // INSERT with multi VALUES
    //dFirebird,  EXECUTE BLOCK with params is slower (at least for embedded)
    dOracle: begin // Oracle expects weird INSERT ALL INTO ... statement
      fBatchSendingAbilities := [cCreate];
      fOnBatchInsert := MultipleValuesInsert;

    end;
    dFirebird: begin // will run EXECUTE BLOCK without parameters
      fBatchSendingAbilities := [cCreate];
      fOnBatchInsert := MultipleValuesInsertFirebird;

    end;
    end;
end;

destructor TSQLDBConnectionProperties.Destroy;
begin
  fMainConnection.Free;
................................................................................
  dNexusDB: result := 'DATE '+Iso8601;
  dDB2: result := 'TIMESTAMP '''+TrimTInIso+'''';
  else  result := ''''+Iso8601+'''';
  end;
end;

function TSQLDBConnectionProperties.SQLCreate(const aTableName: RawUTF8;
  const aFields: TSQLDBColumnPropertyDynArray): RawUTF8;
var i: integer;
    F: RawUTF8;

const EXE_FMT: PUTF8Char = 'CREATE TABLE % (ID % PRIMARY KEY, %)'; // Delphi 5
begin // use 'ID' instead of 'RowID' here since some DB (e.g. Oracle) use it
  result := '';
  if high(aFields)<0 then
    exit; // nothing to create
  for i := 0 to high(aFields) do begin









    F := SQLFieldCreate(aFields[i]);
    if i<>high(aFields) then
      F := F+',';
    result := result+F;
  end;
  if IsRowID(pointer(aFields[0].ColumnName)) then


    result := 'CREATE TABLE '+aTableName+' ('+result+')' else
    // fSQLCreateField[ftUnknown] is the datatype for ID field
    result := FormatUTF8(EXE_FMT,[aTableName,fSQLCreateField[ftUnknown],result]);
  case DBMS of
  dDB2: result := result+' CCSID Unicode';
  end;
end;

function TSQLDBConnectionProperties.SQLFieldCreate(const aField: TSQLDBColumnProperty): RawUTF8;
begin
  if (aField.ColumnType=ftUTF8) and (aField.ColumnAttr-1<fSQLCreateFieldMax) then
    result := FormatUTF8(pointer(fSQLCreateField[ftNull]),[aField.ColumnAttr]) else
    if IsRowID(pointer(aField.ColumnName)) then // ID -> ftUnknown (=INTEGER)
      result := fSQLCreateField[ftUnknown] else
      result := fSQLCreateField[aField.ColumnType];
  if aField.ColumnNonNullable or aField.ColumnUnique then
    result := result+' NOT NULL';
  if aField.ColumnUnique then
    result := result+' UNIQUE'; // see http://www.w3schools.com/sql/sql_unique.asp 
  result := aField.ColumnName+result;
end;

................................................................................
    maxf: integer;
procedure ComputeSQL(rowcount,offset: integer);
var f,r,p,len: integer;
begin
  if (fDBMS<>dFireBird) and (rowcount=prevrowcount) then
    exit;
  prevrowcount := rowcount;
  with TTextWriter.CreateOwnedStream(4096) do
  try
    case Props.DBMS of
    dFirebird: begin
      AddShort('execute block('#10);
      p := 0;
      for r := offset to offset+rowcount-1 do begin
        for f := 0 to maxf do begin
................................................................................
    else begin
      ComputeSQL(RowCount-currentRow,currentRow);
      SQLCached := false; // truncate number of parameters should not be unique
    end;
    if SQLCached then
      Query := Props.NewThreadSafeStatementPrepared(SQL,false) else begin
      Stmt := Props.NewThreadSafeStatement;

      Stmt.Prepare(SQL,false);
      Query := Stmt;




    end;
    if Query=nil then
      raise ESQLDBException.CreateFmt('%s.MultipleValuesInsert() Prepare(%s)',
        [ClassName,SQL]);
    try
      p := 1;
      for i := 1 to prevrowcount do begin
        for f := 0 to maxf do begin
          Query.Bind(p,FieldTypes[f],FieldValues[f,currentRow]);
          inc(p);
        end;
        inc(currentRow);
      end;
      Query.ExecutePrepared;
    finally
      Query := nil;
................................................................................
    ftBlob:     BindBlob(Param,VBlob,VBlobLen,IO);
    else raise ESQLDBException.CreateFmt(
      '%s.Bind(Param=%d,VType=%d)',[fStatementClassName,Param,ord(VType)]);
  end;
end;

procedure TSQLDBStatement.Bind(Param: Integer; ParamType: TSQLDBFieldType;
  const Value: RawUTF8; IO: TSQLDBParamInOutType=paramIn);
var tmp: RawUTF8;
begin

  if Value='null' then // bind null (ftUTF8 should be '"null"')
    BindNull(Param,IO) else
    case ParamType of
      ftNull:     BindNull(Param,IO);
      ftInt64:    Bind(Param,GetInt64(pointer(Value)),IO);
      ftDouble:   Bind(Param,GetExtended(pointer(Value)),IO);
      ftCurrency: BindCurrency(Param,StrToCurrency(pointer(Value)),IO);
      ftBlob:     BindBlob(Param,Value,IO); // already decoded
      ftDate: begin


        UnQuoteSQLString(pointer(Value),tmp);
        BindDateTime(Param,Iso8601ToDateTime(tmp),IO);
      end;
      ftUTF8:
        if ((Value='') or (Value=#39#39)) and
            fConnection.fProperties.StoreVoidStringAsNull then
          BindNull(Param,IO) else begin


          UnQuoteSQLString(pointer(Value),tmp);
          BindTextU(Param,tmp,IO);
        end;
      else raise ESQLDBException.CreateFmt('Invalid %s.Bind(%d,TSQLDBFieldType(%d),%s)',
        [fStatementClassName,Param,ord(ParamType),Value]);
    end;
end;

    procedure Bind(Param: Integer; const Data: TSQLVar;
      IO: TSQLDBParamInOutType=paramIn); overload;
    /// bind one RawUTF8 encoded value
    // - the leftmost SQL parameter has an index of 1
    // - the value should match the BindArray() format, i.e. be stored as in SQL
    // (i.e. number, 'quoted string', 'YYYY-MM-DD hh:mm:ss', null)
    procedure Bind(Param: Integer; ParamType: TSQLDBFieldType; const Value: RawUTF8;
      ValueAlreadyUnquoted: boolean; IO: TSQLDBParamInOutType=paramIn); overload;
    {/ bind an array of const values
     - parameters marked as ? should be specified as method parameter in Params[]
     - BLOB parameters can be bound with this method, when set after encoding
       via BinToBase64WithMagic() call
     - TDateTime parameters can be bound with this method, when encoded via
       a DateToSQL() or DateTimeToSQL() call }
    procedure Bind(const Params: array of const;
................................................................................
    // - this default implementation just returns nothing
    function SQLGetTableNames: RawUTF8; virtual;
    /// should initialize fForeignKeys content with all foreign keys of this
    // database
    // - used by GetForeignKey method
    procedure GetForeignKeys; virtual; abstract;
    /// will use fSQLCreateField[Max] to create the SQL column definition
    // - this default virtual implementation will handle properly all supported
    // database engines, assuming aField.ColumnType=ftUnknown for ID
    function SQLFieldCreate(const aField: TSQLDBColumnProperty): RawUTF8; virtual;
    /// wrapper around GetIndexes() + set Fields[].ColumnIndexed in consequence
    // - used by some overriden versions of GetFields() method
    procedure GetIndexesAndSetFieldsColumnIndexed(const aTableName: RawUTF8;
      var Fields: TSQLDBColumnDefineDynArray);
    /// check if the exception or its error message is about DB connection error
    // - will be used by TSQLDBConnection.LastErrorWasAboutConnection method
................................................................................
    /// convert a textual column data type, as retrieved e.g. from SQLGetField,
    // into our internal primitive types
    // - default implementation will always return ftUTF8
    function ColumnTypeNativeToDB(const aNativeType: RawUTF8; aScale: integer): TSQLDBFieldType; virtual;
    /// returns the SQL statement used to create a Table
    // - should return the SQL "CREATE" statement needed to create a table with
    // the specified field/column names and types
    // - if aAddID is TRUE, "ID Int64 PRIMARY KEY" column is added as first,
    // and will expect the ORM to create an unique RowID value sent at INSERT
    // (could use "select max(ID) from table" to retrieve the last value) -
    // note that 'ID' is used instead of 'RowID' since it fails on Oracle e.g.
    // - this default implementation will use internal fSQLCreateField and
    // fSQLCreateFieldMax protected values, which contains by default the
    // ANSI SQL Data Types and maximum 1000 inlined WideChars: inherited classes
    // may change the default fSQLCreateField* content or override this method
    function SQLCreate(const aTableName: RawUTF8;
      const aFields: TSQLDBColumnPropertyDynArray; aAddID: boolean): RawUTF8; virtual;
    /// returns the SQL statement used to add a column to a Table
    // - should return the SQL "ALTER TABLE" statement needed to add a column to
    // an existing table
    // - this default implementation will use internal fSQLCreateField and
    // fSQLCreateFieldMax protected values, which contains by default the
    // ANSI SQL Data Types and maximum 1000 inlined WideChars: inherited classes
    // may change the default fSQLCreateField* content or override this method
................................................................................
      IO: TSQLDBParamInOutType=paramIn); overload; virtual;
    /// bind one RawUTF8 encoded value
    // - the leftmost SQL parameter has an index of 1
    // - the value should match the BindArray() format, i.e. be stored as in SQL
    // (i.e. number, 'quoted string', 'YYYY-MM-DD hh:mm:ss', null) - e.g. as
    // computed by TJSONObjectDecoder.Decode()
    procedure Bind(Param: Integer; ParamType: TSQLDBFieldType; const Value: RawUTF8;
      ValueAlreadyUnquoted: boolean; IO: TSQLDBParamInOutType=paramIn); overload; virtual;
    {/ bind an array of const values
     - parameters marked as ? should be specified as method parameter in Params[]
     - BLOB parameters can be bound with this method, when set after encoding
       via BinToBase64WithMagic() call
     - TDateTime parameters can be bound with this method, when encoded via
       a DateToSQL() or DateTimeToSQL() call
     - this default implementation will call corresponding Bind*() method }
................................................................................
      while Rows.Step do begin
        // init when first row of data is available
        if Ins=nil then begin
          SQL := Rows.ColumnsToSQLInsert(aTableName,Fields);
          Properties.GetTableNames(Tables);
          if FindRawUTF8(Tables,TableName,false)<0 then
            with Properties do
              ExecuteNoResult(SQLCreate(aTableName,Fields,false),[]);
          Ins := NewStatement;
          Ins.Prepare(SQL,false);
        end;
        // write row data
        Ins.BindFromRows(Fields,Rows);
        Ins.ExecutePrepared;
        Ins.Reset;
................................................................................
  if byte(fBatchSendingAbilities)=0 then // if not already handled by driver
    case aDBMS of
    dSQlite,dMySQL,dPostgreSQL,dNexusDB,dMSSQL,dDB2, // INSERT with multi VALUES
    //dFirebird,  EXECUTE BLOCK with params is slower (at least for embedded)
    dOracle: begin // Oracle expects weird INSERT ALL INTO ... statement
      fBatchSendingAbilities := [cCreate];
      fOnBatchInsert := MultipleValuesInsert;
      fBatchMaxSentAtOnce := 4096; // MultipleValuesInsert will do chunking
    end;
    dFirebird: begin // will run EXECUTE BLOCK without parameters
      fBatchSendingAbilities := [cCreate];
      fOnBatchInsert := MultipleValuesInsertFirebird;
      fBatchMaxSentAtOnce := 4096; // MultipleValuesInsert will do chunking
    end;
    end;
end;

destructor TSQLDBConnectionProperties.Destroy;
begin
  fMainConnection.Free;
................................................................................
  dNexusDB: result := 'DATE '+Iso8601;
  dDB2: result := 'TIMESTAMP '''+TrimTInIso+'''';
  else  result := ''''+Iso8601+'''';
  end;
end;

function TSQLDBConnectionProperties.SQLCreate(const aTableName: RawUTF8;
  const aFields: TSQLDBColumnPropertyDynArray; aAddID: boolean): RawUTF8;
var i: integer;
    F: RawUTF8;
    AddPrimaryKey: RawUTF8;
const EXE_FMT: PUTF8Char = 'CREATE TABLE % (ID % PRIMARY KEY, %)'; // Delphi 5
begin // use 'ID' instead of 'RowID' here since some DB (e.g. Oracle) use it
  result := '';
  if high(aFields)<0 then
    exit; // nothing to create
  for i := 0 to high(aFields) do begin
    if (not aAddID) and (aFields[i].ColumnType=ftUnknown) then begin
      F := aFields[i].ColumnName+' '+fSQLCreateField[aFields[i].ColumnType]+' NOT NULL';
      case DBMS of
      dSQLite, dMSSQL, dOracle, dJet, dPostgreSQL, dFirebird, dNexusDB:
        F := F+' PRIMARY KEY';
      dDB2, dMySQL:
        AddPrimaryKey := aFields[i].ColumnName;
      end;
    end else
      F := SQLFieldCreate(aFields[i]);
    if i<>high(aFields) then
      F := F+',';
    result := result+F;
  end;
  if AddPrimaryKey<>'' then
    result := result+', PRIMARY KEY('+AddPrimaryKey+')';
  if not aAddID then
    result := 'CREATE TABLE '+aTableName+' ('+result+')' else
    // fSQLCreateField[ftUnknown] is the datatype for ID field
    result := FormatUTF8(EXE_FMT,[aTableName,fSQLCreateField[ftUnknown],result]);
  case DBMS of
  dDB2: result := result+' CCSID Unicode';
  end;
end;

function TSQLDBConnectionProperties.SQLFieldCreate(const aField: TSQLDBColumnProperty): RawUTF8;
begin
  if (aField.ColumnType=ftUTF8) and (aField.ColumnAttr-1<fSQLCreateFieldMax) then
    result := FormatUTF8(pointer(fSQLCreateField[ftNull]),[aField.ColumnAttr]) else


    result := fSQLCreateField[aField.ColumnType];
  if aField.ColumnNonNullable or aField.ColumnUnique then
    result := result+' NOT NULL';
  if aField.ColumnUnique then
    result := result+' UNIQUE'; // see http://www.w3schools.com/sql/sql_unique.asp 
  result := aField.ColumnName+result;
end;

................................................................................
    maxf: integer;
procedure ComputeSQL(rowcount,offset: integer);
var f,r,p,len: integer;
begin
  if (fDBMS<>dFireBird) and (rowcount=prevrowcount) then
    exit;
  prevrowcount := rowcount;
  with TTextWriter.CreateOwnedStream(8192) do
  try
    case Props.DBMS of
    dFirebird: begin
      AddShort('execute block('#10);
      p := 0;
      for r := offset to offset+rowcount-1 do begin
        for f := 0 to maxf do begin
................................................................................
    else begin
      ComputeSQL(RowCount-currentRow,currentRow);
      SQLCached := false; // truncate number of parameters should not be unique
    end;
    if SQLCached then
      Query := Props.NewThreadSafeStatementPrepared(SQL,false) else begin
      Stmt := Props.NewThreadSafeStatement;
      try
        Stmt.Prepare(SQL,false);
        Query := Stmt;
      except
        on Exception do
          Stmt.Free; // avoid memory leak in case of invalid SQL statement
      end; // exception leaves Query=nil to raise exception
    end;
    if Query=nil then
      raise ESQLDBException.CreateFmt('%s.MultipleValuesInsert() Prepare(%s)',
        [ClassName,SQL]);
    try
      p := 1;
      for i := 1 to prevrowcount do begin
        for f := 0 to maxf do begin
          Query.Bind(p,FieldTypes[f],FieldValues[f,currentRow],false);
          inc(p);
        end;
        inc(currentRow);
      end;
      Query.ExecutePrepared;
    finally
      Query := nil;
................................................................................
    ftBlob:     BindBlob(Param,VBlob,VBlobLen,IO);
    else raise ESQLDBException.CreateFmt(
      '%s.Bind(Param=%d,VType=%d)',[fStatementClassName,Param,ord(VType)]);
  end;
end;

procedure TSQLDBStatement.Bind(Param: Integer; ParamType: TSQLDBFieldType;
  const Value: RawUTF8; ValueAlreadyUnquoted: boolean; IO: TSQLDBParamInOutType=paramIn);
var tmp: RawUTF8;
begin
  if (not ValueAlreadyUnquoted) and (Value='null') then
    // bind null (ftUTF8 should be '"null"')
    BindNull(Param,IO) else
    case ParamType of
      ftNull:     BindNull(Param,IO);
      ftInt64:    Bind(Param,GetInt64(pointer(Value)),IO);
      ftDouble:   Bind(Param,GetExtended(pointer(Value)),IO);
      ftCurrency: BindCurrency(Param,StrToCurrency(pointer(Value)),IO);
      ftBlob:     BindBlob(Param,Value,IO); // already decoded
      ftDate: begin
        if ValueAlreadyUnquoted then
          tmp := Value else
          UnQuoteSQLString(pointer(Value),tmp);
        BindDateTime(Param,Iso8601ToDateTime(tmp),IO);
      end;
      ftUTF8:
        if ((Value='') or (Value=#39#39)) and
            fConnection.fProperties.StoreVoidStringAsNull then
          BindNull(Param,IO) else begin
          if ValueAlreadyUnquoted then
            tmp := Value else
            UnQuoteSQLString(pointer(Value),tmp);
          BindTextU(Param,tmp,IO);
        end;
      else raise ESQLDBException.CreateFmt('Invalid %s.Bind(%d,TSQLDBFieldType(%d),%s)',
        [fStatementClassName,Param,ord(ParamType),Value]);
    end;
end;

procedure TTestExternalDatabase.Test(StaticVirtualTableDirect: boolean);
var RInt: TSQLRecordPeople;
    RExt: TSQLRecordPeopleExt;
    RBlob: TSQLRecordOnlyBlob;
    Tables: TRawUTF8DynArray;
    aID, i, n: integer;
    ok: Boolean;
    BatchID: TIntegerDynArray;
    aExternalClient: TSQLRestClientDB;
    fProperties: TSQLDBConnectionProperties;
    Start, Updated: TTimeLog; // will work with both TModTime and TCreateTime properties
begin
  // run tests over an in-memory SQLite3 external database (much faster than file)
  fProperties := TSQLDBSQLite3ConnectionProperties.Create(SQLITE_MEMORY_DATABASE_NAME,'','','');
  Check(VirtualTableExternalRegister(fExternalModel,TSQLRecordPeopleExt,fProperties,'PeopleExternal'));
  Check(VirtualTableExternalRegister(fExternalModel,TSQLRecordOnlyBlob,fProperties,'OnlyBlobExternal'));
  Check(VirtualTableExternalRegister(fExternalModel,TSQLRecordTestJoin,fProperties,'TestJoinExternal'));
  Check(VirtualTableExternalRegister(fExternalModel,TSQLASource,fProperties,'SourceExternal'));
  Check(VirtualTableExternalRegister(fExternalModel,TSQLADest,fProperties,'DestExternal'));
  Check(VirtualTableExternalRegister(fExternalModel,TSQLADests,fProperties,'DestsExternal'));



  DeleteFile('testExternal.db3'); // need a file for backup testing 
  aExternalClient := TSQLRestClientDB.Create(fExternalModel,nil,'testExternal.db3',TSQLRestServerDB);
  try
    aExternalClient.Server.DB.Synchronous := smOff;
    aExternalClient.Server.DB.LockingMode := lmExclusive;
    aExternalClient.Server.DB.GetTableNames(Tables);
    Check(Tables=nil); // we reset the testExternal.db3 file
................................................................................
      Check(not aExternalClient.TableHasRows(TSQLRecordPeopleExt));
      Check(aExternalClient.TableRowCount(TSQLRecordPeopleExt)=0);
      Check(not aExternalClient.Server.TableHasRows(TSQLRecordPeopleExt));
      Check(aExternalClient.Server.TableRowCount(TSQLRecordPeopleExt)=0);
      RExt := TSQLRecordPeopleExt.Create;
      try
        n := 0;
        aID := 0;
        while RInt.FillOne do begin
          if RInt.fID<100 then // some real entries for backup testing 
            aExternalClient.Add(RInt,true,true);
          RExt.Data := RInt.Data;
          RExt.FirstName := RInt.FirstName;
          RExt.LastName := RInt.LastName;
          RExt.YearOfBirth := RInt.YearOfBirth;
................................................................................
            Check(RExt.LastName=RInt.LastName);
            Check(RExt.YearOfBirth=RInt.YearOfBirth);
            Check(RExt.YearOfDeath=RInt.YearOfDeath);
            Check(RExt.YearOfBirth<>RExt.YearOfDeath);
          end;
        end;
        Updated := aExternalClient.ServerTimeStamp;
        for i := 1 to aID do
          if i mod 100=0 then begin
            RExt.fLastChange := 0;
            RExt.CreatedAt := 0;
            Check(aExternalClient.Retrieve(i,RExt,true),'for update');
            Check(RExt.YearOfBirth<>RExt.YearOfDeath);
            Check(RExt.CreatedAt<=Updated);
            RExt.YearOfBirth := RExt.YearOfDeath;





            Check(aExternalClient.Update(RExt),'Update 1/100 rows');
            Check(aExternalClient.UnLock(RExt));
            Check(RExt.LastChange>=Updated);
            RExt.ClearProperties;
            Check(RExt.YearOfDeath=0);
            Check(RExt.YearOfBirth=0);
            Check(RExt.CreatedAt=0);
            Check(aExternalClient.Retrieve(i,RExt),'after update');
            Check(RExt.YearOfBirth=RExt.YearOfDeath);
            Check(RExt.CreatedAt>=Start);
            Check(RExt.CreatedAt<=Updated);
            Check(RExt.LastChange>=Updated);
          end;



        for i := 1 to aID do
          if i and 127=0 then





            Check(aExternalClient.Delete(TSQLRecordPeopleExt,i),'Delete 1/128 rows');


        n := aExternalClient.TableRowCount(TSQLRecordPeople);

        Check(aExternalClient.Server.StaticVirtualTable[TSQLRecordPeople]=nil);
        Check(aExternalClient.Server.StaticVirtualTable[TSQLRecordPeopleExt]<>nil);
        Check(aExternalClient.Server.StaticVirtualTable[TSQLRecordOnlyBlob]<>nil);
        aExternalClient.Server.BackupGZ(aExternalClient.Server.DB.FileName+'.gz');
        Check(aExternalClient.Server.StaticVirtualTable[TSQLRecordPeople]=nil);
        Check(aExternalClient.Server.StaticVirtualTable[TSQLRecordPeopleExt]<>nil);
        Check(aExternalClient.Server.StaticVirtualTable[TSQLRecordOnlyBlob]<>nil);
        for i := 1 to aID do begin
          RExt.fLastChange := 0;
          RExt.CreatedAt := 0;
          RExt.YearOfBirth := 0;
          ok := aExternalClient.Retrieve(i,RExt,false);
          Check(ok=(i and 127<>0),'deletion');
          if ok then begin
            Check(RExt.CreatedAt>=Start);

procedure TTestExternalDatabase.Test(StaticVirtualTableDirect: boolean);
var RInt: TSQLRecordPeople;
    RExt: TSQLRecordPeopleExt;
    RBlob: TSQLRecordOnlyBlob;
    Tables: TRawUTF8DynArray;
    i,n, aID: integer;
    ok: Boolean;
    BatchID,BatchIDUpdate: TIntegerDynArray;
    aExternalClient: TSQLRestClientDB;
    fProperties: TSQLDBConnectionProperties;
    Start, Updated: TTimeLog; // will work with both TModTime and TCreateTime properties
begin
  // run tests over an in-memory SQLite3 external database (much faster than file)
  fProperties := TSQLDBSQLite3ConnectionProperties.Create(SQLITE_MEMORY_DATABASE_NAME,'','','');
  Check(VirtualTableExternalRegister(fExternalModel,TSQLRecordPeopleExt,fProperties,'PeopleExternal'));
  Check(VirtualTableExternalRegister(fExternalModel,TSQLRecordOnlyBlob,fProperties,'OnlyBlobExternal'));
  Check(VirtualTableExternalRegister(fExternalModel,TSQLRecordTestJoin,fProperties,'TestJoinExternal'));
  Check(VirtualTableExternalRegister(fExternalModel,TSQLASource,fProperties,'SourceExternal'));
  Check(VirtualTableExternalRegister(fExternalModel,TSQLADest,fProperties,'DestExternal'));
  Check(VirtualTableExternalRegister(fExternalModel,TSQLADests,fProperties,'DestsExternal'));
  fExternalModel.Props[TSQLRecordPeopleExt].ExternalDB.
    MapField('ID','Key').
    MapField('YearOfDeath','YOD');
  DeleteFile('testExternal.db3'); // need a file for backup testing 
  aExternalClient := TSQLRestClientDB.Create(fExternalModel,nil,'testExternal.db3',TSQLRestServerDB);
  try
    aExternalClient.Server.DB.Synchronous := smOff;
    aExternalClient.Server.DB.LockingMode := lmExclusive;
    aExternalClient.Server.DB.GetTableNames(Tables);
    Check(Tables=nil); // we reset the testExternal.db3 file
................................................................................
      Check(not aExternalClient.TableHasRows(TSQLRecordPeopleExt));
      Check(aExternalClient.TableRowCount(TSQLRecordPeopleExt)=0);
      Check(not aExternalClient.Server.TableHasRows(TSQLRecordPeopleExt));
      Check(aExternalClient.Server.TableRowCount(TSQLRecordPeopleExt)=0);
      RExt := TSQLRecordPeopleExt.Create;
      try
        n := 0;

        while RInt.FillOne do begin
          if RInt.fID<100 then // some real entries for backup testing 
            aExternalClient.Add(RInt,true,true);
          RExt.Data := RInt.Data;
          RExt.FirstName := RInt.FirstName;
          RExt.LastName := RInt.LastName;
          RExt.YearOfBirth := RInt.YearOfBirth;
................................................................................
            Check(RExt.LastName=RInt.LastName);
            Check(RExt.YearOfBirth=RInt.YearOfBirth);
            Check(RExt.YearOfDeath=RInt.YearOfDeath);
            Check(RExt.YearOfBirth<>RExt.YearOfDeath);
          end;
        end;
        Updated := aExternalClient.ServerTimeStamp;
        for i := 1 to BatchID[high(BatchID)] do
          if i mod 100=0 then begin
            RExt.fLastChange := 0;
            RExt.CreatedAt := 0;
            Check(aExternalClient.Retrieve(i,RExt,true),'for update');
            Check(RExt.YearOfBirth<>RExt.YearOfDeath);
            Check(RExt.CreatedAt<=Updated);
            RExt.YearOfBirth := RExt.YearOfDeath;
            if RInt.fID>4000 then begin
              if aExternalClient.BatchCount=0 then
                aExternalClient.BatchStart(TSQLRecordPeopleExt);
              Check(aExternalClient.BatchUpdate(RExt)>=0,'BatchUpdate 1/100 rows');
            end else begin
              Check(aExternalClient.Update(RExt),'Update 1/100 rows');
              Check(aExternalClient.UnLock(RExt));
              Check(RExt.LastChange>=Updated);
              RExt.ClearProperties;
              Check(RExt.YearOfDeath=0);
              Check(RExt.YearOfBirth=0);
              Check(RExt.CreatedAt=0);
              Check(aExternalClient.Retrieve(i,RExt),'after update');
              Check(RExt.YearOfBirth=RExt.YearOfDeath);
              Check(RExt.CreatedAt>=Start);
              Check(RExt.CreatedAt<=Updated);
              Check(RExt.LastChange>=Updated);
            end;
          end;
        Check(aExternalClient.BatchSend(BatchIDUpdate)=HTML_SUCCESS);
        Check(length(BatchIDUpdate)=110);
        for i := 1 to BatchID[high(BatchID)] do
          if i and 127=0 then
          if i>4000 then begin
            if aExternalClient.BatchCount=0 then
              aExternalClient.BatchStart(TSQLRecordPeopleExt);
            Check(aExternalClient.BatchDelete(i)>=0,'BatchDelete 1/128 rows');
          end else
            Check(aExternalClient.Delete(TSQLRecordPeopleExt,i),'Delete 1/128 rows');
        Check(aExternalClient.BatchSend(BatchIDUpdate)=HTML_SUCCESS);
        Check(length(BatchIDUpdate)=55);
        n := aExternalClient.TableRowCount(TSQLRecordPeople);
        Check(aExternalClient.Server.TableRowCount(TSQLRecordPeopleExt)=10925);
        Check(aExternalClient.Server.StaticVirtualTable[TSQLRecordPeople]=nil);
        Check(aExternalClient.Server.StaticVirtualTable[TSQLRecordPeopleExt]<>nil);
        Check(aExternalClient.Server.StaticVirtualTable[TSQLRecordOnlyBlob]<>nil);
        aExternalClient.Server.BackupGZ(aExternalClient.Server.DB.FileName+'.gz');
        Check(aExternalClient.Server.StaticVirtualTable[TSQLRecordPeople]=nil);
        Check(aExternalClient.Server.StaticVirtualTable[TSQLRecordPeopleExt]<>nil);
        Check(aExternalClient.Server.StaticVirtualTable[TSQLRecordOnlyBlob]<>nil);
        for i := 1 to BatchID[high(BatchID)] do begin
          RExt.fLastChange := 0;
          RExt.CreatedAt := 0;
          RExt.YearOfBirth := 0;
          ok := aExternalClient.Retrieve(i,RExt,false);
          Check(ok=(i and 127<>0),'deletion');
          if ok then begin
            Check(RExt.CreatedAt>=Start);