Apache OpenOffice (AOO) Bugzilla – Issue 65466
Direct hsqldb modifications to an odb file disappear
Last modified: 2013-08-07 15:45:41 UTC
I'm trying to make programmatic changes to a OOo database without the pain of setting up a database server. My solution is to: 1) Create a database in OOo using the default hsqldb embedded data format 2) Close the file, unzip it, rename the files in "database/" to "foo.data", "foo.script", etc. 3) Programmatically insert data using a Java application with the hsqldb jdbc driver 4) Rename the "database/" files to their original names, and re-zip the odb file 5) Open and browse the new odb file This works satisfactorily (although a more direct option would be nice). However, under some conditions, the data I have programmatically inserted into the database will not appear under OOo, while data I inserted manually via OOo does show up. Specifically, the original filename seems to be linked to this behavior -- I can avoid it by simply renaming my file. There are apparently two sets of data: the manual set (entered via OOo), and the programmatic set (entered directly via hsqldb). When I open my file in OOo, I see the manual set, but not the programmatic set. If I rename the file and open it in OOo, I see both sets. If I add data to the renamed file, it is visible both in OOo and via hsqldb. If I add data to the original file, it will be visible in OOo, but not via hsqldb; and when I rename the file *after* adding data, the new data is no longer visible. The only other unusual thing I'm noticing is that the layout of the table I'm manipulating was set in the original file (columns and rows are wider/taller), and those layout settings are not present in the renamed file. I suppose there may be some link. If so, the bug is probably only evident in the context of my system, and the referenced example file will not demonstrate the bug anywhere else. The file I'm having trouble with is at the linked URL. It contains a "MESSAGES" table that should have 10 records, but when the bug is evident, only has 4 records.
clu->dansmith: for me the description sounds rather like a 'faulty hack' than like a bug - i am not shure if you are right here. please try the corresponding newsgroups and/or mailinglists etc. for help and/or write an explicit and reproducable step by step description or feature enhancement (please read: http://qa.openoffice.org/issue_handling/basic_rules.html) thx
If by "faulty hack" you mean that OOo does not support direct interaction via hsqldb with an embedded database, then of course my issue falls in that domain and is not supported. My assumption was, given the open format of the odb file, external interactions with the file would be supported (otherwise, it's not really "open"). I appreciate the need for step-by-step instructions for reproducing a bug. Unfortunately, this is the best I could do. The bug seems related to the state of my system (preference files, backup/restore files, caches, etc.), and the presence of those hidden variables makes it very difficult for me to understand what is going on. If you can download the linked odb file and only see 4 records in the "MESSAGES" table, then you have something to go on. Otherwise, I can't provide any tangible evidence of the bug. When *I* download the file now, I see all 10 records. My main hope in reporting this is that someone familiar with the Base implementation might think about what happens when an odb file is modified externally and recall that some piece of code makes the assumption that such changes never occur.
clu->dansmith: due to the fact that i neither own a mac nor i see several system and other influences, i will try to reproduce that issue in a quiet minute - if you have anything to add to simplify the bug scenario, please do so (p.s. i do not see the problem in how you use the oo, but in the reproducibility of it - and one oo rule is: bugs must be reproducable) clu->all: if anybody (specially mac owner) can support me with this issue, please do not hestitate to do so thx
clu->dansmith: 1.) may you precise statement: 'Programmatically insert data using a Java application with the hsqldb jdbc driver' 2.) do you have the possibility to try this on a non mac system? thx
Created attachment 37579 [details] Java source to establish external hsqldb connection
I've just attached the Java source that I've used to create a programmatic jdb connection with an OpenOffice database file using HSQLDB. It should compile and run under any Java 5.0 platform. Note that HSQLDB must be on the runtime classpath (as found, for example, in hsqldb.jar, distributed with OpenOffice).
'back in pool' because of vacation
change owner
Hi, I take over this task and have a look on it. Bye Marc
Hi, I can't reproduce this. First I have create a java program to execute your class. Here is the code public class main { public static void main(String [] args) throws Exception { OpenOfficeDatabaseConnector o = null; try { o = new OpenOfficeDatabaseConnector("spam.odb"); java.sql.Connection con = o.connection(); java.sql.Statement stmt = con.createStatement(); java.sql.ResultSet res = stmt.executeQuery("select * from \"MESSAGES\""); while (res.next()) { System.out.println(res.getString(1)); } o.closeAndCompact(); } catch(Exception e) { o.close(); e.printStackTrace(); } } } If I run this code with the given odb file I get 10 records back. Hm, There have to be something different in your environment, but what? Do you have any ideas? Does this only happen under MAC, I have used a SRC680_m189 under Linux. Bye Marc
Here are the steps I followed which led to the bug: 1) Create a new OpenOffice embedded HSQLDB database. 2) Define a table, and add some data. 3) Close the database file. 4) Run a Java program, using the code I've attached, to add data to the file. 5) Open the database file in OpenOffice. 6) Observe the contents of the table. 7) Close the database file. 8) Run a Java program to observe the contents of the table. 9) Rename the database file. 10) Open the renamed database file in OpenOffice. 11) Observe the contents of the table. What I experienced was that step (6) only displayed the data added in step (2), while steps (8) and (11) showed all the data from both step (2) and step (4). It seems that looking at the file I've attached now amounts to repeating step (11). I'm not working on this project anymore, so I can't make a lot of effort trying to reproduce the bug. If you are unable to, and without any intuition as to what might be going wrong, I think this is probably a lost cause.
This Issue requires more information ('needmoreinfo'), but has not been updated within the last year. Please re-test with one of the latest versions of OOo - the problem(s) may have already been addressed. Either use the recent stable version: http://download.openoffice.org/index.html or consider trying the new OOo 3 BETA (still in testing): http://download.openoffice.org/3.0beta/ Please report back the outcome so this Issue may be closed or progressed as necessary - otherwise it may be Resolved as Invalid in the future. You may also wish to search for (and note) any duplicates of this Issue that may have advanced further : http://www.openoffice.org/issues/query.cgi Regards, Andrew Cleaning-up and Closing old Issues as part of: ~ The Grand Bug Squash, pre v3 ~
In response to the "needmoreinfo" prompt, it's not something I'm any longer set up to reproduce. It seems reasonable to close this ticket.
.