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

[DACM] Upgraded to multi-mode database results
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
Zerex71
General User
General User


Joined: 23 Feb 2012
Posts: 46

PostPosted: Tue Feb 28, 2012 10:16 am    Post subject: [DACM] Upgraded to multi-mode database results Reply with quote

Greetings (I'm looking at you, DACM Smile),

I believe that I successfully upgraded one of my work databases to the new HSQL 2.2.8 using mydb.exe and hsqldb2_OOo.exe. I had a few comments and questions (my user experience) with doing it:

1. I started off with my OOB (out of the box) version of the database created in Base and made a backup copy of it.

2. I downloaded and installed mydb.exe. I had to search, but I found the new "template" file-mode and server-mode files actually under C:\Users\Public\databases\mydb. I also saw the script files and the .data and .log files as well.

3. I opened my original database and opened the new file-mode template and saved the latter as my new (migrated) database).

4. I individually copied all the tables, queries, and lone form from the original database into the new database. As I was copying the tables, I looked at the .log file to see it grow with SQL transactions.

5. When satisfied that I was complete, I saved the new database as something like "new file-mode HSQL 1.8" to indicate what version it worked with.

6. I opened up all the tables and all the data were there (whew!) The only problem I had was that three controls on my form got displaced and I manually moved them back to where they were, which is no big deal because they might be obsolete as I enhance and fix the form.

7. I saved everything and executed a SHUTDOWN COMPACT, then closed the database.

8. I made another backup copy of this database (just in case).

9. I downloaded and installed hsqldb2_OOo.exe and installed it as well. It ran smoothly, no issues found. I do notice under my user directory though that there is no more .log file, and the timestamps on the mydb.file.odb and mydb.server.odb files didn't change, although I thought they would be overwritten with this second update.

10. I opened up the previous database, checked everything out (same issue with the form again, which required the same minor repositioning adjustment), then saved a copy as something like "new file-mode HSQL 2.2.8".

11. After I was satisfied everything was cool, I executed the SHUTDOWN COMPACT command, saved, and quit the database.

12. Finally, I overwrote the very original database with the proper name (i.e. users aren't going to care or know anything about file mode or HSQL 2.2.Cool.

Questions:
1. How do I verify what version of HSQL is being used by Base? I don't see a dialog anywhere that would tell me that.

2. Do I explicitly have to do anything with the *.vbs and *.bat files provided by these installations? I was able to do all of my work without touching any of these four files.

3. How can I change the Datasource URL to point to absolute or relative paths to point to where I want the actual data stored? The reason I ask is I have the database in a common network location (T: drive) where anyone with OOo installed could access and use/modify the database, but it's not ready for primetime and I doubt anyone will even know what to do with it for a long time. All my data are currently stored on my C: drive. That being said, eventually I want a small user community to be able to open up the database and work with it, so I have not yet moved the data (where it currently resides under my user directory on my C: drive) to the network drive to sit in the same folder alongside the .odb. (I guess that's question 4.: What's the safest way to move the data and adjust the Datasource URL so Base and HSQL are all happy?)

5. Did I do all of this correctly???

By the way, the installers were fantastic, especially all the background info. I needed to read both installers' info to make sure (a) I understood what I was doing and why I was doing it and (b) the background on what the installers were going to do to my system. Kudos to DACM and fredt and the user forum for a job well-done.

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


Joined: 07 Jan 2010
Posts: 769

PostPosted: Tue Feb 28, 2012 6:42 pm    Post subject: Re: [DACM] Upgraded to multi-mode database results Reply with quote

Zerex71 wrote:

9. ...there is no more .log file, and the timestamps on the mydb.file.odb and mydb.server.odb files didn't change, although I thought they would be overwritten with this second update.

The .log file records session changes, and is incorporated into the .data file upon shutdown of HSQLDB, which coincides with shutdown of Base in file-mode.

Your file-mode .odb file is now your "front-end." It contains your Forms, Queries, Reports and Macros. So that's not a file you would want overwritten, so it's a good idea to keep back-ups through the migration process, as you've done.

Zerex71 wrote:
10. ...same issue with the form again, which required the same minor repositioning adjustment.

That issue should be unrelated to the 'multi-mode' solution. Select everything on your Form using the "selection" tool (arrow). Drag a square around all Form elements) and right-click on a corner of the selection. Choose Anchor > To Page.

Zerex71 wrote:
Questions:
1. How do I verify what version of HSQL is being used by Base? I don't see a dialog anywhere that would tell me that.

