OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Flushing data to a OOo Base database
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
Dennis Jelavic
Power User
Power User


Joined: 13 Nov 2008
Posts: 67

PostPosted: Sun Apr 29, 2012 12:57 am    Post subject: Flushing data to a OOo Base database Reply with quote

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?
Back to top
View user's profile Send private message
Dennis Jelavic
Power User
Power User


Joined: 13 Nov 2008
Posts: 67

PostPosted: Sun Apr 29, 2012 4:21 pm    Post subject: Reply with quote

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

Code:
Sub proc1()
   Dim ID as Integer
   Dim sql1,s as String

   Context = CreateUnoService("com.sun.star.sdb.DatabaseContext")
   DB = Context.getByName("Test")
   conn = DB.getConnection("","")
   
   s = ""
   Stmt1 = conn.createStatement()
   sql1 = "SELECT * FROM EMPLOYEE"
   
   res1 = stmt1.executeQuery(sql1)
   While Res1.next()
      s = s & res1.getInt(1) & "   " & res1.getString(2) & "   " & res1.getString(3) & chr(10)
      ID = res1.getInt(1)
   Wend
   
   id = id + 10
   
   sql2 = "INSERT INTO EMPLOYEE (ID,NAME,CITY) VALUES (?,?,?)"
   stmt2 = conn.prepareStatement(sql2)
   stmt2.setInt(1,ID)
   stmt2.setString(2,"AAAA")
   stmt2.setString(3,"BBBBBBBBB")
   stmt2.executeUpdate()
   
   s = s & id & "   " & "AAAA" & "   " & "BBBBBBBBBBB"
   msgbox s
   'conn.close()
end Sub


Code:
Sub proc2()
   Dim sql1,s as String
   Dim ID as Integer

   Context = CreateUnoService("com.sun.star.sdb.DatabaseContext")
   DB = Context.getByName("Test")
   conn = DB.getConnection("","")
   
   s = ""
   Stmt1 = conn.createStatement()
   sql1 = "SELECT * FROM EMPLOYEE"
   
   res1 = stmt1.executeQuery(sql1)
   While Res1.next()
      s = s & res1.getInt(1) & "   " & res1.getString(2) & "   " & res1.getString(3) & chr(10)
      ID = res1.getInt(1)
   Wend
   
   
   sql2 = "INSERT INTO EMPLOYEE (ID,NAME,CITY) VALUES (?,?,?)"
   stmt2 = conn.prepareStatement(sql2)
   stmt2.setInt(1,44)
   stmt2.setString(2,"XXXX")
   stmt2.setString(3,"YYYYYYY")
   stmt2.executeUpdate()
   
   s = s & 44 & "   " & "XXXX" & "   " & "YYYYYYYY"   
   msgbox    s
   conn.close()
end Sub


I then ran 8 test scenarios executing Proc1 and then Proc2 in each scenario. In the first 4 scenarios Proc2 was executed immediately after Proc1 without closing down the application in between. In the second 4 scenarios the application was closed down after execution of Proc1 and then relaunched to execute Proc2. The 4 scenarios in each of these groups of 4 were with:

1) Proc1 conn.close() included --- Proc2 conn.close() included
2) Proc1 conn.close() included --- Proc2 conn.close() commented out
3) Proc1 conn.close() commented out --- Proc2 conn.close() included
4) Proc1 conn.close() commented out --- Proc2 conn.close() commented out

At the start of each scenario run the database consisted of three records(id =10,20,30)

The records on the database after closing the application at the end of each scenario were:

Group 1 (1)
    10
    20
    30
    40
    44


Group 1 (2)
    10
    20
    30
    40


Group 1 (3)
    10
    20
    30


Group 1 (4)
    10
    20
    30


Group 2 (1)
    10
    20
    30
    40
    44


Group 2 (2)
    10
    20
    30
    40

    Group 2 (3)
