Data Loss, mydb.jar & TASKKILL

[Nocton]

Like many who have used Base, it seems, I find that I frequently lose data. This mostly occurs when entering data into a sub-form. Even though I have a flush routine after entering each record, I find that after I have entered more than about 20 records or so, many records are not saved when I exit the application.
Having searched these forums I thought I would apply the 'Quick Fix' solution of separating the data and the application. Accordingly I downloaded mydb.jar and tried to install it. However, I get an error 'Cannot run program "TASKKILL": Create Process Error=2, The system cannot find the file specified.'

Does anyone know what is causing this error and how to avoid it, please? And is this the best approach to my problem?

OOo 3.2.1; OS Windows XP SP3.

[DACM]

Taskkill is only used to shutdown the Openoffice.org Quickstarter on Windows machines. And now that I look into it, it seems that Taskkill support was added by Microsoft progressively with XP Pro, then Server 2003, then Vista and 7. So Windows XP alone does not include Taskkill. Sorry about that . Although this link doesn't make a distinction with XP...?

In your case just right-click Quickstarter in the Taskbar and Exit Quickstarter in order to make the 'Quick Fix' changes effective, preferably before running the 'Quick Fix' (mydb.jar). And I presume you can click 'Continue' to get past the error when running the 'Quick Fix.'

Is the 'Quick Fix' effective when you open Forms with macros in OOo?

I have not tested this thoroughly. You'll be a good test case. The theory goes something like this: OOo collects UPDATE data in chunks as required by the compression step necessary to store data in the zip-container format (.odb). OOo handles the necessary FLUSH (flushing the data held-up in the JDBC connection) when opening forms normally. But when you open forms with macros, the normal process is disabled and must be added to a Form Event. BTW, be sure to test your Event triggers because I found some that don't triggger within Table Control grids (OOo 3.1.1).

So as the theory goes...a special Form-FLUSH Event is not necessary when using a multi-file database solution such as the 'Quick Fix.' Please test this theory by running both 'mydb.file.odb' and 'START.vbs' (mydb.server.odb). That will test both 'file:' and 'server' modes. I'll be interested to know if one or both fix the macro-Form FLUSH problem.

Otherwise, if you're not opening Forms with macros, then it could be a more fundamental issue with your Form design (cascading sub-forms) or Table design (primary key).

[Nocton]

Many thanks for a comprehensive reply.

Re the theory that the problem is caused by opening forms with macros, I am using the SWITCHBOARD extension to open my forms.
The problem does occur within a Table Control Grid - I have list fields to select the required data. I did check that the event triggers, but I shall check again with several data entries just to make sure.

Re TASKKILL, I did indeed manage to pass by the error and the mydb.file.odb and folder were installed. I then managed to make the new database. However, I need to be able to copy the database to other computers/people in the group and found that I could not do this, i.e. I could not make it work on a PC running OO but without the mydb.jar installation. Is that correct? I also found that running OOo_reset.jar did not return my system to as it was before and my original database would not run as it could not locate the tables. So this looks to be a too complicated solution for my needs.

So I panicked a bit, fearing that I should lose all my work. However, reinstalling/repairing OO I managed to get back to where I was.

[DACM]

The problem does occur within a Table Control Grid - I have list fields to select the required data.

That's precisely where I ran into various, dead, Event triggers with OOo 3.1.1 (Table Control > List Box > Events)

I then managed to make the new database. However, I need to be able to copy the database to other computers/people in the group and found that I could not do this, i.e. I could not make it work on a PC running OO but without the mydb.jar installation. Is that correct?

That is correct. You lose some aspects of portability in order to gain database reliability with Base. You'll need to install the fix on all machines that require access to your new multi-file database. The folder is designed for portability using the START.vbs option where all Forms, Queries, Reports, and Macros are stored in the mydb.server.odb file. But then START.vbs assumes the default installation folder for OOo and Java. It can handle 32-bit and 64-bit variations, but that's about all at the moment. You're currently using the mydb.file.odb which is less portable because the database folder must be placed in the same folder on every machine. Or you'll need to modify some settings to change folders, database name, or switch your current work to server mode to take advantage of the added portability. The Tables, themselves, are always accessible from either .odb file because they are stored separately in the various database files found in the mydb folder.

