Contribution: TSynRestDataset

EMartin · 2015-07-06 16:15:08

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.

ab · 2015-07-06 17:34:24

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!

EMartin · 2015-07-06 17:57:28

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

EMartin.

Junior/RO · 2015-07-07 15:49:38

Very nice. Thank you.

EMartin · 2015-07-07 16:44:15

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.

EMartin · 2015-09-11 18:01:41

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.

miab3 · 2015-09-12 12:34:34

@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

EMartin · 2015-09-12 15:54:23

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

ab ?

AOG · 2015-09-12 17:22:49

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)

miab3 · 2015-09-12 18:23:36

@AOG

Thanks.
Now it compiles.

Michal

edwinsn · 2015-09-13 08:06:21

Sounds interesting. Thanks for sharing.

ab · 2015-09-13 14:30:06

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.

EMartin · 2015-09-13 14:52:35

@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.

EMartin · 2015-09-17 13:49:55

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.

EMartin · 2015-12-09 14:35:53

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

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

Can you update this ?

Thanks.

ab · 2015-12-09 15:35:28

Included as http://synopse.info/fossil/info/606e64463c

Thanks!

EMartin · 2015-12-11 21:07:31

Hi @ab, I have a small bug fix:

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

Sorry.

Esteban.

ab · 2015-12-12 12:57:37

Included as http://synopse.info/fossil/info/287a3dfbec

Thanks!

miab3 · 2015-12-18 20:48:32

@EMrtin, @ab,

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

Michal

EMartin · 2015-12-19 00:02:32

Strange, I will research it.

Thanks.

EMartin · 2015-12-21 14:55:17

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.

miab3 · 2015-12-21 20:41:22

@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

EMartin · 2015-12-22 17:07:41

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.

miab3 · 2015-12-22 19:32:19

@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)

EMartin · 2016-01-25 19:43:01

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)

ab · 2016-01-26 09:50:04

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

Thanks for the input!

EMartin · 2016-05-04 13:27:22

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.

EMartin · 2016-05-09 15:08:30

@ab, can you apply this correction ?

Thanks.

ab · 2016-05-10 06:52:16

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

Thanks for the feedback!

Junior/RO · 2016-05-13 15:38:04

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)

EMartin · 2016-05-13 16:13:00

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

Junior/RO · 2016-05-24 15:49:43

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)

ab · 2016-05-24 18:19:47

Try function DynArrayLoad() from SynCommons.pas.

Junior/RO · 2016-05-24 21:25:57

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)

houdw2006 · 2016-05-30 02:32:33

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)

houdw2006 · 2016-05-30 03:14:01

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;

ab · 2016-05-30 06:45:00

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.

houdw2006 · 2016-05-30 07:12:17

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.

EMartin · 2016-05-30 14:45:46

@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.

houdw2006 · 2016-05-31 01:03:47

@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!

ab · 2016-05-31 06:23:47

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

EMartin · 2016-05-31 11:01:52

@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 ?

houdw2006 · 2016-06-11 11:09:54

@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)

EMartin · 2016-06-11 12:04:19

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.

EMartin · 2016-07-18 17:56:17

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.

ab · 2016-07-19 09:06:42

Please check http://synopse.info/fossil/info/c3e479bc64

Thanks!

EMartin · 2016-08-04 20:31:35

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

mORMot Open Source

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

Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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

Re: Contribution: TSynRestDataset

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