Flushing data to a OOo Base database

[Dennis Jelavic]

I have a calc application in which I have created macros to perform the application tasks. These macros, in addition to manipulating the spreadsheet, also maintain some data on a Base database. I understand that the data base is an in memory database, but I have been having some difficulty in establishing exactly how data is flushed to the disk to ensure data is not lost.

Can anyone give me a succinct statement of what is required. Does closing the connection achieve this? What is the function of the flush method of the datasource?

[Dennis Jelavic]

Trying to examin the behaviour of the connection.Close() function I prepared the following two Calc macros each in a separate module.

[RPG]

Hello

First I do not understand your code and also do not understand what you want. I think you have a problem with not full understand that part of the API you are using.

http://www.openoffice.org/api/docs/common/ref/com/sun/star/sdbc/XConnection.html
In this link you can find more properties of the interfaces you use. Maybe you need to commit data or not.

http://www.openoffice.org/api/docs/common/ref/com/sun/star/util/XFlushable.html
This interfaces is sometime also used for the conecttion and you use it for flushing the data to the object.

Then there is also something with the database. You can commit all the data.

I use most of the time : Checkpoint commit

Be aware when you work with the API you have to check a lot of thing you are not aware. The problem you have can be on a real other place then you think.

There I cannot see what you are doing it is real difficult to tell more.

Romke
_________________
OOo 3.4.5 on openSUSE 12.1
Use this forum : http://user.services.openoffice.org/en/forum

[Dennis Jelavic]

Thanks Romke for your reply. You are right in assuming I don't understand the underlying physical software configuration associated with how Open Office handles database access to an embedded database. I understand the concepts of commitment processing and it is not that that I am worried about here.

Maybe you can help me understand the following:

1. When I create a Calc document and save it, I then have a .ods document on disk. If I now load this document, I have an in-memory copy of my spreadsheet data, but no copy of any database as I have not yet created any database.

2. If I then create and save a Base document complete with database data, I then have a separate document (a .odb document) on disk which contains my database. If I then load this database, I then have my database in memory.

3. If instead of loading individual .ods and .odb documents, I load my Calc (.ods) document only and then execute a macro from within that document that establishes a connection to my database via the following code:

[RPG]

Hello

When you want understand those things start study

http://wiki.services.openoffice.org/wiki/Documentation/BASIC_Guide

The question you asked are to difficult for me.

I have the idea you make it to difficult.
Learn using OOo in a normal way and forget macros. I Have really the idea you make to much problems for your own. Maybe there is not any reason to use a macro.


Romke
_________________
OOo 3.4.5 on openSUSE 12.1
Use this forum : http://user.services.openoffice.org/en/forum

[RPG]

Hello

I did reread your post and have the question: why do you make two connections to one database. I don't know if it give problems but it seems you have problems.

Romke
_________________
OOo 3.4.5 on openSUSE 12.1
Use this forum : http://user.services.openoffice.org/en/forum

[Dennis Jelavic]

The procedures I've presented here are only for illustration purposes. The actual application I am working on is too big and complex to clearly illustrate the probelms that I have been encounting.

I declare multiple connections because I've assumed that they will all refer to the same physical data and the application has many modules and many macros some of which will be run on one occasion and others on other occasions. I suppose I can declare a global connection variable and have that assigned within an event when the document is opened. But I would still like to know what is happening physically to the data in the database.

[RPG]

Hello

I think you search the problems on the wrong place.

Do you work with controls and do you place data in the controls?
when true don't forget to commit the control before you commit the data form.

When you have already a form in your document then you can do
oForm.getActiveConnection

[RPG]

Hello

[Dennis Jelavic]

Hi RPG

Thanks for your perseverence. I must admit that I am getting a little lost.

Firstly the actual application that I am working on is a financial model and it requires both a spreadsheet and a database. The application consists of some 28 modules with 603 macros and a database of 30 tables. I don't use any database forms. All my access to the database is through macros and dialogs in the spreadsheet document. The application works reasonably well except that I sometimes lose data through data not being flushed to disk. It is this issue that I am currently trying to resolve.

The application essentially creates a series of transactions which are initially stored on a spreadsheet. These transactions are then used (by pressing a button on the spreadsheet which holds them) to update balance fields on another sheet of the same spreadsheet document and they are then written to a transactions table on the database. It is this writing to the transaction file that sometimes failed (Note not always.) I have now, through use of connection.flush() statements, got the application to work without failure over the last few days but because I don't really understand what is happening behind the scenes I am not 100% certain that I have solved the problem.


My test macros here do not directly relate to the application itself. They simply mimic the type of updating that I do so that I can get an understanding of what is happening.

Getting back to your last post. It has confused me. By creating a form that is associated with the active sheet, I can't see how you associate this with the database. your code has a

oForm.Load

statement. What can this load. Ican't see that it can load anything.

I presume that what you are trying to do is establish a reference to a form on the database and then use oForm.activeConnection to get a reference to the connection. I can see that if you had a macro in a form document that you could do this, but I can't see how it is possible through a spreadsheet document macro.

[RPG]

Hello

It confuse me too. I'm a home user with only a small database 600 KB you read it real good. But the difference between you and me is I do have a real good understanding of what you can do with OOo and what you can do with the OOo API. You have a real good understanding of databases but not of OOo and the API. I think before you make big changes read on the other forum posts of Villeroy, DACM, Rudolfo. They have better understanding about what you can do with OOo for real big databases.

On the other forum you can also upload files.

I have seen one time earlier somebody did make a real complicated apllication. He did use a lot of macros. There was one problem for him: The person was real smart and could write real complicated code but he did not understand OOo and the API of OOo.The result for him was he stopped before he finished.

Macros
When you work with forms then you can control the complete behavior of the form. The example I give was simple but real. The spreadsheet I used was opened in designmode and for that reason the form was not loaded.

Flushing data
On this moment I have no idea of how you can flush the data in the way you use OOo now. I did point you to the good interface and you have to use that interface. I will try to day to find that interface while using the method you do.

I hope this help you.

Romke
_________________
OOo 3.4.5 on openSUSE 12.1
Use this forum : http://user.services.openoffice.org/en/forum

[RPG]

Hallo

[Dennis Jelavic]

Hi Again,

Thanks again for your help. One final question. Is there any particular reason why, in your sample code, you chose to use

conn.parent.flush

rather than

conn.flush




I will continue my research.

Regards

[RPG]

Hello

You start with this code
Sub Main
Context = CreateUnoService("com.sun.star.sdb.DatabaseContext")
DB = Context.getByName("medic")
conn = DB.getConnection("","")
End Sub

You can see the connection is part of the DB. DB is a far I understand not the databasedocument but maybe the datasource.

So when you want flsuh the data then you need to flush the datasource.
I think there is no difference between the next two lines:
DB.flush
conn.parent.flush

I find it always real difficult to understand which object contains which service and also which service export which interface. There is not always one method to do something but you can follow different methods. When you want continue with macros then try to use the MRI or Xray extension. They can help you a lot.

I do use the technical terms there it learns me also better to understand how it works

Romke
_________________
OOo 3.4.5 on openSUSE 12.1
Use this forum : http://user.services.openoffice.org/en/forum

[RPG]

Hello