I also found that running OOo_reset.jar did not return my system to as it was before and my original database would not run as it could not locate the tables.

The basic Quick Fix doesn't disable the single-file database in Base. Only upgrading Base support to HSQLDB 2.0 disables the single-file database. Is that what you did? Either way, the OOo_reset.jar returns the system to only single-file database support with HSQLDB. Since you're using Win XP Home you'll then need to Exit OOo and Quickstarter for the change to take effect. I'm truly sorry that TASKKILL shutdown of Quickstarter is not supported on Windows XP Home Edition because it's easy to forget as you switch back-and-forth (I'm guilty of this as well...that's why I added the TASKKILL support). I could add Windows XP support but it would cause errors for everyone else with the limited installer I'm using. Maybe someone will get around to writing a scripted, cross-platform installer with error-trapping and more fine-grain, OS version-specific functions. Or maybe the OOoBase devs will re-design the default database to avoid data corruption -- such as a separate process thread.

So this looks to be a too complicated solution for my needs.

Actually you're saying that a reliable database configuration with Base is too complicated so you're willing to risk data loss for the ease and portability of the default configuration. I agree with your conclusions overall, and in your case I would highly recommend dumping Base and buying MS Access. It will be worth every penny, every step of the way.

So I panicked a bit, fearing that I should lose all my work. However, reinstalling/repairing OO I managed to get back to where I was.

Yes that also Exits Quickstarter.

[Nocton]

Thank you DACM for all the feedback.

Actually you're saying that a reliable database configuration with Base is too complicated so you're willing to risk data loss for the ease and portability of the default configuration. I agree with your conclusions overall, and in your case I would highly recommend dumping Base and buying MS Access. It will be worth every penny, every step of the way.

Well I have written several quite complicated databases in MS Access and this would be my choice. However, the database I have written in Base is for a community group and few home PC users have Access as part of their MS Office suite. Also, I have encouraged several to use OO instead of paying for MS Office. So it seemed straightforward and logical to use Base. I knew it did not have all the capabilities of Access, but it seemed to have (and indeed does have) all that I need for the application I have written. What is totally unexpected is this data loss issue when using the built-in embedded mode, which is making me lose confidence in the whole OO project. Surely by version 3 the database should basically be reliable and not lose data? I just do not understand the problem. A standard Access database also has the tables embedded and that does not lose data like this. Why can't the application just flush all the data to disk, at least when it exits or a table is closed?

I am afraid that for portability and convenience I am going to have to stick with Base. I'll check the data loss issue more thoroughly to see if it occurs if I access the forms directly. I'll also look at the form, table and query structure to see if there are any issues there.

[DACM]

I too have an extensive MS Office background and find all components highly refined and stable since Office 97. OOo is more of a mixed bag and can be frustrating to use due to bugs and instabilities. But I use OOo mostly for USB-portable, office-productivity. This may change as cloud-computing matures. I also develop end-user applications with OOo and other FLOSS offerings (Sunbird integration, etc.) as necessary to meet end-user requirements.

But I wonder if you've fully explored your options with Base or MS Access.

First, the data loss issue. Don't overestimate the developer's ability to design a stable environment to host a single-file database. Even MS Access has had some problems with this goal over the years. For Base, I think it's a worthy goal and perhaps possible with a re-design of the current, zip-container approach. But I think they'll need to spawn an independent process to manage the file I/O and perhaps forgo the compression of the database-specific files. And there's other avenues such as expanding the zip-container during use, with automatic re-wrap at shutdown, and recovery mechanisms much like those incorporated in an RDBMS like HSQL. And I wouldn't malign the entire OOo package over the default database configuration in Base, although OOo is filled with frustrating bugs compared to MS Office.

The data loss you're experiencing is only session data. That's bad enough but it's nothing compared to the risk you're taking with total data loss using the default, single-file database with Base. And I'm not convinced the session data loss (macro-Form FLUSH issue) is related to the Switchboard extension, although it's theoretically possible.

