"Order by" change the number of records retrieved

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
radub
Posts: 105
Joined: Sat 10 Jul 2010 18:46

"Order by" change the number of records retrieved

Post by radub » Mon 19 Jul 2010 19:44

Hello,

SDAC Trial version, D6, MSSQL 2005.
I have a n-tier application.
On the client side, I have a clientdataset connected via a DCOM to a provider with a TMSQuery; the CommandText of the clientdataset contains the following query:
select CLT.CLTNAME, CLT.CLTCODE, CLT.CLTID, dl.ITEMID, d.DOCDATE, d.DOCNO, t.TDOCCODE, t.TDOCNAME, dl.DVLN1QTY*dl.UMFACTOR QTY, dl.DVLN1PUNITDEV/dl.UMFACTOR Pret_unitar, dl.DVLN1QTY*dl.DVLN1PUNITDEV Valoare_vinzare, dl.DVLN1QTY*dl.DVLN1PUNITDEV-dl.ITEMPSTOC Adaos_valoric, 100*(dl.DVLN1QTY*dl.DVLN1PUNITDEV-dl.ITEMPSTOC)/dl.ITEMPSTOC Adaos_procentual

from CLT
join DOCVHD dh on CLT.CLTID=dh.CLTID
join DOCVLN1 dl on dl.DOCID=dh.DOCID
join DOCHD d on d.DOCID=dl.DOCID
join TDOC t on t.TDOCID=d.TDOCID
join ITEM on ITEM.ITEMID=dl.ITEMID

where
d.ISVALID=1 and
d.ISANULAT=0 and
t.NOEFFECT=0 and t.OPSTOC=1 and
d.DOCDATE>=20050601 and d.DOCDATE0 and dl.ITEMPSTOC is not null and
dl.ITEMID in (3884)

order by dl.ITEMID, CLT.CLTNAME, CLT.CLTCODE, d.DOCDATE
the number of records retrieved is 67, wrong; if I delete the last field from the order by clause (d.DOCDATE), the received number of records is correctly, 1313. The query in sql studio returns 1313 records, no matter order by clause.

What happens?




I encountered a similar issue. In the same conditions, the query is:
select d.DOCID, ITEM.ITEMCODE, ITEM.ITEMNAME, dl.DSLN1QTY, dl.DSLN1PUNITDEV, UM.UMCODE, UM.UMNAME, ITEM.INTCODE, ITEM.TITEMID
from DOCHD d
join DOCSLN1 dl on dl.DOCID=d.DOCID
join ITEM ITEM on ITEM.ITEMID=dl.ITEMID
left outer join UM UM on UM.UMID=dl.UMID
where d.DOCID=1125754
order by d.DOCID, dl.LINEORD

which returns 1 record, wrong; if I change the alias of the first field, from d to dl (dl.DOCID), the query returns 5 records, correctly; also if I remove completely the order by clause, the result is correct.

No matter what alias I put, in the sql studio there are 5 records in the result.

In both problems, I have tested the record count returned at the server side on AfterOpen of the TMSQuery, and it is correct. I have no idea what is going wrong.

I have dug deep intro the server side and I have noticed that although the SDAC component returns the correct number of records, the provider doesn't receive all of them at the OnGetData event. Seems a communication problem between TDatasetProvider and TMSQuery component.

Finally, I build up a project with this issue and I can send to you.

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Wed 21 Jul 2010 13:18

We have answered you by e-mail.

AndreyZ

Re: "Order by" change the number of records retrieved

Post by AndreyZ » Tue 12 Jun 2012 10:13

Answer: This problem is caused by the specificity of the TDataSetProvider component work. To solve the problem, you should set the TDataSetProvider.Options.poRetainServerOrder option to True.

Post Reply