Tips to avoid BASE Crashes with data loss

Discuss the database features

Tips to avoid BASE Crashes with data loss

Postby evwool » Fri Dec 10, 2010 2:19 am

My medium-size database is crashing intermittently - at least once per session. Each time it crashes it loses all the data I'd input since I opened the database for that session. So far, the only way I can keep any of my data is by regularly closing and re-opening the database.

I'm using coding in the Before Update event of some of my fields to clean my data and then warn me when I'm entering duplicate data (I can't use an Index because I do need to enter duplicate data under certain circumstances) so that could be a culprit but it doesn't crash every time and it crashes even when I'm using an uncoded version of the form. I've not found a pattern yet.
Has anyone any general tips on how I can avoid crashes in Base (not doing anything too quickly is one thing that helps)? Is Base in later versions of OO any more stable? The data loss is proving a real drawback at work.
OpenOffice 3.1.1 on Windows XP and on Windows 7 Starter
evwool
Volunteer
 
Posts: 401
Joined: Fri Oct 09, 2009 9:40 pm
Location: UK

Re: Tips to avoid BASE Crashes with data loss

Postby FJCC » Fri Dec 10, 2010 3:17 am

I had a problem with Base crashing and then tried the file:mode explained by DACM here. All the crashing stopped. Even if Base does crash, the separation of the HSQLDB from the Base file should prevent data loss. The file:mode does diminish the portability of the database, but I'm not aware of any other drawbacks.
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 6959
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Tips to avoid BASE Crashes with data loss

Postby evwool » Sat Dec 11, 2010 11:39 pm

Thnaks for answering, FJCC. DACM did his best to explain the problem - I even understood the odd sentence - but I can't figure out from the link how run Base in 'file:mode' . The only concrete thing to do in the link was to avoid using code to open forms (I don't do that anyway). It tells you what to do but not how to do it.

I'm not sure what you mean by 'portability' If you mean that it can't be moved from one PC to another then that wouldn't be much good as I have to work on whichever PC happens to be available to me at the time so I back up my file on a datapen and copy it onto the PC where I have to work. My database isn't on a server and I'm the only person who uses it. My works computers all use Windows - from XP to Windows 7!
OpenOffice 3.1.1 on Windows XP and on Windows 7 Starter
evwool
Volunteer
 
Posts: 401
Joined: Fri Oct 09, 2009 9:40 pm
Location: UK

Re: Tips to avoid BASE Crashes with data loss

Postby FJCC » Sat Dec 11, 2010 11:51 pm

I admit the posts by DACM seem like a lot of information at first. I had to remind myself of the process for setting up a file:mode database a few days ago and it was very easy. I'm just sneaking a look at the forum right now, so I don't have time to write a careful explanation. I'll do that in the next day or so. Right now I have to get back to the great housecleaning project or my wife will not be happy.
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 6959
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Tips to avoid BASE Crashes with data loss

Postby Greengiant224 » Sun Dec 12, 2010 12:02 am

Sorry to hear about your data loss. I assume you are running in embedded mode?
Using embedded mode for simple db tests is OK. Any data that is important to you
Please use HSQL in file/server mode.
The postings by DACM have recently been updated and should be very simple to follow.

AFAIK You can install your .obd alongside your hsql back-end in the same folder on a thumb drive.
You can use OO.o Portable as well as OO.o installed locally to any PC (cross- platform compliant)

So there really is no excuse not to go along the file/server mode route using native hsql v1.8 or the new hsql v2.0

On and end note:
I personally edited all the files needed for me to use HSQL v2.0 from the DACM postings. There were a few teething
problems which were ironed out very quickly. So if I can do it? You can...
Best wishes

Linux 2.6.38, KDE 4.9.2, LibreOffice 3.5.* (Java 1.6.0_35) utilising HSQL 1.8.10 & 2.3.*, MySQL, PostgreSQL, SQLite
+ Blood, Sweat and Tears (Application, Determination and Perseverance)
User avatar
Greengiant224
 
Posts: 276
Joined: Wed Jun 09, 2010 3:50 pm
Location: All Over The World

Re: Tips to avoid BASE Crashes with data loss

Postby RPG » Sun Dec 12, 2010 12:20 am

Hello

I think that it can be good to follow the advice of the other post. I agree with them. But I think there can be also a problem in your macro code if the one file mode crashed so often as you describe we get here much more posts.

Maybe be you can tell how big is your database? I think big is also related to the size of your memory.

Also look to all controls of your forms and anchor the controls good. What means good? Anchor them all to page or a frame.

See also to your macros. Bad designed macros can also be a reason for crashing.

So it is better to use the file server mode for your database but it is possible your crashes can have a reason in a bad design.
LibreOffice 6.1.3.2 on openSUSE Leap 15
RPG
Volunteer
 
Posts: 2152
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Tips to avoid BASE Crashes with data loss

Postby FJCC » Sun Dec 12, 2010 5:45 am

Here is an edited version of DACM's instructions for manually setting up a file:mode database. Remember that I don't really understand what I'm doing!
Making the folder structure
(0a) in the folder C:\users\public create a folder named databases
(0b) in the new databases folder create a folder named mydb.
Judging from DACM's post the C:\users\public folder should be available on XP, Vista, and Win 7 systems.

Setting up the HSQLDB Java code as a program independent of OpenOffice.org
(1a) Start OpenOffice and go to the menu Tools>Options>Java
Select 'Use a Java runtime environment' and a recent-version Java installation
If you have been using Base, I think this must be already set.
(1b) While still in the same Tools>Options>Java dialog, click on the button Class Path on the right.
Click on the Add Archive button, browse to the location of the hsqldb.jar file used by OpenOffice.org and select it
On my XP system this file is at C:\Program Files\OpenOffice.org 3\Basis\program\classes. Click Open or OK to get out of all the dialogs
(1c) Shutdown and Restart OpenOffice (and Quick-Starter) to save the settings.

