I opened a database file that worked fine in LO 3.6 with LO 4.1 . When changing the value of a check box and trying to save the record, I get: Value too long for type character(1) even though in the check box definition the reference values for ON and OFF are 0 and 1. This worked fine in LO 3.6 To reproduce: - Use the files attached to bug 68656 - Set up database as described in bug 68656 - Open the only form in the .odb file - Change the value of a check box and try to save the record
I changed the column width to 30 characters - and discovered that the values "true" and "false" are written to the database. This means that LO is simply ignoring the values for the reference values ON and OFF that are entered in the check box definition on the form.
But even if I change my reference values to "true" and "false" (or leave the fields empty) and change my database to replace all the 0 and 1 by true and false it doesn't work. true and false are written to the database from the form but when reading from the database into the form only 0 and 1 are interpreted, everything else is interpreted as "false" and leaves the tick box empty. I changed the status to critical because de facto tick boxes simply don't work and there is no work-around
This bug is preventing me switching to LO 4.1 from LO 3.6
I have tried to reproduce this, but I can't start PostgreSQL with ODBC and any LO 3.*-Version. I have tested with the direct connection and LO 4.1.1.2. There the table shows a field with fieldtype "bool" - the right type and also the right checkboxes in the table and in the form. Same LO-version, ODBC, same table. The field is shown as "fix char", all with 1 and 0 inside. When I will create a form for this table the field is a text-field. Changing this field brings the dialog "the target rows is out of the rowset". But I get the same message, when I try to change data in other tables. I can't test with other versions of LO - see above. Could be my ODBC-configuration is wrong ... So I can't confirm this bug. I would prefer to change from ODBC to the direct connection ...
Changing to direct connection means that I have to edit all queries, forms and reports because the "url" schema for tables changes, e.g. cdp.public.villages becomes just public.villages That's a huge amount of work! Apart from that, the reason I chose ODBC is that when I set up the database, it was a lot stabler than direct connection. Or to put it less polite, direct connection was totally unusable :-( So I am wary of going to direct connection because I don't know what new bugs will bite me. And its a production database so I can't experiment too much!
Now I have tested a little bit more. I don't use external databases - only test them. I set in the advanced settings for the database "Respect the result set type from the database driver". With this settings I could input data into PostgreSQL with ODBC - LO 4.1.1.2. I could also write into boolean-fields (which are shown as char-fix). I could also input data into this field with a check-box in a form. All this works with OpenSUSE 64bit rpm and the version of LO, which I could download from OpenSUSE. The connection to PostgreSQL with ODBC doesn't work with any other version of LO I have installed. All versions LO 4.* would crash, when I try to open the table, not when connecting to PostgreSQL. I could see the tables, but not the content.
Thanks for the hint - changing my database column into a boolean data type turns out to be a useable workaround. So I can at least move to LO 4.1 now. But of course this doesn't resolve the bug itself because the values for the reference values ON and OFF that are entered in the check box definition on the form are still being ignored by LO 4.1 .
(In reply to comment #7) > Thanks for the hint - changing my database column into a boolean data type > turns out to be a useable workaround. So I can at least move to LO 4.1 now. > When I have read "checkbox" I thought it must be a boolean field. But my ODBC-connection doesn't show it as boolean - only char(fix) for 1 character. > But of course this doesn't resolve the bug itself because the values for the > reference values ON and OFF that are entered in the check box definition on > the form are still being ignored by LO 4.1 . What type of field have you set for this checkbox before? Where have you created the field? In MySQL directly, with Base and ODBC ...
(In reply to comment #8) > What type of field have you set for this checkbox before? Where have you > created the field? In MySQL directly, with Base and ODBC ... I think I created it in Base, but I am not sure, it might also have been pgadmin. Its about 2 years since. The two columns (old that doesn't work, new that does) show up as following: pgadmin: old: character length 1 new: boolean base: old: text (fix) [char] Length 1 new: text [varchar] length 5 and the value stored in the boolean column shows up as 0 and 1 in Base.
(In reply to comment #9) > > The two columns (old that doesn't work, new that does) show up as following: > pgadmin: > old: character length 1 > new: boolean > > base: > old: text (fix) [char] Length 1 > new: text [varchar] length 5 > > and the value stored in the boolean column shows up as 0 and 1 in Base. I have created a boolean field with the direct connection. It has been shown in ODBC-connection as text (fix) [char] Length 1. So it has been shown as your old version in base. Why should a boolean filed have a length of 5? For "false" and "true"? So let's have a look at the difference between our systems. You aren't using the same system as I. Where does the LO-version come from: Linux-repository or directly from LO (which doesn't work here ...)?
I created the boolean field in pgadmin3 and it doesn't give me the option to enter any field length. No idea why it would turn up as length 5 in base. If I create a "Yes/No (char)" field in Base, it comes out as character length 1 in pgadmin. If I use the "Yes/No" field created in base for my form, I have the same problems as before (value to long for field). So creating a "real" boolean column in pgadmin seems to be the only working solution.
My LO is from here: deb http://ppa.launchpad.net/libreoffice/libreoffice-4-1/ubuntu precise main
(In reply to comment #12) > My LO is from here: > > deb http://ppa.launchpad.net/libreoffice/libreoffice-4-1/ubuntu precise main So you don't use the LO-version directly from LO. There are many differences between the connection to external databases with the original versions and the versions, which are specially created for one Linux-system. Remember, that I could only start the connection to PostgreSQL through ODBC with the LO-version of my System (OpenSUSE) - not with the original rpm-packages from LO. And only with this version I can't reproduce what you are writing. I can create a boolean field with the direct connection and it is shown with ODBC as field fix 1 char. Did you report this bug also to Ubuntu - the packages are from Ubuntu ...
@ALL A user has reported a similar problem using mysql and LO checkboxes linked to boolean fields on the English user list - the two problems might be related. Alex
I have also seen a similar report on the French users' list. Alex
Created attachment 86305 [details] Open the form, try to change "Char5" to "true" - doesn't show the change Hope, that the attachment shows the same bug as reported for the connection to PostgreSQL. LO since 4.1.0.0 beta1 When I open the form and try to change the value for the checkbox of the char-field the value in the table is changed to 'true' (not '1'). This value is not shown in the checkbox, when I open the form again. Up to LO 4.0.5.2 When I change the value for the checkbox of the char-field, it is changed to '1' or '0', not to 'true' or 'false'. This value is shown in the checkbox. But if there are values like 'true' and 'false', this values are also shown ... Is this the same bug? Then let us set it to new and change the title.
(In reply to comment #1) > I changed the column width to 30 characters - and discovered that the values > "true" and "false" are written to the database. > > This means that LO is simply ignoring the values for the reference values ON > and OFF that are entered in the check box definition on the form. The reference-values are completely ignored by every version of LO and OOo. You could write inside what you want. It is set to '1' (true) and '0' (false). This hasn't been changed since LO 3.6 ...
I set this bug to "New" - couldn't confirm it with Postgres as a special database, but with the internal database, when the field isn't a bool-field but a char-field.
Debugging session notes: The problem seems to be that, when saving the form data, for the checkbox one gets into frm::FormOperations::impl_commitCurrentControl_throw (file forms/source/runtime/formoperations.cxx line 397) but neither xCurrentControl nor xCurrentControl->getModel() expose a XBoundComponent interface, so the commit is not done. (gdb) print xCurrentControl $25 = uno::Reference to (frm::OCheckBoxControl *) 0x32dd7f8 (gdb) print $26 = uno::Reference to (UnoControlCheckBoxModel *) 0x30eb7f8 However, weirdly *some* data is written out (not sure how...), but not referencevalue, but "true" or "false". OTOH, it seems that when *reading* the data it expects "0" or "1" to decide the state of the checkbox.
(In reply to comment #19) > The problem seems to be that, when saving the form data, for the checkbox > one gets into frm::FormOperations::impl_commitCurrentControl_throw (file > forms/source/runtime/formoperations.cxx line 397) but neither > xCurrentControl nor xCurrentControl->getModel() expose a XBoundComponent > interface, so the commit is not done. No, that was a red herring. The "commit" is done eagerly as soon as the value changes, by an event listener.
So, the behaviour change, which was not planned / intended, is that now, when the underlying database field is of type (VAR)CHAR (string), then it tries to write the string 'true' or the string 'false', while previous versions wrote the string '1' or the string '0'. Combined with the fact that setting the reference values is ignored, this makes an incompatible behaviour change without any workaround. Additionally, this behaviour is inconsistent with was is expected when *reading* the value, which is '1' or '0'. So for LibreOffice 4.1 series, I'm changing back to the old behaviour of writing '1' or '0', which gives us back the old behaviour. For LibreOffice 4.2, I: - am *leaving* the behaviour of writing 'true' or 'false' - am extending the read behaviour to recognise any of 'true', 'false', '1' or '0' - implemented respecting the reference values for database-bound checboxes This means that for LibreOffice 4.2, the backwards-incompatible behaviour change is STAYING, *but* the read behaviour is made consistent and compatible with *both* the old and the new behaviour. Additionally, people wanting the old write behaviour can set the reference values to: - On: 1 - Off: 0 which will now be taken into consideration. Robert, will you take care of updating the documentation of 4.2 for the above changes? Thanks in advance.
Waiting for review for applying the LibreOffice 4.1 fix at https://gerrit.libreoffice.org/6274
Waiting for review for applying the LibreOffice 4.1 fix to 4.1.3.2 (rc) at https://gerrit.libreoffice.org/6275
(In reply to comment #21) > > Robert, will you take care of updating the documentation of 4.2 for the > above changes? Thanks in advance. I'm only writing the German Documentation. The English Handbook is translated by some people of Documentation@global.libreoffice.org . My English isn't well enough to do this by myself. The German "Handbuch" has tree versions, the last for 4.1. The English "Handbook" is translated from the first German "Handbuch". So I don't know if there is anybody who will translate the newer parts. I have changed the the content for the checkbox in the German "Handbuch" for LO 4.2 - there will be many new content, because the internal database "Firebird" would be added.
Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "libreoffice-4-1-3": http://cgit.freedesktop.org/libreoffice/core/commit/?id=c92809c028b7591129487b276991d50e6acd3c26&h=libreoffice-4-1-3 fdo#68657 bool->string conversion to 1/0, not "true"/"false It will be available already in LibreOffice 4.1.3. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=b89fb0d03ead22f023f367c9a9d046d3d41ba1cf fdo#68657 bool->string conversion to 1/0, not "true"/"false The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=6940532b814669dcb9e4553c5678a9e4670cc6e7 Revert "fdo#68657 bool->string conversion to 1/0, not "true"/"false" The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "libreoffice-4-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=1129d2bfa825a73dc0a619fa3e45a277eaffcb86&h=libreoffice-4-1 fdo#68657 bool->string conversion to 1/0, not "true"/"false It will be available in LibreOffice 4.1.4. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.