mysql Params.TinyIntFormat := tifInteger; causes bit(1) fields to always return false

[desert_coffee 0]

For context this is using Mysql 8 and firedac.

Due to some trouble I was having with tinyint(1) I switched the Tinyint format to be shortint and just did the conversion with a custom class. However it seems that when setting the TinyIntFormat to tifInteger it causes the field to always return false for fields that are bit(1). I've narrowed it down to the TDataSet.GetFieldData(Field: TField; var Buffer: TValueBuffer) setting the Buffer to (255,255) on true bit(1) fields when TinyIntFormat is tifBoolean, and setting it to (0,0) when TinyIntFormat is tifInteger, despite the fact that it does correctly use TBooleanField.

 

Besides switching all my bit(1) columns to tinyint(1) is there a way to fix this?

Edited April 24, 2023 by desert_coffee

[programmerdelphi2k 235]

I dont know about MySQL formats, but you can try use the "MAP" options in FireDAC components, then you can "map any field" to another format when working with FireDAC, see the HELP for more details.

that way, you dont need to do any changes by code, for example!

this is the way that FireDAC try work with fields not supported (if possible).  doublelclick in component and access the "Map" tab

hierarchy:

• FDMannager ( all FireDAC app have it, same that not defined in code) 
  • FDConnection -- inherit from above
    • FDQuery, etc.. (Datasets at general) - inherit from above

[desert_coffee 0]

2 hours ago, programmerdelphi2k said:

I dont know about MySQL formats, but you can try use the "MAP" options in FireDAC components, then you can "map any field" to another format when working with FireDAC, see the HELP for more details.

that way, you dont need to do any changes by code, for example!

this is the way that FireDAC try work with fields not supported (if possible).  doublelclick in component and access the "Map" tab

hierarchy:

• FDMannager ( all FireDAC app have it, same that not defined in code) 
  • FDConnection -- inherit from above
    • FDQuery, etc.. (Datasets at general) - inherit from above

Thanks for the advice but mapping the field from boolean to byte/integer doesn't seem to work, it also returns zero in every case.

[programmerdelphi2k 235]

45 minutes ago, desert_coffee said:

it also returns zero in every case.

did read this ?  https://docwiki.embarcadero.com/RADStudio/Sydney/en/Connect_to_MySQL_Server_(FireDAC)

Quote

tinyint accepts numbers that are in the range of 1 byte. The boolean type is an alias for tinyint. The catch is that zero is treated as false and the rest as true.

https://dev.mysql.com/doc/refman/5.7/en/integer-types.html

https://stackoverflow.com/questions/16798744/why-does-tinyint1-function-as-a-boolean-but-int1-does-not

Edited April 24, 2023 by programmerdelphi2k