Issue 65466 - Direct hsqldb modifications to an odb file disappear
Summary: Direct hsqldb modifications to an odb file disappear
Status: CLOSED WONT_FIX
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: OOo 2.0
Hardware: All All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: marc.neumann
QA Contact: issues@dba
URL: http://www.cs.rice.edu/~dlsmith/spam.odb
Keywords: needhelp, needmoreinfo
Depends on:
Blocks:
 
Reported: 2006-05-16 22:07 UTC by dansmith
Modified: 2013-08-07 15:45 UTC (History)
1 user (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---


Attachments
Java source to establish external hsqldb connection (8.32 KB, text/plain)
2006-07-07 17:59 UTC, dansmith
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description dansmith 2006-05-16 22:07:38 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.
Comment 1 christoph.lukasiak 2006-06-06 13:34:39 UTC
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
Comment 2 dansmith 2006-06-06 15:22:28 UTC
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.
Comment 3 christoph.lukasiak 2006-06-06 17:31:39 UTC
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
Comment 4 christoph.lukasiak 2006-07-04 12:53:14 UTC
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
Comment 5 dansmith 2006-07-07 17:59:38 UTC
Created attachment 37579 [details]
Java source to establish external hsqldb connection
Comment 6 dansmith 2006-07-07 18:03:50 UTC
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).
Comment 7 christoph.lukasiak 2006-07-19 15:35:09 UTC
'back in pool' because of vacation
Comment 8 christoph.lukasiak 2006-09-25 15:05:45 UTC
change owner
Comment 9 marc.neumann 2006-10-25 12:24:05 UTC
Hi,

I take over this task and have a look on it.

Bye Marc
Comment 10 marc.neumann 2006-10-25 14:49:38 UTC
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

Comment 11 dansmith 2006-10-31 17:00:55 UTC
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.
Comment 12 ace_dent 2008-05-15 13:03:47 UTC
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 ~
Comment 13 dansmith 2008-05-15 22:57:10 UTC
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.
Comment 14 Mechtilde 2008-05-18 08:46:26 UTC
.
Comment 15 Mechtilde 2008-05-18 08:47:11 UTC
.