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

Is there a way to save the database from a form?

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


Joined: 11 Mar 2010
Posts: 1

PostPosted: Thu Mar 11, 2010 7:47 am    Post subject: Is there a way to save the database from a form? Reply with quote

Hi, I've had a parts ordering database up and running for a year now. It works for the most part, but if the computer shuts down for any reason whatsoever, it will lose all the data sent from my detattched forms since the last time I actually opened up the database and saved it from there. Is there a macro that I can put on the form to make the .odb file save? I've tried googling for an answer and found nothing. Thanks in advance.
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: Thu Mar 11, 2010 2:05 pm    Post subject: Reply with quote

Hello

I use this macro. And this is activated with a button. I have to say I have it a little adjust so I have not test it in this form.
Also I use checkpoint that works good in my configuration but in the sandard configuration is maybe shutdown compact better. Test both before you use it in production.

Romke


Code:
sub SaveAll (oEv as object)
oForm =oEv.source.model.parent
dim Statement as Object
dim checkpoint
Statement = oForm.ActiveConnection.CreateStatement
checkpoint="CHECKPOINT"
Statement.execute(checkpoint)
end sub
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Fri Mar 12, 2010 8:54 pm    Post subject: Re: Is there a way to save the database from a form? Reply with quote

Bthorson wrote:
...if the computer shuts down for any reason whatsoever, it will lose all the data sent from my detattched forms since the last time I actually opened up the database and saved it from there. Is there a macro that I can put on the form to make the .odb file save?

Hmmm...
Of course I agree with Romke that issuing an SQL statement in a macro with the press of a button may solve (mask?) your issue. Likewise, I think "checkpoint" or "checkpoint defrag" are best, because "shutdown" or "shutdown compact" will require that you restart the database or .odb.

But it sounds like there's another problem. The database should be keeping a log of all committed transactions which can be used to automatically recover the database upon severance/shutdown as you described. As long as your forms are working normally, all data is "auto-committed" to the database table(s) upon saving/changing "records" while using and navigating your forms...? And if the data isn't committed then it won't show up when you navigate away from the current record and return to that record. In other words [AFAIK], the form, itself, only buffers the currently viewed record, but must commit the data to the underlying table in order to re-view that same record after navigating away. Did you recently upgrade to OOo 3.2, because it has some serious form-related bugs? By "detached forms" I presume you mean forms saved as Writer/Calc documents using a .odb as the "data source"?
Back to top
View user's profile Send private message
B Marcelly
Super User
Super User


Joined: 12 May 2004
Posts: 1453
Location: France

PostPosted: Sat Mar 13, 2010 6:46 am    Post subject: Re: Is there a way to save the database from a form? Reply with quote

Hi,
dacm wrote:
Bthorson wrote:
...if the computer shuts down for any reason whatsoever, it will lose all the data sent from my detattched forms since the last time I actually opened up the database and saved it from there. Is there a macro that I can put on the form to make the .odb file save?
(...)
But it sounds like there's another problem. The database should be keeping a log of all committed transactions which can be used to automatically recover the database upon severance/shutdown as you described.

As far as I understand Base, there is a limitation due to the concept of integrating the database (HSQL) into the Base document. Saving one modification of the database requires a complete rewriting of the Base document (which is a zip archive containing many sub-documents). And this takes much more time than updating a real database (where you only need to save the modified records), so it is not realistic to rewrite the whole document after each modification of the database.

For this reason and others, the integrated database is no more than a tool to learn how to handle a database. If you have to rely on a database, use the Base document as front-end to an external database.

You may save the complete Base document with this instruction (since version OOo 3.1)
Code:
ThisDatabaseDocument.store

_________________
Bernard

OpenOffice.org 1.1.5 fr / Apache OpenOffice 4.0.1 / LibreOffice 4.1.0
MS-Windows 7 Home SP1
This forum is spammed, use instead Apache OpenOffice forums
Back to top
View user's profile Send private message Visit poster's website
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Sat Mar 13, 2010 10:37 am    Post subject: Re: Is there a way to save the database from a form? Reply with quote

B Marcelly wrote:
Hi,

As far as I understand Base, there is a limitation due to the concept of integrating the database (HSQL) into the Base document. Saving one modification of the database requires a complete rewriting of the Base document (which is a zip archive containing many sub-documents). And this takes much more time than updating a real database (where you only need to save the modified records), so it is not realistic to rewrite the whole document after each modification of the database.