Create Query in SQL View...
Edit > Run SQL command directly

HSQLDB 1.8
Code:
call "org.hsqldb.Library.getDatabaseFullProductVersion"()


HSQLDB 2.x
Code:
Select DISTINCT DATABASE_VERSION() as "HSQL Version" From INFORMATION_SCHEMA.SYSTEM_TABLES


...derived from: hsql version

Zerex71 wrote:
2. Do I explicitly have to do anything with the *.vbs and *.bat files provided by these installations? I was able to do all of my work without touching any of these four files.

Those 4 files are for running HSQLDB 1.8 in server-mode. There's an installer for those files relative to HSQLDB 2.x (hsqldb2_mydb.exe) but they include some very slight differences. We can go over that when the time comes.

Zerex71 wrote:
3. How can I change the Datasource URL to point to absolute or relative paths to point to where I want the actual data stored...where anyone with OOo installed could access and use/modify the database...

You won't be running in file-mode when sharing the database, so the DataSource URL will be changed to reflect the IP address of the database host, not the absolute/relative path to the database. Again, we'll go over that in more detail when you get that far. Or you can read the 'Related Posts' in the tutorial.

Zerex71 wrote:
5. Did I do all of this correctly???

YES! Very impressive!
And Kudos to all who contributed to this subject.
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
Zerex71
General User
General User


Joined: 23 Feb 2012
Posts: 46

PostPosted: Wed Feb 29, 2012 10:30 am    Post subject: Reply with quote

Hi DACM,

One further question in response to your "which version of SQL am I using?" response: Where do I see the answer? I execute the command in Tools | SQL, but nothing comes out. In fact, it's a general question I have: Many websites will show you the results of commands you get when operating on tables and data, but all I get in the lower window of the SQL command dialog is the *status* of the last command I executed, not the actual results of the command. Is there some SQL 'console' where I can see the output of my commands?

Mike
Back to top
View user's profile Send private message
Zerex71
General User
General User


Joined: 23 Feb 2012
Posts: 46

PostPosted: Wed Feb 29, 2012 10:33 am    Post subject: Reply with quote

Another question: When it comes time to migrate the database again to a server-mode/multi-user configuration, is there anything I'm doing now that will cause me to have to rebuild the database to convert it to server-mode? Or will it be a trivial matter of changing the Datasource URL? I ask this because in my procedure as mentioned, I did not start with the server.mydb.odb template file - I used the file.mydb.odb file instead. I won't want to have to recreate the database in a new template all over again, even though it's probably < 30:00 of work.

Oh, thanks again for the repositioning tip. It turns out that this happens every time I open the form, not just because of the migration. Having to do this didn't start happening until the migration, so I assumed that was an unintended side effect of the migration. I'll try the anchor and let you know what happens.

Mike
Back to top
View user's profile Send private message
Zerex71
General User
General User


Joined: 23 Feb 2012
Posts: 46

PostPosted: Wed Feb 29, 2012 10:35 am    Post subject: Reply with quote

Oops, I answered my own question - I just saw where you wrote about creating the query. That's cool, I had not seen that before. Thanks so much!

Mike
Back to top
View user's profile Send private message
Zerex71
General User
General User


Joined: 23 Feb 2012
Posts: 46

PostPosted: Wed Feb 29, 2012 12:29 pm    Post subject: Reply with quote

Okay, the anchoring worked. I had grouped the offending controls (two labels and a button) and right-clicked to do Anchor to Page and they stuck and saved with the form, so that the next time I opened up the form, it was where I wanted to be. Thanks, DACM.

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


Joined: 07 Jan 2010
Posts: 769

PostPosted: Wed Feb 29, 2012 12:57 pm    Post subject: Reply with quote

Zerex71 wrote:
server-mode...will it be a trivial matter of changing the Datasource URL?

Yes.

There's no "migration" between modes. That's what is meant by "multi-mode." You can access the database in either file-mode or server-mode, although not simultaneously. Although once you start HSQL in server-mode, you can access the database from multiple front-ends simultaneously, including multiple copies of your Base front-end file configured with a server-mode Datasource URL.

[Windows] Although, since you didn't install the HSQLDB 2.x specific front-ends (using hsqldb2_mydb.exe), to run in server-mode you'll need to adjust the existing paths inside server.start.bat and server.stop.bat to reflect the location of the new hsqldb.jar file -- which was installed by the main HSQLDB 2.x installer (hsqldb2_OOo.exe).

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

