#1 2015-07-06 16:15:08

EMartin
Member
From: Buenos Aires - Argentina
Registered: 2013-01-09
Posts: 332

Contribution: TSynRestDataset

Hi,

Migrating from RemObjects to mORMot I had to implement a GUI functionality that RemObjects has, an editable dataset connected through URL (RO version 3 use SOAP and other components adapters, etc.). My implementation is basic and the most probably is not the best, but works for me, the same use RESTful URL for get and update data, also get data from a mORMot interface based services returning a mORMot JSON array but cannot update because the table not exists.

   In this google drive link https://drive.google.com/open?id=0Bx7LP … HFpQ04zd0k there are two units: SynRestVCL.pas and SynRestMidasVCL.pas, both have some duplicated code from its counterpart (SynDBVCL.pas and SynDBMidasVCL.pas) and the others, but the rest are modifications with use of RESTful instead of the TSQLDBConnection (this require the database client installed in the client machine).

   A TSQLModel is required because the TSynRestDataset get the fields definition column type and size from this. Also is used from the TSQLRecord the defined validations (I used InternalDefineModel) and the ComputeFieldsBeforeWrite (I used this for default values).

   Example 1: from a table

  // defining the table
  TSQLRecordTest = class(TSQLRecord)
  private
    fDecimal: Double;
    fNumber: Double;
    fTestID: Integer;
    fText: RawUTF8;
    fDateTime: TDateTime;
  protected
    class procedure InternalDefineModel(Props: TSQLRecordProperties); override;
  public
    procedure ComputeFieldsBeforeWrite(aRest: TSQLRest; aOccasion: TSQLEvent); override;
  published
    property Test_ID: Integer read fTestID write fTestID;
    property Text: RawUTF8 index 255 read fText write fText;
    property Date_Time: TDateTime read fDateTime write fDateTime;
    property Number: Double read fNumber write fNumber;
    property Decimal_: Double read fDecimal write fDecimal;
  end;

  ...

  { TSQLRecordTest }

  procedure TSQLRecordTest.ComputeFieldsBeforeWrite(aRest: TSQLRest; aOccasion: TSQLEvent);
  begin
    inherited;
    fDateTime := Now;
  end;

  class procedure TSQLRecordTest.InternalDefineModel(Props: TSQLRecordProperties);
  begin
    AddFilterNotVoidText(['Text']);
    AddFilterOrValidate('Text', TSynValidateNonNull.Create);
  end;

  // client
  type
    TForm3 = class(TForm)
      DBGrid1: TDBGrid;
      DBNavigator1: TDBNavigator;
      btnOpen: TButton;
      edtURL: TEdit;
      dsRest: TDataSource;
      procedure FormCreate(Sender: TObject);
      procedure btnOpenClick(Sender: TObject);
      procedure DBNavigator1Click(Sender: TObject; Button: TNavigateBtn);
    private
      { Private declarations }
      fRestDS: TSynRestDataset;
    public
      { Public declarations }
    end;

  ...

  procedure TForm3.FormCreate(Sender: TObject);
  begin
    fRestDS := TSynRestDataset.Create(Self);
    fRestDS.Dataset.SQLModel := TSQLModel.Create([TSQLRecordTest], 'root');
    dsRest.Dataset := fRestDS;
  end;

procedure TForm3.btnOpenClick(Sender: TObject);
begin
  fRestDS.Close;
  fRestDS.CommandText := edtURL.Text; // edtURL.Text = 'http://localhost:8888/root/Test/select=*
  fRestDS.Open;
  // you can filter by
  // where: fRestDS.CommandText := edtURL.Text; // edtURL.Text = 'http://localhost:8888/root/Test/select=*&where=CONDITION
  // fRestDS.Open;
  // named parameter: fRestDS.CommandText := edtURL.Text; // edtURL.Text = 'http://localhost:8888/root/Test/select=*&where=:PARAMNAME
  // fRestDS.Params.ParamByName('PARAMNAME').Value := XXX
  // fRestDS.Open;
end;

procedure TForm3.DBNavigator1Click(Sender: TObject; Button: TNavigateBtn);
begin
  if (Button = nbPost) then
    fRestDS.ApplyUpdates(0);