For this reason and others, the integrated database is no more than a tool to learn how to handle a database. If you have to rely on a database, use the Base document as front-end to an external database.

Hmmm...well...as I understand it, the reason to run Base as a front-end to a back-end RDBMS server is an issue of stability. OpenOffice.org, in general, has more glaring bugs than any major software package that I've encountered. Base is the most immature product in the mix and is plagued with various bugs -- so much so, that I was immediately forced to rollback to Base 3.1.1 due to show-stopper bugs in OOo 3.2 forms. Assuming that Base is intercepting, compressing, and packaging HSQLDB writes, then your data is definitely passing through more risky hands. But even if Base is simply holding the .odb file open as a 'mounted file sysem' of some sort, then Base stability remains an issue for HSQLDB writes. Hence the reason to use Base as a front-end to a proven RDBMS server (perhaps HSQLDB server in this case) is to separate the database write operations exclusively into more robust hands.

As far as speed is concerned, a single-user running HSQLDB 'embedded' using the Base defaulted 'cached' tables is inherently ~5x faster than any non-embedded/external RDBMS (including HSQLDB in server mode) due to external protocol inefficiencies. Additionally, considering the highly-compressible nature of most database data (~10:1), Zip compression speeds-up the entire process to/from most persistent-medium because it's faster to process the compression for incremental reads/writes than it is to simply read/write uncompressed data. Pre-compressed (BLOB) data such as multimedia is the exception and, as such, is normally filed separately. And nowadays a fast Solid State Drive combined with a slow or overworked CPU might challenge this compression speed advantage. And lastly, AFAIK there's no reason to write an entire Zip file (.odb in this case) simply to add data incrementally to an existing file within the Zip. So AFAIK, HSQLDB simply appends data incrementally to the 'data' file within the .odb -- but evidently through some OpenOffice file-hosting mechanisms.

That said, Base has the potential to be several-times faster than any non-embedded/external RDBMS setup because it combines embedded RDBMS efficiencies with high data compression. Now, if you're sure that Base rewrites the entire .odb file for each database write operation, then the OpenOffice software engineers are beyond incompetent. But I don't think so. I'm not even sure that Base is interacting with the HSQLDB write process (except to open the zip/odb as a mounted file system) because the GUI must be "refreshed" in order to reflect changes through direct-SQL commands whether through macros or the provided SQL command-line interface.

Romke and I both skipped over Bthorson's comment about saving the .odb file and Romke began pointing Bthorson toward direct SQL interaction with the embedded-mode RDBMS. The "CHECKPOINT" command is a fine workaround, but I was just wondering why Bthorson's forms don't "auto-commit" records to disk (I presume incrementally to the 'data' file within the .odb file) in the normal fashion.


Last edited by dacm on Sat Mar 13, 2010 12:33 pm; edited 1 time in total
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: Sat Mar 13, 2010 12:30 pm    Post subject: Reply with quote

Hello

I think there is only one reason to use the complete embedded database: You want easy share your database with other people or use it on an other computer. The zip container makes it unsecure but also there is AFAIK no logging of the data. When you use the same software but not in embedded mode then there is logging of the data.

There is an other different when you use the embedded database the HSQLDB engine use cached tables. When you use the not embedded database then you use memory tables.

I do tell this I would like if some people can tell more about this. I do trust the embedded database only for small databases who you want share with other people. For me the problem is not the embedded database but more that for most users it is to diffcult to use the not embedded database. I have study several weeks before I understand it how to do that was before OOo did use the HSQLDB but I did succeed. Then after OOo did use the embedded database I could not switch to it there I did not know the magic words: default_schema=true.

I never have had problems with HSQLDB in the way I use it. I think it would be nice if there is a good tutorial what explains what is a good use of the standard database voor OOo. There I'm only a home user with little experience I'm afraid to tell people how to use it when they want use it for serious things.

Romke
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Sat Mar 13, 2010 2:11 pm    Post subject: Reply with quote

RPG wrote:
...but also there is AFAIK no logging of the data. When you use the same software but not in embedded mode then there is logging of the data.

Yes, I mentioned logging because Bthorson specified "detached forms" which I momentarily assumed to be client-server. But you're right, I think Bthorson was specifying an embedded 'data source' .odb for use with Writer/Calc/Impress. Or maybe Bthorson didn't mean anything by it and we can simply assume embedded.

RPG wrote:
There is an other different when you use the embedded database the HSQLDB engine use cached tables. When you use the not embedded database then you use memory tables.

