Splitting an ODB File

From NeoWiki

(Difference between revisions)

Current revision

By default NeoOffice uses the embedded HSQL 1.8 database engine when creating a odb file. This is fine for quick development, but it can cause problems data loss problems if NeoOffice crashes and it makes it hard to deploy updated forms and reports to the user if you are developing a database for someone else. By splitting the database from the odb file the HSQL databse engine runs as a separate process and is unaffected inthe event of a NeoOffice crash.

This page is based on posting on the oooForum, specifically this page Base and HSQL together but not mixed? How to do it? - The Quick Fix.

1 Setting up NeoOffice
2 Testing it works
3 Splitting you databse
4 Copying across Queries, Forms and Reports
5 Copying across Macros
6 Tidying up
7 Conclusion

Setting up NeoOffice

Start NeoOffice
Select NeoOffice then Preferences
In the Preferences window select NeoOffice then Java
Click on the button Class Pathâ€¦
Click on the button Add Archiveâ€¦
Type Shift-Cmd-G
Enter the path /Applications/NeoOffice.app/Contents/basis-link/program/classes/ and click Go(N.B. you can type the first few letter of each folder name and press TAB to speed things up).
Click the button Go
Select the file hsqldb.jar and click and click Open.
Click OK to get rid of all the dialogue boxes and restart NeoOffice.

Testing it works

Download the file mydb.odb
Create a folder in your Users folder called public/databases/mydb/
Copy the mydb.odb file into this folder.
Open the mydb.odb file
It is an empty database. Add some tables to it. You should see several new files appear in the mydb folder that the mydb.odb file is in.

Splitting you databse

This section is based on Printing Multiple Values in One Field - Migrating an existing database from the all-inclusive database file (.odb) to a robust multi-mode database: on the oooForum.

Make a copy of your odb file (from now on we will call the database <dbname> so the odb file is called <dbname>.odb, so if your database is called fred.db then all occurences of <dbname> should be replaced with fred.)
Drag the copy of the database to the folder /Users/public/databases/<dbname>
Make sure it is called <dbname>.odb
Make another copy of this file and change the extension to .zip
Extract the files (using BOM Archive Helper or Stuffit Expander). There should now be a folder call <dbname> or <dbname> Folder
Double click this folder and then on the folder database
Rename the files in this folder by pre-pending the name of your database and a period. ie backup becomes <dbname>.backup, data <dbname>.data, etc.
Copy these renamed files back down the folder hierarchy to the <dbname> folder with the .odb file in it.
Rename the .odb file old<dbname>.odb
In NeoOffice from the File select New then Database
Select Connect to an Existing Database and select JDBC. Click Next
In the Data Source URL box enter: hsqldb:file:/users/public/databases/<dbname>/<dbname>;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false
In the JDBC driver class box enter: org.hsqldb.jdbcDriver (NB there must not be a space at the end of this string)
Click Next
Enter SA as the Username
Click Next
Select to register the database and open it for editing.
Save the file as <dbname>.odb in the /users/public/databases/<dbname> folder

You should now have a database with no Queries, Form or Reports. However all of your tables should be visible.

Copying across Queries, Forms and Reports

Open both old<dbname>.odb and <dbname>.odb
Drag each of the Queries, Forms and Reports from old<dbname>.odb to the corresponding section of <dbname>.odb

You will need to re-enter the name of all of the Queries. Make sure you don't make any typos!

Copying across Macros

With old<dbname>.odb open, from the Tools select Macros then Organise Macros then NeoOffice Basic
Select one of your Macro Modules and click Edit
Save each module using the Save BASIC button.
With <dbname>.odb open, from the select Macros then Organise Macros then NeoOffice Basic
Create a new Module in <dbname>
Load in the macros you saved above using the Insert BASIC Source button.
Save each module with the same name as it had before.

Tidying up

The databse is now split in to separate databse and odb files and should work ok. I have however had a few problems with Autonumber fields and checkboxes