Mode switching your Base front-end (.odb) from file to server mode:

Change the 'data source URL' to reflect the IP Address as follows:

    Edit > Database > Properties...
    Change to read:
    hsqldb:hsql://localhost/;default_schema=true;get_column_name=false

    Note: localhost will change again once you're actually running in a multi-user environment.


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

[Windows] Adjusting your HSQLDB 1.8 batch files for use with HSQLDB 2.x:

server.start.bat (changes in red)
    @echo off

    REM The database name is reflected twice after the 'CONTINUE' label below
    set java=Java\jre6\bin
    set jar=openoffice.org 3\Basis\program\classes

    IF EXIST "%PROGRAMFILES(X86)%" GOTO Win64

    :Win32
    set javapath=%PROGRAMFILES%\%java%\Java.exe
    set jarpath=%PROGRAMFILES%\%jar%\
    hsqldb2\hsqldb.jar
    GOTO CONTINUE

    :Win64
    set javapath=%PROGRAMFILES(X86)%\%java%\Java.exe
    set jarpath=%PROGRAMFILES(X86)%\%jar%\
    hsqldb2\hsqldb.jar

    :CONTINUE
    start mydb.server.odb
    "%javapath%" -cp "%jarpath%" org.hsqldb
    .server.Server -database.0 file:mydb;hsqldb.default_table_type=cached;shutdown=true

    REM pause
    exit


    Note: Currently the .server insert is not required. But you never know when legacy compatibility will be discontinued as you continue to upgrade to the latest HSQL engine over time.

    Note: Eventually, you'll need to eliminate ;shutdown=true but it's nice to have until you're actually running in a multi-user environment.


server.stop.bat (changes in red)
    Well...it's the same two path changes ( hsqldb2\ ) as shown above.

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


Joined: 23 Feb 2012
Posts: 46

PostPosted: Thu Mar 01, 2012 7:01 am    Post subject: Reply with quote

Thanks, DACM. I will bookmark this for future reference since I'm nowhere near ready to deploy this thing. It might just end up being me using it after all, due to my role here at the company.

I will still need to get with you on instructions about moving the "actual data" from my C: drive to our network drive to sit in the same folder as the .odb container and to make sure all my references are updated accordingly.

Mike
Back to top
View user's profile Send private message
Zerex71
General User
General User


Joined: 23 Feb 2012
Posts: 46

PostPosted: Thu Mar 01, 2012 7:03 am    Post subject: Reply with quote

By the way, where can I find documentation on what each of the Datasource URL parameters mean? I can only assume what "shutdown=true" means...

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


Joined: 07 Jan 2010
Posts: 769

PostPosted: Thu Mar 01, 2012 11:37 pm    Post subject: Reply with quote

Zerex71 wrote:
...where can I find documentation on what each of the Datasource URL parameters mean?

See the HSQLDB 2.x documentation under 'Chap 12: Connection Properties'
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
caravas
General User
General User


Joined: 05 Sep 2011
Posts: 31

PostPosted: Sun Mar 04, 2012 6:22 pm    Post subject: Reply with quote

Hello,

