[Solved] Split a database with back-end files on a server

[SLOShane]

I have a database that I would like to split so the back-end files can be located on a server. I attempted this tutorial by DACM http://forum.openoffice.org/en/forum/vi ... 83&t=61183. It all seems fairly straight forward, but there are some parts I don't understand.

I downloaded the Split_HSQLDB_Wizard_v3c.odb file and placed it in a folder I named "Split Database." Upon opening Split_HSQLDB_Wizard_v3c.odb with Base I enabled Macros and named my back-end files "mydb." From what I understand the Split_HSQLDB_Wizard_v3c.odb file then becomes the split database I desire. Is that correct? If so then I would just have to move the folder I mentioned, "Split Database," to the designated spot on the server and create a shortcut on my desktop. Assuming that is correct, there is still one thing I don't understand.

I renamed Split_HSQLDB_Wizard_v3c.odb as PartLabeling.odb. I Have one form I would like to access from this split database and it depends on four tables. Do I simply drag the form and tables from the database in which they already exist into PartLabeling.odb? I need an explanation of where I can paste my form and tables so the form can be accessed from the split database.

Thank you.

[SLOShane]

Upon doing more research, I realized that I don't want to create a new split database. I want to convert my current embedded database into a split database. I would like it to be formatted so the beck-end files are on my company server and a form on the front-end files can be accessed by multiple users at the same time. I am having a difficult time finding a tutorial that satisfies both of these aspects. If anyone knows of a good tutorial or has any relevant information, it would be much appreciated. Thank you

[DACM]

Do I simply drag the form and tables from the database in which they already exist into PartLabeling.odb?

You can drag-&-drop everything except the tables between .odb files. You extract and rename the 'database' folder files.

...the beck-end files are on my company server and ... the front-end files can be accessed by multiple users at the same time.

A 'split database' can be run in single-user "file mode" or in multi-user "server mode" (to include "web server/servlet mode" using a "web application server").

If your company server supports Java applications, then you should be fine. But there's no guarantees with a company server because it's likely running a 'server operating system' which runs as few services as possible in the name of stability and speed. Your Admin can enable the necessary services but they're not always willing. Otherwise, you can run your database in server-mode on any computer on the LAN with a shared folder and standard Java support. You'll need to extract your 'embedded database' to a 'split HSQL database' and run it in "server mode" for concurrent read/write access among multiple users.

The Base template (Split_HSQLDB_Wizard_v3c.odb) is setup for single-user file-mode access only. Since that template is macro-enhanced, you'll have to disable the macro in order to setup server-mode access. Everything must be setup manually for server-mode access. For instance, you'll change the 'Data Source URL' in the Base file to reflect the name of the database-host computer (the name or static IP address of the computer used to host your database by running HSQLDB in server mode). You can store the Base front-end file on the database-host computer and create a shortcut on each client; or you can generate a copy of the .odb file on each client. You'll also need to configure each *Office installation for external JDBC access by setting-up the global 'Class Path' to a local copy of hsqldb.jar.

Try this link: Convert an 'embedded database' file to a 'split database' folder using server mode (MS Windows-oriented steps)

...then follow the manual Server-mode steps here: Base FAQ

Edit: I added a 'split HSQL 2.3 database' version to the demos in your previous thread ... .

[SLOShane]

Thanks again! I'll read up some more and make a new attempt.

I did see and read the link in the demo, that is how I figured the first part out. I just realized after a while that it wasn't friendly for multiple users. Thanks for the new info.

[Villeroy]

Once you separated the backend from the frontend and your connection jdbc:hsqldb:file:///path/directory/Name;option=value works as expected in single-user mode you can move the directory to a server, write a simple configuration file, run a batch script which can be used to start up the HSQL program in server mode (waiting for connections from outside).
In the frontend you call Edit>Database and change the URL to jdbc:hsqldb:hsql:192.168.0.1/Name;option=value (replacing the file: part with hsql:IP-number) and distribute that frontend to all client computers or put it in a shared directory.

[SLOShane]

You extract and rename the 'database' folder files.

I'm having trouble with this. So I right click my database file and select Send To>Zip. after that the idea is to navegate the zip file to find the back-end database files so I can move them and rename them. However, when I open the zip file the only thing inside is a copy of the original file. I realize this is less of a Base problem and more lack of computer knowledge, but I would appreciate the help.

[DACM]

I right click my database file and sent Send To>Zip. after that the idea is to navigate the zip file to find the back-end database files.

Hmmm...I've never thought of that, but that's an interesting way to add a .zip extension to the .odb file, automatically. When opening the resulting .zip file, you should see your .odb file. If you continue by clicking on the .odb file within the .zip, you should see the internal contents of the .odb to include the 'database' folder. From there, you should have the option to extract the contents of the .odb one way or another.

[SLOShane]

When opening the resulting .zip file, you should see your .odb file. If you continue by clicking on the .odb file within the .zip, you should see the internal contents of the .odb to include the 'database' folder.

Strangely enough, the .odb file in the .zip file would just open up the database when I clicked on it haha. I figured it out though. 7-zip compressed it in a way where I can view the individual data folders.

[SLOShane]

