If I create a view in my MySQL database and open it in Base, the boolean fields display as 0’s and 1’s instead of boolean checkboxes. Is there anything I can do to change this?
Hello,
There is no Boolean or Bool type in MySQL. Instead it is TINTINT(1) or Bit(1) - both of which come up as Bit (fix) [Bit} in Base (JDBC connector).
MySQL Workbench:
Base & View:
This is using:
Version: 7.3.1.3 / LibreOffice Community
Build ID: a69ca51ded25f3eefd52d7bf9a5fad8c90b87951
CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded
and MySQL v8.0.28
Thanks for your response! I’m not looking to change the values returned by the view, only to change how Base displays them. I want the checkboxes, like how it looks in the tables queried by the view, instead of numbers. Maybe it can’t be done; I admit I’m being picky.
.
Not suggesting that at all.
.
.
And that is what I have shown in my answer. Why is that not answering the question?
.
Here is the same data in the table view:
.
.
It is the same as the View in the answer.
Sorry. I’m working on Windows 10. Your screenshots look like you’re on Mac or some Linux distro. Maybe that’s the difference.
The LO version was posted in the answer and shows it to be Linux. The result in Windows should be no different. If so it is a bug.
My booleans are coming through as TINYINT(1). I don’t see how I can change that.
What connector are you using? TINYINT(1) (and/or BIT(1)) is what I show in the answer for MySQL. Should still show check boxes.
Type BIT is also available in direct connection to MariaDB (and MySQL). This must be choosen for checkboxes in table view and queries.
BOOLEAN will be changed to TINYINT, because no BOOLEAN is available in MyriaDB/MySQL. But TINYINT will also work in Forms together with a checkbox.
Currently testing as JDBC works for either BIT or TINYINT (shown in my answer) but Native connection does not work in tables. Shows digits. Do not know of ODBC yet.
.
Edit
ODBC works for table view. From my tests problem is with native connector.
.
Edit #2:
.
Have been able to set checkbox type using Native connector. TINYINT(1) which works with JDBC and ODBC connector cannot get to work with Native connector.
.
BIT(1) (used from MySQL Workbench) does work. In Base this is represented as Yes/No [BIT]:
.
Works same in View.
Field types are not consistent across connectors.
Actually, I’m using the mysql-connector-java-5.1.44-bin.jar connector (which I had to download from Oracle’s website), and under the Database Properties>Advanced, it says JDBC.
For me, MySQL is v8.x (noted earlier) and the connector is mysql-connector-java-8.0.12.jar and that produces noted results as you wanted using TINYINT(1) (which shows as Bit (fix) [BIT] in Base as does BIT(1)).
Great! So maybe if I bump up to the newer connector, it will start returning bit result types. (Or maybe, that connector won’t work with MySQL 5.7.)
Do not see that as the answer. Have changed to a Mint 18.3 system where I have MySQL v5.7.26 installed. Check boxes worked fine (both table and view) using connector 8.0.12 or 5.1.45
This was LO v6.2.x that was on that OS.
So, did Mint come with the connector already installed?
It needed to be downloaded.