GROUP BY or OTHER aggregate result

Hi,

If I port a VCL C/S project to XData, how I can obtain totals, sums, etc from DB server? 
How using XData for request "Group By" results? I can only request single or list entities?
Thanks.

You can create service operations (custom server-side logic) and then use TMS Aurelius projections to bring such type of data. Or, of course, just use raw SQL statements are you are used to, in worse case.


So, Is possibile to use SQL statement (in a service) and return Json?
Any example of this?

Ok. I will try.
 Thanks 

You can add any logic to service operation. It's Delphi. As long it's "server-compatible" (thread safe, doesn't require any user interaction like user dialogs, etc). 

So of course you can execute SQL statements as you would do with a regular Delphi application. 
If you want to make use of something ready-to-use in XData, you can execute statements using TAureliusConnection interfaces (again, not required), like this:

https://www.tmssoftware.com/site/tips.asp?s=faq&show=793

For returning JSON, you can return simple Delphi objects (fill them manually) or can simply return a TJSonObject, TJsonValue (Delphi classes) or use BCL Json objects like TJElement, TJObject, etc.

Hi,
Thank you,
There is a Xdata direct Json result from Resultset? (Resultset.tojson())
I don't want to use another library or additional routines, only Xdata and its functions....
  
  1. var  
  2.   Statement: IDBStatement;  
  3.   ResultSet: IDBResultSet;  
  4.   
  5. Statement := Manager.Connection.CreateStatement;  
  6. Statement.SetSQLCommand(TheSQLStatement);  
  7. ResultSet := Statement.ExecuteQuery;  
  8.   
  9. while ResultSet.Next do  
  10.   Value := ResultSet.GetFieldValue(SomeFieldName);  
  11.   

Unfortunately no, there is no such mechanism. You would have to manually iterate through the REsultSet and create the json response. You can use bcl.json classes for that (TJArray, TJObject, etc.) or use regular System.Json Delphi classes.

Well. Please insert in todo list.