end;

   Example 2: from a service

  // defining the table, the service name and operation name are required
  TSQLRecordServiceName_OperationName = class(TSQLRecord)
  private
    fText: RawUTF8;
  published
    property Text: RawUTF8 index 255 read fText write fText;
  end;

  ...

  // server (the implementation)

  TServiceName =class(TInterfacedObjectWithCustomCreate, IServiceName)
  public
    ...
    // this function can also be function OperationName(const aParamName: RawUTF8): RawUTF8;
    function OperationName(const aParamName: RawUTF8; out aData: RawUTF8): Integer;
    ...
  end;

  ...

  function TServiceName.OperationName(const aParamName: RawUTF8; out aData: RawUTF8): Integer;
  begin
     Result := OK;
     aData := '[{"text":"test"},{"text":"test1"}]';    
  end;

  ...

  // client
  type
    TForm3 = class(TForm)
      DBGrid1: TDBGrid;
      DBNavigator1: TDBNavigator;
      btnOpen: TButton;
      edtURL: TEdit;
      dsRest: TDataSource;
      procedure FormCreate(Sender: TObject);
      procedure btnOpenClick(Sender: TObject);
      procedure DBNavigator1Click(Sender: TObject; Button: TNavigateBtn);
    private
      { Private declarations }
      fRestDS: TSynRestDataset;
    public
      { Public declarations }
    end;

  ...

  procedure TForm3.FormCreate(Sender: TObject);
  begin
    fRestDS := TSynRestDataset.Create(Self);
    fRestDS.Dataset.SQLModel := TSQLModel.Create([TSQLRecordServiceName_OperationName], 'root');
    dsRest.Dataset := fRestDS;
  end;

procedure TForm3.btnOpenClick(Sender: TObject);
begin
  fRestDS.Close;
  fRestDS.CommandText := edtURL.Text; // edtURL.Text = 'http://localhost:8888/root/ServiceName.OperationName?aParamName=XXX
  fRestDS.Open;
  // you can filter by named parameter: 
  // fRestDS.CommandText := edtURL.Text; // 'http://localhost:8888/root/ServiceName.OperationName?aParamName=:aParamName
  // fRestDS.Params.ParamByName('aParamName').Value := XXX
  // fRestDS.Open;
end;

procedure TForm3.DBNavigator1Click(Sender: TObject; Button: TNavigateBtn);
begin
  if (Button = nbPost) then
    fRestDS.ApplyUpdates(0); // raise an error "Cannot update data from a service"
end;

This was developed with Delphi 7 on Windows 7 and probably (almost sure) is not cross platform.

If this servers for others may be the best option will be that @ab integrate this in the framework and make this code more mORMot. Meanwhile I will update on the google drive.

I hope this is helpful to someone.

EMartin.


Esteban

Offline

#2 2015-07-06 17:34:24

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Contribution: TSynRestDataset

We would take a look at this nice piece of code, and eventually integrate it to the framework.

In the meanwhile, we integrated it as third-party sample - feedback is welcome!
See http://synopse.info/fossil/info/3587eefc22

Thanks a lot, Esteban, for sharing!

Offline

#3 2015-07-06 17:57:28

EMartin
Member
From: Buenos Aires - Argentina
Registered: 2013-01-09
Posts: 332

Re: Contribution: TSynRestDataset

Thanks ab, I'll do any update on third-party sample.

EMartin.


Esteban

Offline

#4 2015-07-07 15:49:38

Junior/RO
Member
Registered: 2011-05-13
Posts: 207

Re: Contribution: TSynRestDataset

Very nice. Thank you.

Offline

#5 2015-07-07 16:44:15

EMartin
Member
From: Buenos Aires - Argentina
Registered: 2013-01-09
Posts: 332

Re: Contribution: TSynRestDataset

Thanks Junior/RO, I was looking for something and I only found in RemObjects and DataSnap, this is more basic than these products but works for me.

Best regards.

EMartin.


Esteban

Offline

#6 2015-09-11 18:01:41

EMartin
Member
From: Buenos Aires - Argentina
Registered: 2013-01-09
Posts: 332

Re: Contribution: TSynRestDataset

Hi ab, I did a sample application based on FishFact Delphi demo using TSynRestDataset.

https://www.dropbox.com/s/lrnx4fnzw26gj … t.zip?dl=0

Can you update the sample ?

Thanks.

EMartin.


Esteban

Offline

#7 2015-09-12 12:34:34

miab3
Member
From: Poland
Registered: 2014-10-01
Posts: 188

Re: Contribution: TSynRestDataset

@EMartin,

There are problems in Delphi XE2-32.
Specifically  in SynRestVCL.pas:

 lBlob :=  BlobToTSQLRawBlob(aParams[i].AsBlob);

[DCC Error] SynRestVCL.pas(688): E2250 There is no overloaded version of 'BlobToTSQLRawBlob' that can be called with these arguments

