MySql BIT column mapping

[oliverdarvall]

I have a MySql database (version 5.7.23) with a BIT datatype column. When retrieving records from the table that BIT column is mapped to a ulong/UInt64 value in the ExpandoObject. Seems thus there is a datatype mapping problem ...

[mikebeaton]

Yes, that sounds wrong. I'll have a look at it.

[mikebeaton]

Okay, so this gets a bit complicated!

Mighty (like Massive before it and also just like Dapper) is really just a nice wrapper around the ADO.NET driver for your database.

In the case of MySql.Data.MySqlClient, the underlying driver (and not Mighty/Massive) returns ulong for any and every size of BIT(n) (i.e. anything from BIT(1) to BIT(64)). The column types which come back as a C# bool using that driver are BOOL, BOOLEAN and TINYINT(1) (which are all aliases for each other in MySQL). Mighty just passes on these types.

What I've also just found/remembered, on double-checking it, is that Devart.Data.MySql makes more or less exactly the opposite decisions(!!): BIT(1) comes back as a bool, as you want, but BOOL/BOOLEAN/TINYINT(1) come back as short (!) (while other sizes of BIT(n) come back as a long (not ulong)).

Workarounds?

• Use the Devart driver instead of the MySql driver?
• If you have control over the underlying database, then change to using TINYINT(1) (aka BOOL aka BOOLEAN) for your column.
• If you don't want to change the driver and you don't have control of (or don't want to change) the underlying database, then you can use the fact that Mighty, unlike Massive, also works with strongly typed objects. Create a class with the property names and types which you want returned, and create an instance of Mighty using that as its generic type - Mighty's mapping from incoming integer data to a boolean property already does what you want, and this approach has the added advantage that it produces exactly the same results on both the MySQL ADO.NET drivers despite their differences about this (I've just double-checked: the mapping to bool works fine and hides the differences between the drivers).

[mikebeaton]

Optional additional info (partly as notes to self...)

It not clear that there is any clean way to 'fix' this behaviour in Mighty - e.g. to make all the plausible types map to bool even though they don't in the underlying drivers... since Mighty is really a wrapper round the underlying drivers and since the two different MySQL drivers make quite different decisions about this.

And there's an additional problem, about getting hold of the info needed to change this behaviour: you can prompt Mighty to read and cache meta-data for the current table by accessing the read-only property TableMetaData, but that info is NOT currently ever read by Mighty just for streaming data from the database; instead, when reading rows of data into dynamic objects, Mighty relies on the data types which the drivers send back (the types listed above, in this case), and I think it should continue to do so since it's a micro- ORM, a reasonably lightweight wrapper around operations which you might otherwise have coded by hand in ADO.NET yourself.

Mighty does do some manual type-changing which looks a bit like what would be needed here, for both input and output stored procedure parameters; the reason we can do that there is that it is more like the case of generic types (which, as mentioned above, actually is one way to solve the problem here): in both cases, you've already got info about what type the user wants to get back and so you don't have to read any table meta-data to work out what the target type should be.

[mikebeaton]

@oliverdarvall - I'm marking this as an 'invalid' issue for the reasons given above - not because I don't think it was a useful report. It was - thank you.

I'm open to further discussion if you disagree with my reasoning - feel free to re-open the issue.

[oliverdarvall]

Thanks for the feedback Mike ! I will revert to using the strongly typed options.

Regards

On Mon, 20 Jul 2020 at 19:22, Mike Beaton notifications@github.com wrote:

@oliverdarvall https://github.com/oliverdarvall - I'm marking this as an 'invalid' issue for the reasons given above - not because I don't think it was a useful report. It was - thank you.

I'm open to further discussion if you disagree - feel free to re-open the issue.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/MightyOrm/Mighty/issues/30#issuecomment-661212911, or unsubscribe https://github.com/notifications/unsubscribe-auth/ADMOTYSA4FCB5K775JCZUT3R4R4NDANCNFSM4OSV3F5A .