TWebCore and XDATA

BIZ TMS XData
1

Hi,


I try to learn Web-programming by TWebCore. In past I design vcl, only.

I have a connection to the MySQL-Server and can get the data from an easy query.
Server-Connection:
TFDConnection
TAureliusConnection
TFDPhysMySQLDriverLink

WebCore Project
TXDataWebDataSet

When I understand it right, I have to learn the 'new' query-language from xdata. 
For examble: '$filter=AbteilungsID gt 40' in stead of 'select * from table where AbteilungsID>40'
Is that right?

If I have to use this query-language, can you give me support for join-statements?
When I like to link table in my query. 

Table_Language
ID----------LANGUAGE
1----------- gernam
2------------english

Table_User
ID----------Name----------Language_ID
1----------- Max---------------2
2-----------Moriz-------------1

In vcl I solved it like
select u.name, l.language from table_user u
left join table_language l on l.id=u.language_id
where u.id=1

Result would be Max, english

How can I solve that in the WebCore environment?

Many thanks 
Patrick

2

Hello Patrick,

The associations (joins) are defined in the model itself. You will have two classes, TUser and TLanguage, for example, and your class TUser will be something like this:

TUser = class
  ID: Integer;
  Name: string;
  Language: TLanguage;

Thus when you query your users, each user object will have a Language property with all the TLanguage data contained in it.
More info here: http://www.tmssoftware.biz/business/aurelius/doc/web/lazy-loading_associations.html

Note that this XData approach is just one way to use it. You can also, alternatively, just create service operation where you add your own business logic to it (using raw SQL server and using FDQuery, if you want), and then fill your own objects and return the objects in the method result.
3

Hi,


I am back from holiday...

Thank you very much for your replay. I like to test the way with raw MySQL statements and the FDQuery. 

I have a TMS WEB App + XData Server Group. The Server is connected with that database. How can I create a connection between the FDQuery to the WebServer?

I tried to use the FDQuery and a FDConnection in a TMS Web Application without the server, but then I get a fatal error because the FireDAC.Stan.Intf and others file are not found?

Is there a description or an example available where I can learn the different way of DB (MySQL) Connection?

Many thanks
Patrick
4

Can you explain me, why the FDPhysMySQLDriverLink is only available in a connection module? In a TMS WebCore Project it is not, I suppose.


Thanky
Patrick
5

You can't use FireDac directly in a TMS Web Core application. The Web Core application must communicate via HTTP with the server, and then the server will communicate with the database.

More info here: 
https://www.youtube.com/watch?v=AZs9e2DNXdI
and here:
http://www.tmssoftware.biz/business/xdata/doc/web/web-applications-tms-web-core.html
6

Hi Wagner,


thank you for replay!
I realized the co0nnection between the TMS WeCore Project via the XDataServer to the database, before. That works. But I have problems with the needed SQL-statement-syntax. I like to use raw sql, like you mentioned before. 

Your post: ...  You can also, alternatively, just create service operation where you add your own business logic to it (using raw SQL server and using FDQuery, if you want) ...

How can I use raw sql in a project with TMS WebCore. Can I use it with the XData Server? 

In the documentation A could not find anything to FDQuery...
I have really problems to start working with it and I thank you for support!
Patrick
7

Maybe this video helps?

https://youtu.be/2SCvzw0L27op
8

The video is great tutorial! In the last days, I had a strong learncurve. Thanks


I was able to rebuild the example in my environment and it works nice with a vcl application. Afterwards I tried to rebuild it as a webapplication, but I failed. I get an error message in the included interface of the xdatawebservice:  "[Fehler] uData_xChangeService.pas(6): can't find unit "XData.Server.Module" 
and 
"[Fehler] uData_xChangeService.pas(7): can't find unit "XData.Service.Common"

Can you support me, to understand, what I have to do different?

Many thanks

This is the whole unit:
unit uData_xChangeService;

interface

uses
   XData.Server.Module,
   XData.Service.Common,
   System.classes;

type
   [ServiceContract]
   IData_xChangeService = interface(IInvokable)
      ['{D0943D39-176C-4F8A-826E-0ECD69880CA0}']

      [HTTPGet]
      function Get_Abteilung: TStream;
      [HTTPGet]
      function Get_DLZHistorie: TStream;
  end;

