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

Printing Multiple Values in One Field
Goto page Previous  1, 2, 3, 4  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Tue Aug 10, 2010 5:45 am    Post subject: Reply with quote

Porting an "embedded database" from the all-inclusive database file (.odb) to a robust "split database" folder:

NOTE: a 'file mode' database acts just like the default all-in-one (.odb) file database generated by the "Create a new database" wizard in Base. You'll still open the .odb file directly, or register it in OOo for seamless access from other OOo applications. But there are differences:
    1) The first thing you'll notice is multiple database files. It is necessary to expose the (extracted) database files in order to gain the proper database reliability -- otherwise an OpenOffice.org crash can cause total, unrecoverable data loss.
    2) The next thing you might notice in Base is that the status bar has changed from "Embedded database...HSQL database engine" to read "JDBC...hsqldb:file" reflecting the database file location, as well as, various HSQLDB settings reflecting best practices.
    3) And lastly, during table development using the Base GUI, you'll notice that column properties appear frozen when attempting to edit various things like the data-type, etc. Don't worry, there's a simple workaround, or two, mentioned in the notes below.

An additional benefits of a "split database" that may not be immediately obvious includes:
    1) upgradability to HSQLDB 2.x which offers much improved features and scalability.
    2) use of the same "split database" files in 'server mode' which promotes data durability, enhances database portability, and adds multi-user access (client-server).

The HSQLDB documentation says:
Quote:
Converting .odb files to use with HyperSQL Server

You may already have an OOo database file, which you want to use outside OOo, or as a server database. The file is in fact in the standard ZIP format and contains the normal HyperSQL database files. Just use a utility such as 7Zip to expand the .odb file. In the /db directory, there are files such as .script, .data, etc. Just rename these files into mydb.script, mydb.data, etc. You can now open the mydb database directly with HyperSQL as an embedded database or as a server instance.

Here's the migration steps in detail:

A. Windows users can download mydb.exe from this post and install.
Or, follow the manual setup steps applicable to any operating system in order to add 'split HSQL database' support to Base AND create a new 'file mode' database using Base+HSQLDB.
B. Now locate your original .odb file and make a backup copy
C. Rename the copy .odb file with a .zip extension
D. Open the renamed zip-container file with a zip compression tool and find the database files (script, data, etc.) found in the 'database' folder within the zip-container file
E. Extract these files to the recently created folder: /users/public/database/mydb/ (created in Step A).
F. Rename these files in the following manner: mydb.script, mydb.data, etc.
NOTE: The naming convention must be consistent across the files, and for compatibility with this tutorial and the settings within the mydb.odb download/installation, you must use "mydb" as shown above to rename each existing database file. This naming convention can be changed later, but wait until you finish this tutorial and gain some experience with the associated OpenOffice (odb file) settings. Here's a few recommended settings for the extracted .scrip and .properties files.
G. Open mydb.file.odb in Base and click on the Tables icon to see your tables.
NOTE: at this point the mydb.file.odb file simply contains the information necessary to connect OpenOffice to your database files (mydb.data, etc.). It does not contain your Tables, but if everything is setup correctly (Java location, Class Path to hsqldb.jar, Datasource URL, etc.) then you should see your Tables upon clicking the Tables icon after opening mydb.file.odb in Base.
NOTE: If you get an error when clicking on the Tables icon, then we're into troubleshooting the "Edit > Database > Properties..." or perhaps "Tools > Options > Java"
NOTE: The mydb.<file or server>.odb file is empty otherwise but it will eventually include any forms, queries, reports, or macros you create. You can also migrate these elements from another .odb file. Simply drag&drop each individual Form, Query and Report between two instances of Base running side-by-side as follows...

H. Now start a second instance of Base, opening your source (original) .odb file.
NOTE: You should now have two instances of Base running simultaneously. One instance is your source (original) .odb while the other instance is your target (mydb.file.odb) database application. Place both instances side-by-side on screen in order to perform drag&drop operations between instances with the mouse.
J. Then click&drag each Form, Query, and Report to their respective icon in the target mydb.odb instance of Base. Click "Finish" to accelerate the popup Wizard as applicable.
NOTE: Queries may require re-typing each query name.
NOTE: Macros require export/import within the Base IDE (Integrated Development Environment) "organize" functions (more on this as needed).
NOTE: you could actually drag&drop your Tables for migration as well, but this is not the recommended method because Tables become read-only until re-adjusting the field-type attributes on each table. Once you move away from the all-in-one odb file database you'll need a slight workaround to adjust table structure such as field/column properties. In fact the table properties will appear frozen (dimmed) in the Base GUI. Don't worry, you can still use the GUI through a workaround.
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.

