Hi vejrous,
I did add your first post example to our tests.
Note Zeos maps enum('y','n') as boolean type. Up until your post the was no known issue using that.
Other projects like phpMyAdmin or components did use the tinyint(1) as boolean field which is plain wrong because
singned tinint(1) has a range of ShortInt: -127..128
unsigned tinint(1) has a range of Byte: 0..255
And this is also suggested by
https://dev.mysql.com/doc/refman/5.7/en ... types.html 
Your true/false comparision is available sinze MySQL 5.7, so to say it's "new".
What happens in your case:
Zeos binds a 'y' to your first parameter :ShowSubName and the second parameter is the expected id as integer. Sadly MySQL does not raise an error for this example. I assume they do convert 'true' to a string expression like '1'.
Code: Select all
SELECT
ID,
SubjectID,
/* IF condition, show SubName value*/
IF ('y' = True, SubName, NULL) AS SubName
FROM
Address
WHERE
Address.SubjectID = 1 <- example
is what zeos sends. The comparision fails on the first parameter not on the second parameter as you wrote.
Your example would work if you would use it like this:
Code: Select all
qry.ParamByName('ShowSubName').AsInteger := Ord(True)
or change the query to
Code: Select all
SELECT
ID,
SubjectID,
/* IF condition, show SubName value*/
IF (:ShowSubName = 'Y', SubName, NULL) AS SubName
FROM
Address
WHERE
Address.SubjectID = 1 <- example
To be honest both improvements are not very nice. And there are users who what to see the enum('Y','N') as it's string representation not as boolean converted.
Since MySQL 5.0.3 there is the type "bit" and bit(1) is a true switch with just 0/1.
I've added a TZConnection.Properties parameter 'MySQL_FieldType_Bit_1_IsBoolean' which can be used as:
Code: Select all
TZConnection.Properties.Values['MySQL_FieldType_Bit_1_IsBoolean'] := 'true';
or
TZConnection.Properties.Add('MySQL_FieldType_Bit_1_IsBoolean=true');
or just type it in your PropertiyEditor.
If connected once you can't change it anymore.
If this switch is set to a "true" expression Zeos mapps the type bit(1) as boolean fields. In addition we replace all bool expression as ordinal representation so your exapmle would be bound and send as:
Code: Select all
SELECT
ID,
SubjectID,
/* IF condition, show SubName value*/
IF (0 = True, SubName, NULL) AS SubName
FROM
Address
WHERE
Address.SubjectID = 1 <- example
and you can use the Parameter as you want it:
Code: Select all
qry.ParamByName('ShowSubName').AsBoolean := True;
And MySQL successfully can evaluate the result.
the patch is available on SVN only. Patch done R4396 /Testing-7.2 (SVN)
To all other users:
This switch will become default for 7.3 + MySQL >= 5.0.3