problem with order by (param type: string)

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

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]

thank you very much! it works, but only if put in uses this: 'SqlExpr, ZConnection, ZAbstractRODataset, ZAbstractDataset, ZAbstractTable, ZDataset'

[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