_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'


Last edited by dacm on Mon Oct 29, 2012 9:06 pm; edited 17 times in total
Back to top
View user's profile Send private message
cazbym
Power User
Power User


Joined: 16 Mar 2009
Posts: 63

PostPosted: Thu Mar 17, 2011 6:00 am    Post subject: Start HSQLDB Server Script File Reply with quote

Hi all,

I am trying to set up a client/server database, but reading through the instructions am not sure what I do with the code or script file for starting the server. Does it have to be saved in a particular place? I have a Linux operating system and can adapt the path to my JRE and HSQLDB.jar, but have no idea what I do after this! Can it just be saved anywhere?Confused
_________________
OpenOffice 3.3
Windows 7
HSQL 2.1 file mode
JRE 1.6.0 30
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Thu Mar 17, 2011 12:58 pm    Post subject: Re: Start HSQLDB Server Script File Reply with quote

cazbym wrote:
...script file for starting the server. Does it have to be saved in a particular place?

The HSQLDB server startup string such as found in 'server.start.bat' (for Windows) must be run from the same folder that contains the (back-end) database files. This would normally mean that the script/batch file, itself, should be co-located, but it's not absolutely necessary because you can change folders within the script/batch before issuing the HSQLDB startup string/command.

Code:
CD <your database folder>

<your path to Java> -cp <your path to hsqldb.jar> org.hsqldb.Server -database.0 file:<your database filename>;hsqldb.default_table_type=cached;shutdown=true


Otherwise, consider the use of shortcut files on your desktop or elsewhere in order to leave all database-related files in a single folder for ease of management (backup). But it's up to you.