Michal

Offline

#8 2015-09-12 15:54:23

EMartin
Member
From: Buenos Aires - Argentina
Registered: 2013-01-09
Posts: 332

Re: Contribution: TSynRestDataset

I hadn't problem with that, I am using Delphi 7.

ab ?


Esteban

Offline

#9 2015-09-12 17:22:49

AOG
Member
Registered: 2014-02-24
Posts: 490

Re: Contribution: TSynRestDataset

Here you have the files that will (hopefully) work with XE2..8
SynRestMidasVCL.pas: https://drive.google.com/file/d/0B96fg3 … sp=sharing
SynRestVCL.pas: https://drive.google.com/file/d/0B96fg3 … sp=sharing
Good luck !

Last edited by AOG (2015-09-12 17:23:01)

Offline

#10 2015-09-12 18:23:36

miab3
Member
From: Poland
Registered: 2014-10-01
Posts: 188

Re: Contribution: TSynRestDataset

@AOG

Thanks.
Now it compiles.

Michal

Offline

#11 2015-09-13 08:06:21

edwinsn
Member
Registered: 2010-07-02
Posts: 1,215

Re: Contribution: TSynRestDataset

Sounds interesting. Thanks for sharing.


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

#12 2015-09-13 14:30:06

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Contribution: TSynRestDataset

I updated the sample.
http://synopse.info/fossil/info/a1bf5114de
Thanks for sharing!

In fact, you are not using the ORM layer on the server side...
I do not know in fact why it is working, since you do not use the ID field, and I'm confused with your use of string values...
But it is an interesting POV.

Offline

#13 2015-09-13 14:52:35

EMartin
Member
From: Buenos Aires - Argentina
Registered: 2013-01-09
Posts: 332

Re: Contribution: TSynRestDataset

@ab, I think so that I am using the ORM layer and the ID field when building the http://host:port/root/tablename/ID in the PSExecuteStatement function in SynRestVCL.pas (now I haven't the  code by hand), but I will verify this.

I wanted to implement an blob field update but I couldn't find the way of encode the blob content for JSON transmission.


Esteban

Offline

#14 2015-09-17 13:49:55

EMartin
Member
From: Buenos Aires - Argentina
Registered: 2013-01-09
Posts: 332

Re: Contribution: TSynRestDataset

Hi @ab, I downloaded 1.18.1899 and now I have an error in SynRestMidasVCL.pas compiling with my Delphi 7.

The error:

  TSynRestDataSet = class(TCustomClientDataSet)
  protected
    ...
    {$IFNDEF NEXTGEN}
    procedure SetCommandText(Value: WideString); override; // -> In Delphi 7 is DBClient.TCustomClientDataset.SetCommandText(Value: String); override;
    {$ELSE}
    procedure SetCommandText(Value: String); override;
    {$ENDIF !NEXTGEN}
    ...
  end;

I have corrected this:

  TSynRestDataSet = class(TCustomClientDataSet)
  protected
    ...
    {$IFNDEF NEXTGEN}
    {$ifdef ISDELPHIXE2}
    procedure SetCommandText(Value: WideString); override;
    {$else ISDELPHIXE2}
    procedure SetCommandText(Value: String); override;
    {$endif ISDELPHIXE2}
    {$ELSE}
    procedure SetCommandText(Value: String); override;
    {$ENDIF !NEXTGEN}
    ...
  end;

but I am not sure if it's the best way, I don't know the Delphi version when TCustomClientDataset.SetCommandText change from String to WideString. And finally I think this patch was not made by you.

Can you correct this or to implement a better solution ?

Thanks and best regards.

EMartin.


Esteban

Offline

#15 2015-12-09 14:35:53

EMartin
Member
From: Buenos Aires - Argentina
Registered: 2013-01-09
Posts: 332

Re: Contribution: TSynRestDataset

Hi @ab, I made corrections to SynRestMidasVCL.pas and SynRestVCL.pas.

https://drive.google.com/open?id=0Bx7LP … nd2UXFjQ3M

Can you update this ?

Thanks.


Esteban

Offline

#16 2015-12-09 15:35:28

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Contribution: TSynRestDataset

Offline

#17 2015-12-11 21:07:31

EMartin
Member
From: Buenos Aires - Argentina
Registered: 2013-01-09
Posts: 332

Re: Contribution: TSynRestDataset

Hi @ab, I have a small bug fix:

https://drive.google.com/open?id=0Bx7LP … jdLSUFkZm8

