mORMot Open Source

function TSQLRestStorageExternal.CreateSQLMultiIndex(
  Table: TSQLRecordClass; const FieldNames: array of RawUTF8;
  Unique: boolean; IndexName: RawUTF8): boolean;
var SQL: RawUTF8;
    ExtFieldNames: TRawUTF8DynArray;
    IntFieldIndex: TIntegerDynArray;
    Descending: boolean;
    i,n,extfield: integer;
begin
  ..........................................
      dPostgreSQL,dMSSQL,dMySQL,dOracle,dNexusDB: begin // as most DB on primary key
        result := true;
        exit;
      end;
      dFirebird:  // see http://www.firebirdfaq.org/faq205
        Descending := true;
      end;
   .........................................

function TSQLRestStorageExternal.CreateSQLMultiIndex(
  Table: TSQLRecordClass; const FieldNames: array of RawUTF8;
  Unique: boolean; IndexName: RawUTF8): boolean;
var SQL: RawUTF8;
    ExtFieldNames: TRawUTF8DynArray;
    IntFieldIndex: TIntegerDynArray;
    Descending: boolean;
    i,n,extfield: integer;
begin
  .........................................
      dPostgreSQL,dMSSQL,dMySQL,dOracle,dNexusDB, dFirebird: begin // as most DB on primary key
        result := true;
        exit;
      end;
//      dFirebird:  // see http://www.firebirdfaq.org/faq205
//        Descending := true;
      end;
    .........................................

IRemoteSQL = interface(IInvokable)
  ['{0E34DC10-0A32-4A19-97E4-8012E06D32AA}']
    function Execute(const aSQL: RawUTF8; aExpectResults, aExpanded: Boolean): RawJSON;
  end;

TServiceRemoteSQL = class(TInterfacedObject, IRemoteSQL)
  protected
  public
    destructor Destroy; override;
  public
    function Execute(const aSQL: RawUTF8; aExpectResults, aExpanded: Boolean):
        RawJSON;
  end;
implementation
destructor TServiceRemoteSQL.Destroy;
begin
  inherited;
end;
function TServiceRemoteSQL.Execute(const aSQL: RawUTF8; aExpectResults,
    aExpanded: Boolean): RawJSON;
var
  res: ISQLDBRows;
begin
  if Props=nil then
    raise Exception.Create('Connect call required before Execute');
  res := Props.ExecuteInlined(aSQL,aExpectResults);
  if res = nil then
    result := ''
  else
    result := res.FetchAllAsJSON(aExpanded);
end;

property Service: IRemoteSQL read GetService write FService;

procedure TfrmAssetProperty.LoadAssetProperties;
var
  lSQLTable: TSQLTable;
  sSQL: string;
  lDataSet  : TDataSet;
begin
  if cxGridDBTableProp.DataController.DataSource = nil then
    cxGridDBTableProp.DataController.DataSource := TDataSource.Create(Self);
  sSQL := 'select a.id, a.Kode, a.nama, a.DefaultValue, a.Description , b.kode as UOM ' +
          ' from AssetProperty a' +
          ' LEFT JOIN UOM b on a.UOM = b.id ' +
          ' ORDER BY a.Nama';
  lDataSet := JSONToDataSet(Self,Service.Execute(sSQL,True, True));
  cxGridDBTableProp.DataController.DataSource.DataSet := lDataSet;
end;