After my Open office .odb database crashed and I lost about a day of work, I decided to convert my database from embedded to the more robust front end - back end configuration.
http://user.services.openoffice.org/en/forum/viewtopic.php?p=162653#p162653
I found help sources like the above and a few others, but it seemed not all the information was put together neatly for Linux Mint / Ubuntu /Debian users, so I thought I would document my own conversion attempt.
!! FINAL WORD FROM ME !! Moving onto another approach for now. Got stuck on Java Heap Space errors.
In the End: I changed to MySQL backend with LibreOffice Base front end. I've been running solid for over a year with this new configuration, and I love it.... Runs well and MySQL is forgiving of me turning my computer on and off daily without taking special steps. Speed Now is much FASTER! Here are my notes:
http://techtips-tom.blogspot.com/2012/05/libreoffice-base-embedded-hsqldb.html
I will either use database as embedded until OpenOffice/LibreOffice implements full support of HSQLDB 2.x or I will Install MySQL server and use it as the back end.... look for upcoming posts :)
I use Linux Mint 11, 64bit Gnome with LibreOffice 3.5.3.2 Build 2
First step is to setup the database program to allow multi-mode
http://www.oooforum.org/forum/viewtopic.phtml?p=396523#396523
Being on Linux Mint 11, I need to follow the 'Manual "multi-mode" database setup:' section of the above reference. Here are my steps precisely
- Make a couple copies of my database and place them in other directories and back up devices for safe keeping away from harm!
- Open my database and check Tools/Options/Java
- Use a Java runtime environ IS already checked
- Sun Microsystems Inc. 1.6.0_26 is selected
- I suppose this is recent enough, though I have a later version downloading in case I need to install it.
- Could not find hsqldb.jar on my system anywhere, searched many forums and help documents with no good reference on what to do other than install HSQL 2.x. In searching for help, I found it very wanting, so I stumbled along.... and happened to get it right the first time: I went to http://hsqldb.org/ and downloaded 2.2.8 file as a .zip file.
- Downloaded hsqldb-2.2.8.zip
- unzip this file
- transfer the hsqldb directory from inside this directory into my Home folder /home/UserName/hsql
- renamed it with . in front so it is hidden directory... /home/UserName/.hsqldb
- the hsqldb.jar file is located in the /lib subdirectory and the help document states it was designed to work with java 1.6, so the java runtime I have should work fine.
- Back to Base....
- Open Base
- Open tools/options/java and push the Class Path button
- Add Archive... and select /home/UserName/.hsqldb/lib/hsqldb.jar
- Creating a blank two part database (in this example I will use 'MyAccounts' as the database name
- create an empty new directory for your database like: /home/UserName/Accounting/MyAccounts
- Select Connect to an existing database
- Select JDBC and hit next
- Datasource URL: jdbc: hsqldb:file:/home/UserName/Accounting/MyAccounts/MyAccounts;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false
- there are no spaces in the above
- the first MyAccounts is the residing directory name
- the second MyAccounts is for the created file name
- the other parameters I don't really understand yet, though one can guess at some obvious interpretation
- JDBC driver class = org.hsqldb.jdbcDriver
- no idea why it is that, but it works
- UserName = SA
- unclear if that was needed or just preference of the original blog poster
- Keep both defaults of register the database and save it
- this created a new database, but asked me to save it, so I put in the same name, MyAccounts in the MyAccounts directory and it seemed to work.
- So what I have now is a totally blank database named MyAccounts in the /home/UserName/Accounting/MyAccounts directory. Besides the .odb file there are a few other files which we will replace in the next step.
Next convirt my existing database into the new file:
To one of your copies of your original database:
- Rename the dabase to [itsName.zip] .... that is change .odb to .zip
- Open the zip with a zip tool and extract the contents of the 'database' folder into your MyAccounts folder.
- in that folder, delete the existing MyAccounts.script, MyAccounts.properties, MyAccounts.data files if they exist
- Do NOT delete the MyAccounts.odb file :)
- Rename the extracted files (script, properties, data) to MyAccounts.script, MyAccounts.properties and MyAccounts.data
- Now when I open my database, MyAccounts.db, I see the tables from my old database, but none of the queries, forms or reports
Now Copy Queries, Forms and Reports
The above items need to be copied from the old database.
- For Forms and Reports, it is as easy as opening your old database at the same time as your new one and dragging each Form and Report from the old database into the new Database front end.
- For Queries, I ran into a problem that I got an error whenever I tried to drag a query over:
- Error given:
- The connection to the data source "NAME of database" could not be established.
- file input/output error
- java.lang.NullPointerException.properties /path/of/database/
- This error is apparently caused by the later, incompatible version of hsqldb.jar being used. I was able to correctly open older databases when I removed the reference to the new hsqldb.jar file in the LibreOffice Tools/Options/java/Class Path menu area. So I could then open the older database file and access tables and queries, but not the new separated one (of course).
- The solution was to keep the reference to the new hsqldb.jar, then in the old database, Right click each Query, select Copy, then acknowledge the error message, then in the new database, right click in the Query area and select paste. Simple work around once discovered.
Persistent Java Heap Space Error
OK, so now when I open a large table or query I get an out of memory Java Heap space error. So far unable to work around even with parameters passed to java -Xms1024m -Xmx2048m. I tried many things here, but in the end was still stuck. I then got thinking, maybe it is better to wait until hsqldb 2.x is officially supported in Base, otherwise I may struggle with this with every updated (assuming I could work through it). I could end up with problems bigger than I currently have.
FINAL WORD FROM ME: Moving onto another approach for now. Got stuck on Java Heap Space errors.
Copying Macros ... I did this, but no bother sharing detailed steps, here is other help copied from someone else's online post.....
Macros require export/import within the Base IDE (Integrated Development Environment) "organize" functions (more on this as needed).
NOTE: table column Field Properties workaround: Right-click your table(s) to Edit or Copy them. The Edit function is now limited to Add/Delete table columns/rows. The Copy function is now used to adjust existing table column/row properties. Simply Copy the table to a newly named table; adjust column properties as desired using the popup copy-table Wizard; after confirming the results simply delete the original table; then repeat the table Copy to reuse/reset the table to its original name; reset any global table relationships. Of course, those proficient with SQL can simply bypass the GUI workaround and perform these adjustments directly with ALTER TABLE ALTER COLUMN and/or ALTER TABLE RENAME TO commands. In any case, it's a good practice to issue the CHECKPOINT DEFRAG command using 'Tools>SQL...' after adjusting tables in this manner. And don't forget to use View > Refresh Tables to see your changes when working directly with SQL to alter table structures.
Autonumber malfunction:
http://www.oooforum.org/forum/viewtopic.phtml?p=398884#398884
http://www.oooforum.org/forum/viewtopic.phtml?p=396523#396523
Other helpful references:
Memory issues:
https://confluence.atlassian.com/display/JIRA/Increasing+JIRA+Memory#IncreasingJIRAMemory-guidance
http://www.techpaste.com/2012/04/java-lang-outofmemoryerror-class-allocation-error-weblogic-jrockit-jdk-r28/
http://www.oooforum.org/forum/viewtopic.phtml?p=391946#391946