Second, the portability issue. MS Access offers a free download that runs most, if not all, MS Access database applications on any Windows machine. Versions for MS Access 2010/2007/2003 are available from Microsoft. So home users without the MS Access component won't be able to develop, but the final database application runs just fine.

The 'quick fix' also requires end-user installation at home, resulting in a highly portable database solution, especially on Windows machines using START.vbs (this scripted portability could be enhanced and expanded to other platforms someday using a Java applet). Everything is contained in a single-folder (as opposed to a single-file) but that shouldn't be a significant portability issue. After all, it should be just as easy to click-and-drag a single folder (not the individual files) to a USB flash drive as it is to drag a single-file. In your case, simply add mydb.jar to the database folder so your users can install the 'quick fix' support anywhere they go. There's a couple of limitations to portability but these are easily changed in the server.(start/stop).bat files which are plain text:

• (1) the default folder installation is assumed for both OOo and Java;
  (2) and the database folder (mydb) must be copied/moved to the same drive as the OOo and Java installation (typically C:). This limitation is easily removed if you assume the C: drive. Simply add 'C:\' to the OOo and Java paths in both server.(start/stop).bat files which are simple text files in your database folder (mydb). That would allow the home user to run the database (START.vbs) directly from a USB flash drive as well.

And there's a fully portable solution using OpenOffice.org Portable 3.1.1 and Java Portable with Quick Fix support. That would allow you to copy everything to USB flash drive and run it in place even without OOo or Java installed on the host machine. That's how I do it and I can give you the details if you're interested.

[RPG]

Hello

When you open a database document with embedded data not from the datasource then there can be two problem, AFAIK:
a) There is no connection
b) The data is not stored.

I think both can lead to dataloss

Code: Select all

with Thisdatabasedocument.currentcontroller
	if  not .isConnected then  .connect
end with
Thisdatabasedocument.currentcontroller.datasource.flush

Romke

[Nocton]

Thank you, Romke, for the information. You then give a bit of code for flushing the data, but without any explanation.
Where should I put this code?

Regards, Nocton

[Nocton]

Thank you, DACM, for this further feedback. It is good to learn from someone with such experience of the problems.

The data loss you're experiencing is only session data. That's bad enough but it's nothing compared to the risk you're taking with total data loss using the default, single-file database with Base.

This is very true and more than once while developing the database I have had a crash which left me with a totally unrecoverable database. Fortunately, I always make a backup after each major change or data input - but it is still disconcerting. The OO Recovery procedure is of little use here.

Second, the portability issue. MS Access offers a free download that runs most, if not all, MS Access database applications on any Windows machine. Versions for MS Access 2010/2007/2003 are available from Microsoft. So home users without the MS Access component won't be able to develop, but the final database application runs just fine.

This may be an option, but a quick check seems to suggest that the free version is only available for MS Office 2010, which few people have. I am using Access 2003, but will shortly upgrade to 2010. Also some of the users do not have the MS Office suite at all - they are happy with OO Writer & Calc, so I assume they would not be able to use an MS Access runtime extension.

And there's a fully portable solution using OpenOffice.org Portable 3.1.1 and Java Portable with Quick Fix support. That would allow you to copy everything to USB flash drive and run it in place even without OOo or Java installed on the host machine. That's how I do it and I can give you the details if you're interested.

This sounds an excellent solution. Please let me have more details.

Regards, Nocton

[RPG]

Hello

You can find here more information.

I'm not sure it can help you but you can test it.

This part you can use when the databasedoucment opens. Use the event: OpenDocument

Code: Select all

with Thisdatabasedocument.currentcontroller
   if  not .isConnected then  .connect
end with

This part you have to use when the database is closed. I bind it to the event: View Closed

Code: Select all

Thisdatabasedocument.currentcontroller.datasource.flush

I don't know if this can be the solution for your dataloss. I never used it but it seems this is the solution when I open the database from the explorer. From time to time I read about dataloss and the last time I found there was a problem there was no connection when you open the database from the explorer. The dataloss happens when you exit OpenOffice.org. I'm not sure this can help you. I donot understand the problem complete there I have never this problem. It seems there is a different between flushing your document and flushing a form.