Sorry.

Esteban.


Esteban

Offline

#18 2015-12-12 12:57:37

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Contribution: TSynRestDataset

Offline

#19 2015-12-18 20:48:32

miab3
Member
From: Poland
Registered: 2014-10-01
Posts: 188

Re: Contribution: TSynRestDataset

@EMrtin, @ab,

1.18.2193
In the demo, EMartin stopped working Update!!!

Michal

Offline

#20 2015-12-19 00:02:32

EMartin
Member
From: Buenos Aires - Argentina
Registered: 2013-01-09
Posts: 332

Re: Contribution: TSynRestDataset

Strange, I will research it.

Thanks.


Esteban

Offline

#21 2015-12-21 14:55:17

EMartin
Member
From: Buenos Aires - Argentina
Registered: 2013-01-09
Posts: 332

Re: Contribution: TSynRestDataset

It's working for me. I tested modifying Category, Common_Name and Species_Name columns and then post in navigator component, the modifications were applyied. Scrolling not apply updates.

Best regards.


Esteban

Offline

#22 2015-12-21 20:41:22

miab3
Member
From: Poland
Registered: 2014-10-01
Posts: 188

Re: Contribution: TSynRestDataset

@EMartin, @ab,

After the change, and post by dbnavigator I get error:
"rdsBioLife.InternalDataSet: SQL Not supported"

Delphi 10 Seattle, Win32, mORMot 1.18.2202

Michal

Offline

#23 2015-12-22 17:07:41

EMartin
Member
From: Buenos Aires - Argentina
Registered: 2013-01-09
Posts: 332

Re: Contribution: TSynRestDataset

mmm I work with Delphi 7, navigator post event already included apply updates, nothing to change. I have a modification (pending of delivery to @ab) where non-ascii characters raise an error on server side when updating the database. That was my situation working with Firebird.

Sorry for my english.


Esteban

Offline

#24 2015-12-22 19:32:19

miab3
Member
From: Poland
Registered: 2014-10-01
Posts: 188

Re: Contribution: TSynRestDataset

@EMartin, @ab,

A little explained.
Delphi XE2-Win32 does not have this error,
Delphi 10 Seattle-Win32 has it.
(mORMot 1.18.2203)

Michal

Last edited by miab3 (2015-12-22 19:35:01)

Offline

#25 2016-01-25 19:43:01

EMartin
Member
From: Buenos Aires - Argentina
Registered: 2013-01-09
Posts: 332

Re: Contribution: TSynRestDataset

Hi @ab, I uploaded bug fixes:

SynRestVCL.pas
- bug fix when updating accented string fields.
- bug fix with length string fields

https://drive.google.com/open?id=0Bx7LP … jNCS3R3bGM

Can you apply this change ?

Thanks.

Esteban

Last edited by EMartin (2016-05-04 18:21:49)


Esteban

Offline

#26 2016-01-26 09:50:04

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Contribution: TSynRestDataset

It has been applied by http://synopse.info/fossil/info/8dc1d8f87e

Thanks for the input!

Offline

#27 2016-05-04 13:27:22

EMartin
Member
From: Buenos Aires - Argentina
Registered: 2013-01-09
Posts: 332

Re: Contribution: TSynRestDataset

Hi @ab, I uploaded this bug fix:

SynRestVCL.pas
- bug fix with datetime fields

https://drive.google.com/open?id=0Bx7LP … jFVNTYzMG8

Can you apply this change ?

Thanks.


Esteban

Offline

#28 2016-05-09 15:08:30

EMartin
Member
From: Buenos Aires - Argentina
Registered: 2013-01-09
Posts: 332

Re: Contribution: TSynRestDataset

@ab, can you apply this correction ?

Thanks.


Esteban

Offline

#29 2016-05-10 06:52:16

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Contribution: TSynRestDataset

Please see http://synopse.info/fossil/info/84de3cad52

Thanks for the feedback!

Offline

#30 2016-05-13 15:38:04

Junior/RO
Member
Registered: 2011-05-13
Posts: 207

Re: Contribution: TSynRestDataset

Esteban, what is the correct CommandText syntax? Using the syntax from the documentation (Readme.md) this code

http://address:port/root/tablename/select=*

Don't work and give me the exception 'rdsTableName/select=*'' is not a valid component name'.

If I change for tablename?select=* as

http://address:port/root/tablename?select=*   // using ? now

Then it works

Last edited by Junior/RO (2016-05-13 15:41:39)

Offline

#31 2016-05-13 16:13:00

