problem with order by (param type: string)

In this forum we will discuss things relating the ZEOSLib 6.6.x stable versions

Moderators: gto, EgonHugeist

Post Reply
railgun3r
Fresh Boarder
Fresh Boarder
Posts: 9
Joined: 03.05.2011, 19:20

problem with order by (param type: string)

Post by railgun3r »

Hi. Could somebody give me the answer of question: how correctly to use param (datatype: ftString) in SQL (Zquery component)?

It works when I use param (ftString) in WHERE clause
It works when I use param (ftInteger) in ORDER BY clause
BUT! when I use param (ftString) in ORDER By clause its not working!


In example:

this sql code I put in Zquery:
SELECT *
FROM production
WHERE (deleted=0 OR deleted= :del)
ORDER BY :z
this one going to .pas:
datamodule2.production.Active:=false;
datamodule2.production.Params.ParamByName('z').value:=column.FieldName;
datamodule2.production.Active:=true;

AND its not working.
BUT! If i put in .pas this:
a:=column.Field.FieldNo;
datamodule2.production.Active:=false;
datamodule2.production.Params.ParamByName('z').value:=a;
datamodule2.production.Active:=true;
everything is ok! why?


how to use param like ftString in ORDER BY clase?


P.S. I use MYSQL 5.1, Zeos 6.6.6 stable, Delphi7
josimarz
Junior Boarder
Junior Boarder
Posts: 41
Joined: 14.09.2009, 17:29
Location: Brazil

Post by josimarz »

Hello railgun3r!

To order queries with TZQuery I prefer to use the properties SortedFields and SortType.

For example, a query with the following:

Code: Select all

SELECT
   CODE,
   NAME,
   BIRTHDAY,
   ID
FROM
   PERSONS
WHERE
   NAME LIKE '%Osama%'
To sort the ascending the NAME field, do the following:

Code: Select all

ZQuery1.SortedFields := 'NAME';
ZQuery1.SortType := 'stAscending';
To sort descending, do the following:

Code: Select all

ZQuery1.SortedFields := 'NAME';
ZQuery1.SortType := 'stDescending';
To order two or more fields:

Code: Select all

ZQuery1.SortedFields := 'NAME;CODE';
ZQuery1.SortType := 'stAscending';
{...}
ZQuery1.SortedFields := 'NAME;CODE';
ZQuery1.SortType := 'stDescending';
Sort for two or more fields with different types of ordering:

Code: Select all

ZQuery1.SortedFields := 'NAME Asc;CODE Desc;ID Asc';
ZQuery1.SortType := 'stIgnored';
Hope this helps!

Greetings,

Josimar
railgun3r
Fresh Boarder
Fresh Boarder
Posts: 9
Joined: 03.05.2011, 19:20

Post by railgun3r »

thank you very much! it works, but only if put in uses this: 'SqlExpr, ZConnection, ZAbstractRODataset, ZAbstractDataset, ZAbstractTable, ZDataset'
User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Post by mdaems »

railgun3r,

This may be because the name is a string value and is probably surrounded by quotes when it's sent to the server. Did you try adding a TZSQLMonitor to your project to write a log file showing the different statements?

Mark
Image
Post Reply