Romke

[Nocton]

That is clear, thank you, Romke. But where do I find the events OpenDocument and View Closed for the database document? I am familiar with events for forms, but not for the database document as a whole. (Edit/Database/Properties is greyed out/disabled)

Regards, Nocton

[RPG]

Hello

The event are part of the database document. There where you can see the forms names and tablesnames.

menu --> tools --> customize.

the fourth from above : Open Document.
the nineth ( 9) from above : View closed.

Romke

[dstockman12]

Greetings:

I took the plunge and attempted to install the 1.8 then 2.0 version of HSQL/OOBase. I have one DB that is about 150 MB and has probably a couple hundred macros, and a different DB I am building that is about 10 MB and maybe 15-20 macros tied to it so far. After the install (I used the automated install on a WinXP Pro machine) I was unable to successfully convert the two files. The tables never appeared in the mydb DB. Given I use the big database every day, I decided to go back to the not-so-robust version of OO. I ran the reset utility. Unfortunately, whenever I attempt to use either database I get an error: "connection could not be established. The database was created by a newer version of OO.org". Now my DBs are not accessible. I went so far as uninstalling OO3.2 and downloading the program again and re-installing. I have the same problem. Anyone have suggestions?

Doug

[DACM]

Doug,

Sorry to hear you're having trouble.

I was unable to successfully convert the two files.

Sounds like you got ahead of yourself with the HSQLDB 2.0 upgrade. What steps did you take? The conversion of legacy databases must be accomplished after the 'Quick Fix' but before upgrading to HSQLDB 2.0. Legacy 'embedded database' conversion is non-trivial, but it can be accomplished with the proper steps...

The tables never appeared in the mydb DB.

Did you extract the database files (script, data, etc) from a copy of the original .odb file and place them in the mydb folder? Did you then rename them adding the "mydb" file-name prefix to each file (mydb.script, mydb.data, etc.)? That alone would grant access to your previously 'embedded database' tables in Base running either mydb.file.odb or START.vbs (mydb.server.odb)?

I ran the reset utility. Unfortunately, whenever I attempt to use either database I get an error: "connection could not be established.

Windows XP Pro should include TASKKILL support which the installer uses to Exit Quickstarter. If for any reason OOo and Quickstarter are never shutdown, the installers have no effect until both are shutdown.

The database was created by a newer version of OO.org". Now my DBs are not accessible.

What do you mean by "not accessible"? The OOo_reset utility disables multi-mode/file database support (including mydb), leaving only the original 'Embedded database' support. Either way, I would just run the Quick Fix (mydb.jar) to reset your configuration from HSQLDB 2.0 to HSQLDB 1.8, which leaves you with both original 'Embedded database' support and 'muli-mode/file' support using HSQLDB 1.8. Also delete the .tmp folder created by HSQLDB 2.0 if you're using START.vbs (mydb.server.odb) because it looks for that folder to determine which version of HSQLDB you're using.

I went so far as uninstalling OO3.2 and downloading the program again and re-installing. I have the same problem.

Do you still have your original, un-modified, un-touched .odb? There's nothing magical going on that would require re-installation of OOo. The 'quick fix' (mydb.jar) only adds support for multi-mode/file databases using HSQLDB 1.8. The HSQLDB 2.0 upgrade (hsqldb2_OOo.jar) disables the 'embedded database' because Base is redirected to use HSQLDB 2.0 which doesn't support 'embedded databases'. Running The 'quick fix' (mydb.jar) will reset back to HSQLDB 1.8 which re-enables the 'embedded database' support, but upgraded databases (mydb) cannot be downgraded. The OOo_reset utility will go one step further and disable 'multi-mode/file' database support, but don't use OOo_reset unless you don't use HSQLDB at all and/or need to save one-millionth of a second during Base startup.

[dstockman12]

DACM:

