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”.
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.
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.
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
Wend
The 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
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.
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
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