Question about Boolean and Parameters

[swierzbicki]

Hi,

I'm using such kind of SQL Script :

Code: Select all

SET @Value = :prvalue;
Select 
(case 
when @Value  then 'True' 
when not @Value then 'False'  
else '?' end)
as Test

When setting my parameter like this :

Code: Select all

MyQuery.Parambyname('prvalue').AsBoolean := False;
or
MyQuery.Parambyname('prvalue').AsBoolean := True;

Test is always 'False' !

When setting my parameter like this :

Code: Select all

MyQuery.Parambyname('prvalue').AsInteger := 0;
or
MyQuery.Parambyname('prvalue').AsInteger := 1;

Test is either True or False

How can I get this to work with parameters as boolean ?

[Dimon]

This behaviour is connected with specificity of work of MySQL server with Boolean type. MySQL uses one format for parameters in SELECT, INSERT, UPDATE and other statements, but in your case MySQL uses another format.
You may use AsInteger as a workaround for the problem.

[swierzbicki]

Hi Dimon,

Casting to an integer is fine for me. I'm doing this as a workaround :

Code: Select all

MyQuery.Parambyname('prvalue').AsInteger := Integer(MyBooleanVariable);

I think there is still an issue since nothing seems to be passed to the parameter when using .AsBoolean :

Code: Select all

SET @Value = :prvalue;
Select @Value;

@Value is always blank (or null, need to check this) when using :

Code: Select all

MyQuery.Parambyname('prvalue').AsBoolean := False;
or
MyQuery.Parambyname('prvalue').AsBoolean := True;

[Dimon]

We have fixed this problem. This fix will be included in the next MyDAC build.

[swierzbicki]

that's great !
Thank you