EMartin
Member
From: Buenos Aires - Argentina
Registered: 2013-01-09
Posts: 332

Re: Contribution: TSynRestDataset

Sorry for the mistake in Readme.md, and yes, using ? is the right way as any HTTP GET with parameters.


Esteban

Offline

#32 2016-05-24 15:49:43

Junior/RO
Member
Registered: 2011-05-13
Posts: 207

Re: Contribution: TSynRestDataset

Esteban, Arnaud... I have this situation:

A field in a TSQLRecord descendant that is a TDoubleDynArray, which is retrieved as a blob field by TSynDBDataSet or TSynRestDataSet, or any TSynBinaryDataSet descendant.

type
  TTree = class(TSQLRecord)
  private
    FCircumferences: TDoubleDynArray;
  public
    Circumferences: TDoubleDynArray read FCircumferences write FCircumferences; 
  end;

My question is: How can I convert this TDataset blob field in a dynamic double array? Which functions I use to do this?

Last edited by Junior/RO (2016-05-24 15:51:43)

Offline

#33 2016-05-24 18:19:47

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Contribution: TSynRestDataset

Try function DynArrayLoad() from SynCommons.pas.

Offline

#34 2016-05-24 21:25:57

Junior/RO
Member
Registered: 2011-05-13
Posts: 207

Re: Contribution: TSynRestDataset

A extra step was needed. Works now:

var
  Blob: RawByteString;
  SQLRawBlob: TSQLRawBlob;
  Circumferences: TDoubleDynArray;
begin
  Blob := TreeDataset.FieldByName('Circumferences').AsString;
  SQLRawBlob := BlobToTSQLRawBlob(Blob);
  DynArrayLoad(Circumferences, Pointer(SQLRawBlob), TypeInfo(TDoubleDynArray));
end;

Last edited by Junior/RO (2016-05-24 21:42:27)

Offline

#35 2016-05-30 02:32:33

houdw2006
Member
Registered: 2015-05-23
Posts: 48

Re: Contribution: TSynRestDataset

To many of us, the delphi program developers, TDataset is a good helper for designing the GUI. And the Restful way is better than the db based way to retrieve contents from the background database, that's one reason why we enjoyed using mORMot so much.

I think the TSynRestDataSet class, contributed by EMartin and implemented in the  unit SynRectMidasVCL, is very useful if it can work in a more mORMot way, so I have made some changes to TSynRestSQLDataSet and TSynRestDataSet in the last few days. Thanks, @EMartin, for your contribution.

At present, after insert a new row into the table, the ID field is NOT updated in TSynRestDataSet, and the blob fields are not handled yet. My main idea is that if we use a instance of TSQLRest  as the broker,  instead of using the TWinHTTP instance directly, to handle the insert, delete and update requests, would be a more mORMot way, and should be better, IMHO.


Two event handlers are added, in the private section of TSynRestDataSet, to handle the deletion and insertion event respectively.

TSynRestDataSet = class(TCustomClientDataSet)
private
  procedure DoOnBeforeDelete(aDataSet: TDataSet);
  procedure DoOnAfterInsert(aDataSet: TDataSet);
  .......
public
  ......
  function Execute(const aSQL: RawUTF8): boolean;
  function RetrieveBlob(Table: TSQLRecordClass; aID: TID;
    const BlobFieldName: RawUTF8; out BlobData: TSQLRawBlob): boolean;
  function UpdateBlob(Table: TSQLRecordClass; aID: TID;
    const BlobFieldName: RawUTF8; BlobData: TStream): boolean;
  .....
end;

procedure TSynRestDataSet.DoOnAfterInsert(aDataSet: TDataSet);
begin
  if not (State in [dsEdit, dsInsert]) then Edit;

  // InsertedID is a new added attribute of TSynRestSQLDataSet, and has been assigned to it just before calling this event handler.
  FieldByName('ID').AsInteger := TSynRestSQLDatasetHack(aDataSet).InsertedID;
end;

procedure TSynRestDataSet.DoOnBeforeDelete(aDataSet: TDataSet);
begin
  // fDataSet is a Instance of TSynRestSQLDataSet, and DeletedID is a new added attribute of TSynRestSQLDataSet
  fDataSet.DeletedID := FieldByName('ID').AsInteger;
end;

And these two event handlers are associated with the instance of TSynRestDataSet when it is created.

constructor TSynRestDataSet.Create(AOwner: TComponent);
begin
   .......
  fDataSet.AfterInsert := Self.DoOnAfterInsert;
  Self.BeforeDelete := Self.DoOnBeforeDelete;
