Problems with ADS and ORDER 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?

[rdonnay]

Are you using the same ACE32.DLL with your application as that which is used by the Architect?

[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.

[rdonnay]

What version of eXpress++ do you have?

I would like you to try it with SqlQuery.

[Manfred]

Version:20267

[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.

[Manfred]

This is the result from 2 tests

sqlquery2.PNG (26.51 KiB) Viewed 10755 times

sqlquery1.PNG (92.2 KiB) Viewed 10755 times

[Manfred]

this will work fine also
with 1 fields in Order by and DESC

sqlquery3.PNG (99.58 KiB) Viewed 10753 times

[rdonnay]

I don't understand. Are you still having problems?

[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?