Web forum is in read-only mode. Login as active registered customer for write access
  Forum Search   New Posts New Posts

GROUP BY or OTHER aggregate result

 Post Reply Post Reply
Author
Monterisi Stefano View Drop Down
New Member
New Member
Avatar

Joined: 08 Apr 2013
Posts: 4
Post Options Post Options   Quote Monterisi Stefano Quote  Post ReplyReply Direct Link To This Post Topic: GROUP BY or OTHER aggregate result
    Posted: 09 Nov 2018 at 6:56pm
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.

Back to Top
Wagner R. Landgraf View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 2022
Post Options Post Options   Quote Wagner R. Landgraf Quote  Post ReplyReply Direct Link To This Post Posted: 09 Nov 2018 at 11:11pm
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.


Back to Top
Monterisi Stefano View Drop Down
New Member
New Member
Avatar

Joined: 08 Apr 2013
Posts: 4
Post Options Post Options   Quote Monterisi Stefano Quote  Post ReplyReply Direct Link To This Post Posted: 12 Nov 2018 at 4:10pm
So, Is possibile to use SQL statement (in a service) and return Json?
Any example of this?
Ok. I will try.
 Thanks 
Back to Top
Wagner R. Landgraf View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 2022
Post Options Post Options   Quote Wagner R. Landgraf Quote  Post ReplyReply Direct Link To This Post Posted: 12 Nov 2018 at 5:24pm
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:


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.

Back to Top
Monterisi Stefano View Drop Down
New Member
New Member
Avatar

Joined: 08 Apr 2013
Posts: 4
Post Options Post Options   Quote Monterisi Stefano Quote  Post ReplyReply Direct Link To This Post Posted: 17 Nov 2018 at 12:42pm
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.   
Back to Top
Wagner R. Landgraf View Drop Down
TMS Support
TMS Support
Avatar

Joined: 18 May 2010
Posts: 2022
Post Options Post Options   Quote Wagner R. Landgraf Quote  Post ReplyReply Direct Link To This Post Posted: 19 Nov 2018 at 1:11pm
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.
Back to Top
Monterisi Stefano View Drop Down
New Member
New Member
Avatar

Joined: 08 Apr 2013
Posts: 4
Post Options Post Options   Quote Monterisi Stefano Quote  Post ReplyReply Direct Link To This Post Posted: 19 Nov 2018 at 1:20pm
Well. Please insert in todo list. 
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down