Thanks very much for replying. I am still having problems. I re-ran the Quick Fix (mydb.jar). It does not allow me to open the .odb file that is my main database. When I copy a version of the DB I had before I made the attempt to upgrade, I am able to open it. But once I save the DB and shut down OO, I am again unable to open the DB I just saved. I get the same error about the DB being created with a newer version of OO. I will attempt to paste a graphic that shows the error message I get. The graphic also includes the renamed files when I was trying to use HSQL 2.0. I am not worrying about HSQL 2.0 right now, I just need the ability to use my DB again.

At this point, I just want to get back to where I was before I attempted the upgrade. As it stands, I cannot use the DB any more.

Thanks for any guidance you can offer.

Some more information.
Before the upgrade, if I did not add anything to the database, but just opened it, when I closed it the DB would close quickly. After the upgrade, and the first time I open the pre-update DB file, even if I do not use the DB, when I close the DB, it goes through the lengthy save process (I assume zipping the files). Then if I attempt to open the DB again, I get the error that the DB was created by a newer version of OO and I cannot open the DB. This suggests to me it is saving the DB in a newer format, then it cannot open it later. I again re-installed OO3.2.1 hoping this would return my system to a pre-update state, but to no avail. This suggests I must have some file that is not removed when I un-install OO. Any thoughts on where I should look to find a file that is doing this?

Doug

[img][/img]

[DACM]

Doug,

I presume your original .odb is an all-inclusive, 'embedded database.' Take a copy and add a .zip extension (DB_Pts090714.odb.zip) and open it with a zip tool. Navigate to the database 'properties' file and open it in a text editor (Notepad). What does the first line say?
#HSQL Database Engine 1.8.1.3 -or- #HSQL Database Engine 1.8.0.10?
The 'Quick Fix' gives you the option to install HSQLDB 1.8.1.3 but AFAIK it's backwards compatible with HSQLDB 1.8.0.10 <EDIT: this upgrade has since been deleted from the 'Quick Fix' because it disables "Embedded database" support in Base>. But this 'properties' file entry could be causing problems if you're now reset to HSQLDB 1.8.0.10 as shipped with all 3.x versions of OOo (so far).
You can close the zip tool.

Now navigate to your OpenOffice 3 installation and find 'fundamentalbasis.ini.'
Program FIles/OpenOffice.org 3/Basis/program/fundamentalbasis.ini.
Open it in a text editor and post the contents.

In the meantime. Download the new version of the OOo_reset utility that installs the original HSQLDB 1.8.0.10. It also uses the original version of fundamentalbasis.ini. The version in the 'Quick Fix' is from OOo 3.3, but I've tested it for backwards compatibility with OOo 3.1.1 and 3.2.1 so I presume it ignores the extra lines of info in all older versions of OOo 3.x. But I could be wrong.

Also check these forums for reseting your "user profile." That's the only thing left when you uninstall and re-install OOo.

[Nocton]

Returning briefly to my original post and problem: After further development and extensive testing, I can now say that Romke's flushing macros (as in post Fri Nov 26, 2010 1:55 pm) do appear to have solved the problem of data loss.

In the 'After record change' and 'Before unloading' events on my three data entry forms I also use the code:
sub FlushdataDirect
' this must be activate
dim oda,oControl
oDa=Thisdatabasedocument ' This is the model of the database
oControl=oDa.currentcontroller 'This is the controller of the database
oControl.datasource.flush
'msgbox ("Database successfully flushed",0,"Flush")
end sub

I also got this from the Forum - and looking back, I now see that it was also supplied by Romke, so I am doubly indebted to you. Thank you.

Even though the database has crashed a couple of times while entering data, the data was not lost. However, I have again had once a crash that was unrecoverable - not just does one lose the data just entered, but the database is totally corrupt and unusable. This a very unattractive feature of Base. My only protection is to make copies frequently.

Regards, Nocton

[dstockman12]

DACM:

Thanks so much for taking the time to help me. The original .odb is a all-inclusive single file. I unzipped and posted the properties below.

I am not sure where the most recent version of the updated OOo_reset.jar is. I just went back to the original post and downloaded that again. Did you just repost an updated version of the file to the same URL? If so, I am good to go. If not, where should I go to find the update?