Yes, there's very little difference between the two. Both are 'persistent' and write every change to disk immediately (after the performance- and concurrency-enhancing delay of typically 1 second). Memory tables allow much faster reads but must be loaded at startup and must fit in RAM. Cached tables are somewhat slower upon cache-miss but allow for faster start-up and for much larger databases since only a subset (~60:1) is cached to RAM. I don't know any more technical details except that the slower cached tables were used for benchmarking HSQLDB here as mentioned before.

It is possible to convert between the two. Wink

When using the Base GUI as a front-end to HSQLDB server, you can get Cached tables by default by changing the .properties file for each database file-set.
Code:
hsqldb.default_table_type=cached


Here's the properties file dump from a standard OOo Base (embedded HSQLDB) .odb file:
Code:
#HSQL Database Engine 1.8.0.10
#Wed Feb 10 11:21:53 CST 2010
hsqldb.script_format=0
runtime.gc_interval=0
sql.enforce_strict_size=true
hsqldb.cache_size_scale=8
readonly=false
hsqldb.nio_data_file=false
hsqldb.cache_scale=13
version=1.8.0
hsqldb.default_table_type=cached
hsqldb.cache_file_scale=1
hsqldb.lock_file=true
hsqldb.log_size=10
modified=no
hsqldb.cache_version=1.7.0
hsqldb.original_version=1.8.0
hsqldb.compatible_version=1.8.0


FAQ
Quote:
Does HSQLDB store all data in memory. Doesn't memory run out as a result?

Only if you want to. By default, CREATE TABLE results in a memory table, as this is the best type for smaller tables. For larger tables, use CREATE CACHED TABLE and adjust the hsqldb.cache_scale to suite your memory use requirements (as little as 8MB or so). See the Deployment Issues section of the Guide. There is no simple rule and no imposition on the part of HSQLDB as maximum flexibility is allowed using only a couple of settings. A popular use of HSQLDB is for OLAP, ETL, and data mining applications where huge Java memory allocations are used to hold millions of rows of data in memory.


Cache Memory Allocation
Quote:
With CACHED tables, the data is stored on disk and only up to a maximum number of rows are held in memory at any time. The default is up to 3*16384 rows. The hsqldb.cache_scale database property can be set to alter this amount. As any random subset of the rows in any of the CACHED tables can be held in the cache, the amount of memory needed by cached rows can reach the sum of the rows containing the largest field data. For example if a table with 100,000 rows contains 40,000 rows with 1,000 bytes of data in each row and 60,000 rows with 100 bytes in each, the cache can grow to contain nearly 50,000 rows, including all the 40,000 larger rows.

An additional property, hsqldb.cache_size_scale can be used in conjunction with the hsqldb.cache_scale property. This puts a limit in bytes on the total size of rows that are cached. When the default values is used for both properties, the limit on the total size of rows is approximately 50MB. (This is the size of binary images of the rows and indexes. It translates to more actual memory, typically 2-4 times, used for the cache because the data is represented by Java objects.)

If memory is limited, the hsqldb.cache_scale or hsqldb.cache_size_scale database properties can be reduced. In the example above, if the hsqldb.cache_size_scale is reduced from 10 to 8, then the total binary size limit is reduced from 50MB to 12.5 MB. This will allow the number of cached rows to reach 50,000 small rows, but only 12,500 of the larger rows.


Consider the following OOo Base settings versus the HSQLDB recommended defaults:
OOo Base:
hsqldb.cache_scale=13
which equals 3*2^13 rows or 3*8192 rows or 24576 rows
hsqldb.cache_size_scale=8
which equates to 2^8 * (cache_scale rows) Bytes or 256*24576 Bytes or ~6 MB of the .data file able to be stored in the memory cache

HSQLDB default:
hsqldb.cache_scale=14
which equals 3*2^14 rows or 3*16384 rows or 49152 rows
hsqldb.cache_size_scale=10
which equates to 2^10 * (cache_scale rows) Bytes or 1024*49152 Bytes or ~48 MB of the .data file to be stored in the memory cache.

Bump those HSQLDB defaults by +1 each and you'll use up to 192 MB of RAM cache. However, but you'll need to increase the Java Virtual Machine "heap" memory size to accommodate this 192 MB of cache. This is normally done by adding a parameter (-Xmx256m) to the Java settings in OOo. If you're running hsqldb in "server- mode" then make changes to the Java "heap" settings ( -Xmx256m) in the start-up script.
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 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