When creating autoincrement-values in external HSQLDB you have to add "GENERATED BY DEFAULT AS IDENTITY(START WITH 0)" in the advanced features of the database. When creating the table with autoincrement the GUI produces the Code "CREATE TABLE "table" (ID (INTEGER NOT NULL GENERATED ....)). "NOT NULL" is the wrong code in this command. In earlier versions of OpenOffice (3.1.1) you could solve the by disabling the primary key in the GUI. Now disabling the primary key disables also the autoincrement-value, which normally is OK. But with the wrong code in the GUI its a problem ... You could only create a table with autoincrement-value by creating a table with primarykey and without autoincrement in the GUI and then changing it in SQL directly with ALTER TABLE "Tabelle" ALTER COLUMN "ID" INT GENERATED BY DEFAULT AS IDENTITY(START WITH 0)
@ Lionel Greetings What do You think about this bug?
Could you please prepare step-by-step instructions on how to reproduce this bug? How does one use "external HSQLDB"? Is it through some ODBC or JDBC driver? Which one, what version? What version of HSQLDB itself? I'm not sure what you mean by "disable the primary key". Is that "remove / delete" the primary key? This looks like a non-solution, since as far as I know without a primary key LibreOffice will not be able to do any update to the data in the tables. So it is weird you mention that as a solution. Is the "GENERATED BY DEFAULT AS IDENTITY(START WITH 0)" specific to some versions of HSQLDB, or will it work across all reasonable versions (and in particular with the older version that is bundled with LibreOffice)?
Created attachment 61920 [details] You see where to create the autoincrement-values in external HSQLDB
External HSQLDB could be only a hsqldb.jar, which is in the classpath. It runs with JDBC. You could create tables in an external database. You could also create primary-keys without any problems. But you can not use the automatic incremented value in the gui. In the advanced features for an external database could explain how the automatically generated value must be created. It must be the SQL-Code "GENERATED BY DEFAULT AS IDENTITY(START WITH 0)" - or any other start-value. This dows not work. For a primary-key "ID" it creates the code "CREATE TABLE "table" (ID (INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY(START WITH 0)))" This is a wrong SQL-Code for the HSQLDB. It is created, when you create a table and choose "autovalue" for the primary-key.
@ Lionel have you still any questions?
*** Bug 59464 has been marked as a duplicate of this bug. ***
The latest HSQLDB 2.3.0 snapshot jar (a pre-release version) accepts the setting and allows you to define a column as GENERATED BY DEFAULT AS IDENTITY, with or without primary key. Note version 2.x.x separates the IDENTITY and PK attributes of a column. An IDENTITY column does not have to be a PK. Also note (START WITH 0) is not necessary as it is the default anyway. As for version 1.8.0, any change to the way LO works should be make in LO code, as version 1.8.0 will not be modified.
This bug still persists with HSQLDB 2.3.2 I'm happy to write out steps to recreate. ALTER TABLE "Tabelle" ALTER COLUMN "ID" INT GENERATED BY DEFAULT AS IDENTITY(START WITH 0) This command also sets the primary key, not just auto increment. There is also a bug when displaying primary key/auto increment immediately after creating a new record in a form.
Adding self to CC if not already on
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present on a currently supported version of LibreOffice (5.0.4 or later) https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System Please DO NOT: - Update the version field - Reply via email (please reply directly on the bug tracker) - Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to "inherited from OOo"; 4b. If the bug was not present in 3.3 - add "regression" to keyword Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa Thank you for your help! -- The LibreOffice QA Team This NEW Message was generated on: 2016-01-17
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present on a currently supported version of LibreOffice (5.2.5 or 5.3.0 https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) http://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to "inherited from OOo"; 4b. If the bug was not present in 3.3 - add "regression" to keyword Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug-20170306
Hello everybody The bug still persists in the following configuration: -Using an external HSQLDB for the Base -LibreOffice 5.2.7.2 (ie. Build ID: 1:5.2.7-0ubuntu1~xenial1) -Linux 4.10.0-37-generic (ie. Linux Mint 18.2 "Sonya") Here are the steps I did : Create a table in design view Add a "text" field with field type Text [ VARCHAR ] Add a "ID" field with field type Interger [ INTEGER ] Set AutoValue for "ID" to yes, with the auto-increment statement GENERATED BY DEFAULT AS IDENTITY and Length = 10 On saving, a dialog box shows up to give a name to the created table. Clicking OK shows an error dialog box that reads: Error while saving the table design. Unexpected token: GENERATED in statement [CREATE TABLE "Table1" ("text" VARCHAR(100),"ID" INTEGER NOT NULL GENERATED] and that's it. The only way to have this table saved is to remove the auto-increment statement. However, on re-opening the table in Edit mode, "ID" has its AutoValue set to "No" and locked (disabled control). To make this table work, I had to run the SQL string that follows: ALTER TABLE "Table1" ALTER COLUMN "ID" INT GENERATED BY DEFAULT AS IDENTITY Still, re-opening the table in Edit mode shows "ID" field's AutoValue set and locked to "No". The Base file has to be saved and re-open for the changes to take effect (ie. "ID" field's AutoValue set and locked to "Yes"). Now Table1 functions as it should. So, this bug is still around, at least for my configuration and the LO version I use (and the one before: 5.2.5).
(In reply to excorbac from comment #12) > Hello everybody > > The bug still persists in the following configuration: > -Using an external HSQLDB for the Base > -LibreOffice 5.2.7.2 (ie. Build ID: 1:5.2.7-0ubuntu1~xenial1) > -Linux 4.10.0-37-generic (ie. Linux Mint 18.2 "Sonya") > Unfortunately, LO 5.2.x is EOL/obsolete (irrespective of the support you might get from your distro). The test needs to be made against a current TDF release.
Note that we are talking about EXTERNAL HSQLDB in this bug report, not embedded. With embedded hsqldb, my testing in master Version: 6.0.0.0.alpha1+ Build ID: 57c5f980835f834f6ea96c6f5ca841b5372aa61d CPU threads: 4; OS: Mac OS X 10.13; UI render: default; Locale: fr-FR (fr_FR.UTF-8); Calc: group shows that the AUTOVALUE is set correctly via the UI and the state saved in the UI on subsequent opening. Additionally, the AUTOVALUE field is automatically set to be the PRIMARY KEY. Unfortunately, I can't test with external HSQLDB on Mac because of the conflict it provokes when pointing to (via the "Advanced" LO configuration tab), and loading, a different hsqldb.jar. I am blocked by bug 34411.
Dear Robert Großkopf, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
Seems the behavior has changed in Version: 7.2.0.2 / LibreOffice Community Build ID: 614be4f5c67816389257027dc5e56c801a547089 CPU threads: 6; OS: Linux 5.3; UI render: default; VCL: kf5 (cairo+xcb) Locale: de-DE (de_DE.UTF-8); UI: de-DE Calc: threaded You have to add "GENERATED BY DEFAULT AS IDENTITY(START WITH 0)" at the described position. When creating a new table and choosing "AutoValue" there will be shown the Auto-increment statement in the File Properties. If you donÄ't touch this field and save the table the autoincrement will work. I will close this one as WORKSFORME