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.
@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)?
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.
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).
(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.
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
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.
