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.
Dear Gerhard Schaber, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
There are different problems reported in this bug. One seems to be: How to get the right value for an autoincremented field by macro. Code with RETURNING "ID" isn't working at this moment in Base, but the following construction will help by getting the right behavior in Firebird: stSql = "SELECT RDB$GENERATOR_NAME FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME = 'Table1' AND RDB$FIELD_NAME = 'ID' AND RDB$GENERATOR_NAME IS NOT NULL" oResult = oSQL_Statement.executeQuery(stSql) WHILE oResult.next stGenerator = oResult.getString(1) WEND stSql = "SELECT GEN_ID("+stGenerator+",1) FROM RDB$DATABASE" oResult = oSQL_Statement.executeQuery(stSql) WHILE oResult.next stID = oResult.getString(1) WEND stSql = "INSERT INTO ""Table1"" (""ID"",""Date"") VALUES ("+stID+", CURRENT_DATE)" First the name of the generator for the table will be selected. Then the next generated ID of this generator will be selected and this selected value will be used for insert. So you will know the inserted ID and could use it as foreign key for other queries, updates and inserts.