Autonumber Fields: These seem to forget the next index, resulting in duplicate entries and failure to insert records due to duplicate Primary key fields.; Select Tools then SQLâ€¦; Use the SQL Statement ALTER TABLE "TableName" ALTER COLUMN "PrimaryKeyField" RESTART WITH xxx where xxx is the next unused value in the table.

Checkboxes: These seem to default to NULL which can cause problems; Use the SQL Statement ALTER TABLE "TableName" ALTER COLUMN "TrueFalseField" SET DEFAULT FALSE (or TRUE as required)

Conclusion

Everything should now work as before, but should NeoOffice crash it is much less likely that you will loose data. Also if you are developing a database for a client you can duplicate the above setup on their machine and yours. As you develop new versions of the Forms, Queries and Reports you can simply send then an updated odb file to copy over the old one. Their data will be unaffected. Obviously if you make changes to the tables you will have to visit the client or give them a list of steps or SQL commands to enter, but for the purposes of this guide I'll assume you know what you are doing if you are doing that!

The next steps in this process are to upgrade to HSQLDB 2.0 to take advantage of the latest features (not least the use of ~ in paths which makes it possible to put the database in your home directory without having to know the user name) and to set the database in to server mode which allows multi user access and storage of the database on a server.

I hope to have articles on this soonâ€¦

Retrieved from "https://neowiki.neooffice.org/index.php/Splitting_an_ODB_File"

 == Setting up NeoOffice ==
-Start NeoOffice
+# Start NeoOffice
+# Select {{menu|NeoOffice}} then {{menu|Preferences}}
-Select {{menu|NeoOffice}} then {{menu|Preferences}}
+# In the {{window|Preferences}} window select {{section|NeoOffice}} then {{section|Java}}
+# Click on the button {{button|Class Pathâ€¦}}
-In the {{window|Preferences}} window select {{section|NeoOffice}} then {{section|Java}}
+# Click on the button {{button|Add Archiveâ€¦}}
+# Type {{key|Shift-Cmd-G}}
-Click on the button {{button|Class Pathâ€¦}}
+# Enter the path /Applications/NeoOffice.app/Contents/basis-link/program/classes/ and click Go(N.B. you can type the first few letter of each folder name and press {{key|TAB}} to speed things up).
+# Click the button {{button|Go}}
-Click on the button {{button|Add Archiveâ€¦}}
+# Select the file hsqldb.jar and click and click {{button|Open}}.
+# Click {{button|OK}} to get rid of all the dialogue boxes and restart NeoOffice.
-Type {{key|Shift-Cmd-G}}
-Enter the path /Applications/NeoOffice.app/Contents/basis-link/program/classes/ and click Go(N.B. you can type the first few letter of each folder name and press {{key|TAB}} to speed things up).
-Click the button {{button|Go}}
-Select the file hsqldb.jar and click and click {{button|Open}}.
-Click {{button|OK}} to get rid of all the dialogue boxes and restart NeoOffice.
 == Testing it works ==
