Created Database in 2014. Been working fine.
Recently receiving this error message when opening DATA ENTRY FORM.
I thought it was due to an update, but IT updated Libre software and is still happening.
The contents of a combo box or list field could not be determined.
org.hsqldb.HsqlException:java.io.IOException:
java.lang.IndexOutOfBoundsException
I thought it was confused at Row ID for first record, so I deleted the first record. Still receiving the problem.
Hello,
You need to specify what: OS; specific LO version; Database used; circumstance - combo box or list box and what triggers it.
Best would be to post a sample without personal/confidential information.
Use list boxes with & without macros almost daily and see no problem even with latest LO v7.0.4.2 on Ubuntu 20.04
Also you state IT updated LO. Did you question them? Is this unique to your installation?
I had the same problem, when using ListBox (but not with ComboBox).
I use:
OS: Windows. version 10.
LibreOffice 7.2.4.1 (x64)
Database: MariaDB 10.6.5 (x64)
For to fill a VARCHAR field of a table. A list (of ListBox) from SQL (normal or native) in
SELECT “name” FROM “ophthalmo”.“alist”
produces the above error, when “alist” is a single table with ONE only column (i.e. a VARCHAR (20))
You can solve this problem, adding a new INT column (i.e. ID) in “alist”, and set ID as PRIMARY KEY (or not). Then with:
SELECT “name”,“ID” FROM “ophthalmo”.“alist”
the ListBox run correctly.
OBVIOUSLY, this is a LibreOffice error…
I see no such error with a quick test using HSQLDB and:
Version: 7.2.4.1 / LibreOffice Community
Build ID: 27d75539669ac387bb498e35313b970b7fe9c4f9
CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded
Edit: In your one column table, is that column set as a primary key? I seem to recall Base not dealing with non key tables.
Edit 2: Non-key, single column table worked for me on same system. Did use MySQL database (closer to MariaDB) using JDBC connector.
No error!
Hi, Ratslinger!. In the DatabaseWizard I do not use HSQLDB.
I use the third option (Connect to an existing database) of the first step (1. Select database), selecting MySQL from the list.
So if it works properly with the ComboBox but not in ListBox this is a LibreOffice bug, as I pointed out.
Luckily and after a lot of testing I found a way to dodge the error (as I pointed out in the previous post), but it would be nice if this bug could be fixed.
@VeridicalAngel: Could you list the SQL-code for the listbox here? Also a screenshot of the data properties of the listbox?
If you look again at my comment (Edit 2), I have tried that method also. Additionally the OP was using HSQLDB embedded and appears your situation may be different as, contrary to original question, it only involves the list box.
.
Please provide information askey for by @RobertG
There is the problem that there is no contextual help for the assignment of the list, either for the ComboBox or for the ListBox. Appears the message “Could not find Help page (404)”.
Then the utility of “Bound field” remains unknown, but it is relevant.
FIRST CASE
A table with only one column, i. e.:
CREATE TABLE alist (
name VARCHAR(50) NOT NULL COLLATE ‘utf8mb3_general_ci’,
PRIMARY KEY (name) USING BTREE
)
COLLATE=‘utf8mb3_general_ci’
ENGINE=InnoDB
;
The solution was easier: just change the “bound field” from the default value of 1 to the value 0. Then runs correctly in ListBox.
SECOND CASE
A table with two columns, to supply a value to a numeric column :
CREATE TABLE alistid (
ID INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL COLLATE ‘utf8mb3_general_ci’,
PRIMARY KEY (ID) USING BTREE
)
COLLATE=‘utf8mb3_general_ci’
ENGINE=InnoDB
AUTO_INCREMENT=3
;
Please note that (in a ListBox) it will always be the first column of the table or SQL (assigned in the “List content”) that will be displayed. Then “Bound field” will be the value that will determine which column will be supplied to “Data field”, considering that 0 corresponds to the first column and 1 to the second column.
So in this example only:
SELECT name, ID FROM alistid
with
Bound field = 1
will display name and provide ID
Another possibility for this example could be:
SELECT CONCAT(ID,’|’,name),ID FROM alistid
FINALLY
For the first item you can request that on → Bugzilla although I believe it is best the way presented. Seems more often this is used to display a string and save an integer.
.
For item 2, if I place the cursor in Bound Field and select F1, I get (this is off line help installed):
.
and more, and can also search on-line help and get the same page. There is also more information in the documentation-> LibreOffice Base Guide. See Chapter 4 - Forms
The default usage for listboxes is: Show the value of a second table, put the foreignkey to the first table. So the default for the bound field is set to “1”. Wizards in Base will create a listbox exactly this way.