How to: Migrate Base Projects to Multi-User

[glj]

Thanks for the replies dacm and sliderule. I tried to follow all the suggestions but I still can't get very far through the Wizard.

Sure would be nice if this forum's "post a reply" had some way to include screenshots (perhaps sliderule can tell me how he did it). I thought the Img button might help, but of course that would be too easy.

The hsqldb server DOS window displays among other things:
...
[Server@e09713]: Loaded properties from [C:\library\server.properties]
...
[Server@e09713]: Database [index=0, id=0, db=file:library, alias=] opened successfully in 297 ms.
...

Following sliderule's suggestion I copied his two code lines (changing bldtest to library) to the Datasource URL and JDBC driver class boxes. Clicking the Test Class button seemed to go OK. Message: "The JDBC driver was loaded successfully." So I clicked Next. I just left the Username blank and the Password Required box unchecked in the next screen and clicked Next. I clicked Finish on the next screen. I named the database library. That created the file library.odb in the directory (C:\library) where the hsqldb server had previously created files: library.script, library.properties, library.log, and library.lck. Unfortunately, Base immediately gave me the following error message:

The connection to the data source "library" could not be established. S1000 General error java.lang.RuntimeException: database alias does not exist.

Simultaneously, the hsqldb server DOS window popped up the message:

[Server@83cc67]: [Thread[HSQLDB Connection @f38798,5,HSQLDB Connections @83cc67]]: database alias=library does not exist

Well, of course, that alias doesn't exist as can be seen by the second line I quoted earlier in this post. Why is it trying to connect to an alias anyway?

So, please, what is my next step here? I guess I'll to progress just one tiny step at a time.

[Sliderule]

glj:

You said:

[glj]

Well, sliderule, I did follow the instructions as much as I could. It seems that every time I reload the posts in this thread a different assortment of your screenshots appears. So when I followed your instructions as they appeared the first time, the next-to-last screenshot and the one before that were absent so I did not know to put a username in the box.

Well, be that as it may, this time I typed SA for the username with the same error as before.

So now I don't understand.

[glj]

Got it to work. Two things caused the difficulty and both were a direct result of my inexperience with a client/server database.

First, I did not know there was a default user name (SA) for the connection. Thanks to sliderule for pointing that out.

Second, when I saw that sliderule added bldtest behind localhost in the Datasource URl, I did the same (using a different name, of course) not realizing that was an alias. When I eliminated the bldtest equivalent in my Datasource URL statement, clicking the Test Connection button showed a good connection.

Thanks dacm and sliderule!

[dacm]

Well Great!

I'm just getting in from work and notice you and sliderule got it working. I'll have to check the tutorial because it basically skips the steps for creating a new JDBC database in Base. And that's interesting that the username ('SA') became an issue because I've never entered a username at this step with my HSQLDB server-mode databases. I've read that ooBase automatically handles the username in 'embedded' mode, but I guess it makes sense that it would be needed when running in server mode...? I'll need to add that step as well, if it helps.

Thanks sliderule for the advice and visuals!!! The tutorial really needs visuals, but I don't have a direct-download store on the net at the moment. Maybe I'll try MediaFire.

[glj]

Just one more comment is in order regarding my experience.

I used the drag and drop method to transfer everything from my embedded database to the client/server database. dacm was right: it only took a few minutes. Please note: AFAIK, I do NOT have OpenOffice Portable.

Again, thanks guys!

[dacm]

You're certainly welcome!

And I'm glad the drag&drop migration method worked as well, because I was beginning to wonder if that step was going to involve a specialized setup involving OpenOfficePortable. Whew!

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

But I have to say, your painful experience with this setup and migration process reveals a fundamental flaw [IMHO] in the direction of OpenOffice Base. I support the dev-leadership choice of HSQLDB among those available at the time, but in light of stronger choices in recent years I think their commitment to HSQLDB is dragging down the product in this area of multi-user embedded support. 'H2' has all of the features of HSQLDB and more -- to include seamless multi-user support while embedded.

And get this... According to sliderule, HSQLDB 2.0 integration in OOo 3.3 (available June 2010) will require additional migration code by the devs because HSQLDB 2.x is not compatible with the current HSQLDB 1.8. So I see this as the perfect time to use that migration code effort to transition OOo Base to H2 embedded -- seamlessly. H2 even has a compatibility mode which makes it act like HSQLDB. So I see no reason based on licensing, speed, compatibility, footprint, stability, or features not to give OOo 3.3 a multi-user, embedded database this summer.

[dacm]

Concerning OpenOfficePortable...

EDIT: We now have a better workaround so disregard this post...see my signature links

The portable launcher (OpenOfficeBasePortable.exe) finds Java and portablizes several paths so everything works in portable environments. Well, it fails to portablize the Java Class paths (Folder and Archive settings) which are critical for Java client-server database access. In fact, the launcher simply blanks these paths.

The only workaround is to run Base using the portable launcher, but only the first time at each computer:

In other words, use the portable launcher first to find Java Portable:
OpenOfficePortable\OpenOfficeBasePortable.exe

Then run through Step 2 of this tutorial which manually resets the Java Class Folder and Archive paths.

Then start Base directly for all subsequent starts on that computer:
OpenOfficePortable\App\openoffice\program\sbase.exe

But this only works until you move to another location/computer unless each computer assigns the same drive letter to your flash drive device.

This is hardly an acceptable workaround for portable environments but I did get a response on the OOP forum, so perhaps we'll get a truly portable solution soon.
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Migrating 'Embedded databases'

[glj]

I just discovered that I have two more problems. The first relates to a clean shutdown procedure for the hsqldb server. The second relates to editing table definitions in the client/server version of the database.

Regarding the first problem ...

[dacm]

Here's a snippit from the batch I use to shutdown the HSQLDB server:

[glj]

Thanks for the quick response!

[glj]

Thanks for this batch file!

[housefull]

With the HSQL server running and having finished Step 2, I restarted Base. In the Database Wizard step 1 I assumed I was supposed to select the third option "Connect to an existing database" and leave it set at JDBC. The next window "Set up a connection to a JDBC database" (step 2 of the Wizard) has two empty boxes that I had to fill before I could proceed, "Datasource URL jdbc:" and "JDBC driver class". Were these supposed to be empty (remember, I did steps 2a through 2e)? In the Datasource box am I supposed to type a DOS path to the files that the batch file created? I have no idea what I'm supposed to type in the "JDBC driver class" box.
_________________
Watch Brooklyn's Finest Online Free

[dacm]

[glj]