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
Use of freedesktop.org services, including Bugzilla, is subject to our Code of Conduct. How we collect and use information is described in our Privacy Policy.