It probably means nothing, but I did a search on hsql and came up with a few hits for hsqldb2.dll in .cab folders. I am not sure why I would have hsql2's in the .cabs if I am no longer using.

I will run the reset and report back on what I get. If no luck, I will attempt to re-install QQ3.2.1 and change user profile as suggested. Thanks.

I ran the reset utility, but it did not help. I then unzipped the last version of the DB file that I can open. The properties file shows it is 1.8.0.10. When I open that file all works well, but then when I close the file and OO, it is automatically "upgraded" to version 1.8.1.3. Then when I attempt to open that, I get the error message about not being able to open because created with a newer version of OO. So OO automatically saves the file in the 1.8.1.3 version, but then cannot open the 1.8.1.3 version. Help!!!

From an unzipped "corrupted" version:
#HSQL Database Engine 1.8.1.3
#Mon Nov 29 08:31:08 EST 2010
hsqldb.script_format=0
runtime.gc_interval=0
hsqldb.incremental_backup=false
sql.enforce_strict_size=true
hsqldb.cache_size_scale=8
readonly=false
hsqldb.nio_data_file=false
hsqldb.cache_scale=13
version=1.8.1
hsqldb.default_table_type=cached
hsqldb.cache_file_scale=1
hsqldb.lock_file=true
hsqldb.log_size=10
modified=yes
hsqldb.cache_version=1.7.0
hsqldb.original_version=1.8.0
hsqldb.compatible_version=1.8.0


From Fundamentalbasis.ini

[Bootstrap]
BUNDLED_EXTENSIONS=${$ORIGIN/uno.ini:BUNDLED_EXTENSIONS}
BUNDLED_EXTENSIONS_USER=${$ORIGIN/uno.ini:BUNDLED_EXTENSIONS_USER}
SHARED_EXTENSIONS_USER=${$ORIGIN/uno.ini:SHARED_EXTENSIONS_USER}
UNO_SHARED_PACKAGES_CACHE=${$ORIGIN/uno.ini:UNO_SHARED_PACKAGES_CACHE}
TMP_EXTENSIONS=${$ORIGIN/uno.ini:TMP_EXTENSIONS}
UNO_USER_PACKAGES_CACHE=${$ORIGIN/uno.ini:UNO_USER_PACKAGES_CACHE}
URE_BIN_DIR=${.link:$ORIGIN/../ure-link}/bin
URE_MORE_JAVA_CLASSPATH_URLS=${BRAND_BASE_DIR}/Basis/program/classes/hsqldb.jar
URE_OVERRIDE_JAVA_JFW_SHARED_DATA=${BRAND_BASE_DIR}/share/config/javasettings_${_OS}_${_ARCH}.xml
URE_OVERRIDE_JAVA_JFW_USER_DATA=${${BRAND_BASE_DIR}/program/bootstrap.ini:UserInstallation}/user/config/javasettings_${_OS}_${_ARCH}.xml
URE_LIB_DIR=${.link:$ORIGIN/../ure-link}/bin
URE_MORE_JAVA_TYPES=$ORIGIN/classes/unoil.jar $ORIGIN/classes/ScriptFramework.jar ${${$ORIGIN/uno.ini:PKG_UserUnoFile}:UNO_JAVA_CLASSPATH} ${${$ORIGIN/uno.ini:PKG_SharedUnoFile}:UNO_JAVA_CLASSPATH} ${${$ORIGIN/uno.ini:PKG_BundledUnoFile}:UNO_JAVA_CLASSPATH}
URE_MORE_SERVICES=${${$ORIGIN/uno.ini:PKG_UserUnoFile}:UNO_SERVICES} ${${$ORIGIN/uno.ini:PKG_SharedUnoFile}:UNO_SERVICES} ${${$ORIGIN/uno.ini:PKG_BundledUnoFile}:UNO_SERVICES} $ORIGIN/services.rdb
URE_MORE_TYPES=$ORIGIN/offapi.rdb $ORIGIN/oovbaapi.rdb ${${$ORIGIN/uno.ini:PKG_UserUnoFile}:UNO_TYPES} ${${$ORIGIN/uno.ini:PKG_SharedUnoFile}:UNO_TYPES} ${${$ORIGIN/uno.ini:PKG_BundledUnoFile}:UNO_TYPES}