end;

The three method, Execute, RetrieveBlob, and UpdateBlob are delegated to the instance of TSynRestSQLDataSet:
function TSynRestDataSet.Execute(const aSQL: RawUTF8): boolean;
begin
  Result := fDataSet.Execute(aSQL);
end;

function TSynRestDataSet.RetrieveBlob(Table: TSQLRecordClass;
  const BlobFieldName: RawUTF8; out BlobData: TSQLRawBlob): boolean;
var
  fID: TID;
begin
  fID := FieldByName('ID').AsInteger;
  Result := fDataSet.RetrieveBlob(Table, fID, BlobFieldName, BlobData);
end;

function TSynRestDataSet.UpdateBlob(Table: TSQLRecordClass;
  const BlobFieldName: RawUTF8; BlobData: TStream): boolean;
var
  fID: Integer;
begin
  fID := FieldByName('ID').AsInteger;
  Result := fDataSet.UpdateBlob(Table, fID, BlobFieldName, BlobData);
end;

Last edited by houdw2006 (2016-05-30 03:19:54)

Offline

#36 2016-05-30 03:14:01

houdw2006
Member
Registered: 2015-05-23
Posts: 48

Re: Contribution: TSynRestDataset

Modification to the TSynRestSQLDataSet Class, implemented in unit SynRestVCL, follows:

  TSynRestSQLDataSet = class(TSynBinaryDataSet)
  private
    fRestClient: TSQLRest;  // mORMot broker for handling delete, insert, and update evnets.

    procedure SetupRestClient;
    procedure TearDownRestClient;
  protected
    .....
  public
  public
    /// initialize the instance
    constructor Create(AOwner: TComponent); override;
    destructor Destroy; override;

    function Execute(const aSQL: RawUTF8): boolean; overload;
    function RetrieveBlob(Table: TSQLRecordClass; aID: TID;
      const BlobFieldName: RawUTF8; out BlobData: TSQLRawBlob): boolean; overload;
    function UpdateBlob(Table: TSQLRecordClass; aID: TID;
      const BlobFieldName: RawUTF8; BlobData: TStream): boolean; overload;

    property DeletedID: TID read fDeletedID write fDeletedID;
    property InsertedID: TID read fInsertedID;
    .....
  end;

constructor TSynRestSQLDataSet.Create(AOwner: TComponent);
begin
  inherited Create(AOwner);

  fRestClient := nil;
end;

destructor TSynRestSQLDataSet.Destroy;
begin
  TearDownRestClient;

  inherited;
end;

procedure TSynRestSQLDataSet.SetCommandText(const Value: string);
begin
  if (Value <> fCommandtext) then
  begin
    fCommandText := Value;
    ParseCommandText;
    SetupRestClient;     // create fRestClient, an instance of TSQLRest;
  end;
end;

The implementation of these three methods of TSynRestSQLDataSet quite simple, just delegate the call to the broker, fRestClient.
function TSynRestSQLDataSet.Execute(const aSQL: RawUTF8): boolean;
begin
  Result := fRestClient.Execute(aSQL);
end;

function TSynRestSQLDataSet.RetrieveBlob(Table: TSQLRecordClass; aID: TID;
  const BlobFieldName: RawUTF8; out BlobData: TSQLRawBlob): boolean;
begin
  Result := fRestClient.RetrieveBlob(Table, aID, BlobFieldName, BlobData);
end;

function TSynRestSQLDataSet.UpdateBlob(Table: TSQLRecordClass; aID: TID;
  const BlobFieldName: RawUTF8; BlobData: TStream): boolean;
begin
  Result := fRestClient.UpdateBlob(Table, aID, BlobFieldName, BlobData);
end;

The most significant part of TSynRestSQLDataSet, is function TSynRestSQLDataSet.PSExecuteStatement.
{$ifdef ISDELPHIXE3} // note: I moved the original local functions to the private section of TSynRestSQLDataSet
function TSynRestSQLDataSet.PSExecuteStatement(const ASQL: string;
  AParams: TParams): Integer;
var DS: TDataSet;
begin
  DS := nil;
  result := PSExecuteStatement(ASQL,AParams,DS);
  DS.Free;
end;

function TSynRestSQLDataSet.PSExecuteStatement(const ASQL:
    string; AParams: TParams; var ResultSet: TDataSet): Integer;
{$else}
function TSynRestSQLDataSet.PSExecuteStatement(const ASQL: string;
    AParams: TParams; ResultSet: Pointer): Integer;
{$endif}
var
  lJSON: SockString;
  lOccasion: TSQLOccasion;

  fID: TID;
  fFieldNames: RawUTF8;
  fRec: TSQLRecord;