implementation

initialization
   RegisterServiceType(TypeInfo(IData_xChangeService));

end.
9

Forget my last question. I found this tutorial, where it is descriped very clearly. Great one!


I understood, that a can use "string(TJSObject(Response.Result)['value'])" to pickup a single value. But I am struggling to get a list of values.

How can I fill a xDataWebDataSet with response?

Many thanks
Patrick
10

The link of the tutorial: https://flixengineering.com/archives/697

11

You can use


xDataWebDataset.SetJsonData(TJSArray(Response.Result))

or

xDataWebDataset.SetJsonData(TJSArray(TJSObject(Response.Result)['value']))

if the response comes wrapped in a JSON object.
12

I tried it and I read the documentation, but I am not able to solve it, sorry!


When I check the Response.ResponseText I can see, that the data are in.

When I try "XDS_Abteilung.SetJsonData(TJSArray(Response.Result));" nothing happens. I wanted to check an fieldvalue by:

XDS_Abteilung.Active:=true;
XDS_Abteilung.First;
Showmessage(XDS_Abteilung.FieldByName('abteilung').AsString);

But I get the error message: "EntitySetName not specified"

When I try fill the DataSet by 
"XDS_Abteilung.SetJsonData(TJSArray(TJSObject(Response.Result)['value']));"
I get the error message: Can not read property 'value' of null as OnResponse

Can you support me?

13

For a better understanding the whole procedure with the service all and the OnResponse procedure...


procedure TFrmMain.btnAbteilungClick(Sender: TObject);
   procedure OnResponse(Response: TXDataClientResponse);
   begin
      showmessage(Response.ResponseText);
      XDS_Abteilung.SetJsonData(TJSArray(Response.Result));
      XDS_Abteilung.Active:=true;
      XDS_Abteilung.First;
      Showmessage(XDS_Abteilung.FieldByName('abteilung').AsString);
   end;
begin
   XMyClient.RawInvoke('IData_xChangeService.Get_Abteilung',[], @OnResponse);
end;
14

The content of Response.Result:

{"FDBS":{"Version":15,"Manager":{"UpdatesRegistry":true,"TableList":[{"class":"Table","Name":"Query_Main","SourceName":"abteilung","SourceID":1,"TabID":0,"EnforceConstraints":false,"MinimumCapacity":50,"ColumnList":[{"class":"Column","Name":"AbteilungsID","SourceName":"AbteilungsID","SourceID":1,"DataType":"UInt32","Precision":10,"Searchable":true,"AllowNull":true,"AutoInc":true,"Base":true,"AutoIncrementSeed":-1,"AutoIncrementStep":-1,"OAllowNull":true,"OInWhere":true,"OInKey":true,"OAfterInsChanged":true,"OriginTabName":"nedcom2.abteilung","OriginColName":"AbteilungsID","SourcePrecision":10},{"class":"Column","Name":"Abteilung","SourceName":"Abteilung","SourceID":2,"DataType":"AnsiString","Size":45,"Searchable":true,"Base":true,"OInUpdate":true,"OInWhere":true,"OriginTabName":"nedcom2.abteilung","OriginColName":"Abteilung","SourceSize":45},{"class":"Column","Name":"Test","SourceName":"Test","SourceID":3,"DataType":"AnsiString","Size":45,"Searchable":true,"Default":true,"Base":true,"OAllowNull":true,"OInUpdate":true,"OInWhere":true,"OAfterInsChanged":true,"OriginTabName":"nedcom2.abteilung","OriginColName":"Test","SourceSize":45}],"ConstraintList":[],"ViewList":[],"RowList":[{"RowID":0,"Original":{"AbteilungsID":10,"Abteilung":"Verkauf","Test":"0"}},{"RowID":1,"Original":{"AbteilungsID":20,"Abteilung":"Anwendung & Entwicklung","Test":"0"}},{"RowID":2,"Original":{"AbteilungsID":30,"Abteilung":"Technische B?ro","Test":"0"}},{"RowID":3,"Original":{"AbteilungsID":40,"Abteilung":"Arbeitsvorbereitung","Test":"0"}},{"RowID":4,"Original":{"AbteilungsID":50,"Abteilung":"Produktion","Test":"0"}},{"RowID":5,"Original":{"AbteilungsID":60,"Abteilung":"Werkzeugbau","Test":"0"}},{"RowID":6,"Original":{"AbteilungsID":70,"Abteilung":"Einkauf","Test":"0"}},{"RowID":7,"Original":{"AbteilungsID":80,"Abteilung":"Qualit?tssicherung","Test":"0"}},{"RowID":8,"Original":{"AbteilungsID":90,"Abteilung":"EDV","Test":"0"}},{"RowID":9,"Original":{"AbteilungsID":100,"Abteilung":"Finanzbuchhaltung","Test":"0"}},{"RowID":10,"Original":{"AbteilungsID":110,"Abteilung":"Gesch?ftsf?hrung","Test":"0"}},{"RowID":11,"Original":{"AbteilungsID":111,"Abteilung":"PM","Test":"0"}}]}],"RelationList":[]}}}
15

