Datasnap -> MARS conversion

[ertank 27]

Hello,

 

There is that custom build old DataSnap application. It works with Firebird database and only provides 3 methods; GetData, ExecuteQuery, SwitchDatabase. Their use is as their names indicate.

 

All of the methods are called in a single long URL such as

datasnapserver/GetData/SELECT * FROM EMPLOYEE

That returns SQL result as JSON string. Other 2 methods simply return True/False and error message if False.

 

As the nature of the GetData method, it sometimes gets quite long and complicated URL including all that JOINs, WHERE and conditions added.

 

I wonder if similar can be done using MARS, and have it handle returned data to be filled in a memory table.

 

This maybe a long shot, but I also would like to put all these SQLs in body of the GET request, if such a thing is possible.

 

Thanks.

[Arnaud Bouchez 407]

Putting the SQL in the URI is IMHO a way (and unsecure) way of defining a service.
Leaking the database access (and structure) is a bad practice.

This is not REST-oriented at all - see https://en.wikipedia.org/wiki/Representational_state_transfer

 

The first step would be to put the SQL in the REST body, not in the URI.

Then there is no reason why MARS shouldn't be able to execute it.

[Andrea Magni 75]

@Arnaud Bouchez great to see you chiming in here :-)

 

@ertank I agree with Arnaud that having the client sending the SQL statements to be executed on the server is basically breaking the separation between layers in the multi-tier system and this has consequences (changes in client and/or db structure are probably deeply tied).

It is easy though to implement what you are asking for:

1) find a way to send SQL statements to the server (I would probably use a JSON structure in the body of a POST request)

2) grab the list (or the single) of SQL statements from the body

3) build an array of corresponding datasets

4) return that array to the client

Extra: if you need to modify data and you are building a Delphi-To-Delphi system (Delphi client, Delphi server), you can even consider then posting back delta changes to the server and have these applied as in a C/S scenario (MARS has FireDAC integration for this task, just ask if it's your case).

 

Simple example:

0) bootstrap a new MARS server application (using MARSCmd) or use an existing one

1) include these units:   Data.DB, MARS.Data.FireDAC, FireDAC.Phys.FB (or whatever physical driver is needed for your DB)

2) define data structures:

  TStatement = record
    SQL: string;
    name: string;
  end;
  TStatements = TArray<TStatement>;

 

3) define a POST method having TStatements as input and an array of datasets as output:

 

  [Path('helloworld')]
  THelloWorldResource = class
  protected
    [Context] FD: TMARSFireDAC;
  public
    [POST, Path('data'), Produces(TMediaType.APPLICATION_JSON)]
    function GetData([BodyParam] AStatements: TStatements): TArray<TDataSet>;
  end;

implement it:

 

function THelloWorldResource.GetData(AStatements: TStatements): TArray<TDataSet>;
var
  LStatement: TStatement;
begin
  Result := [];

  for LStatement in AStatements do
    Result := Result + [
      FD.SetName<TDataSet>(FD.Query(LStatement.SQL), LStatement.name)
    ];
end;

4) make the POST call from the client side (using MARS Client or whatever other http library available):

 

POST /rest/default/helloworld/data HTTP/1.1
Host: localhost:8080
Accept: application/json
Content-Type: application/json
Host: localhost:8080

[
  {
 	"SQL": "select * from EMPLOYEE order by EMP_NO",
 	"name": "Employees"
  }
, {
 	"SQL": "select * from COUNTRY where COUNTRY like 'I%'",
 	"name": "Country"
  } 
]

 

5) this is what the server response will look like:

{
    "Employees": [
        {
            "EMP_NO": 2,
            "FIRST_NAME": "Robert",
            "LAST_NAME": "Nelson",
            "PHONE_EXT": "250",
            "HIRE_DATE": "1988-12-28T00:00:00.000+01:00",
            "DEPT_NO": "600",
            "JOB_CODE": "VP",
            "JOB_GRADE": 2,
            "JOB_COUNTRY": "USA",
            "SALARY": 105900,
            "FULL_NAME": "Nelson, Robert"
        },
        {
            "EMP_NO": 4,
            "FIRST_NAME": "Robert",
            "LAST_NAME": "Vecchio",
            "PHONE_EXT": "233",
            "HIRE_DATE": "1988-12-28T00:00:00.000+01:00",
            "DEPT_NO": "621",
            "JOB_CODE": "Eng",
            "JOB_GRADE": 2,
            "JOB_COUNTRY": "USA",
            "SALARY": 97500,
            "FULL_NAME": "Vecchio, Robert"
        }
    ],
    "Country": [
        {
            "COUNTRY": "Italy",
            "CURRENCY": "Lira"
        }
    ]
}

 