begin // only execute writes in current implementation
  Result := -1;
  if IsTableFromService then
    DatabaseError('Cannot apply updates from a service');
  lOccasion := GetSQLOccasion(aSQL);
  case lOccasion of
  soDelete:
    begin
      fID := fDeletedID;  // fDeletedID is set by instance of TSynRestDataSet
      if fRestClient.Delete(GetSQLRecordClass, fID) then
        Result := 1;
    end;
  soInsert:
    begin
      lJSON := SQLFieldsToJSON(soInsert, fFieldNames, aSQL, '(', ') ', aParams);
      fRec := GetSQLRecordClass.CreateFrom(lJSON);
      try
        fInsertedID := fRestClient.Add(fRec, fRec.RecordProps.FieldBitsFromCSV(fFieldNames), True);
        if fInsertedID > 0 then
        begin
          Result := 1;
          AfterInsert(Self);  // Update the ID field in the instance of TSynRestDataSet
        end;
      finally
        fRec.Free;
      end;
    end;
  soUpdate:
    begin
      lJSON := SQLFieldsToJSON(soUpdate, fFieldNames, aSQL, 'set ', 'where ', aParams);
      fRec := GetSQLRecordClass.CreateFrom(lJSON);
      try
        fID := aParams.ParamByName('ID').Value;
        fRec.IDValue := fID;  // fRec.ID is readonly, fRec.IDValue is writable
        if fRestClient.Update(fRec, fRec.RecordProps.FieldBitsFromCSV(fFieldNames)) then
          Result := 1;
      finally
        fRec.Free;
      end;
    end
  end;
end;

And I have added an extra parameter, aFieldNames, to function SQLFieldsToJSON in order to return the related simple column names.
I can't show the code here because of the restriction of post regulation of this forum. (prompt: i was opened within itself, this is not allowed). The related simple column names are returned in the CSV form.

function TSynRestSQLDataSet.SQLFieldsToJSON(const aSQLOccasion: TSQLOccasion;
  var aFieldNames: RawUTF8;
  const aSQL, aAfterStr, aBeforeStr: string; aParams: TParams): SockString;

Offline

#37 2016-05-30 06:45:00

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Contribution: TSynRestDataset

Please do not post huge pieces of code in the forum.
It is bearly readable, and the FluxBB php forum tends to break the web server when you try to modify the content afterwards.

The best is to fork the project on github.
Or supply a link to a public .zip containing the modified files, or use something like http://pastebin.com/

This is clearly asked in the forum rules:

Rules wrote:

7. Please do not post any huge code or log content in the forum: this would load the server DB for nothing. Rather post your data in a remote storage location, like gist, paste.ee, DropBox or Google Drive.

Offline

#38 2016-05-30 07:12:17

houdw2006
Member
Registered: 2015-05-23
Posts: 48

Re: Contribution: TSynRestDataset

Sorry for the crash of web server. I do read the Rules of this forum before, but I don't know exactly how much is huge. I'll pay attention to this rule anyway. I can access Github, and I'll try to learn gist later.

Offline

#39 2016-05-30 14:45:46

EMartin
Member
From: Buenos Aires - Argentina
Registered: 2013-01-09
Posts: 332

Re: Contribution: TSynRestDataset

@houdw2006 I thought about that, injecting the TSQLRestClientURI to the TSynRestDataset instead of create it. I used TWinHTTP because building the URI was more easy, maybe using TSQLRestClientURI.URI(...) function can get the same result and using the other functions from TSQLRestClientURI for updating tables. I developed this with two requirements in mind, can update tables and can call interface based service returning JSON object array as TSQLTable do it.


Esteban

Offline

#40 2016-05-31 01:03:47

houdw2006
Member
Registered: 2015-05-23
Posts: 48

Re: Contribution: TSynRestDataset

@EMartin, This sounds better and hope ab can update the new implementation to the ThirdParty Demo in the near future. And hope that SynRestDataSet becomes a party of Synopse mORMot eventually!

Offline

#41 2016-05-31 06:23:47

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Contribution: TSynRestDataset

Please fork the project and github and submit the patch, or send a link to a .zip archive containing the modified files.
smile

Offline

#42 2016-05-31 11:01:52

EMartin
Member
From: Buenos Aires - Argentina
Registered: 2013-01-09
Posts: 332

Re: Contribution: TSynRestDataset

