Changes to DB are not persistent

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Changes to DB are not persistent

listsandstuff
Hello,

I am new to this list, hello everybody! I already posted the following
problem to [hidden email], but here it is probably more
appropiate. Sorry!

I've a problem with a Base-Application (Basic). See below for a codesample.

The code is adding a row to a database (Base-Document with embedded
HSQL-DB) every time it gets called. However the changes are lost after
closing OOO. To make them permanent it's necessary to flush() the
DataSource.

First question: This is not exspected bahaviour, is it? Changes should
be persistently saved without calling flush(), or am I wrong?

But the trouble gets worse... The flush() - workaround works fine on
Linux (Using OOO 3.2.1 and 2.4 on Debian), but it crashes OOO 3.2.1 on
Windows (XP) ungracefully.

Any ideas? Thanx a lot,
Daniel


REM  *****  BASIC  *****
Option Explicit

REM Sub TestDB
REM Inserts a new row into a database every time it is executed
REM Requires a registered database called "test" with one column: "id"

Sub TestDB
        Dim DBContext, DataSource, Connection As Object
        Dim Statement, ResultSet As Object
        Dim NrOfRows As Integer
               
        REM Establish database connection
        DBContext = createUnoService("com.sun.star.sdb.DatabaseContext")
        DataSource = DBContext.getByName("test")
        Connection = DataSource.GetConnection("","")

        REM Insert a new row into database
        Statement = Connection.createStatement()
        Statement.executeUpdate("INSERT INTO ""test"" (""id"") VALUES (NULL)")
       
        REM Count rows in database, to check if insertion did work
        ResultSet = Statement.executeQuery("SELECT COUNT(*) FROM ""test""")

        ResultSet.next
  NrOfRows = ResultSet.getInt(1)
 
  REM Show result
        MsgBox "Rows in database: " & NrOfRows
       
        REM The above code works, but changes to DB are not persistent.
        REM That means: after closing OOO the added rows are lost
       
        REM Flushing the DataSource helps, but ... (see mail)
        REM DataSource.flush()

End Sub

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Changes to DB are not persistent

Frank Schönheit
Hi Daniel,

> The code is adding a row to a database (Base-Document with embedded
> HSQL-DB) every time it gets called. However the changes are lost after
> closing OOO. To make them permanent it's necessary to flush() the
> DataSource.
>
> First question: This is not exspected bahaviour, is it? Changes should
> be persistently saved without calling flush(), or am I wrong?

Well ... the problem with the ZIP-file approach of ODBs is that it is
impossible to immediately write all changes you did to the file.
(Anybody wishing to start a discussion how this violates fundamental
database principles ... please direct your concerns to the ODF faction.
I'd be grateful for any assistance in explaining why ZIP as a database
backend is a joke, at best.) That's basically a question of performance.

So, some kind of flushing is necessary. This is inconvenient, but in the
above sense, expected behavior.

However, note that your macro contains a resource leak, in that it
retrieves a connection, which it does not dispose afterwards - at the
very end of the Sub, you should have a
  Connection.close()
This should free the resources associated with the connection, and at
the same time flush its changes.

> But the trouble gets worse... The flush() - workaround works fine on
> Linux (Using OOO 3.2.1 and 2.4 on Debian), but it crashes OOO 3.2.1 on
> Windows (XP) ungracefully.

If you can create a small self-contained .odb (including the macro)
showing this, please submit an issue at
http://www.openoffice.org/issues/enter_bug.cgi?component=Database%20access.
Feel free to assign it to me (fs).

Ciao
Frank

--
ORACLE
Frank Schönheit | Software Engineer | [hidden email]
Oracle Office Productivity: http://www.oracle.com/office

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Changes to DB are not persistent

Alex Thurgood
In reply to this post by listsandstuff
Hi Daniel,

How about using commit() or setting up the connection instance as autoCommit ?


http://api.openoffice.org/docs/common/ref/com/sun/star/sdbc/XConnection.html#commit

http://api.openoffice.org/docs/common/ref/com/sun/star/sdbc/XConnection.html#setAutoCommit

Alex

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Changes to DB are not persistent

