Apache OpenOffice (AOO) Bugzilla – Issue 62771
SQL query in List content of Form listbox gets broken when using shift-enter
Last modified: 2017-05-20 10:48:11 UTC
This was working in OOo2.0.1, but something has changed for OOo2.0.2RC4. In OOo2.0.2RC4 any SQL query entered into the List Content box of the ListBox Form control properties is being wrapped in double quotes. This appears to be causing the Listbox to fail where it comes up with no entries in the drop list. E.g. Typing: SELECT somefield FROM sometable is appearing as "SELECT somefield FROM sometable" List boxes created in OOo2.0.1 are still working fine, and the SQL statements are not wrapped in quotes. Combo boxes do not have this problem (not wrapped in quotes).
Actually, I can't confirm that Listboxes in OOo2.0.1 didn't also have this problem - I thought I had used them but haven't and I can't recall if I tried them or not. Combo boxes are definitely working ok.
The problem has shifted now that I understand a bit more. I had assumed list boxes work just like combo boxes by default. I think they should. The cause of my problem, I believe, is that the Bound Field value equals 1 by default, which assumes that the list content is going to be generated by a SQL statement like: SELECT displayfield, boundfield FROM sometable I.e. a linked reference list box. If Bound Field is cleared, the select in my earlier comment works. Can someone please check if Bound field is meant to be '1' by default? Or is it an index off-by-one error and meant to be 0 (or empty)? Using '1' to refer to the second select field surely confuses the average person. (Yes, I realise it's a C thing). Why the SQL query is wrapped in quotes is still odd. It seems to be a display error in the property.
I have attached an odb file that demonstrates corruption in the List box List content property. - Open the attached junk.odb. - Edit the Orders form. - Open the control dialog for the Order# list box. - Look at the List content SQL query. You should see that it is missing the FROM clause. - Append "FROM "Stock Items" to the SQL query. - Turn design mode off and check that the list box works. - Save and exit the form editor. - Reopen the form editor and check the List content property that you appended to. You should see that the FROM clause is gone again.
Created attachment 34632 [details] Corrupt List content property demo
change owner
clu->rosspjohnson: if i understand you right, you loose your new inputted properties - does it happen also with other settings in property browser (issue 63814)? if that is the case the workaround is to click into an other cell after inserting your properties. may you acknowledge that thx
Issue 63814 doesn't solve this. However, I've just realised that my instructions to reproduce the problem missed a crutial fact. The problem only seems to appear if I add a newline (Shift+Enter) in the SQL query in the list content property box. In the sample database, if I append 'FROM "Stock Items"' to the end of the existing line it appears to remain unchanged through the save/close/(re)edit cycle. If I type Shift+Enter to start a new line in the list box, and then type: FROM "Stock Items" then the FROM clause is removed again. That is, anything after the Shift+Enter is removed. I checked this by appending 'FROM [Shift+Enter]"Stock Items"'. I don't believe this is how it is meant to behave since it is inconsistent with the Check box control and with other multiline property boxes where an SQL statement can be entered. However, the workaround in this one case does appear to be to avoid Shift+Enter when entering an SQL statement.
"shift-enter" is the point. I use OOo 2.0.2 on WinXP. If I insert a shift-enter, then it becomes a ";" in the SQL-string. If I reopen the file the string is cut at the first ; It seems to get lost, when closing the file. If I extract the content.xml while the file is open, the SQL-string is complete. If I extract it, after closing the file, the SQL-string is cut in the content.xml.
i can repro that like described
Reset assigne to the default "issues@openoffice.apache.org".