@ab, this patch https://drive.google.com/open?id=0Bx7LP … jJhay1YN0E corrects memory leak found and solved by @houdw2006.

@houdw2006 my current implementation of TSynRestDataset works for me, I don't send the ID (is generated by the database), I will try to implement the TSQLRestClientURI but right now I have no time. The modifications put into this thread works with interface based service ?


Esteban

Offline

#43 2016-06-11 11:09:54

houdw2006
Member
Registered: 2015-05-23
Posts: 48

Re: Contribution: TSynRestDataset

@EMartin, I have made some modification to the SynRestDataset, an instance of TSQLHttpClient is injected into TSynRestDataset, which can fulfill all the CRUD tasks. I think that using an instance of TSQLRest to access the data is a more mORMot way.

All the details can be accessed by this URI: https://gist.github.com/anonymous/d0700 … 7b928c9eaf

Unfortunately, in this modification, RESTful service is not supported anymore. But the CommandText of TSynRestDataset can be used in a more DELPHI way, such as:

  SynRestDataset.CommandText := 'SELECT * FROM BioLife '
      + 'ORDER BY Species_No ';

In order to support accessing the image data from the blob field, a helper unit, ImageLoader, is provided.

I found an bug in TSynRestSQLDataSet.InternalInitFieldDefs when handling the TEXT field with unlimited length (such as the Notes field, defined by: property Notes: RawUTF8 read fNotes write fNotes;),  the lFieldDef.Size will be set to 20(the default FieldWidth of DB.ftString), but I don't know how to fix this.

Last edited by houdw2006 (2016-06-11 11:13:40)

Offline

#44 2016-06-11 12:04:19

EMartin
Member
From: Buenos Aires - Argentina
Registered: 2013-01-09
Posts: 332

Re: Contribution: TSynRestDataset

Good work Dewen ! I'll test it.

Regarding to text field unlimited length is put any size in the field in ORM table, i.e "property Notes: RawUTF8 index 32767 read fNotes write fNotes" (32K).

Best regards.


Esteban

Offline

#45 2016-07-18 17:56:17

EMartin
Member
From: Buenos Aires - Argentina
Registered: 2013-01-09
Posts: 332

Re: Contribution: TSynRestDataset

Hi @ab, I updated the SynRestVCL.pas:

- added sftSessionUserID to SQLFIELDTYPETODBFIELDTYPE and SQLFieldTypeToVCLDB

https://drive.google.com/file/d/0Bx7LPc … sp=sharing

Can you update it ?

Thanks.


Esteban

Offline

#46 2016-07-19 09:06:42

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Contribution: TSynRestDataset

Offline

#47 2016-08-04 20:31:35

EMartin
Member
From: Buenos Aires - Argentina
Registered: 2013-01-09
Posts: 332

Re: Contribution: TSynRestDataset

Hi @ab, I have reworked the TSynRestDataset (I renamed the folder as SynRESTDataset), this work is based on previous modifications from Dewen Hou (houdw2006). I left the executable files for those that have not Delphi 7.

- reworked based on work of Dewen HOU (houdw2006) with some changes:
  * added again the capability of invoke mORMot interface based service
  * changed TSQLRestClientURI instead of TSQLRest.
  * removed Compute procedure for local ComputeFieldsBeforeWrite because now mORMot have it in server side.
  * removed OnGetURISignature because TSQLRestClientURI generates it 

More information in Readme.md

https://drive.google.com/open?id=0Bx7LP … EZxcUZvbnc

Can you update this ?

Thanks and best regards.


Esteban

Offline

#48 2016-08-05 15:47:11

EMartin
Member
From: Buenos Aires - Argentina
Registered: 2013-01-09
Posts: 332

Re: Contribution: TSynRestDataset

@ab, I made a small bug fix.

- bug fix not sent blob fields on update/insert

https://drive.google.com/open?id=0Bx7LP … EtvMjdJSmc

Dismiss the former thread, this zip have all.

Can you udpdate this ?

Thanks.


Esteban

Offline

#49 2016-08-08 10:44:14

EMartin
Member
From: Buenos Aires - Argentina
Registered: 2013-01-09
Posts: 332

Re: Contribution: TSynRestDataset

Hi @ab, what about this ?

Thanks.


Esteban

Offline

#50 2016-08-08 11:39:53

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,534
Website

Re: Contribution: TSynRestDataset

@EMartin - why you do not commit directly into the repository?  it will be much easier for all. @AB I'm right?

Offline

Board footer

Powered by FluxBB