(2a) Start OpenOffice.org and choose to create a new Base file. Select Connect to an existing database > JDBC (Next...)
(2b) Set the Datasource URL: to
hsqldb:file:/users/public/databases/mydb/mydb;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false
Just copy and paste the above text into the URL box
(2c) Set the JDBC driver class to: org.hsqldb.jdbcDriver
(2d) Set the user name to: SA
(2e) Allow OOo to register the database if you want to
(2f) Save the file in the \users\public\databases\mydb folder giving it the name mydb.

In the long URL string in step 2b the first occurrence of mydb refers to the folder name and the second mydb refers to the file name. You can change either one of these to a name you prefer, just keep the URL , the folder name and the file name consistent in steps 0b, 2b and 2f.

After saving the Base file (and perhaps adding a table), if you look in the folder C:\users\public\databases\mydb you will see the Base file and several other files created by the HSQL data base. This group of files must all be moved together to port the database, because the tables are no longer within the Base file. You should be able to copy the entire mydb folder to another system as long as the same C:\users\public\databases folder structure is used on the new system.

If you can't use the C:\users\public\databases folder structure, you can use any other, you just need to change the URL in step 2b to refer to the correct file path. I tried storing a database in the folder D:\Databases\DB and I named the Base file TestPortableDB. I used the following URL in step 2b
hsqldb:file:D:/databases/DB/TestPortableDB;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false
and everything seemed to work fine.

If you can't use the same file structure on every computer, it might be feasible to edit the database properties at the time of use. If you open the Base file and select Edit → Database → Properties, you will see the URL. I think you could edit that to refer to whatever file path currently refers to the Base file. I haven't tried that, so definitely don't do it the first time with a database that contains important data.
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 6959
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Tips to avoid BASE Crashes with data loss

Postby RPG » Sun Dec 12, 2010 8:18 pm

Hello

When you want change to a more save use of the HSQLDB database there are several steps you can do. I will point only to the most easy method that is what called the :file mode. For this mode you have not to do new downloads or do any change to your settings. It is also easy to understand for starters. It is only usable for a single user but when you want change this for multi-user on one computer or even for more computers then it is not so difficult to change it.

I think the file mode is a real good start for most people who have difficult to understand the post of dacm. When you get working a file mode database then it can be enough for you but maybe a step for server mode.

Here dacm give a step by step explanation for the filemode
http://www.oooforum.org/forum/viewtopic ... 793#379793

Here I give all the steps for file mode
http://www.oooforum.org/forum/viewtopic ... 397#344397

Romke
LibreOffice 6.1.3.2 on openSUSE Leap 15
RPG
Volunteer
 
Posts: 2152
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Tips to avoid BASE Crashes with data loss

Postby DACM » Mon Dec 13, 2010 3:05 am

Hi everyone,

A short time ago I appended this tutorial which serves (at least in my mind) as the main jumping-point for information and links associated with the file/server mode fix to the data loss issues. The provided links include automated and manual solutions. Windows users, in particular, sould be up and running in a few minutes using the provided installer (mydb.jar). Otherwise, you choose the difficulty level from the automated 'quick fix' installer, to pre-configured downloads with instructions, to completely manual step-by-step instructions.
Last edited by DACM on Mon Dec 13, 2010 7:34 am, edited 1 time in total.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Tips to avoid BASE Crashes with data loss

Postby DACM » Mon Dec 13, 2010 7:29 am

evwool wrote:DACM did his best to explain the problem - I even understood the odd sentence
Sorry that was unclear, but I re-wrote the explanation for clarity.

evwool wrote:...but I can't figure out from the link how run Base in 'file:mode'...It tells you what to do but not how to do it.

That's true. That link mostly serves as an explanation of the problem.

However, at the end of the same post is a large underlined link that takes you to the 'Quick FIx' tutorial page with several options including a downloadable installer. The installer sets-up Base automatically and installs pre-configured file/server mode (.odb) files for immediate use. As a Windows user, you'll have seamless database access in either mode. Just click on 'mydb.file.odb' to start Base in file: mode. Or click on 'START.vbs' (Windows only) to start Base in server mode.

Here's your overall options:
(1) download 'mydb.jar' and run it. This automatically adds multi-mode support and installs the pre-configured file/server mode (.odb) files. This 'quick fix' option simply adds multi-mode support for HSQLDB allowing legacy "embedded databases" to function normally. This is useful for users who need to migrate legacy databases to the multi-mode/file format for reliability. The main tutorial includes a link for step-by-step migration if this is necessary.
(2a) download 'hsqldb2_OOo.jar' and run it to upgrade to HSQLDB 2.0.1. There's no need to run the 'quick fix' (mydb.jar) if you're starting from scratch without legacy databases to migrate/upgrade.
(2b) download 'hsqldb2_mydb' if you want the file/server mode (.odb) files pre-configured for HSQLDB 2.0.1.
(3) download two files (fundamentalbasis.ini and mydb.odb) and install them manually using the step-by-step tutorial on the same post as the 'quick fix' downloads.
(4) manually follow the step-by-step as FJCC outlined above, as also found here on the 'quick fix' page.

evwool wrote:I'm not sure what you mean by 'portability'...pendrive...all use Windows - from XP to Windows 7!

A multi-mode database (file/server) is never quite as portable as the legacy "embedded database" (.odb) file. But you can move your database (folder named mydb) from computer to computer perhaps with a copy of the installer (mydb.jar or hsqldb2_OOo.jar) because you'll need to add multi-mode support to each computer:

'Portable' multi-mode database options:
(1) Add the applicable installer (either mydb.jar or hsqldb2_OOo.jar) to your database folder so you'll always have a copy on your pen-drive. This option requires administator privileges on each computer.
(2) Without administrator privileges, you'll need to become familiar with the manual procedures necessary to setup multi-mode (file/server) using Base settings. Maybe place a copy of those step-by-step instructions in your database folder for future reference.
(3) Or, simply add OpenOfficePortable and JavaPortable (link) to your USB thumb drive and run everything from the pen-drive.
Last edited by DACM on Sun Dec 26, 2010 3:58 am, edited 3 times in total.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Tips to avoid BASE Crashes with data loss

Postby evwool » Tue Dec 14, 2010 10:20 pm

Romke, thank you for your patience. You say:
for datasource url
hsqldb:file:/home/romgro/Documents/opof/data/database/boeken5/boeken;default_schema=true
;default_schema=true
If you do not add this it is only readable.
username: sa
JDBC class: org.hsqldb.jdbcDriver

What is a Datasource URL?
Where do you put this text?
OpenOffice 3.1.1 on Windows XP and on Windows 7 Starter
evwool
Volunteer
 
Posts: 401
Joined: Fri Oct 09, 2009 9:40 pm
Location: UK

Re: Tips to avoid BASE Crashes with data loss

Postby DACM » Tue Dec 14, 2010 11:18 pm

evwool wrote:What is a Datasource URL?
Where do you put this text?

This is precisely why we (the user community) have worked to give users the option to download an installer and set this up automatically in 2 minutes, or download the individual components to copy into the proper folders in 15 minutes, or to setup everything manually in both OOo and the Base (.odb) file to be up and running in mere days... ;)
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Tips to avoid BASE Crashes with data loss

Postby RPG » Tue Dec 14, 2010 11:51 pm

Hello

I think it is better that dacm give the answers as he did now.

For dacm can I try the installer with linux or is the installer only for windows?

I'm a little afraid for testing it. I have a good working system. So maybe you can understand it.

Romke
LibreOffice 6.1.3.2 on openSUSE Leap 15
RPG
Volunteer
 
Posts: 2152
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Tips to avoid BASE Crashes with data loss

Postby DACM » Wed Dec 15, 2010 12:20 am

Romke,
I don't think it works in Linux based upon a recent Linux (Ubuntu) user post. The Java-based installer is supposed to work in Windows, Linux, and Mac, but I think we should consider it Windows-only until I (or someone) can iron out the non-Windows issues. You can always try the 'Semi-automatic' method for us under Linux. Simply make a backup of your 'fundamentalbasis.ini' file -- that's the only change to your system. But, you would need to delete your classpath settings in OOo as well. You're existing "embedded databases" and file/server mode databases should then work without the OOo settings due to the .ini file technique. Of course, you can download the pre-configured .odb file (mydb.odb) and adjust the datasource URL for experimentation as well. Thank you. Without the automation of an installer, the .ini file technique would simply alleviate the issues associated with more manual methods (finding the particular settings; syntax errors; etc.). In other words, it becomes a matter of placing pre-configured files in the correct folders, rather than getting tedious settings setup correctly.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Tips to avoid BASE Crashes with data loss

Postby RPG » Wed Dec 15, 2010 1:06 am

DACM

Thank you for the answer. Now I can be sure it does not work for linux. For me it is no problem. Maybe I will test the mydb.odb

For other users I can tell with openSUSE 11.3 you can easy install a server for multiuser. I have test it only for HSQLDB 1.8 but I think it works for all databases. The explanation is maybe a little difficult but for a user who needs a server I think he can understand it.

Romke
LibreOffice 6.1.3.2 on openSUSE Leap 15
RPG
Volunteer
 
Posts: 2152
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Tips to avoid BASE Crashes with data loss

Postby Nocton » Wed Dec 15, 2010 10:40 am

Previously I did try using the 'quick fix' (mydb.jar) but got into problems and reverted to an embedded database - see
http://user.services.openoffice.org/en/forum/viewtopic.php?f=13&t=36095.