I have a base database which was made following the instructions on this forum, and works in a multi-user server mode with the native hsqldb 1.8 , and all forms are standalone (thanks to dacm, you're the one!). So far it's working fine in a network environment, and I do want to keep it cause it took me several hours of work and brain damage...
I'm kind of missed around doing the possible next step: upgrading to hsqldb 2.x or h2?? I'm really concerned about having troubles for the future as my database is growing up in size.
I Tried to upgrade to hsqldb 2.x without success, even using the automated process, I had troubles with a large field in one table and the data in there could not be loaded, and so far, I was not able to fix it. I'm also worried about losing the embedded environment of base after the upgrade process...

Could someone help-me please?
Back to top
View user's profile Send private message MSN Messenger
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Sun Mar 04, 2012 9:23 pm    Post subject: Reply with quote

caravas wrote:
...works in a multi-user server mode with the native hsqldb 1.8 , and all forms are standalone...working fine in a network environment...

Great work! Very Happy

caravas wrote:
...I do want to keep it cause it took me several hours of work and brain damage...

Well there's no reason to lose your work with free, automated, versioned backups available.

caravas wrote:
...possible next step: upgrading to hsqldb 2.x or h2?? I'm really concerned about having troubles for the future as my database is growing up in size.

Database size shouldn't be an issue for future migration, but it sounds like you're running into an acute issue with a particular, large, VARCHAR column...? HSQLDB 2.x does handle Large Objects (LOBs) differently than 1.8, but that shouldn't be a migration issue because 2.x continues to support VARCHAR up to 2 billion characters (2^31) just like 1.8. H2 is also a good choice, but it will expose your schema (PUBLIC, etc.), so Queries, Forms, etc. will require some re-work. And with H2, all IDENTITY keys and their FOREIGN equivalents must be changed to BIGINT type.

I would ask some specific migration questions on the official HSQLDB support forum. Fred Toussi (fredt) should be able to guide you. Sliderule may also have some insights here.

caravas wrote:
I'm also worried about losing the embedded environment of base after the upgrade process...

Well definitely don't worry about that because you can maintain legacy "embedded database" support through an installation of LibO. Or, on the Windows platform, you can opt for one of the Portable variants (OpenOfficePortable or LibreOffice Portable & JavaPortable). Or, you can become proficient at changing the 'class path' to the old/new hsqldb.jar in OOo using Tools > Options > Java> Class Path in Base -- which requires exiting both OOo and Quickstarter to effect the changes.
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'


Last edited by dacm on Tue Mar 06, 2012 9:09 am; edited 2 times in total
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Mon Mar 05, 2012 11:32 pm    Post subject: Reply with quote

caravas wrote:
I Tried to upgrade to hsqldb 2.x without success, even using the automated process, I had troubles with a large field in one table and the data in there could not be loaded...


If your "large field" is a VARCHAR column, make sure it has a size assigned in your .script file, especially if there's more than 32KB of data stored in any record. For example VARCHAR(65536) allows 64KB; VARCHAR(1048576) allows 1MB, VARCHAR(16777216) allows 16MB, and so on.

Excerpt from: http://hsqldb.org/doc/2.0/guide/guide.html#dec_backware_compatibility
Quote:
In connection with the above, an old database that did not have the enforce_strict_size property, is now converted to version 2.x with the engine supplying the missing size parameters. For example, a VARCHAR column declaration that has no size, is given a 32K size, a LONGVARCHAR column is given a 16MB size. Check these sizes are adequate for your use, and change the column definition as necessary.

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


Joined: 05 Sep 2011
Posts: 31

PostPosted: Tue Mar 06, 2012 5:49 am    Post subject: Reply with quote

dacm wrote:
caravas wrote:
I Tried to upgrade to hsqldb 2.x without success, even using the automated process, I had troubles with a large field in one table and the data in there could not be loaded...


If your "large field" is a VARCHAR column, make sure it has a size assigned in your .script file, especially if there's more than 32KB of data stored in any record. For example VARCHAR(65536) allows 64KB; VARCHAR(1048576) allows 1MB, VARCHAR(16777216) allows 16MB, and so on.

Excerpt from: http://hsqldb.org/doc/2.0/guide/guide.html#dec_backware_compatibility
Quote:
In connection with the above, an old database that did not have the enforce_strict_size property, is now converted to version 2.x with the engine supplying the missing size parameters. For example, a VARCHAR column declaration that has no size, is given a 32K size, a LONGVARCHAR column is given a 16MB size. Check these sizes are adequate for your use, and change the column definition as necessary.


Thank you so much dacm!

I'll take a look at this new information. Anyway, I tried to set up my database with hsqldb 2.x again and this time the things seems to be ok, at least for now.
I'm not sure if I did it in the right way because of at the first attempt using the automated process, I recovered the db's files (mydb.data, mydb.Script...) from the previous backup, adjusted the classpath for hsqldb 1.8 and also changed the batch file for starting up the server in the old configuration. Now I just removed all the classpaths, and also changed the batch file for hsqldb2.
Did I miss something?
By the way, on network environment, do I need to upgrade hsqldb in all clients machines or just in the server one? All my forms are standalone and share the same .odb file as DataSource, which is placed on the server machine.

Thanks again! Smile
Back to top
View user's profile Send private message MSN Messenger
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Tue Mar 06, 2012 8:31 am    Post subject: Reply with quote

caravas wrote:
Did I miss something?

That all sounds good. Note there are two server-mode batch files to update when converting the 1.8 versions for starting or stopping 2.x.

caravas wrote:
By the way, on network environment, do I need to upgrade hsqldb in all clients machines or just in the server one?

All machines require the 2.x update (simply run hsqldb2_OOo.exe on Windows clients) because the local copy of hsqldb.jar is used for driver (JDBC) function, which is version specific.
_________________
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 1, 2  Next
Page 1 of 2

 
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