Bug 45947 - EDITING: Autoincrement in external HSQLDB could not be created in GUI - wrong SQL-Code
Summary: EDITING: Autoincrement in external HSQLDB could not be created in GUI - wrong...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
: 59464 (view as bug list)
Depends on:
Blocks: Database-Tables
  Show dependency treegraph
 
Reported: 2012-02-12 00:06 UTC by Robert Großkopf
Modified: 2019-07-31 15:12 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments
You see where to create the autoincrement-values in external HSQLDB (24.81 KB, image/png)
2012-05-21 08:29 UTC, Robert Großkopf
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Robert Großkopf 2012-02-12 00:06:55 UTC
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)
Comment 1 sasha.libreoffice 2012-05-17 06:01:12 UTC
@ Lionel
Greetings
What do You think about this bug?
Comment 2 Lionel Elie Mamane 2012-05-21 07:49:04 UTC
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)?
Comment 3 Robert Großkopf 2012-05-21 08:29:54 UTC
Created attachment 61920 [details]
You see where to create the autoincrement-values in external HSQLDB
Comment 4 Robert Großkopf 2012-05-21 08:31:39 UTC
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.
Comment 5 Jochen 2012-08-25 20:23:54 UTC
@ Lionel

have you still any questions?
Comment 6 Robert Großkopf 2013-01-19 10:02:32 UTC
*** Bug 59464 has been marked as a duplicate of this bug. ***
Comment 7 Fred Toussi 2013-01-23 21:33:42 UTC
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.
Comment 8 derbyflies 2014-11-16 21:29:28 UTC
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.
Comment 9 Alex Thurgood 2015-01-03 17:41:22 UTC Comment hidden (no-value)
Comment 10 QA Administrators 2016-01-17 20:05:13 UTC Comment hidden (obsolete)
Comment 11 QA Administrators 2017-03-06 14:37:25 UTC Comment hidden (obsolete)
Comment 12 excorbac 2017-10-26 00:22:12 UTC
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).
Comment 13 Alex Thurgood 2017-10-26 07:27:29 UTC
(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.
Comment 14 Alex Thurgood 2017-10-26 07:40:00 UTC
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.