Bug Hunting Session
Bug 124340 - Firebird: Autovalue inconsistency when inserting values into table via macro
Summary: Firebird: Autovalue inconsistency when inserting values into table via macro
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.2.2.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Firebird-Default
  Show dependency treegraph
 
Reported: 2019-03-27 08:16 UTC by Gerhard Schaber
Modified: 2019-05-23 11:08 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Test file (10.19 KB, application/vnd.sun.xml.base)
2019-03-27 10:19 UTC, Gerhard Schaber
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Gerhard Schaber 2019-03-27 08:16:04 UTC
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.
Comment 1 Alex Thurgood 2019-03-27 10:01:40 UTC
@Gerhard : any chance of a test embedded firebird ODB file containing the macro with instructions on how to fire the macro (e.g. form with button)?
Comment 2 Gerhard Schaber 2019-03-27 10:05:51 UTC
BTW, I am aware that it is possible to omit the autovalue field like that:

INSERT INTO "table1" ("FirstName", "LastName") VALUES ( 'firstname', 'lastname' )

But when restoring from a backup, I would like to set the ID field explicitly, but there does not seem to be a trigger that increases the generator value to the maximum value. Maybe this is something I have to do myself, but I think HSQLDB handled it differently.

I will create a simple example.
Comment 3 Gerhard Schaber 2019-03-27 10:19:26 UTC
Created attachment 150304 [details]
Test file

Run SQL command "INSERT INTO "table1" VALUES ( 1, 'firstname', 'lastname' )"
Then try to add a record with the form (there is only one).
Comment 4 Drew Jensen 2019-04-20 12:40:28 UTC
(In reply to Gerhard Schaber from comment #3)
> Created attachment 150304 [details]
> Test file
> 
> Run SQL command "INSERT INTO "table1" VALUES ( 1, 'firstname', 'lastname' )"
> Then try to add a record with the form (there is only one).

two things here:

First the insert command, if you change that to:
INSERT INTO "table1" ("FirstName", "LastName") VALUES ( 'Walter', 'Cronkite' )

It works fine. ALMOST

So, running that in the SQL window works without a glitch.

However, if you a Query Definition window, SQL Direct set to true, and run that it successfully updated that table, with autoinc value for ID, but then returns an error that 'The cursor is not open'. But the values are already updated in the table.

I'll do this in a macro also, just to be sure but I won't be surprised if I get that, what appears to be erroneous, error message again.
Comment 5 Drew Jensen 2019-04-20 15:43:50 UTC
Using Ubuntu 18.04 with LO 6.2.3rel 

Macro execution is working as designed.

Made a copy of the firebird example odb with hsql and duplicate macro both are working identically, at least so far as I tested with table views and form views.

When a user opens a view (window on the desktop) for a Table or a Form this creates  a resultset and that result set is not automatically refreshed if the data Table is updated. A snapshot view of the data as it was at the last refresh of the resultset. 

So, as designed the database front end document developer creating the macro is responsible for refershing open result sets in their database application as appropriate. 

Setting this to Works For Me
Comment 6 Drew Jensen 2019-04-20 16:24:29 UTC
Well, it appears I was wrong.

Firebird and hsql is not identical here.

The views (table or form) under hsql sdbc will recognize that an insert happened since the last refresh on a resultset and retrieve a valid (new I am guess) autoinc value. 

Views under firebird sdbc generate an error message that there is a PK violation, and in the error message displays a value that would in fact be a collision in the table; HOWEVER if you examine the underlying Table there is an insert of the supplied data from the form and the, database wise, correct next autoinc value. 

In both cases if I refresh open result sets after the insert in the macro however there is no problem with finding the next autoinc value, but with hsql that is not required. 

I can make a quick video I think to show this.. so it is not works for me.