listsandstuff
In reply to this post by Frank Schönheit
Hi,

Frank Schönheit schrieb:

> However, note that your macro contains a resource leak, in that it
> retrieves a connection, which it does not dispose afterwards - at the
>  very end of the Sub, you should have a Connection.close() This
> should free the resources associated with the connection, and at the
> same time flush its changes.

That's a good hint. I experimented with Connection.close(), but it
didn't solve the problem. But freeing the DataSource with
DataSource.Dispose() seems to work. Now (cached) changes to the DB are
written to the odb-file. And the lock gets removed - I suspect a
locking-problem to be responsible for the crash of the windows-version.

However some uncertainty remains. I can't figure out from the
documentation when a close() or a dispose() is necessary and when not.
For example Dispose()-ing the DB-Context doesn't seem to be a good idea
as one has to restart OOO then to open a new DB-Connection ... So are
there any rules when to Dispose() and when not?

> So, some kind of flushing is necessary. This is inconvenient, but in
> the above sense, expected behavior.

Good to know, because neither the Basic Programming Guide nor the
API-reference state this necessity to flush(), close() or dispose() and
it seems to be unique to the HSQLDB-Backend. Seems to be at least
confusing to me.

>> But the trouble gets worse... The flush() - workaround works fine
>> on Linux (Using OOO 3.2.1 and 2.4 on Debian), but it crashes OOO
>> 3.2.1 on Windows (XP) ungracefully.
>
> If you can create a small self-contained .odb (including the macro)
> showing this, please submit an issue at
> http://www.openoffice.org/issues/enter_bug.cgi?component=Database%20access.
> Feel free to assign it to me (fs).

I hope DataSource.Dispose() will solve this issue, but I will figure
that out after holidays (no windows at home). Is it ok to post (odb-)
attachments to this list?

Below is the updated code.
Thank You very much and merry christmas to everybody!
Daniel.

REM ***** BASIC *****

Sub TestDB
        Dim Doc As Object
        Dim DBContext, DataSource, Connection As Object
        Dim Statement, ResultSet As Object
        Dim NrOfRows As Integer
               
  REM This (Base-)Document
  Doc = ThisComponent
 
        REM Establish database connection
        DBContext = createUnoService("com.sun.star.sdb.DatabaseContext")
        DataSource = DBContext.getByName(Doc.getLocation()) REM Open by URL
        Connection = DataSource.GetConnection("","")

        REM Insert a new row into database
        Statement = Connection.createStatement()
        Statement.executeUpdate("INSERT INTO ""testtable"" (""id"") VALUES (NULL)")
       
        REM Count rows in database, to check if insertion did work
        ResultSet = Statement.executeQuery("SELECT COUNT(*) FROM ""testtable""")
        ResultSet.next
    NrOfRows = ResultSet.getInt(1)
   
    REM Show result
        MsgBox "Rows in database: " & NrOfRows
       
        REM The above code works, but changes to DB are not persistent.
        REM That means: after closing OOO the added rows are lost
       
        REM Flushing the DataSource helps, but ... (see mail)
        REM DataSource.flush()
       
        REM This seems to solve the problem
        Connection.Close()
        DataSource.Dispose()
End Sub


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Changes to DB are not persistent

Frank Schönheit
Hi Daniel,

> That's a good hint. I experimented with Connection.close(), but it
> didn't solve the problem.

Hmm, I would have expected this to implicitly flush the changes.

> But freeing the DataSource with
> DataSource.Dispose() seems to work.

Not a good idea, see below.

> Now (cached) changes to the DB are
> written to the odb-file. And the lock gets removed - I suspect a
> locking-problem to be responsible for the crash of the windows-version.

I don't have time trying at the moment, but the lock file should be
removed, too - basically, it indicates that there is an open connection
to the database. Assuming that your code is the only client which
accesses the database, disposing your connection should also remove the
lock.
Or is it that there are other places (the same DB doc opened in a
window? Other Basic code? An open data source browser displaying some
table?) which access the DB doc or the data source?