-Download the file [http://www.fileden.com/files/2010/4/30/2845000/Public/OO_o/mydb.odb '''mydb.odb''']
+# Download the file [http://www.fileden.com/files/2010/4/30/2845000/Public/OO_o/mydb.odb '''mydb.odb''']
+# Create a folder in your <tt>Users</tt> folder called <tt>public/databases/mydb/</tt>
-Create a folder in your <tt>Users</tt> folder called <tt>public/databases/mydb/</tt>
+# Copy the '''mydb.odb''' file into this folder.
+# Open the '''mydb.odb''' file
-Copy the '''mydb.odb''' file into this folder.
+# It is an empty database. Add some tables to it. You should see several new files appear in the mydb folder that the '''mydb.odb''' file is in.
-Open the '''mydb.odb''' file
-It is an empty database. Add some tables to it. You should see several new files appear in the mydb folder that the '''mydb.odb''' file is in.
 == Splitting you databse ==
 This section is based on [http://www.oooforum.org/forum/viewtopic.phtml?p=386995#386995 Printing Multiple Values in One Field - Migrating an existing database from the all-inclusive database file (.odb) to a robust multi-mode database:] on the oooForum.
-Make a copy of your odb file (from now on we will call the database '''<dbname>''' so the odb file is called '''<dbname>.odb''', so if your database is called '''fred.db''' then all occurences of '''<dbname>''' should be replaced with '''fred'''.)
+# Make a copy of your odb file (from now on we will call the database '''<dbname>''' so the odb file is called '''<dbname>.odb''', so if your database is called '''fred.db''' then all occurences of '''<dbname>''' should be replaced with '''fred'''.)
+# Drag the copy of the database to the folder <tt>/Users/public/databases/<dbname></tt>
-Drag the copy of the database to the folder <tt>/Users/public/databases/<dbname></tt>
+# Make sure it is called '''<dbname>.odb'''
+# Make another copy of this file and change the extension to '''.zip'''
-Make sure it is called '''<dbname>.odb'''
+# Extract the files (using BOM Archive Helper or Stuffit Expander). There should now be a folder call '''<dbname>''' or '''<dbname> Folder'''
+# Double click this folder and then on the folder '''database'''
-Make another copy of this file and change the extension to '''.zip'''
+# Rename the files in this folder by pre-pending the name of your database and a period. ie '''backup''' becomes '''<dbname>.backup''', '''data''' '''<dbname>.data''', etc.
+# Copy these renamed files back down the folder hierarchy to the '''<dbname>''' folder with the '''.odb''' file in it.
-Extract the files (using BOM Archive Helper or Stuffit Expander). There should now be a folder call '''<dbname>''' or '''<dbname> Folder'''
+# Rename the '''.odb''' file '''old<dbname>.odb'''
+# In NeoOffice from the {{menu|File}} select {{menu|New}} then {{menu|Database}}
-Double click this folder and then on the folder '''database'''
+# Select {{prefName|Connect to an Existing Database}} and select {{prefName|JDBC}}. Click {{button|Next}}
+# In the {{prefName|Data Source URL}} box enter: <tt>hsqldb:file:/users/public/databases/<dbname>/<dbname>;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false</tt>
-Rename the files in this folder by pre-pending the name of your database and a period. ie '''backup''' becomes '''<dbname>.backup''', '''data''' '''<dbname>.data''', etc.
+# In the {{prefName|JDBC driver class}} box enter: <tt>org.hsqldb.jdbcDriver</tt> (NB there must not be a space at the end of this string)
+# Click {{button|Next}}
-Copy these renamed files back down the folder hierarchy to the '''<dbname>''' folder with the '''.odb''' file in it.
+# Enter <tt>SA</tt> as the {{prefName|Username}}
+# Click {{button|Next}}
-Rename the '''.odb''' file '''old<dbname>.odb'''
+# Select to register the database and open it for editing.
+# Save the file as '''<dbname>.odb''' in the <tt>/users/public/databases/<dbname></tt> folder
-In NeoOffice from the {{menu|File}} select {{menu|New}} then {{menu|Database}}
-Select {{prefName|Connect to an Existing Database}} and select {{prefName|JDBC}}. Click {{button|Next}}
-In the {{prefName|Data Source URL}} box enter: <tt>hsqldb:file:/users/public/databases/<dbname>/<dbname>;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false</tt>
-In the {{prefName|JDBC driver class}} box enter: <tt>org.hsqldb.jdbcDriver</tt> (NB there must not be a space at the end of this string)
-Click {{button|Next}}
-Enter <tt>SA</tt> as the {{prefName|Username}}
-Click {{button|Next}}
-Select to register the database and open it for editing.
-Save the file as '''<dbname>.odb''' in the <tt>/users/public/databases/<dbname></tt> folder
 You should now have a database with no Queries, Form or Reports. However all of your tables should be visible.
 == Copying across Queries, Forms and Reports ==
-Open both old<dbname>.odb and <dbname>.odb
+# Open both '''old<dbname>.odb''' and '''<dbname>.odb'''
+# Drag each of the Queries, Forms and Reports from '''old<dbname>.odb''' to the corresponding section of '''<dbname>.odb'''
-Drag each of the Queries, Forms and Reports from old<dbname>.odb to the corresponding section of <dbname>.odb
 You will need to re-enter the name of all of the Queries. Make sure you don't make any typos!
 == Copying across Macros ==
-With old<dbname>.odb open select Tools->Macros-> Organise Macros->NeoOffice Basic
+# With '''old<dbname>.odb''' open, from the {{menu|Tools}} select {{menu|Macros}} then {{menu|Organise Macros}} then {{menu|NeoOffice Basic}}
+# Select one of your Macro Modules and click {{button|Edit}}
-Select one of your Macro Modules and click Edit.
+# Save each module using the {{button|Save BASIC}} button.
+# With '''<dbname>.odb''' open, from the {{menus|Tools}} select {{menu|Macros}} then {{menu|Organise Macros}} then {{menu|NeoOffice Basic}}
-Save each module using the Save BASIC button.
+# Create a new Module in '''<dbname>'''
+# Load in the macros you saved above using the {{button|Insert BASIC Source}} button.
-With <dbname>.odb open select Tools->Macros-> Organise Macros->NeoOffice Basic
+# Save each module with the same name as it had before.
-Create a new Module in <dbname>
-Load in the macros you saved above using the Insert BASIC Source button.
-Save each module with the same name as it had before.
 == Tidying up ==
 The databse is now split in to separate databse and odb files and should work ok. I have however had a few problems with Autonumber fields and checkboxes
-Autonumber Fields: These seem to forget the next index, resulting in duplicate entries and failure to insert records due to duplicate Primary key fields.
+; Autonumber Fields : These seem to forget the next index, resulting in duplicate entries and failure to insert records due to duplicate Primary key fields.
+: Select {{menu|Tools}} then {{menu|SQLâ€¦}}
+: Use the SQL Statement <tt>ALTER TABLE "TableName" ALTER COLUMN "PrimaryKeyField" RESTART WITH xxx</tt> where <tt>xxx</tt> is the next unused value in the table.
-Select Tools->SQLâ€¦
+; Checkboxes : These seem to default to <tt>NULL</tt> which can cause problems
+: Use the SQL Statement <tt>ALTER TABLE "TableName" ALTER COLUMN "TrueFalseField" SET DEFAULT FALSE</tt> (or <tt>TRUE</tt> as required)
-Use the SQL Statement ALTER TABLE "TableName" ALTER COLUMN "PrimaryKeyField" RESTART WITH xxx where xxx is the next unused value in the table.
+== Conclusion ==
-Checkboxes: These seem to default to NULL which can cause problems
+Everything should now work as before, but should NeoOffice crash it is much less likely that you will loose data. Also if you are developing a database for a client you can duplicate the above setup on their machine and yours. As you develop new versions of the Forms, Queries and Reports you can simply send then an updated odb file to copy over the old one. Their data will be unaffected. Obviously if you make changes to the tables you will have to visit the client or give them a list of steps or SQL commands to enter, but for the purposes of this guide I'll assume you know what you are doing if you are doing that!
-Use the SQL Statement ALTER TABLE "TableName" ALTER COLUMN "TrueFalseField" SET DEFAULT FALSE (or TRUE as required)
+The next steps in this process are to upgrade to HSQLDB 2.0 to take advantage of the latest features (not least the use of ~ in paths which makes it possible to put the database in your home directory without having to know the user name) and to set the database in to server mode which allows multi user access and storage of the database on a server.
-== Conclusion ==
+I hope to have articles on this soonâ€¦
-Everything should now work as before, but should NeoOffice crash it is much less likely that you will loose data. Also if you are developing a database for a client you can duplicate the above setup on their machine and yours. As you develop new versions of the Forms, Queries and Reports you can simply send then an updated odb file to copy over the old one. Their data will be unaffected. Obviously if you make changes to the tables you will have to visit the client or give them a list of steps or SQL commands to enter, but for the purposes of this guide I'll assume you know what you are doing if you are doing that!

Splitting an ODB File

From NeoWiki

Current revision

Contents

Setting up NeoOffice

Testing it works

Splitting you databse

Copying across Queries, Forms and Reports

Copying across Macros

Tidying up

Conclusion

Views

Personal tools

Navigation

Search

Toolbox