To materialize back data into datasets on the client side you can use several strategies (including the Delphi REST Client library TRESTResponseDataSetAdapter, either using REST Client library components or MARS Client library components to perform the request). If you are using FireDAC on the client side and you can afford relying on having the same FireDAC version on client and server, consider also using the TMARSFDResource component (see MARS' FireDAC Basic demo).

 

Sincerely,

Andrea

 

[ertank 27]

Hello,

 

Thanks for step by step instructions.

4 hours ago, Andrea Magni said:

 

  TStatement = record
    SQL: string;
    name: string;
  end;
  TStatements = TArray<TStatement>;

 

I am informed that I do not need multiple SQL results to be returned at once. So I choose not to use an array here.

4 hours ago, Andrea Magni said:

4) make the POST call from the client side (using MARS Client or whatever other http library available):

That will be a "Delphi to Delphi" solution. I would like to use MARS Client for requests.

I have following setup in DataModule: MARSApplication -> MARSClient1 -> MARSFDResource1

One question here: How should I set POST BODY (TStatement) before I call MARSFDResource1.POST() or MARSClient1.Post()?

 

I am not even sure which component to use for doing post request here. I would like MARSFDResource1 to fill up TFDMemTable right after receiving response.

 

Sorry for all these newbie questions. I hope some other new starters will benefit in the future 🙂

 

Thanks & regards,

Ertan

[Andrea Magni 75]

TMARSFDResource is tightly coupled with a server side resource that has this interface:

 

    [GET, IsReference]
    function Retrieve: TArray<TFDDataSet>;
    [POST]
    function Update([BodyParam] const ADeltas: TArray<TFDMemTable>): TArray<TMARSFDApplyUpdatesRes>;

 

One example of such resource is TMARSFDDataModuleResource (MARS.Data.FireDAC.DataModule.pas) that is the base class of the datamodule in FireDAC Basic demo.

The logic of this resource does not allow the client to push statements to server (the Retrieve method is marked with GET verb so you don't have body available to send statements to be executed to sever side). Also, the Update method needs to know the structure (statements) of dataset in order to proprerly apply deltas to them.

 

You can build a different resource on the same idea but adding statements as input to both methods. The Retrieve part we already covered in the previous message (using POST verb). You may want to have a PUT verb to apply updates.

On the client side, you can use a TMARSClientResourceJSON and the TFDDataSets utility class (MARS.Data.FireDAC.Utils unit) to manage persistence of an array of datasets and/or an array of deltas (have a look at MARS.Client.FireDAC unit for some examples).

 

As said, it is a bit unusual to send statements to be executed from the client to the server so there's no specific integration in MARS for this.

It should not be hard to implement and all building blocks are already there. However if you find difficulties just write on this forum and I'll be happy to help.

 

Sincerely,

Andrea

 

[ertank 27]

It will be easier for me to use any preferred http component on client side. I am going to have one dataset per request, always. Now, I have some JSON received at client like following:

[
    {
        "EMP_NO": 2,
        "FIRST_NAME": "Robert",
        "LAST_NAME": "Nelson",
        "PHONE_EXT": "250",
        "HIRE_DATE": "1988-12-28T00:00:00.000+02:00",
        "DEPT_NO": "600",
        "JOB_CODE": "VP",
        "JOB_GRADE": 2,
        "JOB_COUNTRY": "USA",
        "SALARY": 105900,
        "FULL_NAME": "Nelson, Robert"
    },
    {
        "EMP_NO": 4,
        "FIRST_NAME": "Bruce",
        "LAST_NAME": "Young",
        "PHONE_EXT": "233",
        "HIRE_DATE": "1988-12-28T00:00:00.000+02:00",
        "DEPT_NO": "621",
        "JOB_CODE": "Eng",
        "JOB_GRADE": 2,
        "JOB_COUNTRY": "USA",
        "SALARY": 97500,
        "FULL_NAME": "Young, Bruce"
    }
]

In brief, I am getting what I want. Now, I would like to make life much easier on client side to manipulate that data and decrease lines of code. That is if such a thing is possible...

 

As this is a simple JSON string, there is no datatype information like TDateTime, Integer, Double, etc. Is it possible to receive raw memory table data which contains all necessary information to build back easily TDataSet or TFDMemTable at client side? My current server code is as following:

unit Server.Resources;

interface

uses
  SysUtils, Classes, MARS.Core.Attributes, MARS.Core.MediaType, MARS.Core.JSON, MARS.Core.Response, MARS.Core.URL, MARS.Core.Token.Resource, Data.DB, MARS.Data.FireDAC, FireDAC.Phys.FB;

type
  TStatement = record
    SQL: string;
    Name: string;
  end;

  [Path('helloworld')]
  THelloWorldResource = class
  protected
    [Context] FD: TMARSFireDAC;
  public
    [POST, Produces(TMediaType.APPLICATION_JSON)]
    function GetData([BodyParam] AStatement: TStatement): TDataSet;
  end;

  [Path('token')]
  TTokenResource = class(TMARSTokenResource)
  end;

implementation

uses
    MARS.Core.Registry;

{ THelloWorldResource }

function THelloWorldResource.GetData(AStatement: TStatement): TDataSet;
begin
  Result := FD.SetName<TDataSet>(FD.Query(AStatement.SQL), AStatement.Name);
end;

initialization
  TMARSResourceRegistry.Instance.RegisterResource<THelloWorldResource>;
  TMARSResourceRegistry.Instance.RegisterResource<TTokenResource>;
end.

 

I already tried and failed to convert [POST, Produces(TMediaType.APPLICATION_JSON)] to [POST, Produces(TMediaType.APPLICATION_JSON_FireDAC)] which I suppose is what I need to achieve above. I solved one error and that lead to another one. Just could not solve them all.

 

Again, this is a Delphi to Delphi solution. Client here is FMX mobile. Data is for reporting purposes and it is read-only on client.

 

Thanks for all your help.

 

Regards,

Ertan
 

[Andrea Magni 75]

Change TDataSet to TFDDataSet and you'll be able to produce both TMediaType.APPLICATION_JSON and TMediaType.APPLICATION_JSON_FireDAC (MARS will match what client prefer, looking for the Accept header's value):

  [POST]
  function GetData([BodyParam] AStatement: TStatement): TFDDataSet;