10
20
30
44


    Group 2 (4)
10
20
30


    I can understand all the results apart from Group 1 (3). Does anyone have any ideas on why the records added were not flushed to the database on disk.
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Mon Apr 30, 2012 12:24 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Dennis Jelavic
Power User
Power User


Joined: 13 Nov 2008
Posts: 67

PostPosted: Mon Apr 30, 2012 1:26 pm    Post subject: Reply with quote

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:

Code:
Context = CreateUnoService("com.sun.star.sdb.DatabaseContext")
   DB = Context.getByName("Test")
   conn = DB.getConnection("","")


I presume at this point that the data from the .odb document on disk is used to create an in-memory copy of the database. Is this correct? Where is this data stored?

4. IF I now execute a second macro from within the same .ods Calc document which also executes

Code:
Context = CreateUnoService("com.sun.star.sdb.DatabaseContext")
   DB = Context.getByName("Test")
   conn = DB.getConnection("","")


Does this create a second copy of in-memory data? Or does this second connection simply point to the copy established in 3.

The test scenarios seem to show that there is only one copy of in-memory database data. However the only way I can explain the results I obtained in my test scenario Group 1 test 3 is by assuming the in-memory database was never flushed to disk.

In this test I have exactly the scenario I described above - 2 Calc macros within the same Calc document which connect to a separate Base document.

The database has, before any test executions, 3 records in it. The id's of these 3 records are 10,20 and 30. Proc1 adds an additional record with id = 40. This macro (Proc1) does not contain a conn.close() statement. After execution of the Proc1 macro, I then execute the Proc2 macro. This reads the database (the in-memory databse) and I can see that it has read all the data as it exists after the execution of Proc1 - namely records 10,20,30,40. It then adds a record of its own (id = 44) and executes a conn.close().

I then close and exit the Calc document and open the .odb database document. At this point I would expect the see all the data (namely records 10,20,30,40 and 44) on the database but in fact only records 10,20,30 are there. That is, neither of the records added by Proc1 or Proc2 have been flushed to the physical disk storage.

How is the in-memory data propagated to disk? I presume the act of exiting the Calc document does this in some way. When I exit the Calc document I do so by clicking the Windows "X" in the top right hand corner of the window.

The interesting thing is that when I add a conn.close() statement to the end of Proc1, everything happens as you would expect - that is, after running Proc1 and Proc2 , closing the Calc document and then opening the .odb document all the data (records,10,20,30,40 and 44) are there.

My confusion started with what exactly does the conn.close() do but it has widened to a deeper confusion. Any light you can throw on this will be much appreciated.
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Mon Apr 30, 2012 1:49 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Mon Apr 30, 2012 2:32 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Dennis Jelavic
Power User
Power User


Joined: 13 Nov 2008
Posts: 67

PostPosted: Mon Apr 30, 2012 3:21 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Tue May 01, 2012 12:23 am    Post subject: Reply with quote

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

Quote:
But I would still like to know what is happening physically to the data in the database.
I don't know the answer and as far I understand it is to difficult for me to explain it. I have the idea when this question is important for you, you use the wrong tool for your problem. This can be also true for you when you need a lot of macros.

Study the tutorials and examples on the other forum

Romke
_________________
OOo 3.4.5 on openSUSE 12.1
Use this forum : http://user.services.openoffice.org/en/forum
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Tue May 01, 2012 2:40 am    Post subject: Reply with quote

Hello

Code:
REM  *****  BASIC  *****
option explicit
Sub Main
' You can use this for a from in a calc spreadsheet.
' The form does not need controls.
' When you want use this then the sheet must be the Active sheet
Dim oDoc,oSheet,oForm
oDoc= thiscomponent

dim oController
oController=oDoc.currentcontroller

'test for formdesignmode
if oController.isFormDesignMode=true then oController.FormDesignMode=false


oSheet=oController.ActiveSheet
oForm=oSheet.drawpage.forms.getbyindex(0)
'print oForm.name