> However some uncertainty remains. I can't figure out from the
> documentation when a close() or a dispose() is necessary and when not.
> For example Dispose()-ing the DB-Context doesn't seem to be a good idea
> as one has to restart OOO then to open a new DB-Connection ... So are
> there any rules when to Dispose() and when not?

First, for a connection (and this is special to this object, /not/ a
general rule), "close" is equivalent to "dispose". So, let's talk about
dispose only.

In general: Dispose objects when you *own* them, don't dispose them if
you don't.

A connection obtained with getConnection is yours, you are responsible
for freeing - i.e. disposing - it.

The DBContext isn't yours - it is a global object, existing in a single
instance only, available to the complete OOo application - so, do not
dispose it, or be punished with an unstable OOo.

As for the data source: They're shared objects, owned by the database
context. So, you should not dispose them - is it perfectly possible that
some other instance - e.g. the data source browser, a mail merge
operation, or some such - holds the data source, too.

>> So, some kind of flushing is necessary. This is inconvenient, but in
>> the above sense, expected behavior.
>
> Good to know, because neither the Basic Programming Guide nor the
> API-reference state this necessity to flush(), close() or dispose() and
> it seems to be unique to the HSQLDB-Backend. Seems to be at least
> confusing to me.

That's because HSQL is the only backend which stores the data in the
.odb file - for database where this is not the case, flushing is not
necessary by definition.

>> If you can create a small self-contained .odb (including the macro)
>> showing this, please submit an issue at
>> http://www.openoffice.org/issues/enter_bug.cgi?component=Database%20access.
>> Feel free to assign it to me (fs).
>
> I hope DataSource.Dispose() will solve this issue, but I will figure
> that out after holidays (no windows at home). Is it ok to post (odb-)
> attachments to this list?

They should come through, however - at some point in time, an issue is
needed, anyways. So I'd prefer you submitting the issue, and atttaching
the .odb there.

Ciao
Frank

--
ORACLE
Frank Schönheit | Software Engineer | [hidden email]
Oracle Office Productivity: http://www.oracle.com/office


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Changes to DB are not persistent

listsandstuff
Hello Frank,

Frank Schönheit schrieb:

>> That's a good hint. I experimented with Connection.close(), but it
>> didn't solve the problem.
>
> Hmm, I would have expected this to implicitly flush the changes.

You're perfectly right. I just re-checked the code and I must have been
making some mistake earlier this day. A single Connection.Close()
flushes the changes and removes the lock.

>> But freeing the DataSource with DataSource.Dispose() seems to work.
>
> Not a good idea, see below.

That's true as well. DataSource.Dispose() doesn't seem to do any harm
when I test the code with an old OOO 2.4 (as I did earlier) but crashes
OOO 3.2.1 reproduceably.

I'll review my code after Christmas and watch for the windows-problem,
but I suspect some mistake with un-closed connections resulting in
problems with locking (like a lock is held by an unclosed connection
...). Definitely my mistake and not an OOO-issue.

Thank You very much for Your profound explanation. I'm rather new to
OOO-coding, so your help is very welcome

Bye,
Daniel.


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Changes to DB are not persistent

Frank Schönheit
Hi Daniel,

> I'll review my code after Christmas and watch for the windows-problem,
> but I suspect some mistake with un-closed connections resulting in
> problems with locking (like a lock is held by an unclosed connection
> ...). Definitely my mistake and not an OOO-issue.

Well ... nearly everything which crashes is an OOo issue. In some cases,
we tend to say that fixing the particular crash resulting of an API
misuse is so difficult (for whatever reasons) that simply using the API
the right way is the easier "fix" :), but a crash is always worth a
look, at least.
So, if you have a reproducible crash - don't hesitate to submit an issue
for it. Fixing crashes can just make OOo better (and be it by a
developer thinking about why the crash could happen at all, what's
broken in the design, and how to prevent that kind of bugs in the future
:) ).

> Thank You very much for Your profound explanation. I'm rather new to
> OOO-coding, so your help is very welcome

You're certainly welcome.

Ciao
Frank

--
ORACLE
Frank Schönheit | Software Engineer | [hidden email]
Oracle Office Productivity: http://www.oracle.com/office

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]