function THelloWorldResource.GetData(AStatement: TStatement): TFDDataSet;
begin
  Result := FD.SetName<TFDDataSet>(FD.Query(AStatement.SQL), AStatement.Name);
end;

 

Beware to remove the explicit Produces attribute you had.

You may want to use a REST debugger (I like Postman) to make a call using Accept: application/json-firedac or Accept: application/json to see the two serializations.

 

Now you can add a TMARSClientResourceJSON to your client application, note the SpecificAccept header where I am instructing to set Accept header to application/json-firedac media type:

object MARSClientResourceJSON1: TMARSClientResourceJSON
  Application = MARSApplication
  SpecificAccept = 'application/json-firedac'
  SpecificContentType = 'application/json'
  Resource = 'helloworld'
end

Then use the following code (TMainDataModule is a datamodule containing the REST components, TMARSClient, TMARSApplication, TMARSClientResourceJSON):

 

uses MARS.Core.JSON, MARS.Core.Utils, MARS.Data.FireDAC.Utils;

procedure TMainDataModule.RetrieveData(const ASQL, AName: string;
  const AOnCompleted: TProc<TFDMemTable>; const AOnError: TProc<Exception> = nil);
begin
  MARSClientResourceJSON1.POSTAsync(
    procedure (ABody: TMemoryStream)
    var
      LJSON: TJSONObject;
    begin
      LJSON := TJSONObject.Create;
      try
        LJSON.WriteStringValue('SQL', ASQL);
        LJSON.WriteStringValue('name', AName);

        JSONValueToStream(LJSON, ABody);
      finally
        LJSON.Free;
      end;
    end
  , procedure (ARes: TMARSClientCustomResource)
    var
      LData: TArray<TFDMemTable>;
    begin
      LData := TFDDataSets.FromJSON((ARes as TMARSClientResourceJSON).Response as TJSONObject);
      try
        if (Length(LData)>0) and Assigned(AOnCompleted) then
          AOnCompleted(LData[0]);
      finally
        TFDDataSets.FreeAll(LData);
      end;
    end
  , AOnError
  );
end;

 

Then you can have a call like:

 

  MainDataModule.RetrieveData('select * from EMPLOYEE', 'Employee'
  , procedure (ADataSet: TFDMemTable)
    begin
      FDMemTable1.Data := ADataSet.Data; // cloning dataset
    end
  , procedure (AError: Exception)
    begin
      ShowMessage(AError.ToString);
    end
  );

 

FDMemTable1 is a TFDMemTable to store data on the client side.

 

Please consider:

1) code could have been more simple than this but I wanted to include the fact this call is actually made asynchronously (so if you need to transfer a large dataset or connection is not very fast, you are not going to hang your client application);

2) this is a good example how to retrieve a dataset and then have a single (synchronized) point where the resulting dataset is then cloned to a local TFDMemTable that can be easily involved in databinding (LiveBindings) without being affected by the async call.

 

Please let me know if this helps.

Sincerely,

Andrea

 

 

[ertank 27]

I knew that there has to be an easier way. That did work nicely.

 

I have one question standing in my mind. As I know current code in mobile app, I may need to make synchronous calls to server, just because to let user know there is something going on at the moment (screen wait text is changing with each SQL command run). I appreciate an example on a synchronous call, please.

 

I am starting to implement my UPDATE/INSERT/DELETE handling now. I believe that will be easier having all these SELECT working. They will reply as a simple True/False after all.

 

Thank you.