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

Commit changes to a odb db through a macro

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
scananza
General User
General User


Joined: 28 Sep 2005
Posts: 32
Location: Udine - Italy

PostPosted: Thu Nov 02, 2006 11:49 pm    Post subject: Commit changes to a odb db through a macro Reply with quote

Hello,
working with odb database and calc spreadsheet I've come through this problem:

my calc spreadsheet is connected through a form to a odb database; I've written a couple of macro that insert db data into the spreadsheet when user selects an item in a dropdown menu, this works correctly.
Then I wrote a macro to do the opposite job, inserting data from spreadsheet to odb db: it _seems_ to work in the sense that after the macro is run new data appear in the dropdown menu but they are not effectively committed to the db file and when the document is cloesd data are not in the db anymore. I noticed that if I push the data source button and I connect to the db by there new data are swapped to the file, so my question is: how can I accomplish this through a macro?

By the way: Andrew Pitonyak's base guide points out some hints on this topics on page 7 "Refresh the tables" but none seem to work for me..
Back to top
View user's profile Send private message Visit poster's website
Trex78
General User
General User


Joined: 30 Aug 2006
Posts: 24
Location: France

PostPosted: Fri Nov 03, 2006 11:26 am    Post subject: Reply with quote

Hello,

I have noticed the same, or very similar problem with Calc and Base : updating (Or inserting) a row in a table, using SQL statements, seems to work : new data appears in associated table control, but after closing and reopening the sheet, update is lost !
And, like you, commit is effective if the data source is opened with the toolbar button before closing.
Seems to happen when a new "updating" connection is opened while the "read" is still open. If connections are always closed after use, the problem does not occur ...

I was trying to avoid the overhead of these repeated open / close actions, by having a "read only" connection always opened, and an isolated one opened when necessary to perform update transactions, commited, and closed. But when the 1st connection is left open, updates are lost, although commit method is performed successfuly.

Don't know if the failure is in OpenOffice, OOBasic, or HSQLDB, but I gave up optimization until I have some time to investigate more.

Can you confirm that you have both connections opened at the same time, and see what happens if the first is closed before updating ?
Back to top
View user's profile Send private message
scananza
General User
General User


Joined: 28 Sep 2005
Posts: 32
Location: Udine - Italy

PostPosted: Sun Nov 05, 2006 11:16 am    Post subject: Reply with quote

Hello,
yes I confirm this, I guess the read-connection is the one you open when the form connects to the database through the dropdown isn't it?
So if this would be closed then the macro should commit the changes effectively? But is there a way to do this within a macro?
Back to top
View user's profile Send private message Visit poster's website
Trex78
General User
General User


Joined: 30 Aug 2006
Posts: 24
Location: France

PostPosted: Sun Nov 05, 2006 12:01 pm    Post subject: Reply with quote

Hello,

Glad you can confirm my guess, but the "read" connection is one I explicitely opened, not an automatic connection used by the table control.
This one doesn't cause any problem in my case, maybe it is closed once the rows have been read.

I only have troubles when I open a second "read-write" connection while my first one is left opened.

I've seen in another post (Lost it, sorry) that a "SELECT COUNT(*)" on the update connection, before closing it, might help (Forcing buffers reuse ? ), but I didn't try this. And it might be costly for large tables.

I feel that there's a bug somewhere : either opening two connections together should be forbidden, or any committed updates saved to disk.

Would be nice if a Base specialist can help ...

Anyway, good evening
Back to top
View user's profile Send private message
scananza
General User
General User


Joined: 28 Sep 2005
Posts: 32
Location: Udine - Italy

PostPosted: Sun Nov 05, 2006 2:54 pm    Post subject: Reply with quote

Hi,
thank you, tomorrow I'm gonna try that query (cannot do here since that's a file at office :^) and see if problem persists. I don't actually have opened any other connection to database and all I've done is an INSERT query through a macro (this should handle the open/close connection routine I think), as I pointed out no data are written to the actual file but one can refresh menubar items (I do this switching on-off design mode) and new data display in effects in the dropdown.
I think developers can easily reprdouce this with a few lines macro and a simple database/calc file and see if it is or not a bug.
This is really annoying and can cause loss of data if someone isn't aware of the problem and doesn't refresh the tables by hand.

Thank you, later
Back to top
View user's profile Send private message Visit poster's website
scananza
General User
General User


Joined: 28 Sep 2005
Posts: 32
Location: Udine - Italy

PostPosted: Mon Nov 06, 2006 3:50 am    Post subject: Reply with quote

Hello,
nope, invoking "SELECT.." won't do any difference for me....
Back to top
View user's profile Send private message Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
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