Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Using Firedac in three tier REST application


This question is answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 1 - Last Post: Feb 4, 2015 12:41 AM Last Post By: Davide Rubbiani
Davide Rubbiani

Posts: 70
Registered: 8/15/03
Using Firedac in three tier REST application  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 3, 2015 9:07 AM
Hello,


I'm using Delphi XE7 Upd1, SQL 2008R2 SP3, Win7 64bit Upd1 to develop a mobile three tier application.

I followed the docwiki documentation and everything seems ok but during the testing I encountered the following problems related to a particular query.

The SQL statement of the REST server TFDQuery is :

SELECT TOP (100) PERCENT CustomerId, CustomerCode, CustomerName, LastOrderDate
FROM (
SELECT dbo.Customers.CustomerId, dbo.Customers.CustomerCode, dbo.Customers.CustomerName, dbo.Customers.AgentCode, dbo.Orders.LastOrderDate
FROM dbo.Customers LEFT OUTER JOIN
dbo.CustomersBlockCodes ON dbo.Customers.BlockCode = dbo.CustomersBlockCodes.BlockCode LEFT OUTER JOIN
dbo.Agents ON dbo.Customers.AgentCode = dbo.Agents.AgentCode LEFT OUTER JOIN
dbo.Discounts ON dbo.Customers.DiscountCode = dbo.Discounts.DiscountCode LEFT OUTER JOIN
dbo.Countries ON dbo.Customers.CodNaz = dbo.Countries.CodNaz LEFT OUTER JOIN
dbo.PayemntModes ON dbo.Customers.PaymentModesCode = dbo.PayemntModes.PaymentModesCode LEFT OUTER JOIN
dbo.Orders ON dbo.Customers.CustomerCode = dbo.Orders.CustomerCode AND dbo.Orders.OrderId =
(SELECT TOP (1) OrderId
FROM dbo.Orders AS SubOrders
WHERE (CustomerCode = dbo.Orders.CustomerCode)
ORDER BY dbo.Orders.LastOrderDate DESC)
) AS Customers
WHERE (AgentCode IN
(SELECT AgentCode
FROM dbo.UsersAgents
WHERE (UserId = :UserId)))
ORDER BY LastOrderDate DESC

With the above SQL I encountered the following problems :

Running the query on the REST server I retrieved 390 records but only 340 in the client TFDMemTable. Why ?

With the ORDER BY clause in the server TFDQuery SQL statement I can't get any values for the "LastOrderDate" field (which is always null) in the client TFDMemTable.
To get the correct values for the "LastOrderDate" field I have to remove the ORDER BY clause. Why ?
In any case the ORDER BY clause seems hasn't any effect on the order of the records in the client TFDMemTable. Why ?
Considering I'm developing a mobile application it should be preferable the record sorting is made on the server.

Thank you,
Davide

Davide Rubbiani

Posts: 70
Registered: 8/15/03
Re: Using Firedac in three tier REST application  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Feb 4, 2015 12:41 AM   in response to: Davide Rubbiani in response to: Davide Rubbiani
Hello,

setting the FetchOptions.Mode to fmAll on the server TFDQuery solves all the problems I reported in my post.

Regards,
Davide

Davide Rubbiani wrote:
Hello,


I'm using Delphi XE7 Upd1, SQL 2008R2 SP3, Win7 64bit Upd1 to develop a mobile three tier application.

I followed the docwiki documentation and everything seems ok but during the testing I encountered the following problems related to a particular query.

The SQL statement of the REST server TFDQuery is :

SELECT TOP (100) PERCENT CustomerId, CustomerCode, CustomerName, LastOrderDate
FROM (
SELECT dbo.Customers.CustomerId, dbo.Customers.CustomerCode, dbo.Customers.CustomerName, dbo.Customers.AgentCode, dbo.Orders.LastOrderDate
FROM dbo.Customers LEFT OUTER JOIN
dbo.CustomersBlockCodes ON dbo.Customers.BlockCode = dbo.CustomersBlockCodes.BlockCode LEFT OUTER JOIN
dbo.Agents ON dbo.Customers.AgentCode = dbo.Agents.AgentCode LEFT OUTER JOIN
dbo.Discounts ON dbo.Customers.DiscountCode = dbo.Discounts.DiscountCode LEFT OUTER JOIN
dbo.Countries ON dbo.Customers.CodNaz = dbo.Countries.CodNaz LEFT OUTER JOIN
dbo.PayemntModes ON dbo.Customers.PaymentModesCode = dbo.PayemntModes.PaymentModesCode LEFT OUTER JOIN
dbo.Orders ON dbo.Customers.CustomerCode = dbo.Orders.CustomerCode AND dbo.Orders.OrderId =
(SELECT TOP (1) OrderId
FROM dbo.Orders AS SubOrders
WHERE (CustomerCode = dbo.Orders.CustomerCode)
ORDER BY dbo.Orders.LastOrderDate DESC)
) AS Customers
WHERE (AgentCode IN
(SELECT AgentCode
FROM dbo.UsersAgents
WHERE (UserId = :UserId)))
ORDER BY LastOrderDate DESC

With the above SQL I encountered the following problems :

Running the query on the REST server I retrieved 390 records but only 340 in the client TFDMemTable. Why ?

With the ORDER BY clause in the server TFDQuery SQL statement I can't get any values for the "LastOrderDate" field (which is always null) in the client TFDMemTable.
To get the correct values for the "LastOrderDate" field I have to remove the ORDER BY clause. Why ?
In any case the ORDER BY clause seems hasn't any effect on the order of the records in the client TFDMemTable. Why ?
Considering I'm developing a mobile application it should be preferable the record sorting is made on the server.

Thank you,
Davide

Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02