Trouble with tinyint booleans

[skydvrz]

I have a query that uses a union of two separate queries. Several of the columns are tinyint boolean fields in the table. The trouble is that when I use the TMyQuery field editor, it casts all the tinyint boolean fields as TinyInt - even if I check the EnableBoolean property.

Obviously, my app won't correctly set checkboxes that use these fields when they are cast as Tinyints.

If I delete the automatically assigned tinyint fields using the field editor and replace them with boolean data fields, my application dies with a "quAccountsReceivable: Type mismatch for field 'deposited', expecting: Boolean actual: SmallInt."

Does anyone have any ideas on how to work around this problem?

Here is the table that contains the boolean flags:

Code: Select all

CREATE TABLE  `zzzz`.`incomeitems` (
  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'unique id',
  `oid` int(10) unsigned default NULL, 
  `amount` double(9,2) NOT NULL ,
  `checknum` varchar(20) default NULL,
  `checkdate` datetime NOT NULL default '0000-00-00 00:00:00' COMMENT 'today',
  `note` blob COMMENT 'misc note',
  `did` int(10) unsigned default NULL COMMENT 'deposit id',
  `paymenttype` int(10) unsigned NOT NULL COMMENT '1=cash,2=check,3=MO,4=other (use note)',
  `aid` int(10) unsigned NOT NULL COMMENT 'paid-to agency',
  `rid` int(10) unsigned default NULL COMMENT 'pay against this registration',
  `deposited` tinyint(1) default '0' COMMENT 'Has this item been deposited?',
  `nsf` tinyint(1) default '0' COMMENT 'Non sufficient funds?',
  `voided` tinyint(1) NOT NULL default '0' COMMENT 'Has this item been voided?',
  `hide` tinyint(1) NOT NULL default '0' COMMENT 'hide reversing items and the voided item',
  `payingagency` int(10) unsigned default NULL COMMENT 'if agency payment, then put AID here',
  `parent` int(10) unsigned default NULL COMMENT 'if this is a child record, then this field points to the parent incomeitems.id of parent',
  `paymentsource` int(10) unsigned NOT NULL default '1' COMMENT '1=normal 2=split 3=collections',
  PRIMARY KEY  USING BTREE (`id`),
  KEY `Index_2` USING BTREE (`oid`,`aid`),
  KEY `Index_3` (`checkdate`),
  KEY `Index_4` USING BTREE (`rid`)
) ENGINE=InnoDB AUTO_INCREMENT=967295 DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED COMMENT='InnoDB free: 1146880 kB';

Here is the query:

Code: Select all

select  concat(concat_ws(", ", o.lastname, o.firstname), coalesce(concat(" ", o.middlename),"")) as payor,
        i.*,
        d.depositdate,
        d.voided as depvoid
from incomeitems i
left join deposits d on (i.did=d.id)
left join offenders o on (i.oid=o.id)
where i.oid is not null and parent is null

union

select  o.agencyname as payor,
        i.*,
        d.depositdate,
        d.voided as depvoid
from incomeitems i
left join deposits d on (i.did=d.id)
left join contacts.agencies o on (i.payingagency=o.id)
where i.oid is null and parent is null

[AndreyZ]

Hello,

The point is that if there is the UNION clause in a query, MySQL returns incorrect length (4 instead of 1) for TINYINT(1) fields. That's why MyDAC maps TINYINT(1) to TSmallintField. You can check it by removing the UNION statement and the second statement from your query. For example:

Code: Select all

select  concat(concat_ws(", ", o.lastname, o.firstname), coalesce(concat(" ", o.middlename),"")) as payor,
        i.*,
        d.depositdate,
        d.voided as depvoid
from incomeitems i
left join deposits d on (i.did=d.id)
left join offenders o on (i.oid=o.id)
where i.oid is not null and parent is null

To avoid this problem, you should not use the UNION clause.

[zeltron73]

Hello,

I had the same problem last few months...
For components of type TDBCheckBox, you can assign "TDBCheckBox.ValueChecked" property to 1 and "TDBCheckBox.ValueUnchecked" to 0 to reflect the field result.

In your code, you have to replace "BoolField.Value" by "SmallIntField.AsVariant" to have the compiler do the VariantToBool and BoolToVariant conversion, because TBooleanField and TSmallIntField are not compatible...

Regards.

[waheed]

I solved it like this. Assuming that you have column C defined as tinyint(1), then you had to do a UNION (to solve the problem of FULL OUTER JOIN), it will return tinyint(4). So I wrote a function that will convert it to tinyint(1). I know this will take processing time from MySQL server, but it will allow me to keep my Delphi code as it is.

Created a MySQL function

Code: Select all

DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `getTinyInt`(i int) RETURNS tinyint(1)
    DETERMINISTIC
BEGIN
    Declare x TinyInt(1);
    set x = i;
    RETURN x;
END

Then in the union I have done this:

Code: Select all

Select a, b, getTinyInt(c) from
(select a, b, c from x
UNION
select a, b, c from y)
as z

Any comments are welcomed...

[AndreyZ]

You can avoid this problem using the Data Type Mapping feature (MyDAC supports it since version 7.2.7). Here is a code example:

Code: Select all

MyQuery1.SQL.Clear;
MyQuery1.SQL.Add('select a, b, c from x');
MyQuery1.SQL.Add('union');
MyQuery1.SQL.Add('select a, b, c from y');
MyQuery1.DataTypeMap.AddFieldNameRule('c', ftBoolean);
MyQuery1.Open;
ShowMessage(MyQuery1.FieldByName('c').ClassName); // here you will see that the c column is mapped to the TBooleanField field

For more information about the Data Type Mapping feature, please read the "Data Type Mapping" article in the MyDAC documentation.