Why Can't You Display Boolean (bit) Values?

[tkarakai]

SQLYog cannot display columns with the type of “bit”? Bit is used instead of “boolean” by Hibernate ORM, and it's not my choice, and it would be nice if I could see the values… Please make them readable as 1/0, just like you already do for boolean. Thank you!

[peterlaursen]

Basically it is a problem with the MySQL itself.

This is a good summary of the problems:

http://www.google.com/search?client=opera&…-8&oe=utf-8

Or as the MySQL documentation says here: http://dev.mysql.com/doc/refman/5.0/en/bit-field-values.html

“Bit values are returned as binary values.” BOOM! 🙂

But the solution is also there: “To display them in printable form, add 0 or use a conversion function such as BIN().”

say you have a col1 that is INT and a col2 that is BIT(n) you may do:

Code:

SELECT col1, bin(col2) FROM thetable

or

Code:

SELECT col1, col2+0 FROM thetable

and data will be displayed in the the RESULT tab (but also note the pasage from documentation as regards the bin() function “High-order 0 bits are not displayed in the converted value”).

To INSERT or UPDATE I think the b'..' notation is most useful so that SQL like this is generated

Code:

INSERT INTO t SET b = b'11111111';

Actually BIT type is only part of the problem. Problem also is with any data returned by the server as a binary string (such as BINARY and VARBINARY where HEX-notation would be the only meningfull representation unless it is plain ASCII). User and client will need to handle it.

We have this recorded already: http://code.google.com/p/sqlyog/issues/detail?id=15. We did not find a solution for a safe way of doing it transparently for user.

[peterlaursen]

I missed one point in my reply!

There is no BOOL (or BOOLEAN) in MySQL. The server accepts the “BOOL” Keyword but creates a TINYINT(1). This one is displayed of course as it is a plain integer.

[Author]

Posts