Description: If Firebird database is not opened, the SQL 'insert' seems to work. But Firebird table doesn't save the data. Steps to Reproduce: 1.Connect Firebird database 2.Execute SQL statement to insert data 3. Actual Results: No new records added and Firebird doesn't show any error message. Expected Results: It should be like HSQLDB. HSQLDB doesn't have to be opened, it saves new records smoothly. Reproducible: Always User Profile Reset: No Additional Info:
@Nukool: we need a sample ODB and Calc file with basic macros in order to attempt to reproduce this. Please also provide detailed instructions on how to proceed to show the buggy behaviour.
Created attachment 155595 [details] Calc with LibreOffice BASIC 1. Change data in cell A10. 2. Run UpdateSQL_Original without opening database file REG_AA. 3. Open database file REG_AA, you will find no new record. 4. Do not close database file REG_AA. 5. Run UpdateSQL_Original. 6. Open database file REG_AA, you will find new record inserted.
Created attachment 155596 [details] This is the related/embedded Firebird database file Thank you so much in advance, now I have to use HSQLDB for solving unexpected problems. Expecting to hearing a good news from you shortly and I will move to Firebird then.
[Automated Action] NeedInfo-To-Unconfirmed
@Nukool : thanks ! You did register your ODB file with the LO application environment first didn't you ? If you read the macro code in the Calc file, it requires a database context to be correctly setup : rem Change this for your registered Base name Dim dbNAME As String : dbNAME = "REG_AA" ' 'Add Dim dbNAME As String : >>> REG_AA Db = Context.getByName(dbNAME) In other words, you are advised to register the database with the LO environment through Tools > Options > LibreOffice Base > Databases > New If you load REG_AAA into LO then the database context is automatically established, and running the macro will let you write to the database for as long the ODB file is in memory. However, if you just try running the macro without having registered the database with the LO environment so that it knows where to find (and load) the ODB file, then running the macro will fail. Note that if I register the ODB with the LO environment, when I try to run the Update_SQLOriginal macro, I get an error message: Erreur d'exécution BASIC. Une exception s'est produite : Type: com.sun.star.sdbc.SQLException Message: firebird_sdbc error: *conversion error from string "23/02/2019" caused by 'INSERT INTO "TABLE3" ("NAME", "Note") VALUES ('Input 104', '23/02/2019');' . I get this error irrespective of whether the ODB is file is loaded or not.
Tested with Version: 6.3.1.2 Build ID: b79626edf0065ac373bd1df5c28bd630b4424273 Threads CPU : 4; OS : Mac OS X 10.15.1; UI Render : par défaut; VCL: osx; Locale : fr-FR (fr_FR.UTF-8); Langue IHM : fr-FR Calc: threaded
(In reply to Alex Thurgood from comment #5) > @Nukool : thanks ! > > You did register your ODB file with the LO application environment first > didn't you ? > > If you read the macro code in the Calc file, it requires a database context > to be correctly setup : > > rem Change this for your registered Base name > Dim dbNAME As String : dbNAME = "REG_AA" ' 'Add Dim dbNAME As String > : >>> REG_AA > Db = Context.getByName(dbNAME) > > In other words, you are advised to register the database with the LO > environment through > > Tools > Options > LibreOffice Base > Databases > New > > If you load REG_AAA into LO then the database context is automatically > established, and running the macro will let you write to the database for as > long the ODB file is in memory. > > However, if you just try running the macro without having registered the > database with the LO environment so that it knows where to find (and load) > the ODB file, then running the macro will fail. > > > > Note that if I register the ODB with the LO environment, when I try to run > the Update_SQLOriginal macro, I get an error message: > > Erreur d'exécution BASIC. > Une exception s'est produite : > Type: com.sun.star.sdbc.SQLException > Message: firebird_sdbc error: > *conversion error from string "23/02/2019" > caused by > 'INSERT INTO "TABLE3" ("NAME", "Note") VALUES ('Input 104', '23/02/2019');' > . > > > I get this error irrespective of whether the ODB is file is loaded or not. Yes, I have registered.
Created attachment 155660 [details] From your sample data in cell A10
Created attachment 155661 [details] SQL statement used
Created attachment 155662 [details] New record inserted
Created attachment 155663 [details] Working with embedded HQSQLDB, no need to open database
Created attachment 155664 [details] HSQLDB
There appears to be two problems with the original post. The first is the Calc file code. In `Sub UpdateSQL_Original` this statement: Dim TestDate AS Date should be: Dim TestDate AS String This corrects the error seen by @Alex Thurgood. The second problem is the title of the bug. The database does not need to be open to process the SQL transactions. The actual problem is with the registered database never opened, if the Calc file is closed, a Commit is not issued to the Firebird DB and the data is thus not saved. There also does not seem to be any method to send a Commit from Calc to accommodate this. If before closing The Calc file, the Base file is opened and saved (save indicator is on at this point), the data is saved (committed). There is no warning anywhere when in Calc that upon closing the database data needs to be saved. Confirmed with: Version: 6.3.3.2 Build ID: a64200df03143b798afd1ec74a12ab50359878ed CPU threads: 8; OS: Linux 5.0; UI render: default; VCL: gtk3; Locale: en-US (en_US.UTF-8); UI-Language: en-US Calc: threaded
@Stang : thanks! Seems to be a duplicate of bug 112458 ? Whilst in this case it is a macro executed SQL update statement that is executed, the data isn't saved to the database file if the ODB file isn't opened or loaded in memory (hidden should also be possible) As Robert mentioned in comment 10 of bug 112458, there would appear to be a workaround using the active connection FLUSH command.
@Alex Interestingly enough, with your comment went back and re-tested tdf#112458 and that now works for HSQLDB embedded. However not for Firebird embedded and the flush command is not valid - see attachment there. For this report, FLUSH gives same error for both HSQLDB and Firebird embedded. Also keep in mind this deals strictly with SQL and not a form although I tested this using a form in Calc also. Still appears both issues, at least with Firebird, involve the commit situation.
With further testing, can get to FLUSH. Doing this with the connection, just need to add another line in the macro after the SQL executeUpdate and before the close() line: Conn.getParent().flush Now data is saved to Firebird embedded without it being opened.
(In reply to Stang from comment #16) > With further testing, can get to FLUSH. Doing this with the connection, > just need to add another line in the macro after the SQL executeUpdate and > before the close() line: > > Conn.getParent().flush > > Now data is saved to Firebird embedded without it being opened. @Stang: thanks for your tests ! From memory, the .getParent call is what binds the form (be it Calc or an independent Writer form) to the connection context (in the MVC world of UNO), so this is indeed the issue with the commit failing in normal use, i.e. the failure to find the parent if the ODB file isn't open simultaneously. However, if this now works for embedded hsqldb, then it should also work for Firebird...so perhaps the code that creates an embedded Firebird ODB file fails to expose that MVC correctly ? I'm not up to date with the way MVC works in the context of an embedded Firebird ODB, but really we ought to be striving to obtain the same thing. @Lionel, Tamas : putting you on copy for thoughts on this?
Dear Nukool Chompuparn, 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://web.libera.chat/?settings=#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug