Custom database split

[Lovro]

Hello,
I want to have database environment with several users using base front-end which I developed. I want to use HSQLDB 2.3.4 database on my computer running in server mode, to which users connect.
For the sake of simplicity, I developed base document so far as embedded solution and now I want to split it into front-end and back-end parts.
After reading about splitting database not so thoroughly due to size and complexity of the text, I decided to take simpler approach (e.g. I did it my way :)) :

Since there is no way to connect to a different database from base UI, nor possibility to import forms and reports from another base file, I did it with a “hack”.

1. I took odb file A (embedded HSQLDB solution with forms, reports and views), opened it with a zip utility and extracted database files (according to the HSQLDB manual) into separate folder. I started HSQLDB server, which I downloaded from official page with my database.
2. I prepared odb file B as an empty Base document, but I prepared connection to my HSQLDB server
3. I opened odb file B .odb file with zip utility and extracted content.xml. I copied everything between db:data-source opening and closing XML tag. I closed database B
4. I opened odb fileA .odb file and replaced everything between db:data-source opening and closing XML tags with copied text
5. For the sake of neatness, I deleted database folder in odb file A

I found that this “hack” solution at my surprise works! I tested it and everything seems fine.

Now, the question: can I expect data corruption/odd behaviour/other problems with this solution?
Everything seems fine so far.
Thanks!

[Lovro]

If anyone is interested in my “splitting” database solution, as well as running HSQLDB server instance as a service (without ugly console window), and how to connect to it from Base, ask for instructions and I will provide them.

[Ratslinger]

Splitting an embedded database isn’t difficult (newer version here). The text gives a lot of relevant information worth knowing.

I imagine for your system to work, in LO you added a Class Path for HSQL v2.3.4 to get all working. This can be a potential problem depending on your use of Base. If you open or create an embedded database with that setting it is no longer usable once closed. For further information on that, see my answer in this post: click here.

The error generated will be:

I believe you would be better with a different DB server such as MySQL or PostgreSQL. The “Embedded” problem then goes away.

[Lovro]

Dear Ratslinger,
once again you offered great help. I encountered the very same problem you are talking about (i.e. embedded base not working any more). I started to repair old database myself, but I did not manage to do it all the way. Thanks for anticipating my problem and offering solution to it.