[Libreoffice-bugs] [Bug 124340] New: Firebird: Autovalue inconsistency

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=124340

            Bug ID: 124340
           Summary: Firebird: Autovalue inconsistency
           Product: LibreOffice
           Version: 6.2.2.2 release
          Hardware: All
                OS: All
            Status: UNCONFIRMED
          Severity: normal
          Priority: medium
         Component: Base
          Assignee: libreoffice-bugs at lists.freedesktop.org
          Reporter: gerhard.schaber at gmx.at

When adding a record to a table with an autovalue, it is not possible to leave
the autovalue field NULL. Otherwise you get the following kind of error:

BASIC runtime error.
An exception occurred 
Type: com.sun.star.sdbc.SQLException
Message: firebird_sdbc error:
*validation error for column "table1"."ID", value "*** null ***"
caused by
'INSERT INTO "table1" VALUES ( NULL, 'firstname', 'lastname' )'

Well, it is impossible to calculate the largest value, and specify it with the
INSERT statement, which is okay.

However, when doing that, the generator for that field is not getting updated,
and no form will work anymore. It will display this kind of error, because the
autoincrement value is lower than the largest ID (and therefore was already
used).

Error inserting the new record
firebird_sdbc error:
*violation of PRIMARY or UNIQUE KEY constraint "INTEG_245" on table "table1"
*Problematic key value is ("ID" = 3892)
caused by
'isc_dsql_execute'

Well, it is possible to do something like that after each INSERT statement, but
that is only a dirty workaround in my opinion:
ALTER SEQUENCE RDB$82 RESTART WITH 78010;

First you need to find the right generator for that field with something like:
SELECT RDB$FIELD_NAME, RDB$RELATION_NAME, RDB$GENERATOR_NAME  FROM
RDB$RELATION_FIELDS WHERE RDB$GENERATOR_NAME IS NOT NULL AND RDB$RELATION_NAME
= 'table1'

It would be good to either allow NULL for autovalue fields with INSERT
statements (such as with HSQLDB), or to have the generators update
automatically with every INSERT statement.

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20190327/2b6d479f/attachment.html>