if oForm.isloaded= false then oForm.load

End Sub



function givemelocal( sSQL as string,oResulset as object )
' Geef een formulier of result set op als parameter met de SQL
'
oResult=oResulset.ActiveConnection.CreateStatement.executeQuery(sSQL)
'+++++++++++++++++++++++++++++++++++
If NOT IsNull(oResult) AND NOT IsEmpty(oResult) Then
   If oResult.next() Then
      givemelocal=oResult.getString(1) 'give returnvalue
   else givemelocal=""
   End If
else
givemelocal="" ' Maak een zero lenght string
End If
end function

sub FlushConnection
' You can use this for a from in a calc spreadsheet.
' The form does not need controls.
' When you want use this then the sheet must be the Active sheet
Dim oDoc,oSheet,oForm
oDoc= thiscomponent

dim oController
oController=oDoc.currentcontroller

'test for formdesignmode
if oController.isFormDesignMode=true then oController.FormDesignMode=false

oSheet=oController.ActiveSheet
oForm=oSheet.drawpage.forms.getbyindex(0)
if oForm.isloaded= false then oForm.load
oForm.ActiveConnection.parent.flush
end sub
This code example is not useful but shows only that you have to use the service already exported by your document. When you need a database connection make a form to that database then OOo will do all the things you need. This form can be only a reference to a table or query without controls.

I have change the code: I did get more information and so adjust the code.
Romke
_________________
OOo 3.4.5 on openSUSE 12.1
Use this forum : http://user.services.openoffice.org/en/forum


Last edited by RPG on Wed May 02, 2012 5:17 am; edited 2 times in total
Back to top
View user's profile Send private message
Dennis Jelavic
Power User
Power User


Joined: 13 Nov 2008
Posts: 67

PostPosted: Tue May 01, 2012 4:58 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Wed May 02, 2012 12:20 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Wed May 02, 2012 12:34 am    Post subject: Reply with quote

Hallo

Code:
Sub Main
   Context = CreateUnoService("com.sun.star.sdb.DatabaseContext")
   DB = Context.getByName("medic")
   conn = DB.getConnection("","")
   conn.parent.flush
End Sub


I did have also change the example code. I have expand it for flushing data.

I hope this is what you need.

Romke
_________________
OOo 3.4.5 on openSUSE 12.1
Use this forum : http://user.services.openoffice.org/en/forum
Back to top
View user's profile Send private message
Dennis Jelavic
Power User
Power User


Joined: 13 Nov 2008
Posts: 67

PostPosted: Wed May 02, 2012 1:48 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Wed May 02, 2012 2:35 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Wed May 02, 2012 5:48 am    Post subject: Reply with quote

Hello

Code:
Sub Main2
   Context = CreateUnoService("com.sun.star.sdb.DatabaseContext")
   DB = Context.getByName("medic")
   conn1 = DB.getConnection("","")
   conn2 = DB.getConnection("","")
   print "same Object" , EqualUnoObjects(conn1,conn2)
   print "same Object" , EqualUnoObjects(conn1.parent,conn2.parent)
   print "same Object" , EqualUnoObjects(DB,conn2.parent) 
   print "has interfaces" ,HasUnoInterfaces(conn1,"com.sun.star.util.XFlushable")
   print "has interfaces" ,HasUnoInterfaces(conn1.parent,"com.sun.star.util.XFlushable")
   print "has interfaces" ,HasUnoInterfaces(DB,"com.sun.star.util.XFlushable")
End Sub


With this code you can see and learn what are the same objects and what interfaces are exported.

Now you can see the different connections are real different objects but the parent of this object are the same. So my idea the have to much connections is possible wrong.
I do this also for the learning me self.

Romke
_________________
OOo 3.4.5 on openSUSE 12.1
Use this forum : http://user.services.openoffice.org/en/forum
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group