Page 1 of 2
Problems with ADS and ORDER BY
Posted: Thu Nov 26, 2020 2:42 am
by Manfred
Hi,
i have some problems with SELECT and ORDER BY ON ADS, if ORDER BY have more then 1 Colums (for example order by field1, field2, field3)
Code: Select all
TEXT INTO cSQLStmt WRAP Chr(13) TRIMMED
SELECT mm.id, mm.textorg, mm.datumein, mm.extern, mm.idfahrer, mm.idfahrzeug, mm.zeitein, fz.kfznr
FROM maengelmeldung mm
LEFT JOIN fahrzeuge fz ON mm.idfahrzeug=fz.id
ORDER BY fz.kfznr, mm.datumein, mm.zeitein
ENDTEXT
oSqlStmt := DC_AdsStatement():new(cSQLStmt,oAdsServer:oSession)
cAlias := oSqlStmt:execute()
cAlias returns NIL. The same statement in Architect SQl Utility works fine.
What is wrong?
Re: Problems with ADS and ORDER BY
Posted: Thu Nov 26, 2020 6:52 am
by rdonnay
Are you using the same ACE32.DLL with your application as that which is used by the Architect?
Re: Problems with ADS and ORDER BY
Posted: Thu Nov 26, 2020 7:26 am
by Manfred
Hi Roger,
yes it seems so.
ace32.dll 2017-11-07
But adsloc32.dll was different. Since i change it, this works
Code: Select all
TEXT INTO cSQLStmt WRAP Chr(13) TRIMMED
SELECT mm.id, mm.textorg, mm.datumein, mm.extern, mm.idfahrer, mm.idfahrzeug, mm.zeitein, fz.kfznr, fa.name, fa.vorname
FROM maengelmeldung mm
LEFT JOIN fahrzeuge fz ON mm.idfahrzeug=fz.id
LEFT JOIN fahrer fa ON mm.idfahrer=fa.id
WHERE fz.kfznr = {#kfznr#}
ORDER BY mm.datumein, mm.zeitein
ENDTEXT
cSqlStmt := StrTran(cSqlStmt,"{#kfznr#}",Alltrim(Str(kfznr)))
oSqlStmt := DC_AdsStatement():new(cSQLStmt,oAdsServer:oSession)
cAlias := oSqlStmt:execute()
but this not. calias ist NIL
Code: Select all
TEXT INTO cSQLStmt WRAP Chr(13) TRIMMED
SELECT mm.id, mm.textorg, mm.datumein, mm.extern, mm.idfahrer, mm.idfahrzeug, mm.zeitein, fz.kfznr, fa.name, fa.vorname
FROM maengelmeldung mm
LEFT JOIN fahrzeuge fz ON mm.idfahrzeug=fz.id
LEFT JOIN fahrer fa ON mm.idfahrer=fa.id
WHERE fz.kfznr = {#kfznr#}
ORDER BY mm.datumein DESC, mm.zeitein
ENDTEXT
cSqlStmt := StrTran(cSqlStmt,"{#kfznr#}",Alltrim(Str(kfznr)))
oSqlStmt := DC_AdsStatement():new(cSQLStmt,oAdsServer:oSession)
cAlias := oSqlStmt:execute()
In Architect it works fine.
Re: Problems with ADS and ORDER BY
Posted: Thu Nov 26, 2020 8:44 am
by rdonnay
What version of eXpress++ do you have?
I would like you to try it with SqlQuery.
Re: Problems with ADS and ORDER BY
Posted: Thu Nov 26, 2020 8:53 am
by Manfred
Version:20267
Re: Problems with ADS and ORDER BY
Posted: Thu Nov 26, 2020 10:01 am
by rdonnay
You should give this a try with SQLQuery.
Read about it in \exp20\sqlquery\readme.txt and builds.txt
If you give me your connection information, I will give you what to put into your Sqlquery.ini file.
Re: Problems with ADS and ORDER BY
Posted: Thu Nov 26, 2020 10:51 am
by Manfred
This is the result from 2 tests
- sqlquery2.PNG (26.51 KiB) Viewed 10777 times
- sqlquery1.PNG (92.2 KiB) Viewed 10777 times
Re: Problems with ADS and ORDER BY
Posted: Thu Nov 26, 2020 10:57 am
by Manfred
this will work fine also
with 1 fields in Order by and DESC
- sqlquery3.PNG (99.58 KiB) Viewed 10775 times
Re: Problems with ADS and ORDER BY
Posted: Fri Nov 27, 2020 8:39 am
by rdonnay
I don't understand. Are you still having problems?
Re: Problems with ADS and ORDER BY
Posted: Fri Nov 27, 2020 8:46 am
by Manfred
yes,
Order by with one field and DESC works, Order by with more than one fields and DESC don't works. I don't know ist it a express++ Problem, or xbase++ problem?