[Nocton]

Doug, I know it is no help, but you are having just the problems I had - see earlier post (Tue Nov 23, 2010 8:23 am). The worrying thing was that all my backups gave the error that they could not connect, due to the problem that you are trying to resolve. I hope that you solve things. I had to reinstall OO.
Regards, Nocton

[DACM]

...my backups gave the error that they could not connect, due to the problem that you are trying to resolve...I had to reinstall OO.

Actually these are two different issues:

Nocton's connection problem was caused by the upgrade to HSQLDB 2.0 (hsqldb2_OOo.jar) which disables legacy 'embedded database' support as mentioned during installation. This is normal because because Base can only support one version of HSQLDB at one time. However, running the Quick Fix (mydb.jar) re-establishes 'embedded database' support, while also downgrading your OOo installation to HSQLDB 1.8.

HSQLDB 2.0 upgrade installer (hsqldb2_OOo.jar)

I suspect the reason Nocton's downgrade did not take effect until re-install of OOo was due to the Quickstarter running. The reinstall of OOo was not necessary but it had the same effect while automatically exiting Quickstarter. This can be a problem with Windows XP Home because the 'Quick Fix'-associated installers utilize the built-in Windows TASKKILL utility which is not included with XP Home. All newer versions of Windows, including XP Pro, are reported by Microsoft to include the TASKKILL ulility/support. TASKKILL is simply an automated aid, in this case, for shutting down the OpenOffice.org Quickstarter. You can always shut it down manually by right-clicking it in the Windows Taskbar and selecting 'Exit'.

As an alternative, running the OOo_reset utility will aslo re-establish 'embedded database' support, but it goes one step further and also disables 'multi-mode/file' support of 'file:' and 'sever' mode databases under HSQLDB. It's never necessary to disable 'multi-mode/file' support, but there may be a reason to use the reset utility. If you selected the upgrade to HSQLDB 1.8.1.3 while running the Quick Fix, then you may downgrade to the original HSQLDB 1.8.0.10 using OOo_reset. Simply select HSQLDB 1.8.0.10 when running OOo_reset.

Let's break it down:

Two files are affected by the various Quick Fix-associated installers: fundamentalbasis.ini and hsqldb.jar

Here's the original setting in the file fundamentalbasis.ini as found in your Programs folder:
Program Files(?)/OpenOffice.org 3/Basis/program/fundamentalbasis.ini

Code: Select all

URE_MORE_JAVA_CLASSPATH_URLS=

Notice below that the Quick Fix (mydb.jar) simply adds a file-path to hsqldb.jar to the same line. This adds 'multi-mode/file' support under HSQLDB 1.8. This maintains legacy 'embedded database' support using the same hsqldb.jar installed by OOo. However, any version of HSQLDB 1.8.x will also support the legacy 'embedded database' with Base.

Code: Select all

URE_MORE_JAVA_CLASSPATH_URLS=${BRAND_BASE_DIR}/Basis/program/classes/hsqldb.jar

You do have the option to upgrade to HSQLDB 1.8.1.3 when running the Quick Fix (mydb.jar) as shown below. This replaces the original hsqldb.jar with a newer version. WARNING: this upgrade to HSQLDB 1.8.1.3 will disable 'embedded database' support! If you accidently open a legacy 'embedded database' with HSQLDB 1.8.1.3 you'll need to follow the next post to downgrade to HSQLDB 1.8.0.10 for use with Base. Or you can migrate the 'embedded database' to a multi-mode/file database to avoid the data loss issue.

The Quick Fix (mydb.jar) with all options selected including the upgrade to HSQLDB 1.8.1.3

If this causes problems, you can downgrade to the original HSQLDB 1.8.0.10 using the OOo_reset.jar utility. Select HSQLDB 1.8.0.10 during the installation process as shown below.

OOo_reset.jar utility with downgrade to the original HSQLDB 1.8.0.10 selected

[DACM]