4. Edit mydb.start.bat AND mydb.stop.bat to reflect the path to hsqldb.jar as follows:

• Code: Select all

  @echo off
  REM The database name is specified within the datasource-URL after the 'CONTINUE' label below
  
  REM Edit the *Office installation folder name below to reflect your specific version...
  set java=Java\jre6\bin
  set jar=OpenOffice.org 3.4\Basis\program\classes
  REM set jar=LibreOffice 3.6\program\classes
  
  ...

So I'm at this step in the tutorial. Which exact part of this code do I change to link it to my hsqldb.jar? I tried once but the mydb.server.odb could not connect to the tables. My hsqldb.jar is located in C:\Program Files (x86)\OpenOffice 4\program\classes if you want to make an example.

[SLOShane]

Nevermind I got it to work. I think that's it! I just need to move the folder onto the server and help the other people set up their shortcuts. I don't know if you will look at this again DACM, but thanks for all of your help and time. You really went over the top with your explanations and I can't thank you enough

[DACM]

4. Edit mydb.start.bat AND mydb.stop.bat to reflect the path to hsqldb.jar as follows:

• Code: Select all

  set jar=C:\Program Files (x86)\OpenOffice 4\program\classes
  
  ...

I ... named my back-end files "mydb."

Good, that should work with server.start.bat without further modification. Otherwise the following line must reflect the name of your back-end database files. Notice the name after "file:"

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

  Code: Select all

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

...but the mydb.server.odb could not connect to the tables.

Let's take it step by step.
(1) Temporarily enable the "pause" command at the end of your server.start.bat file (eliminate the REM element as shown below)
(2) Run the .bat directly (without using START.vbs)
(3) Does the popup window indicate a successful start-up of the HSQL database server?

Code: Select all

@echo off
REM The database name is specified within the datasource-URL after the 'CONTINUE' label below

REM Edit the *Office installation folder name below to reflect your specific version...
set java=Java\jre6\bin
set jar=C:\Program Files (x86)\OpenOffice 4\program\classes
REM set jar=LibreOffice 3.6\program\classes

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

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

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

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

pause
exit

See also: Tables not seen when moved to server mode for additional tips and clues.
...

[DACM]

Oh good! Glad to hear it's working, but you may have some additional steps:

• (1) Make sure the same paths are reflected in server.stop.bat as well.
  
  (2) Make sure to re-apply REM to the pause statement in these .bat files to ensure proper housekeeping (REM pause).
  
  (3) Use START.vbs and STOP.vbs to run the .bat files quietly (no popup windows) after you're satisfied with the .bat function.
  
  (4) If you're using a Data Source URL with 'localhost' for testing purposes, then you'll need to change that to the name of the database-host computer on the network (or static IP address) for client-server access from the .odb file.
  
  (5) As a final step, eliminate ;shutdown=true from server.start.bat so the server remains running after closing the last connection. Otherwise, you would have to keep Base open on at least one client in order to keep the database running in server mode.

You're certainly welcome...

[SLOShane]

Everything seems to be working properly. The database can access the tables when I open it using server.start.bat, START.vbs, mydb.server.jar, and the mydb.server.jar - Shortcut on my desktop. I changed 'localhost' to 'Server-02' (the server) in the data source URL and then everything ceased to work so I changed it back to 'localhost'. Is it safe to leave it like that?

[DACM]

I changed 'localhost' to 'Server-02' (the server) in the data source URL and then everything ceased to work so I changed it back to 'localhost'. Is it safe to leave it like that?

No. Localhost runs the database-server locally but the clients need to know where the server is located on the network. This is accomplished through a convenient, one-time setup of the Data Source URL in the central .odb file. Make sure 'Server-02' is the correct name of the server computer (database host) as seen on the network. You might try the IP address of the server, but make sure it's a static IP address or things will stop working upon restart of the server or of the router (DHCP service) running the LAN.

Otherwise, I think you mean mydb.server.odb (as opposed to .jar).

[SLOShane]

I had it working on my computer, but when I tested it on a co-workers computer it would not connect. The error messages reads "The connection to the data source 'mydb.server' could not be established". When I restarted my computer and attempted to view the form I received the same error message. I assume this is because I still had it set as 'localhost'. So, if I understand what you said, the fix for this would be replacing 'localhost' in Edit>Database>Properties>DataSourceURL with 'Server-02' or the IP (in mydb.server.odb). If I do that do I need to log onto the server and run the START.vbs file? I tried to edit the DataSourceURL a minute ago but it would not let me change it, is that also because I need to run the START.vbs?

[DACM]

Remember, you need to setup the global Class Path to hsqldb.jar in *Office on each client for Base access. You must use the same version of hsqldb.jar on the server and clients.

Make sure you have read/write access to the database folder on the server from all clients. You will need to run START.vbs on the server (and STOP.vbs to shutdown HSQLDB whenever necessary).

[MTP]

You'll also need to 'register' your Base file on each client computer. Tools -> Options -> OpenOffice.org Base -> Databases

[SLOShane]

Works perfectly! My boss loves it! Thanks a ton everybody. Especially you DACM, I couldn't have done it without your help.