Do I have to define the fields? I tried it manually without a EntitySetValue. Afterwards I got a new error-message:


ERROR
Uncaught TypeError: Cannot read property 'FAttributes' of null | TypeError: Cannot read property 'FAttributes' of null at Object.LoadData (http://localhost:8000/Web_App/Web_App.js:45609:113) at Object.ActiveChange (http://localhost:8000/Web_App/Web_App.js:45766:14) at Object.cb [as FOnActiveChange] (http://localhost:8000/Web_App/Web_App.js:222:26) at Object.ActiveChanged (http://localhost:8000/Web_App/Web_App.js:43368:46) at Object.CheckActiveAndEditing (http://localhost:8000/Web_App/Web_App.js:33702:14) at Object.DataEvent (http://localhost:8000/Web_App/Web_App.js:33766:14) at Object.DistributeEvent (http://localhost:8000/Web_App/Web_App.js:34021:44) at Object.ProcessEvent (http://localhost:8000/Web_App/Web_App.js:34045:12) at Object.DataEvent (http://localhost:8000/Web_App/Web_App.js:32534:52) at Object.SetState (http://localhost:8000/Web_App/Web_App.js:32989:14)
at http://localhost:8000/Web_App/Web_App.js [45609:113]
16
I tried it on this way:
XDS_Abteilung.Close;
XDS_Abteilung.SetJsonData(TJSArray(TJSObject(Response.Result)['FDBS.Manager.TableList.RowList']));
XDS_Abteilung.Open;

But I get the error-message:  Cannot read property 'FDBS.Manager.TableList.RowList' of null at OnResponse

That is the formated JSON-Stream:
{"FDBS":
{"Version":15,"Manager":
{"UpdatesRegistry":true,"TableList":[
{"class":"Table","Name":"Query_Main","SourceName":"abteilung","SourceID":1,"TabID":0,"EnforceConstraints":false,"MinimumCapacity":50,"ColumnList":[
{"class":"Column","Name":"AbteilungsID","SourceName":"AbteilungsID","SourceID":1,"DataType":"UInt32","Precision":10,"Searchable":true,"AllowNull":true,"AutoInc":true,"Base":true,"AutoIncrementSeed":-1,"AutoIncrementStep":-1,"OAllowNull":true,"OInWhere":true,"OInKey":true,"OAfterInsChanged":true,"OriginTabName":"nedcom2.abteilung","OriginColName":"AbteilungsID","SourcePrecision":10},
{"class":"Column","Name":"Abteilung","SourceName":"Abteilung","SourceID":2,"DataType":"AnsiString","Size":45,"Searchable":true,"AllowNull":true,"Default":true,"Base":true,"OAllowNull":true,"OInUpdate":true,"OInWhere":true,"OAfterInsChanged":true,"OriginTabName":"nedcom2.abteilung","OriginColName":"Abteilung","SourceSize":45},
{"class":"Column","Name":"Test","SourceName":"Test","SourceID":3,"DataType":"AnsiString","Size":45,"Searchable":true,"AllowNull":true,"Default":true,"Base":true,"OAllowNull":true,"OInUpdate":true,"OInWhere":true,"OAfterInsChanged":true,"OriginTabName":"nedcom2.abteilung","OriginColName":"Test","SourceSize":45}],
"ConstraintList":[],"ViewList":[],"RowList":[
{"RowID":0,"Original":
{"AbteilungsID":10,"Abteilung":"Verkauf","Test":"0"}
},
{"RowID":1,"Original":
{"AbteilungsID":20,
"Abteilung":"Anwendung & Entwicklung",
"Test":"0"}
},
{"RowID":2,"Original":
{"AbteilungsID":30,
"Abteilung":"Technische Büro",
"Test":"0"}
},
{"RowID":3,"Original":
{"AbteilungsID":40,
"Abteilung":"Arbeitsvorbereitung",
"Test":"0"}
},
{"RowID":4,"Original":
{"AbteilungsID":50,
"Abteilung":"Produktion",
"Test":"0"}
}]
}],
"RelationList":[]}
}
}
17

Your JSON contain several structures, this is how you should access the array (you can simplify this code of course using a single line, I have created several variables for better understanding:


var

  JFDBS: TJSObject;

  JManager: TJSObject;  

  JTableList: TJSObject;

  JRowList: TJSArray;


..

  JFDBS := TJSObject(TJSObject(Response.Result)['FDBS']);

  JManager := TJSObject(JFDBS['Manager']);

  JTableList := TJSObject(JManager['TableList']);

  JRowList := TJSArray(JTableLlist['RowList']);


  XDS_Abteilung.Close;

  XDS_Abteilung.SetJsonData(JRowList);

  XDS_Abteilung.Open;

And you have to define fields, yes.  
  
18

Hi Wagner,


thanks for support! I defined the fields and changed my procedure like you descripted:

procedure TFrmMain.btnAbteilungClick(Sender: TObject);
   procedure OnResponse(Response: TXDataClientResponse);
   var JFDBS: TJSObject;
         JManager: TJSObject;  
         JTableList: TJSObject;
         JRowList: TJSArray;
   begin
      JFDBS := TJSObject(TJSObject(Response.Result)['FDBS']);
      JManager := TJSObject(JFDBS['Manager']);
      JTableList := TJSObject(JManager['TableList']);
      JRowList := TJSArray(JTableList['RowList']);
      XDS_Abteilung.Close;
      XDS_Abteilung.SetJsonData(JRowList);
      XDS_Abteilung.Open;
   end;
begin
   XMyClient.RawInvoke('IData_xChangeService.Get_Abteilung',[], @OnResponse);
end;


But unfortunally I get an error-message, like before:
ERROR
Uncaught TypeError: Cannot read property 'FDBS' of null | TypeError: Cannot read property 'FDBS' of null at OnResponse (http://localhost:8000/Web_App/Web_App.js:57008:62) at Object.DoLoad (http://localhost:8000/Web_App/Web_App.js:50388:11) at Object.DoLoad (http://localhost:8000/Web_App/Web_App.js:50662:20) at OnSuccess (http://localhost:8000/Web_App/Web_App.js:50778:19) at LocalSuccess (http://localhost:8000/Web_App/Web_App.js:50033:9) at XMLHttpRequest.XhrLoad (http://localhost:8000/Web_App/Web_App.js:50132:34)
at http://localhost:8000/Web_App/Web_App.js [57008:62]

What can be caused the error?

Greetings from Germany
Patrick
19

I checked the function via swagger. May be the following message helps to understand what is happen:

"Unrecognized response type; displaying content as text."

The function is defined:
function TData_xChangeService.Get_Abteilung: TStream;
var   LResult: TMemoryStream;
      SQL_String: string;
begin
   LResult:=TMemoryStream.Create;
   SQL_String:='select * from abteilung';
   DBController.Get_Data( LResult, SQL_String );
   Result:= LResult;
end;

The function Get_Data:
procedure TDBController.Get_Data (AStream: TStream; SQL_String: String);
begin
   if Assigned(AStream) then begin
      Query_Main.SQL.Text:=SQL_String;
      Query_Main.Open;
      Query_Main.SaveToStream(AStream, sfJSON);
   end;
end;

I expected that the result is JSON-format?

Patrick


20

First you need to make sure your server is returning correct data. Open your browser console and check the network tab to see what the server is returning when you are invoking the IData_xChangeService.Get_Abteilung method. This way we can focus either on client or server.