I ran the reset utility, but it did not help. I then unzipped the last version of the DB file that I can open. The properties file shows it is 1.8.0.10. When I open that file all works well, but then when I close the file and OO, it is automatically "upgraded" to version 1.8.1.3. Then when I attempt to open that, I get the error message about not being able to open because created with a newer version of OO. So OO automatically saves the file in the 1.8.1.3 version, but then cannot open the 1.8.1.3 version. Help!!!

Oops!
I'm so sorry. I just tested HSQLDB 1.8.1.3 with the 'embedded database' and it doesn't work! That was careless of me to include a newer version of HSQLDB 1.8 without thoroughly testing all possibilities; I was concentrating on the multi-mode/file database configurations (which work great with the HSQLDB 1.8.1.3 upgrade) but I never fully tested the legacy 'embedded database.' I'll definitely remove the default selection for 1.8.1.3 in the 'Quick Fix' (mydb.jar) and include a warning about the 'embedded database' issue you've discovered here. Thank you!!!

But don't worry. You can edit the 'properties' file inside your .odb (zip container) using 7-zip and downgrade to HSQLDB 1.8.0.10 using OOo_reset.jar.
(1) Make a copy of your orignal .odb
(2) Rename the copy with a .zip extension -or- right-click 'Open with... > Choose program... > 7-ZipPortable.exe'
(3) Navigate the zip-container 'database > properties > right-click: Edit [F4]

Edit 'properties' using 7-zip

(4) Change all three references to 1.8.1 as follows...

Change FROM:
#HSQL Database Engine 1.8.1.3
version=1.8.1
hsqldb.cache_version=1.7.0
hsqldb.original_version=1.8.1
hsqldb.compatible_version=1.8.0

Change TO:
#HSQL Database Engine 1.8.0.10
version=1.8.0
hsqldb.cache_version=1.7.0
hsqldb.original_version=1.8.0
hsqldb.compatible_version=1.8.0

(5) Save your changes to the zip archive

Save the changes through the 7-zip utility

(6) Rename the .odb by removing the .zip extension

[DACM]

Help!!!

See the above post. I just used those steps to successfully downgrade an 'embedded database' opened in HSQLDB 1.8.1.3 back to the original HSQLDB 1.8.0.10 for use in Base 3.x. I hope that works for you, but if not there's a more tedious route to recovery similar to migration.

[dstockman12]

DACM:

Thank you so much. I was able to return to a state where I can use my original embedded database. When I used the reset utility and selected the 1.8.0.10 version, all went well. Of note, on my system, the listbox that contains the selections was collapsed and not easily seen/found. I am still very interested in the multi-file approach due to multiple DB corruptions, but am afraid modifying my existing large database that gets used every day will be difficult due to the time constraints. Besides using 2 computers, is there a simple way to run the embedded file BASE and a development HSQL 2.0 multi-file version on the same computer? I really appreciate your work on improving the functionality and robustness of Base.

Thanks!!!!!!

Doug

[DACM]

Yes, you can run as many different configurations as you want using OpenOffice.org Portable (OOP). Of course you'll need to use separate installation folders. I'm fairly sure you can use the same Java installation for all installations of OOP, but otherwise each OOP installation can also have it's own copy of Java Portable.
Here's the components:
OpenOfficePortable or LibreOffice Portable & JavaPortable
or the old version:
OpenOffice.org Portable 3.1.1

I don't recommend OpenOffice.org Portable 3.2 when using Base due to various Form bugs. OOP 3.2.1 is not available at this time but it, or perhaps OOP 3.3, should be available soon, but in the meantime I use OOP 3.1.1.

You can run an 'Embedded database" and "multi-mode/file" database side-by-side with the 'Quick Fix' using the built-in, original, HSQLDB 1.8.0.10. You'll need to run them side-by-side to migrate your Forms, Queries, Reports and Macros using drag-&-drop.
However, Table extraction/migration is manual using 7-zip; you could drag-&-drop Tables and follow the popup Wizard but you'll need to be familiar with the Table column 'field properties' workaround.
After everything is working you can update the multi-mode/file configuration to HSQLDB 2.0 perhaps using 'hsqldb2_OOo.jar'.