However, like the original poster I still get occasional crashes and very occasionally complete loss of data/unusability of the database. I have looked through the posts and the help and tutorial information and am clear what to do, in fact the first time I used mydb.jar it was not a problem to understand. However, just to make clear what I want to do:
1. I wish to be able to run the application on several computers and I want to be able to email users with new data and new application code (currently I just email the single Base file).
2. Do I have to run mydb.jar on each computer?
3. I want to end up with my database in folders as:
main folder 'NameOfDatabase' with subfolders 'Data' for the data and 'Application' for the forms/reports/code, etc. (I don't want stuff buried in the user folders).
4. I then want to be able to simply copy any or all of the folders or their contents to another computer.

Could someone please comment and advise particularly re 2 & 3. I note that in the QuickFix DACM has said
NOTE: You can certainly change the name and/or location of this database but these changes must be reflected in various spots depending upon the mode.
but I am not fully clear how to do it.
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 493
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Tips to avoid BASE Crashes with data loss

Postby DACM » Thu Dec 16, 2010 9:03 pm

WARNING: the following post is generally Windows specific...

Nocton wrote:Previously I did try using the 'quick fix' (mydb.jar) but got into problems and reverted to an embedded database - see
http://user.services.openoffice.org/en/forum/viewtopic.php?f=13&t=36095.

I've removed the HSQLDB 1.8.1.3 upgrade from the 'Quick Fix' installer (mydb.jar) to alleviate the problems it caused, and I found an issue with the reset utility (OOo_reset.jar) that may have caused it to ignore non-default installation folders and alternate versions (LibreOffice, etc.). I've uploaded the latest to the 'Quick Fix' post.

Nocton wrote:1. I wish to be able to run the application on several computers and I want to be able to email users with new data and new application code (currently I just email the single Base file).

Unless you use MS Access, there's no stable, single-file database options complete with front-end GUI components. The stable alternatives involve at least two files (representing the front-end and back-end database components). With Base that means the .odb file plus the native database file(s). Databases engines H2 and SQLite give you the option to manage/email only two files in total (.odb + one database file), but the restrictions on file placement within Base settings still force us to adopt a 'database folder' concept for portability (and ease of backup).

The Quick Fix implements a database folder for portability and backup purposes. Base+HSQLDB involve a few files but the file count isn't an issue once you adopt the database folder concept. Just backup and distribute the folder as a whole as outlined below. The Quick Fix includes 'START.vbs' for use in Windows. This script file allows the database to be run seamlessly from any drive (such as a USB thumb drive) and/or folder on Windows computers with OOo and Java 6 installed to their respective default folders. This 'START.vbs' option uses the 'mydb.server.odb' file to store everything except the Tables (which are stored separately across your .data, .script, .log, and .properties files by the HSQLDB engine). Alternatively, using the 'mydb.file.odb' container reduces portability because the database folder must be placed in exactly the same folder-path on each machine (preset to: /user/public/databases/mydb/mydb). So the 'file:' mode container (mydb.file.odb) is not as easily portable as the 'server' mode version (mydb.server.odb), although either can be quickly converted to the opposite type by adjusting the 'Datasource URL' and filename (mydb.xxxx.odb).

Email systems don't work with folders so you'll probably want to "zip" the folder itself. Right-click the folder in Windows and select 'Send to > Compressed (zipped) folder'. Your email recipients will need to right-click it > 'Extract All...'

Nocton wrote:2. Do I have to run mydb.jar on each computer?

Yes, so please add mydb.jar to the database folder for distribution, and keep in mind that the 'Quick Fix' requires administrator privileges (otherwise your users will need to add the 'multi-mode' support manually to OOo). And let's call mydb.jar "Windows only" at the moment. And please remind Windows XP Home Edition users to 'Exit' Quickstarter manually using right-click on the appropriate Task bar icon when running the Quick Fix installer to allow the settings to take affect. All newer versions of Windows (XP Pro and newer) should get automatic 'Exit' of Quickstarter and OOo upon running the Quick Fix.

This process is similar to distributing MS Access database-applications. Since most users don't have MS Access installed, each end-user needs to install the apporpriate MS Access-Runtime distribution (only 2007/2010 veiwers available).

Nocton wrote:3. I want to end up with my database in folders as:
main folder 'NameOfDatabase' with subfolders 'Data' for the data and 'Application' for the forms/reports/code, etc. (I don't want stuff buried in the user folders).

Well, like I mentioned above, all you need to do is click 'START.vbs' from inside the database folder to use 'mydb.server.odb' from any drive or folder. So it's only the 'file:' mode version (mydb.file.odb) that's folder-location-dependent. But either way, it may take some additional effort (script file adjustments, etc.) to split the database folder as you've outlined.

Nocton wrote:4. I then want to be able to simply copy any or all of the folders or their contents to another computer.

Okay. Simply drag-&-drop the folder itself, or use the right-click folder-zip method outlined above.


Nocton wrote:Could someone please comment and advise particularly re 2 & 3. I note that in the QuickFix DACM has said
NOTE: You can certainly change the name and/or location of this database but these changes must be reflected in various spots depending upon the mode.
but I am not fully clear how to do it.

Ah yes, renaming and moving the database and/or folder path:

With 'START.vbs' you're using 'mydb.server.odb' which is virtually location independent. If you want to rename this .odb file simply do so (Nocton_application.odb). If you want to rename the database files (mydb.script, mydb.properties, etc) then make sure HSQLDB is shutdown (run 'STOP.vbs) and go ahead, but the naming convention must be consistent (Nocton.script, Nocton.properties, etc.). AND in both cases, you'll need to adjust (right-click > Edit) the 'server.start.bat' file found in the database folder (after running the Quick Fix and selecting to install the 'server' mode database) folder as follows:

server.start.bat
    @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%\hsqldb.jar
    IF EXIST "*.tmp" set jarpath=%PROGRAMFILES%\%jar%\hsqldb2\hsqldb.jar
    GOTO CONTINUE

    :Win64
    set javapath=%PROGRAMFILES(X86)%\%java%\Java.exe
    set jarpath=%PROGRAMFILES(X86)%\%jar%\hsqldb.jar
    IF EXIST "*.tmp" set jarpath=%PROGRAMFILES(X86)%\%jar%\hsqldb2\hsqldb.jar

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

    REM pause
    exit

to read:

    @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%\hsqldb.jar
    IF EXIST "*.tmp" set jarpath=%PROGRAMFILES%\%jar%\hsqldb2\hsqldb.jar
    GOTO CONTINUE

    :Win64
    set javapath=%PROGRAMFILES(X86)%\%java%\Java.exe
    set jarpath=%PROGRAMFILES(X86)%\%jar%\hsqldb.jar
    IF EXIST "*.tmp" set jarpath=%PROGRAMFILES(X86)%\%jar%\hsqldb2\hsqldb.jar

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

    REM pause
    exit

Since you're primary objective is 'portability' of the database folder, I do not recommend the 'file:' mode option (mydb.file.odb) so I won't outline the steps necessary to change the database name and/or location in that case, unless you need me to do so.

One note when using the portable 'START.vbs' option: In general, clicking 'START.vbs' in Windows starts Base+HSQLDB, while setting both to shutdown upon Base exit (this would require adjustment in a multi-user environment). So it makes a server mode database just as seamless as a file: mode database, or even an "Embedded database" in this respect. In all cases you get one-click startup with automatic shutdown of all compoents. However, with 'START.vbs' the HSQLDB engine will not shutdown automatically unless you actually click something in Base like the Tables icon to establish the connection (Base+HSQLDB). So, if you simply run 'START.vbs' and never click anything in Base and then immediately shutdown Base, then you'll see extra files in your database folder because without a connection, Base never tells HSQLDB to shutdown. So the extra files will remain until you either re-run 'START.vbs' and actually use Base before shutting down, or simply click on 'STOP.vbs' to shutdown HSQLDB in this case...and you should see the extra files disappear from the database folder. Certainly make sure HSQLDB is shutdown with 'STOP.vbs' before zipping the folder for distribution.
Attachments
mydb.file.odb
(2.19 KiB) Downloaded 43 times
Last edited by DACM on Wed Jan 21, 2015 9:43 pm, edited 7 times in total.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Tips to avoid BASE Crashes with data loss

Postby Nocton » Fri Dec 17, 2010 10:35 am

Many thanks, DACM, for your very comprehensive reply. I shall try it out in the next few days and let you know how I got on.
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 493
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Tips to avoid BASE Crashes with data loss

Postby Nocton » Fri Dec 17, 2010 7:22 pm

Having a couple of hours unexpectedly spare, I have now set up my database as 'server' mode version (mydb.server.odb), following the above instructions. I am pleased to report that all seems to be working OK, but there are one or two points that came up all except one which I have resolved.

1. When running mydb.jar I exited QuickStarter manually as instructed - although it was a little disconcerting nevertheless to get an 'Installation Failed' message at the end, presumably because of this! It is also a little worrying to be told that the installation will overwrite one's existing OO installation, as if one should not do it. But I did and all my settings seem to be as they were.
2. Having got that over I moved my forms, reports, etc. into the new mydb.server.odb as instructed and running from START.vbs all performed OK.
3. However, I then went into Writer to check that my queries/tables for running labels could still be accessed. They could not, even though I had registered mydb.server.odb and the database could be accessed in the label wizard. This was a bit worrying.
4. Having found that the SWITCHBOARD form from the extension of that name would not open automatically via the AutoExec macro I then went to open it via the extension editor. It gave the error " The database [mydb] does not appear to be a registered database. This gave a clue to the problem, so I registered mydb.server.odb as 'mydb' rather than 'server.mdb', the default. Then the SWITCHBOARD editor opened OK and could be used and the labels wizard could see the tables.
5. So only thing not resolved is why the SWITCHBOARD form does not open automatically - not a major problem, but would be nice if it could.

So, if I understand what I have done correctly, my forms. reports, etc. are located in mydb.server.odb and they point to/are connected to my tables in mydb.data. However, one cannot 'see' the tables except via mydb.server.odb. So if I lost/corrupted in some way mydb.server.odb how could I get my data back?

Another point, as the tables cannot be edited directly from mydb.server.odb - I tried out the workaround described copying to anew table and then copying back. This worked OK, but is tedious, so it would seem best to do the development of the application in embedded mode and only change when the main features, especially the table structures, are established.

Thank you again DACM for all your help.
Regards, Nocton
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 493
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Tips to avoid BASE Crashes with data loss

Postby DACM » Sat Dec 18, 2010 5:00 am

:super: That's impressive! Especially the part about migrating everything from your "Embedded database" to a server-mode database including the Switchboard extension considerations.

Nocton wrote:1. When running mydb.jar I exited QuickStarter manually as instructed - although it was a little disconcerting nevertheless to get an 'Installation Failed' message at the end, presumably because of this! It is also a little worrying to be told that the installation will overwrite one's existing OO installation, as if one should not do it. But I did and all my settings seem to be as they were.

Yes, you presumed correctly. When you exit Quickstarter manually, as you need to do with XP Home, you'll a message or two with the current installers. The "overwrite" of your OOo installation amounts to a single file (fundamentalbasis.ini) which adds the necessary path to the built-in HSQLDB (nothing is disabled so "Embedded databases" are still supported after the 'Quick Fix'). IMHO, this should already be the OOo default, but as you can see Base doesn't offer complete GUI support for 'Field Properties' without workarounds when using file/server mode databases.

Nocton wrote:3. However...The database [mydb] does not appear to be a registered database. This gave a clue to the problem, so I registered mydb.server.odb as 'mydb' rather than 'server.mdb', the default. Then the SWITCHBOARD editor opened OK and could be used and the labels wizard could see the tables.

Good work! And that makes sense, because the Switchboard entries you created reference the name of a registered database (just glance at your SWITCHBOARDITEMS table). So each Switchboard entry can open a form in a different, registered, Base (.odb) file. This is consistent with other OOo apps such as Writer, Calc or Impress, because unlike Base, these offer simultaneous access to any number of registered data-sources (databases) -- although limited to one database per query/result-set. So no matter what you rename your database, the registered name should remain 'mydb' unless you rework your individual switchboard entries to reflect a new registration name and/or path...

:ouch: The Switchboard extension really complicates things in terms of 'portability.' Each user/computer will require installation of the Switchboard extension; each user/computer will need to register the database as 'mydb'; and then the database folder location must remain constant after database registration due to the "registration" path in OOo necessary for the Switchboard extension; and if you specify Switchboard icons, their paths are fixed (no relative paths) as well.

I would love to see the Switchboard extension published as a 'library' rather than an 'extension' so it could be integrated and distributed seamlessly inside the Base (.odb) file (as embedded macros). I've got this working but only as a runtime feature, as I never got the editor function working as a library. Perhaps QuazzieEvil will comment on these issues. As I recall I also fixed the relative path issue for icons...?

Nocton wrote:5. So only thing not resolved is why the SWITCHBOARD form does not open automatically - not a major problem, but would be nice if it could.
Off the top of my head you'll need to specify the Switchboard 'AutoExec' macro in Base > Tools > Customize > Events > Open Doccument > Macro after opening your .odb ('START.vbs' in your case).

Nocton wrote:So, if I understand what I have done correctly, my forms. reports, etc. are located in mydb.server.odb and they point to/are connected to my tables in mydb.data. However, one cannot 'see' the tables except via mydb.server.odb. So if I lost/corrupted in some way mydb.server.odb how could I get my data back?
Okay I just want to pause to take in the irony of that statement... <so now that my Tables are maintained and available through a robust, ACID-compliant, RDBMS in server mode, what happens if my front-end crashes and burns?> That's a good question but please understand that you're sooooo much better off than the average Base user, or even MS Access user at this moment. And I think you'll find that Base crashes are fully recoverable now that the back-end database components are removed from the .odb file.
Congratulations are in order! :D

Actually you can see your Tables by running mydb.file.odb when the database folder is located in it's design folder-path -- but I just mention that to highlight the 'multi-mode' nature of your database files. So better yet, use a copy of mydb.server.odb to gain access without folder-path considerations/dependencies. In fact, you can do it simultaneously with randomly-named copies of mydb.server.odb. Try it.
    Make a few backup copies of mydb.server.odb and name them anything (test_1.odb, test_2.odb, etc).
    Then click 'START.vbs'
    And then click each 'test_X.odb'
    And you should have simulaneous access from multiple instances of Base.
So it doesn't even matter if the 'START.vbs' script succesfully opens/finds your main .odb file by name (mydb.server.odb) because the HSQLDB server is started and accessible from any properly-configured Base (.odb) file. This does not apply to a file: mode database (mydb.file.odb) which is single-user.

But now I owe you the details:

accessing the connection settings in your .odb file from Base
DatabaseProperties.jpg
Accessing the Data source URL and JDBC driver settings in each .odb file


mydb.server.odb
AdvancedProperties.jpg
hsqldb:hsql://localhost/;default_schema=true;get_column_name=false


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


Nocton wrote:Another point...it would seem best to do the development of the application in embedded mode and only change when the main features, especially the table structures, are established.
Okay but realize that you've just caused all the database experts to gasp :shock:
But you're correct. You could develop Table structures in an "Embedded database" side-by-side and drop them onto your actual (mydb.server.odb) database 'Tables' icon. But I have to admit, I thought the same thing initially but the workarounds, direct SQL, and administrative front-ends (SQuirreL, SQL Workbench/J, etc. which can be run simulaneously with Base+HSQLDB in server mode) get easier with use.
Last edited by DACM on Mon Dec 20, 2010 7:58 am, edited 1 time in total.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Tips to avoid BASE Crashes with data loss

Postby Nocton » Sat Dec 18, 2010 5:15 pm

Once again a very comprehensive reply, thank you. I should not have felt at all confident doing this without your help.

I have now got everything working with the new database registered as my old application name after changing the file names and server.start.bat as you explained, so the labels documents work exactly as before - only now one must have the database open otherwise the tables cannot be seen. It was stupid of me to forget to specify the SWITCHBOARD AutoExec macro as the Open Document event.

Although it would be good to have the SWITCHBOARD extension as a library/built into OO, it does not really matter in this case, as I also have to install the Oracle Reports extension. In fact I shall install the system on each of the users' computers requiring to run the database. This needs to be done only once and will take only about 15 minutes (now I am an expert at it!) My idea then is that only the file mydb.data needs to be emailed round when there is any change in the data. I have tried that between the two computers in my office and it seems OK. Then when I occasionally update the program I shall circulate round mydb.server.odb. Is that a correct way of doing things?

More questions to help me understand the principles:
1. You have shown me how to access the settings in the .odb file. I have done this, but am none the wiser! What does hsqldb:hsql://localhost/;default_schema=true;get_column_name=false mean? Would it help me in any way to access/see the data if I had only the mydb.data file?
2. If I create a new table within mydb.server.odb is that automatically created in the data file? It appears to be, because after creating it I can no longer edit it, as you explained in an earlier post together with the workaround.
3. I know this may sound odd or even stupid, but if I wanted to return to an embedded database would that be possible from the server files? (Of course I still have a copy of the embedded database, but in future the two will diverge as more data is added and as new reports are added.)

Regards, Nocton
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 493
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Tips to avoid BASE Crashes with data loss

Postby DACM » Sun Dec 19, 2010 12:27 am

Nocton wrote:...only now one must have the database open otherwise the tables cannot be seen.

Yes but it should all be seamless as before using 'START.vbs' including shutdown. So in other words, with an "embedded database" or "file: mode" database you simply clicked on the Base (.odb) file and Base starts with HSQLDB automatically. And you shutdown everything including Base+HSQLDB normally within Base using a single click. That should still be true with your server-mode database using 'START.vbs' to start everything automatically, and a single-click in Base to exit both Base+HSQLDB. There should be no extra steps involved. That makes all modes (file/server/embedded) equally seamless in Windows.

Oh...but I think I see what you mean. Yes, you'll need to 'START.vbs' the HSQLDB engine separately when using Writer, Calc or Impress. You can edit 'start.server.bat' to startup Writer with your document each time you 'START.vbs' the database:

In 'start.server.bat' change...
Code: Select all   Expand viewCollapse view
:CONTINUE
start mydb.server.odb

to...
Code: Select all   Expand viewCollapse view
:CONTINUE
start <your Writer document name here>.odt


Nocton wrote:My idea then is that only the file mydb.data needs to be emailed round when there is any change in the data. I have tried that between the two computers in my office and it seems OK. Then when I occasionally update the program I shall circulate round mydb.server.odb. Is that a correct way of doing things?

The idea is to distribute the entire database folder perhaps best using right-click zip/unzip under Windows. But if you really want to minimize the files involved, then you can't really stop with 'mydb.data'. That file contains 'cached table' data only. The file 'mydb.script' contains all Table structures (such as column names and other field properties), row counts/pointers for all Tables, and all 'memory table' data (although 'memory tables' are not created by default using the 'quick fix'). So I really think you should distribute 'mydb.data' and 'mydb.script' as an absolute minimum. For that matter, if you ever see 'mydb.log' present you better include it as well, OR click 'START.vbs' and then 'STOP.vbs' in order to incorporate the 'session changes' left from an 'abnormal shutdown' situation (or additional apps/users currently connected in server-mode). So it's not as simple as you might think determining which 'mydb.xxxxx' files to distribute individually. That's why you and your users should ultimately be proficient with right-clicking the entire database folder for zip/unzip distribution as a single zipped container file -- at least for email purposes. Otherwise, just drag-&-drop the folder itself for copy/backup purposes. It should run from any drive or folder using 'START.vbs' (as long as OOo/Java are installed to their default folders in Windows).

Nocton wrote:1. You have shown me how to access the settings in the .odb file. I have done this, but am none the wiser! What does hsqldb:hsql://localhost/;default_schema=true;get_column_name=false mean?


Consider it boilerplate...but here's some details:

'hsqldb:' specifies the relational database engine for connection purposes.
'hsql:' specifies the connection protocol used to communicate with HSQLDB. For instance, this could be 'hsqls:' for encrypted communications.
'//localhost/' specifies the first database (of 10 maximum with HSQLDB 1.8.x) served on the default port (that HSQLDB listens on for transactions) on the local computer (not the proper setting for clients on a network).
';default_schema=true' tells HSQLDB that Base does not support multiple "schema" (internal-database-subfolders). Schema are otherwise used to organize multiple databases within a main database such as to divide user-roles easily by folder (schema) for security purposes. Base assumes no folders (schema) with HSQLDB 1.8.x so this setting effectively/indirectly write-enables the database; otherwise the database is read-only. Ironically, Base supports schema with virtually all other database engines, including HSQLDB 2.0.x.
'get_column=false' is not necessary with the 'quick fix' (HSQLDB 1.8) but is included for future upgrade purposes to HSQLDB 2.0 where column alias names are handled slightly differently. So this is a backwards compatibility setting for legacy SQL statements that use alias column names. This setting was contributed by Sliderule.

And there's more settings specifed in 'server.start.bat' but I'll skip those for now unless you're interested.

Nocton wrote:Would it help me in any way to access/see the data if I had only the mydb.data file?

Not the best approach (see previous comments).

Nocton wrote:2. If I create a new table within mydb.server.odb is that automatically created in the data file?

Actually no. The Table structure is defined within the 'mydb.script' file (as described above).

Nocton wrote:It appears to be, because after creating it I can no longer edit it, as you explained in an earlier post together with the workaround.

Yes it is part of the HSQLDB-managed file-set including mydb.script, mydb.properties, mydb.data, mydb.backup and somtimes others (mydb.log, etc.).

Nocton wrote:3. I know this may sound odd or even stupid, but if I wanted to return to an embedded database would that be possible from the server files?

AFAIK, that is possible. The multi-mode database files would need to be renamed using their extensions (mydb.data becomes 'data', mydb.script becomes'script', etc.) and stored properly inside a new "embedded database" file. Or the old drag-&-drop method between two instances of Base should work.
Last edited by DACM on Tue Dec 21, 2010 6:42 pm, edited 6 times in total.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Tips to avoid BASE Crashes with data loss

Postby Nocton » Sun Dec 19, 2010 3:46 pm

Thanks, DACM, that answers all my questions. I consider the issue of avoiding data loss if BASE crashes as now solved. Perhaps the original poster, evwool, should put [Solved] in the subject header?

And perhaps, DACM, when you have time you could collect the information in your posts on this topic together as a tutorial? It would then be easy for others to access.

Regards, Nocton
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 493
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Tips to avoid BASE Crashes with data loss

Postby evwool » Sun Dec 19, 2010 6:49 pm

I may not be given the time at work to separate data from front end and hesitate to install any programs in case they interfer with work programs and I am definitely not certain about the safety of running a database only from a datapen. I've seen those things die without warning before this and I'm not sure, from this discussion if I could then recover my data quickly from a backup and start working again. Also, I have a number of database which I use at work and still have to make sure that I can run them all.
Until I get a chance to experiment further with this, I'm going to see if the following could help with an embedded database:
1. allow BASE to load fully before starting to type and give a bit of extra time for records to be entered. DACM mentions 'OOo sets the database write-delay to 10 seconds by default.' I'm not entirely sure what this means but it's possible that a short delay when I finish one record and before I start a new one, may slow down the crash rate.
2. I'll try to have 1 form only open at a time and certainly don't write into form which contains data linked in some way to data in another open form.
3. I'll allow a few seconds after I close my forms before I close down the database.
4. I'm intrigued by RPG's suggestion that I anchor my controls to a Page or Frame (some of them seem to be anchored to a Paragraph). I don't know if this will make a difference but it is simple enough to right click on a control and choose to Anchor it to a paragraph.
Thanks everyone
OpenOffice 3.1.1 on Windows XP and on Windows 7 Starter
evwool
Volunteer
 
Posts: 401
Joined: Fri Oct 09, 2009 9:40 pm
Location: UK

Re: Tips to avoid BASE Crashes with data loss

Postby DACM » Sun Dec 19, 2010 7:34 pm

evwool wrote:1. allow BASE to load fully before starting to type and give a bit of extra time for records to be entered. DACM mentions 'OOo sets the database write-delay to 10 seconds by default.' I'm not entirely sure what this means but it's possible that a short delay when I finish one record and before I start a new one, may slow down the crash rate.

Now with the latest versions of OOo I'm seeing a 60 second write_delay setting for "Embedded databases." But I don't want to mislead you, as I don't think this write-delay setting has anything to do with the 'total data loss' issue with "embedded databases." But in the event of a recoverable crash, only the 'session' data held up in the write_delay function is lost.

This [greater issue of total data corruption and loss] is a fundamental design flaw with the "embedded database" implementation which waits for the buggie code to be run, so I'm not convinced that user-interaction speed is an issue unless memory-leaks and gabage-collection are involved. I'm also not convinced that multiple, open Forms or a Control's anchor-settings affect it, but you never know.

Shutting down Base/OOo automatically issues a normal SHUTDOWN command to HSQLDB so all 'commited' data is persisted to disk immediately. There's no wait time necessary.
Last edited by DACM on Sun Dec 19, 2010 10:22 pm, edited 1 time in total.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Tips to avoid BASE Crashes with data loss

Postby r4zoli » Sun Dec 19, 2010 9:03 pm

Write delay, from HSQLDB Guide:
Set the WRITE DELAY property of the database. The WRITE DELAY controls the frequency of file sync for the log file. When WRITE_DELAY is set to FALSE or 0, the sync takes place immediately at each COMMIT. WRITE DELAY TRUE performs the sync once every 0.5 seconds (which is the default). A numeric value can be specified instead.

The purpose of this command is to control the amount of data loss in case of a total system crash. A delay of 1 second means at most the data written to disk during the last second before the crash is lost. All data written prior to this has been synced and should be recoverable.

A write delay of 0 impacts performance in high load situations, as the engine has to wait for the file system to catch up.

To avoid this, you can set write delay down to 10 milliseconds.

Each time the SET FILES WRITE DELAY statement is executed with any value, a sync is immediately performed.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
User avatar
r4zoli
Volunteer
 
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: Tips to avoid BASE Crashes with data loss

Postby DACM » Sun Dec 19, 2010 9:58 pm

r4zoli wrote:Write delay, from HSQLDB Guide:

Yes thank you for the clarification, but it appears that the default HSQLDB write_delay is overridden/overwritten by OOo in the "Embedded database" configuration. You can change the Write_delay setting (with a zip tool) inside the 'script' file within the .odb file of an "Embedded database" but OOo overwrites your efforts when it opens the database. In my experience, that's the only setting that OOo insists upon, because I can permanently change the 'cache' settings, for instance, at will. And it may be possible to adjust the session write_delay using SQL even for an "Embedded database," but you'll be back to the lengthy OOo default once you shutdown and restart an "Embedded database."

And that particular (0.5 second) HSQLDB default mentioned in the documentation is the new setting found in HSQLDB 2.0 (which we should applaud), which doesn't support the "Embedded database" configuration with Base. I think the HSQLDB 1.8.0.x (as necessary to support the "embedded database" product) default delay is more like 10 seconds -- but in this case it doesn't matter due to the OOo override.

On the other hand, it's certainly possible to adjust the write_delay for "file/server" mode databases permanently in the 'mydb.script' file.

But again, this write_delay setting is a side-issue and amounts to a distraction from the real problem which is not limited to session data (as with the write_delay and also macro-opened Forms without 'flush' code) but includes all data through the total corruption of the .odb file upon OOo crash.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Tips to avoid BASE Crashes with data loss

Postby evwool » Tue Dec 21, 2010 10:03 am

DACM, thanks for your instructions but in the first illustration: 'accessing the connection settings in your .odb file from Base' Properties and Connection Type is grayed out on my db when I go to Edit, Database.
OpenOffice 3.1.1 on Windows XP and on Windows 7 Starter
evwool
Volunteer
 
Posts: 401
Joined: Fri Oct 09, 2009 9:40 pm
Location: UK

Re: Tips to avoid BASE Crashes with data loss

Postby DACM » Tue Dec 21, 2010 10:25 am

evwool wrote:...Properties and Connection Type is grayed out on my db when I go to Edit > Database.

That would indicate you're using an "embedded database."

In that case, create a new Base file (.odb) using the Base wizard: Connect to an existing database, select JDBC, and fill-in the Properties as follows (or you can use the pre-setup files from the Quick Fix / folder):

accessing the connection settings in your .odb file from Base
Image
Accessing the Data source URL and JDBC driver settings in each .odb file

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

mydb.server.odb
Image
hsqldb:hsql://localhost/;default_schema=true;get_column_name=false

'hsqldb:' specifies the relational database engine/driver for connection purposes.
'hsql:' specifies the connection protocol used to communicate with HSQLDB. For instance, this could be 'hsqls:' for encrypted communications.
'//localhost/' is server-mode specific; specifies the first database (of 10 maximum with HSQLDB 1.8.x) served on the default port (that HSQLDB listens on for transactions) on the local computer (not the proper setting for clients on a network).
';default_schema=true' tells HSQLDB that Base does not support multiple "schema" (internal-database-subfolders). Schema are otherwise used to organize multiple databases within a main database such as to divide user-roles easily by folder (schema) for security purposes. With HSQLDB 1.8.x, Base does not support schema so as an indirect consequence the database becomes read-only without this setting. Base supports schema with virtually all other database engines, including HSQLDB 2.0.x.
'get_column=false' is not necessary with HSQLDB 1.8 but this setting is included for future upgrade purposes to HSQLDB 2.x where column alias names are handled slightly differently. So this is a backwards compatibility setting for legacy SQL statements that use alias column names. This setting was contributed by Sliderule.

And there's more settings specifed in 'server.start.bat' but I'll skip those for now unless you're interested.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
DACM
Volunteer
 
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am


Return to Base

Who is online

Users browsing this forum: No registered users and 0 guests