I have a MySql database with a Base front end. Some of my listboxes use a lookup on a single column in a table, such as: Bound field: 1 Data Field: CurrencyCode Input required: Yes List content: SELECT "CurrencyCode" FROM "MyAccounts"."Currency" AS "Currency" ORDER BY "CurrencyCode" ASC Type of list content: sql In 3.5.7 (ubuntu 12.04) this works fine. I previously tried to set the bound column to 0 (ie the 1st field) but that always gets reset to 1 by Base. The 'CurrencyCode' column in table 'Currency' table is a 3 character code, and is the primary key. With a MySql database, using MySql native connector, on existing valid data, I get the following error: "The Contents of a combo box or list field could not be determined" "index out of range" Trying to emulate this with an HSQL engine failed, it works as expected, so I am unable to post a demonstration odb. I tried changing the list box to use 2 fields in the SELECT statement, as follows: SELECT "CurrencyCode", "CurrencyCode" FROM "MyAccounts"."Currency" AS "Currency" ORDER BY "CurrencyCode" ASC This works with MySql as well as HSQL, so I have a work-around, but I have to edit many list boxes to enable me to continue. I tried placing the single-field sql statement in a Base Query rather than using sql in the control definition. The query run on its own works. When used as the source of the list box it gives the same error as above. To summarise, it appears that there is a problem using a single (text) bound field in an sql or query lookup from a Mysql database to populate a list box. Given that I cannot supply a demo, I'm willing to try other experiments if it would be useful.
Created attachment 81527 [details] An HSQL version The attached ODB contains an HSQL database and a form, AC, that when opened shows items in Table AC with a list box containing Currency codes from Currency. This works. If you create the same two tables (AC and Currency) in MySQL, and modify the ODB and AC form to use them, it fails as described in my report. I also tried using JDBC instead of MySQL Native Connector. This also fails, albeit with a slightly different error message: "Column index out of range, 2>1."
Hi Tim, In your hsqldb file, the link between the two tables shows up as a 1-n join in the Relations dialog. How have you got this set up in your mysql db ? Mysql doesn't enforce referential integrity if your are using In order to give us a better idea, could you post the output of : SHOW CREATE TABLE tablename for each table from your mysql console ? Or better still, dump the database as sql output using mysqldump (which would give us the data too). My concern is that if I just try to convert your hsqldb table definitions from within LO, I might not get exactly what you have in your own db setup (field definitions, foreign keys, etc). Alex
(In reply to comment #2) > Hi Tim, > > In your hsqldb file, the link between the two tables shows up as a 1-n join > in the Relations dialog. > > How have you got this set up in your mysql db ? Mysql doesn't enforce > referential integrity if your are using Ooops, pressed send too soon ;-) Mysql doesn't enforce referential integrity if you are not using the InnoDB storage engine. Alex
So I set up my own test db using the table definitions provided in your hsqldb file and created a corresponding form manually (because the wizard is broken at the moment). I can not reproduce your problem on my master build on OSX : Version: 4.2.0.0.alpha0+ Build ID: 51c79f97a90eaf469d1d2531f3f0eccfafe0f94c FWIW, here are my mysql table definitions : CREATE TABLE `currency` ( `CurrencyCode` varchar(3) NOT NULL, `CurrencyName` varchar(40) NOT NULL, `Default` tinyint(3) NOT NULL, `Base` tinyint(3) NOT NULL, `DefaultRatetoBase` decimal(10,0) NOT NULL, PRIMARY KEY (`CurrencyCode`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 CREATE TABLE `Ac` ( `bid` int(10) NOT NULL AUTO_INCREMENT, `Ac` varchar(100) DEFAULT NULL, `cur` varchar(3) NOT NULL, PRIMARY KEY (`bid`) ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 Then I copied over the data from your hsqldb tables to my mysql tables using the Append data function from the Copy Table Wizard that starts when you drag and drop one table to another db. This fails BTW if you don't append the db alias of the new table before the table name in the wizard dialog, I will see if there is already a bug report for that. Next I set up my main form to be based on table Ac, and then substituted cur for a combobox using the following SQL query : SELECT CurrencyCode from test.Currency with SQL as the "Type of List Contents" and set the properties of the combobox to be dropdown. On testing, this works for me. Alex
Adding Lionel, Julien, Robert to CC to see whether they can reproduce.
Created attachment 81542 [details] Currency This is the Currency table, AC to follow.
Created attachment 81543 [details] AC table
It fails for me every time with Mysql with these two tables.
Tim, It could be the constraint. What happens if you remove that constraint (my sample tables do not have it, as they are ISAM based) ? Alex
Replaced my tables with yours. Still can not reproduce on OSX 10.8.4 with 4.2 master build. The form works just fine for me. Will have to see about Linux later when I'm in front of the machine again. Alex
The constraint makes no difference. Neither does the data content. I'm on Ubuntu 12.04 64 bit. I started with a downloaded RC1 build from the website, then yesterday I added the ppa for pre-releases (http://ppa.launchpad.net/libreoffice/libreoffice-prereleases/ubuntu precise main) and installed: Version: 4.1.0.1 Build ID: 410m0(Build:1) No change in behaviour from the 4.1.0.1 RC1 version I first tried. The Mysql version is 5.5.31. I am using InnoDB. When the problem first arose I only tried HSQL in order to prepare a demo to attach to this report, expecting it to fail. I was very surprised when it worked. I then back-created the sample mysql database tables I sent you from scratch, in a brand new database, to make sure it wasn't something odd in my main system database. I am using MySql Connector 1.0.3 (as well as having tried JDBC as reported previously).
Alex: I'll try to find some time after my day time job. For your information Lionel pushed some fixes about ListBox 3 days ago. Perhaps one of them may have helped but surely Lionel will bring more accurate information:-) Tim: what's your odbc version? Would it be possible you test with a recent daily build from master (future 4.2)? (see http://dev-builds.libreoffice.org/daily/master/)
I haven't tried odbc, just native mysql and jdbc. I haven't got all the libraries loaded for odbc access. If I get time I'll try that (but I'm currently hunting down another problem, this time with dates,which also used to work fine).
Also tested on Linux 32bit (Ubuntu 13.04 Raring Ringtail) with LO master build : Version: 4.2.0.0.alpha0+ Build ID: ee311c55c999a68aa677faac69b3462975ad05d4 Still can't reproduce the problem. Alex
Ah Tim, my apologies, I have been testing with Comboboxes... and not Listboxes :-/ When I swap my Combobox for a Listbox, save the form, then try and use the form, I see that the list is not displayed. If I try to save my data entry session with the form that doesn't display the list of elements, I then get : SQL Status: 23000 Error code: 1452 Cannot add or update a child row: a foreign key constraint fails ("test"."AC", CONSTRAINT "AC_ibfk_1" FOREIGN KEY ("Cur") REFERENCES "Currency" ("CurrencyCode")) So confirming that Listboxes based on SQL queries do not appear to work. Alex
When I reopen the form, I see the same error message as you with regard to the out of range index.
Error message : The contents of a combo box or list field could not be determined. Error code: 1 index out of range Alex
Not being a linux expert I can't test 4.2, since there's no deb version I can find. Being too curious for my own good I did a very quick test using ODBC to mysql. I got a similar error for the listbox, but this time the second line said: [MYSQL][ODBC 5.1 Driver][mysqld-5.5.31-0ubuntu0.12.04.2]Invalid descriptor index
Alex, Thank you for persisting. I was beginning to think I was going slightly crackers.
(In reply to comment #0) > Bound field: 1 > Data Field: CurrencyCode > Input required: Yes > List content: SELECT "CurrencyCode" FROM "MyAccounts"."Currency" AS > "Currency" ORDER BY "CurrencyCode" ASC > Type of list content: sql > In 3.5.7 (ubuntu 12.04) this works fine. Hmmm... I don't immediately understand why it used to work. Maybe it would automatically use the first column if "Bound Field" was out of range. > I previously tried to set the > bound column to 0 (ie the 1st field) but that always gets reset to 1 by Base. Does 4.1 still reset to 1? It does not for me. Set it to 0, IMHO it will work. If not, reopen. In 3.5/3.6, set it to empty instead of zero. This should work (in all versions, also in 4.1). > Trying to emulate this with an HSQL engine failed, it works as expected, so > I am unable to post a demonstration odb. For problems that don't happen with embedded HSQLDB, you can attach the .odb taht connects to MySQL *and* a dump of the MySQL database. So, I'm closing the bugs under the assumption that my explanations above help. If they don't feel free to reopen.
I set the bound field to blank and saved the form. I then opened the form, and got the same error. I edited the form again and looked at the field, the bound field was 1 again. I have tried several times. Nothing except 1 (or more) in the bound field seems to be acceptable.
(In reply to comment #21) > I set the bound field to blank and saved the form. I then opened the form, > and got the same error. > I edited the form again and looked at the field, the bound field was 1 again. In 4.1.0.1rc1?
Since last night when I changed my set-up: Version 4.1.0.1 Build ID: 410m0(Build:1) from the pre-release ubuntu ppa (as reported above).
Confirming Tim's findings also on OSX. If you set the bound field to 0 as Lionel suggests, save the form, then switch to data entry mode, you can enter new data and the listbox will display its values correctly. However, if you then close the form and re-open it, you get the error message about being beyond the index range. If you then switch to Form design mode, you can see that the listbox control has had its bound field reset to 1. Alex
I am testing with master Version: 4.2.0.0.alpha0+ Build ID: 51c79f97a90eaf469d1d2531f3f0eccfafe0f94c Alex
(In reply to comment #24) > If you set the bound field to 0 as Lionel suggests, save the form, then > switch to data entry mode, you can enter new data and the listbox will > display its values correctly. > However, if you then close the form and re-open it, (...) > the listbox control has had its bound field reset to 1. Ah, it reset after a save/load cycle. I see. Working on it.
Fixed in my dev tree. Push to official git soon. Here's a better / more robust work-around: use a combobox instead of a listbox.
Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=f237f1a616d973397511575c1eb033731d6007f7 fdo#66171 ListBox correctly save empty or zero BoundColumn 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=19d08b0c655ff66c2948d6390e32e0ad0a1a67a3&h=libreoffice-4-1 fdo#66171 ListBox correctly save empty or zero BoundColumn It will be available in LibreOffice 4.1. 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-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=60d4d5a4ec70186f64b12ebb485428e905283785&h=libreoffice-4-0 fdo#66171 ListBox correctly save empty or zero BoundColumn It will be available in LibreOffice 4.0.5. 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.
Confirm fixed in 4.1.0.2 (RC2) in 32 bit Linux Mint Debian. I note that setting boundfield to 0 or 1 makes no difference when there is only 1 field, which is fine. The 0 value does now get saved as stated. Thanks.
Setting to VERIFIED (FIXED), as per Tim's comment.