Re: Changes to DB are not persistent

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