[SOLVED] Using Params set by Datasource and by program concurrently

[vejrous]

I have smal SQL (that is tested in HeidiSQL and other components):

Code: Select all

SELECT
  ID,
  SubjectID,
  /* IF condition, show SubName value*/
  IF (:ShowSubName = True, SubName, NULL) AS SubName
FROM
  Address
WHERE
  Address.SubjectID = :ID

Param :ID is get from Datasource (ie. master record).
Param :ShowSubName is set by code

Code: Select all

qry.ParamByName('ShowSubName').AsBoolean := True

or False.
Problem is that this second parametr is not used.
I tried to set this param Bounded := True;

Can somebody help?

[marsupilami]

Hello vejrous,

could you supply us with a small demo project and a small create script for a sample database? This would make debugging into this problem much more easy.

With best regards,

Jan

[vejrous]

Prepare example under Delphi or Lazarus? Which is better for You?

[marsupilami]

For me it doesn't matter - I have both programs installed.

[vejrous]

Example attached:
test.sql - create database with tables and data (database name 'test').
project1 - Delphi project with one form.

ZConnection set to localhost, port 3306, mysql. Will ask for user name and password.
libmysql.dll not attached.
Checkbox1 should set Param and show/hide subname in result (Grid2).

Using SVN: http://svn.code.sf.net/p/zeoslib/code-0 ... esting-7.2
revision 4391.

[EgonHugeist]

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

[vejrous]

Hi,
thank you very very much for help!

I updated SVN for the latest version.
Tested the

Code: Select all

'MySQL_FieldType_Bit_1_IsBoolean=true'

variant under Delphi with Mysql (5.1.73-comunity) and libmysql.dll (6.1.11.0).
Everything works as expected.