mORMot Open Source

bagusprasojo

Member

Registered: 2016-07-26

Posts: 18

How to display Data of a model in DBGrid ?

I have 2 models :

TUOM = class(TSQLRecord)
  private
    FKode: RawUTF8;
    FNama: RawUTF8;
  protected

  published
    property Kode: RawUTF8 index 10 read FKode write FKode;
    property Nama: RawUTF8 read FNama write FNama;
  end;

  TAssetProperty = class(TSQLRecord)
  private
    FDefaultValue: RawUTF8;
    FDescription: RawUTF8;
    FKode: RawUTF8;
    FNama: RawUTF8;
    FUOM: TUOM;
  protected
  public
  published
    property DefaultValue: RawUTF8 read FDefaultValue write FDefaultValue;
    property Description: RawUTF8 read FDescription write FDescription;
    property Kode: RawUTF8 index 10 read FKode write FKode;
    property Nama: RawUTF8 read FNama write FNama;
    property UOM: TUOM read FUOM write FUOM;
  end;

I want to display TAssetProperty into DBGrid, here is my code :

sSQL := 'select Kode, nama, DefaultValue, Description, uom from AssetProperty'  ;

  if cxGridDBTableProp.DataController.DataSource = nil then
    cxGridDBTableProp.DataController.DataSource := TDataSource.Create(Self);

  lSQLTable := Client.ExecuteList([], sSQL);
  cxGridDBTableProp.DataController.DataSource.DataSet := TSynSQLTableDataSet.Create(Self, lSQLTable);

  cxGridDBTableProp.DataController.CreateAllItems(True);

with above code, it displays all data TAssetProperty but Field UOM will display UOM.ID. I want to display UOM.Kode. Then i change the code :

//sSQL := 'select Kode, nama, DefaultValue, Description, uom from AssetProperty'  ;

  sSQL := 'select 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';

with the above code I get Access Violation. Could you give a solution to get what i want ?

Thank You

Last edited by bagusprasojo (2016-07-26 04:45:12)

AOG

Member

Registered: 2014-02-24

Posts: 490

Re: How to display Data of a model in DBGrid ?

Have you tried to run your SQL on your table from within some kind of database test environment, to validate your SQL string itself ?
(for SQLite3, I use http://sqlitebrowser.org/)

sSQL := 'select a.Kode, a.Nama, a.DefaultValue, a.Description , b.Kode as UOMKode ' + <-----
          ' from AssetProperty a' +
          ' LEFT JOIN UOM b on a.UOM = b.ID ' + <------
          ' ORDER BY a.Nama;'; <----

bagusprasojo

Member

Registered: 2016-07-26

Posts: 18

Re: How to display Data of a model in DBGrid ?

I use SQL Server as my database. I executed the SQL and it's executed well.

Thank You

AOG

Member

Registered: 2014-02-24

Posts: 490

Re: How to display Data of a model in DBGrid ?

Then you have to dig deeper : what is the output (JSON) of :lSQLTable.GetJSONValues, after you executed your SQL ?

bagusprasojo

Member

Registered: 2016-07-26

Posts: 18

Re: How to display Data of a model in DBGrid ?

Hi AOG, I execute SQL in SQL Server management Studio. It's executed well.

AOG

Member

Registered: 2014-02-24

Posts: 490

Re: How to display Data of a model in DBGrid ?

For sure.
And, for your info, I have executed much more complicated SQL with the mORMot, without any problem.
So, you will be able to reach your goal ! But you have to debug every single step to get to your particular problem.

lSQLTable := Client.ExecuteList([], sSQL);
writeln(lSQLTable.GetJSONValues(True));

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: How to display Data of a model in DBGrid ?

ExecuteList() is only a fail-over, and should not be used in most cases.
And complex queries would work efficiently only for plain SQlite3 databases.
With an external DB, this complex SQL run on ExecuteList() is executed throughout virtual tables, which is not optimized.
See http://synopse.info/files/html/Synopse% … ml#TITL_20

For all such direct statements running on an external DB, bypass the ORM, and use directly the TSynDBProperties connection.
Then encapsulate the SQL execution within a service on server side.
You may define either a method-based service, and an interface-based service.
For an interface-based service, return the content with a RawJSON parameter, then fill a TDataSet with this JSON using e.g. http://synopse.info/files/html/api-1.18 … PROCEDURES

bagusprasojo

Member

Registered: 2016-07-26

Posts: 18

Re: How to display Data of a model in DBGrid ?

Hi AB I did what you say. The result is OK :). Here is my code :
Server Side
Interface

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;

Client Side

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;

Don't forget to register the service in server and client. Thank You.

Last edited by bagusprasojo (2016-07-27 01:23:44)