Note: part of the beauty of running HSQLDB in server mode is that your database folder becomes portable. Unlike running in 'file:' mode, you can move the database folder at will (even to another drive or USB key) and run the database application immediately -- without modification. You can even right-click zip the database folder and email it to another user with a server-mode setup (Base classpath set to hsqldb.jar; or utilizing the 'fundamentalbasis.ini' default support technique) and upon unzip the database application will run from any location. The same goes for a Dropbox synchronized folder on multiple machines (although I wouldn't allow two people to modify the database simultaneously in this ad-hoc synchronization scheme using Dropbox).

On a related note: the 'default' startup string offered above includes ';shutdown=true' which is a single-user automation consideration. This automatically shuts-down the HSQLDB server after all connections are closed. If you intend to leave the HSQLDB server running such as for multiple-users, then delete this switch.

_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
cazbym
Power User
Power User


Joined: 16 Mar 2009
Posts: 63

PostPosted: Thu Mar 17, 2011 3:36 pm    Post subject: Reply with quote

Hi dacm,

Thank you for that information.

I'm assuming that if I create a separate script file and save it within my folder containing the database files, this will automatically run and start the server, when I open that particular database. I hope I am right. I will tinker with this over the weekend and report back, just in case there are any Linux specific problems if that is okay. Smile
_________________
OpenOffice 3.3
Windows 7
HSQL 2.1 file mode
JRE 1.6.0 30
Back to top
View user's profile Send private message
steveball
General User
General User


Joined: 08 Nov 2010
Posts: 13

PostPosted: Tue Aug 23, 2011 6:13 am    Post subject: multi-mode db conversion Reply with quote

Hi

I have followed the instructions as far as 'G' . I now cannot find a mydb.odb file to open. Where should it be, and if it is not there, what do I need to do?

Thanks
Back to top
View user's profile Send private message
steveball
General User
General User


Joined: 08 Nov 2010
Posts: 13

PostPosted: Tue Aug 23, 2011 6:34 am    Post subject: multi-mode db converson Reply with quote

Further to previous post, presume it is the mydb.server file in /users/public/databases/mydb folder that I should be opening. This opens Base, but when I click on tables I get the error:

'The connection to the data source "mydb.server" could not be established

The driver class 'org.hsqldb.jdbcDriver could not be loaded"

What do I need to do to access the tables I unzipped?

Thanks for your help.
Back to top
View user's profile Send private message
steveball
General User
General User


Joined: 08 Nov 2010
Posts: 13

PostPosted: Tue Aug 23, 2011 8:24 am    Post subject: multi-mode db conversion Reply with quote

I have now been able to get the mydb database running, using some info from postings about migrating manually.

The next step I need to complete is to move from running this database on my PC to transferring the data to our Ubuntu/Samba fileserver.

Any advice welcome please!
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Tue Aug 23, 2011 2:35 pm    Post subject: Reply with quote

Well I'm glad to see you got things working...one way or another...because running in 'embedded database' mode with Base is simply begging to lose all data (short of super-human backups, Dropbox, or other automation).

steveball wrote:
I have followed the instructions as far as 'G' . I now cannot find a mydb.odb file to open.

dacm wrote:
G. Open mydb.file.odb in Base and click on the Tables icon to see your tables.
NOTE: at this point the mydb.file.odb file simply contains the information necessary to connect OpenOffice to your database files (mydb.data, etc.). It does not contain your Tables, but if everything is setup correctly (Java location, Class Path to hsqldb.jar, Datasource URL, etc.) then you should see your Tables upon clicking the Tables icon after opening mydb.odb in Base.
NOTE: If you get an error when clicking on the Tables icon, then we're into troubleshooting the "Edit > Database > Properties..." or perhaps "Tools > Options > Java"
NOTE: The mydb.<file or server>.odb file is empty otherwise but it will eventually include any forms, queries, reports, or macros you create. You can also migrate these elements from another .odb file. Simply drag&drop each individual Form, Query and Report between two instances of Base running side-by-side as follows...


Wow, that was a good catch! The name of that file changed over the life of this post and I thought it was changed everywhere, but you found the old name still buried in the notes shown above in dark red.

dacm wrote:
G. Open mydb.file.odb in Base and click on the Tables icon to see your tables.

steveball wrote:
...presume it is the mydb.server file in /users/public/databases/mydb folder that I should be opening.

Really? Confused

Okay. It may be that you de-selected the "Create a new File mode database" option while working through the installer (mydb.exe or mydb .jar). That's not likely since you did install the "Server mode database" option.

Or, since Windows hides file extensions by default, it may be that mydb.file.odb appears as simply mydb.file without the extension in Windows Explorer.

And just to highlight the differences between 'file' and 'server' modes...you click directly on mydb.file.odb to start Base and the HSQLDB engine in 'file mode' ("in-process")...while you click on START.vbs in Windows to start the HSQLDB server applet and Base in 'server mode'.

Overall, thank you for the feedback. I think this migration procedure could use some fresh visuals and perhaps a video tutorial to really ease the process. I'll plan to do that but I'm a Windows user so it will be from a Windows perspective.
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
steveball
General User
General User


Joined: 08 Nov 2010
Posts: 13

PostPosted: Wed Aug 24, 2011 1:43 am    Post subject: multi-mode db conversion Reply with quote

We use Base for business-critical staff rotas, and had no problems with it while using it on individual PCs. However, we now have the rotas on a file server and we have experienced problems with data loss, hence the need for this development.

In setting it up, it appears that the automated process did not create the correct settings for Java server access, but once I had gone back to Step 2 of your January manual process posting, it is fine. I think I also didn't understand the importance of running from the batch file initially.

I have experimented with duplicating the database in other folders on my PC and am happy with this (each week's rota is a variation of a previous week's). I have just tried doing this by placing the folder on the server, and it has not run. I get the error message 'Cannot find Test2.server.odb' even though it is in the same folder as the batch file.

So any advice you can give about running databases on the server would be welcome.

Thanks for all the great advice in these postings. I am a big fan of OpenOffice and Base, but as a non-techie this support is invaluable.
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Wed Aug 24, 2011 5:10 pm    Post subject: Re: multi-mode db conversion Reply with quote

steveball wrote:
The next step I need to complete is to move from running this database on my PC to transferring the data to our Ubuntu/Samba fileserver.

steveball wrote:
...In setting it up, it appears that the automated process did not create the correct settings for Java server access, but once I had gone back to Step 2 of your January manual process posting, it is fine. I think I also didn't understand the importance of running from the batch file initially.

I have experimented with duplicating the database in other folders on my PC and am happy with this (each week's rota is a variation of a previous week's). I have just tried doing this by placing the folder on the server, and it has not run. I get the error message 'Cannot find Test2.server.odb' even though it is in the same folder as the batch file.

So any advice you can give about running databases on the server would be welcome.

The automated installers are Windows-only. And I don't run Linux so I won't be of much help unless you're using a Windows PC as the file-server. Otherwise, manual setup for Linux in a client-server environment will include writing a script file to start HSQLDB in server mode -- perhaps similar to the Windows .bat version.

But it sounds like you have the database running on various Windows PCs, including running it in server mode on the PC (using START.vbs). Any chance one of those PCs is running through-out the day so other computers can access the database? If so, check-out the links associated with the "data-loss" subject in my signature links below, which includes links for client setup.
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
enjoying28
General User
General User


Joined: 17 Dec 2010
Posts: 13

PostPosted: Sat Oct 22, 2011 11:12 am    Post subject: Reply with quote

I keep getting an error in step J when I open the original database to copy queries forms and reports. "connection to data source "File.odb" could not be established".

The mydb.file.odb is running and working fine and my tables in it but I can drag and drop the other part from the original file.

Please help I have 100s of queries and don't want to remake them. the data is less important then the querys.
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Sat Oct 22, 2011 12:53 pm    Post subject: Reply with quote

Hmmm....

Are you still using the built-in HSQLDB 1.8.0?

When you click on the Tables icon in mydb.file.odb do you get an error?

Note: If you've already upgraded to HSQLDB 2.x, then the legacy "embedded database" file is not supported by that copy of Base. In that case you could install LibO to support your "embedded database" for the migration process. Or, simply downgrade your current instance of Base using mydb.exe; then perform the migration; then upgrade to HSQLDB 2.x using hsqldb2_OOo.exe (folder).
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
fredbram
General User
General User


Joined: 07 Sep 2011
Posts: 13

PostPosted: Mon Oct 24, 2011 9:19 am    Post subject: Reply with quote

So, I am relatively new to Ooo, and we have one database that we use in our small company. I just successfully accomplished steps A through J, and our database is now called mydb.file.odb.
As a competent but non-techie person, I am unclear as to how to take the next step of moving Ooo to our windows based server so that we can all use it from our individual stations. I believe that I can rename the file and place the data on our server, but that's not the whole project, correct? I also need to move Ooo to the server? Or, I need to do something to Ooo so that it works correctly?
Back to top
View user's profile Send private message
fredbram
General User
General User


Joined: 07 Sep 2011
Posts: 13

PostPosted: Mon Oct 24, 2011 9:25 am    Post subject: Reply with quote

I want to clarify my previous post. Before migrating, I stored the database on our server, and we could all access it from our workstations, and that worked fine. I don't necessarily need multi-user read/write access for our purposes. So, maybe all I need to do is rename our new mydb.file.odb, place it back on the server and we're good to go. Is there any reason I can't rename the file and move it?

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


Joined: 07 Jan 2010
Posts: 769

PostPosted: Mon Oct 24, 2011 12:46 pm    Post subject: Reply with quote

That's a bit of a hack, but you can use a 'file mode' front-end with a 'multi-mode' HSQLDB database stored centrally on a LAN computer for concurrent read-only access. I'm not sure you'll be completely satisfied with the results. 'File mode' is designed as a single-user mode, so additional read-only access is roughly provided through OS file-locking mechanisms. So in this case, for instance, you may need to keep the first user connected at all times in order to keep the current, read-only, user-connections open.

And you don't need to bother with the following steps simply to change the name (only) of the front-end file. For instance you could change the name of mydb.file.odb to new_name.odb without issue.

However, if you rename the back-end, database files (mydb.data, etc) OR relocate a 'file mode' Base .odb file/folder then you will need to reflect the new path and filenames in the "data source" URL which is edited within Base and stored in the Base .odb file. Remember, these steps do not apply to a 'server mode' front-end file (.odb), which is more portable.

Make a copy of your latest Base front-end file (.odb) and modify the connection setting (Base > Edit > Database > Properties... > Data-source URL)

The Quick Fix wrote:
step (2b) Datasource URL:

jdbc: hsqldb:file:/users/public/databases/mydb/mydb;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false

NOTE: Administrator privileges may be required to add root-level folders, but the " /users/public " folder-prefix should be available without special privileges, at least in Windows XP/Vista/7
NOTE: /databases/mydb/mydb specifies the location and name for your database files.
The 'databases' folder helps to de-clutter your user folders, while the 'mydb' folder is your actual database folder. The second 'mydb' specifies your database file name. Since your database will consist of multiple files, this folder convention helps group and manage all database files as a single folder-unit. As such, it's a good idea to place the .odb file in this folder as well, as the automated installers do above. You can then manage the database as a unit such as for backup or zipped-folder distribution. I think it's best to use the same name for the folder, database, and .odb file as done here using 'mydb', but feel free to adjust the folder/file names within this Datasource URL. In fact, that's the best way to create new "multi mode" databases.


So you'll also need to change the name of your back-end database files:
mydb.script
mydb.properties
mydb.data
mydb.backup

to something like:
new_name.script
new_name.properties
new_name.data
new_name.backup

...as reflected in your data-source URL (above).

---------------------------------------

If you become interested in running the database in 'server mode' (HSQLDB) for concurrent read/write access by design, then read these posts:

From: [Tutorial] Avoid data loss by avoiding "Embedded databases"...

_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
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
Goto page Previous  1, 2, 3, 4  Next
Page 2 of 4

 
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