Problems with ADS and ORDER BY

This forum is for eXpress++ general support.
Message
Author
Manfred
Posts: 14
Joined: Sun Jul 22, 2012 12:10 am
Location: Wesel

Problems with ADS and ORDER BY

#1 Post 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?

User avatar
rdonnay
Site Admin
Posts: 4722
Joined: Wed Jan 27, 2010 6:58 pm
Location: Boise, Idaho USA
Contact:

Re: Problems with ADS and ORDER BY

#2 Post by rdonnay »

Are you using the same ACE32.DLL with your application as that which is used by the Architect?
The eXpress train is coming - and it has more cars.

Manfred
Posts: 14
Joined: Sun Jul 22, 2012 12:10 am
Location: Wesel

Re: Problems with ADS and ORDER BY

#3 Post 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.

User avatar
rdonnay
Site Admin
Posts: 4722
Joined: Wed Jan 27, 2010 6:58 pm
Location: Boise, Idaho USA
Contact:

Re: Problems with ADS and ORDER BY

#4 Post by rdonnay »

What version of eXpress++ do you have?

I would like you to try it with SqlQuery.
The eXpress train is coming - and it has more cars.

Manfred
Posts: 14
Joined: Sun Jul 22, 2012 12:10 am
Location: Wesel

Re: Problems with ADS and ORDER BY

#5 Post by Manfred »

Version:20267

User avatar
rdonnay
Site Admin
Posts: 4722
Joined: Wed Jan 27, 2010 6:58 pm
Location: Boise, Idaho USA
Contact:

Re: Problems with ADS and ORDER BY

#6 Post 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.
The eXpress train is coming - and it has more cars.

Manfred
Posts: 14
Joined: Sun Jul 22, 2012 12:10 am
Location: Wesel

Re: Problems with ADS and ORDER BY

#7 Post by Manfred »

This is the result from 2 tests
sqlquery2.PNG
sqlquery2.PNG (26.51 KiB) Viewed 8847 times
sqlquery1.PNG
sqlquery1.PNG (92.2 KiB) Viewed 8847 times

Manfred
Posts: 14
Joined: Sun Jul 22, 2012 12:10 am
Location: Wesel

Re: Problems with ADS and ORDER BY

#8 Post by Manfred »

this will work fine also
with 1 fields in Order by and DESC
sqlquery3.PNG
sqlquery3.PNG (99.58 KiB) Viewed 8845 times

User avatar
rdonnay
Site Admin
Posts: 4722
Joined: Wed Jan 27, 2010 6:58 pm
Location: Boise, Idaho USA
Contact:

Re: Problems with ADS and ORDER BY

#9 Post by rdonnay »

I don't understand. Are you still having problems?
The eXpress train is coming - and it has more cars.

Manfred
Posts: 14
Joined: Sun Jul 22, 2012 12:10 am
Location: Wesel

Re: Problems with ADS and ORDER BY

#10 Post 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?

Post Reply