Hi, all! A Xojo newbie here.
I don’t know how to get the value of MySQL’s Bit type field. I can write into it fine but I cannot test it’s status. Xojo’s recordset ColumnType returns -1 but VarType() returns 8. Str() returns “0” even the value is “1”.
Thank you!
I had to use a tinyint(1) column in the table to get it to work with Xojo. I used .BooleanValue, as in rs.Field(“fieldname”).BooleanValue to read the value. If you’re using a PreparedStatement, bind it with MYSQL_TYPE_TINY.
See <https://xojo.com/issue/16547>.
Thanks, Jay! Is there any other workaround other than changing the column type? Because the database is shared by me and .Net developers. They prefer Bit because it is convenient for them using ASP-MVC.
The only workaround I know of is to use the MBS SQL Plugin, but I don’t know if it works or not, as I don’t have it.
My goodness! The first finding was reported 2011 and no patch up to now???
This is going to sounds REALLY strange BUT
bit fields are returned as “a bunch of bytes” - so you can grab the bits in there by using a “string” with a nil encoding
like
While rs<>Nil And rs.eof <> True
dim t as string = rs.field("testBit").StringValue
rs.MoveNext
WendThe hard part is dealing with it in code because BIT doesn’t map to anything natural in Xojo (no they’re not booleans - mostly because they can be up to 4096 bits long)
So you end up doing the masking your self in Xojo code to get at whatever values
@Norman P
@Jay M
I got a band-aid solution:
dim t as string = rs.field("testBit").StringValue
dim i as integer = asc(t) //either 0 or 1well the issue here is if the .Net guys are testing specific bits for being set or not your code wont behave the same
for instance I opened up navicat and set a bit column to b010101 (21)
if the .Net code test bits individually then you would need to as well
But since you get a bunch of individual bytes its a bit of work
you could toss it into a memory block then test each byte in the memory block
They used Bit type as a substitute to Boolean because as they claim it is easily interpreted by MVC (for a boolean). Nothing over the top bit manipulation. I used it to toggle the value of a webcontrol, so, so far this works for everyone.
More often than not under the hood the db engine saves VERY little space using a bit field vs a one byte int (tinyint)
And mysql is this way as well
See https://dev.mysql.com/doc/refman/5.6/en/storage-requirements.html
a single bit in a column still takes 1 byte
ie/ its a “false savings” - store 0 and 1 in a tiny int and that should be just as easy to turn into a boolean 
Thank you, Norman. I will let the rest of the guys know about this as I am not the one who designed the db.
I’ve been around a while and seen this specific “optimization” a few times and have yet to run into a db that didn’t require at least one byte to store a bit column
Old guys 
[quote=337938:@Norman Palardy]More often than not under the hood the db engine saves VERY little space using a bit field vs a one byte int (tinyint)
And mysql is this way as well
See https://dev.mysql.com/doc/refman/5.6/en/storage-requirements.html
a single bit in a column still takes 1 byte
ie/ its a “false savings” - store 0 and 1 in a tiny int and that should be just as easy to turn into a boolean :)[/quote]
True, for a single bit column. In SQL Server if you have multiple bit columns it will optimize the storage (see https://docs.microsoft.com/en-us/sql/t-sql/data-types/bit-transact-sql).
But SQL Server only has a single “bit” bit data type, whereas MySQL, PostgreSQL (and probably others) have a “bit string” data type, where the length is the number of bits you want to store. So optimizing storage for those would be more complicated.
Ah yes
When you mentioned this I had to go dig out an old Sybase reference manual (its been a long time) and that optimization has been there since way back then when MS and Sybase actually worked together on SQL Server