[Solved] S1000 General error java.lang

[GreyEagle06]

When I try to add data to a Form after a few records I get the following error:

"The data content could not be loaded:
S1000 General error java.lang ........"

Even if I remove all data entered in the record the error still occurs. The only way I can exit is to "not save". Then in order to recover, Open Office Base has to be shut down. Then reopening the data base takes an exceptionally long time. When I go to the record in question the data was not saved (as expected) so I reenter the same data and it saves. I can add data to a few more records then the same thing happens. I suspect the Field causing problems is the "Notes" section which is set up as "Text [VARCHAR]". I have tried Memo [LONGVARCHAR] which results in same problem. The problem could be with other Fields .... no clue.

I'm persistent, but have spent days on this problem. I have used other Open Office programs quite successfully, but am ready to give up on Base. Seems like the program is totally unstable.

ANY IDEAS OR SUGGESTIONS WOULD BE APPRECIATED. Thanks.

[Hagar Delest]

Hi and welcom eto the forum!

Some hints with this query in the forum.

[robleyd]

Are you using embedded HSQL and saving images to the database? If so, this post on another forum may help.

Is there more to the error message than you have posted?

[GreyEagle06]

As far as I know I'm using embedded HSQL (reading off the bottom of the opened database) and am saving pictures in each record. I don't think the pictures are the problem because they were added for each record in the beginning of data entry and I didn't get any errors when saving.

See uploaded file for image of latest error, which reads:

S1000 General error java.lang.NullPointerException in statement [UPDATE "Tbl_JewerlyData" SET "Date Purchased" = ?, "Description" = ? WHERE "Record" = ?]

The only thing I tried to add was in "Description". Then I couldn't save the record without getting an error and the only way to recover was to exit without saving. The process of closing out and opening Base then takes 3-5 minutes. A program of this size should take 10-30 seconds with my computer.

When reopened I double checked the Table to insure all parameters were entered correctly. Going back to the Form I would enter one Field then Save then another Field and Save. I could enter data in 2 or 3 Fields then it would error. It's the same error and will occur randomly on any of the Fields. Then reopening it would accept the same data in the same Field that caused an error. Two or Three data entry attempts later then the same error would appear.

"java.lang" is listed in the error syntax so I checked to insure that Java is up to date (1.8.0_241) and entered in Tools / Options / Java (radio button selected).

STILL TOTALLY LOST ON HOW TO SOLVE THIS PROBLEM.

[Villeroy]

what if you click [More]?

[GreyEagle06]

Clicking on "More" is informational about the error, but nothing on how to fix the problem.
A DEAD END.

[RoryOF]

Another manifestation of the Windows Defender problem, perhaps? This tutorial could be helpful:

[Tutorial] Problems with Windows Defender under Windows 10

[GreyEagle06]

I don't use Windows Defender.

[RoryOF]

Are you certain? It may have been silently reinstalled during a Windows update.

[Villeroy]

Does the status bar look like this?

[Villeroy]

Another question: Can you edit the table directly?
Did the form ever work?

[GreyEagle06]

Windows Defender is definitely off.
I show "Embedded database" "HSQL database engine"
I can edit the Table directly but eventually get the same error.
I have done most of the data entry using the Form. Works great until I get an inevitable error.

[RoryOF]

Can you check to see if the database files are writable after you get that error? Perhaps, for whatever reason, they are being marked read-only, or the form is losing the connection to the database.

[GreyEagle06]

Not sure what you mean by "writable".
After the error pop up window there is no way to continue with saving, entering data, going to the next record or back to a previous record. The only way to recover is to exit without saving. Then if I re-initiate the Form the same error is there. The only way to recover is to close the program and reopen, which take an unusual amount of time (3 - 5 minutes).

[RoryOF]

Now that I think of it, it is possible that the "NullPointerException" is actually indicating that the updater cannot read the file, hence is returning the null pointer. Some sophisticated file monitoring is required, I think - also, perhaps, checking for a memory leak.

[Villeroy]

menu:Tools>SQL ...
SHUTDOWN SCRIPT
Note the exact time and push [Execute]
This should shutdown the connection between the database and the office suite after creating a backup script that allows to recreate the database.
Does this run without error?
Close the whole office suite. Mind the nasty "quick starter" in the system tray.
Go to the temporary directory and find the generated script file. On my Linux system I did this with one of my embedded databases and found the script at /tmp/lu30859fa5435.tmp/lu30859fa543b.tmp (a random directory name and a random file name but with the right time stamp). It is a plain text file with SQL statements related to that database.

[GreyEagle06]

I really don't understand the error syntax.
What is a "NullPointer Exception"
I have no idea how to do the file monitoring you mentioned.
"Memory leak" ??? Not sure what this is or how to check it out.

[GreyEagle06]

I'll check the procedure you described.
Thanks

[GreyEagle06]

When I executed the "SHUT DOWN SCRIPT" command and went to the form I got the following error when trying to add a record.

So I couldn't add records and the prior records didn't need editing and I really didn't want to go back and change or delete anything.
I tried to find the tmp file with the associated time stamp and no luck. I even did a *.tmp search in my C drive ..... nothing.

This is a small database with only 20 records. I would like to add 20 more but not at 2 or 3 Fields at a time then close and restart the program. Open Office 4.1.7 has only been out since Sep 2019 so if I wonder if Base is flawed and unstable.

I have spent a week on this small project and think I have to move on and try something else. Thank you for your comments and assistance. I will continue to monitor to see if anyone has a solution.

[Villeroy]

I assume that your database has passed away but with one or more options to recover.

This should shutdown the connection between the database and the office suite after creating a backup script that allows to recreate the database.
Does this run without error?
Close the whole office suite. Mind the nasty "quick starter" in the system tray.
Go to the temporary directory and find the generated script file.

OK, did you find the script file? Copy it out of the temporary directory and rename it to something more descriptive like "database.txt" or something.
We won't need it in the following procedure but I think it is good to have.
-------------------------
Let's try to convert this database to something better:

1) Create a new directory and copy your database to that new directory. In the following we will use this copy in that directory and keep your original file untouched.
2) Create a subdirectory "driver" within the new directory.
3) Download this zip archive: https://sourceforge.net/projects/hsqldb ... p/download and open the zip archive.
We need a single file /hsqldb-2.5.0/hsqldb/lib/hsqldb.jar from this archive. Copy this file out of the archive into the driver directory created in step 2.
4) Open your office suite, call menu:Tools>Options>Security... button [Macro Security...], choose the highest security level, click the tab "Trusted Sources" and add one or more trusted directories. May be your documents folder but certainly not your downloads folder beause the downloads folder gets anything from anywhere.
5) Download this document of mine: download/file.php?id=35569 and put it in your trusted directory
6) Open the downloaded document of mine. It should open without any macro warning because you trust me and put it into a trusted directory. Click the button [Install Script]. Confirm that you want to install the script.
7) Open your database copy from the folder you have created in step 1 and call menu:Tools>Macros>RunMacro..., navigate to MyMacros>pyDBA>ExtractHSQL>Main and click the [Run] button. Confirm that you want to extract the embedded database and reconnect your database document with the extracted copy. Finally you should get a confirmation that the document has been connected to the extracted database using the HSQLDB driver that you created in step 3)

Test your new database.

[GreyEagle06]

No, could not find the tmp file. I did a search again this morning and looked at every tmp file generated for that day.
It was not there.

I'll try your procedure to convert the data base and get back to you.
Thanks.

[GreyEagle06]

INCREDIBLE !!

Converting the database worked!!
Very nice to get help from smart people.
I use some complex programs in my computer adventures but Open Office Base brought me to my knees.

Could you please explain to me what went on with the conversion process.
If I should do additional databases do I need to attach the hsqldb.jar driver per the same procedure?
Does this mean that there is a flaw in the 4.1.7 Open Office Base?

Thanks again Mr Villeroy.
Hope all is well in Germany

[Villeroy]

Base is not a database. Base is a set of tools to work with databases. Base is a so called database frontend.

[Database] <---> {db driver} <---> [Frontend]

[Database] can be a set of files or a program that runs on some other computer.
A database driver is a software to read or write from/to a specific type of database, a program to "open" a database.
A frontend can be a browser, an office suite or some application that is written for a specific database.
For instance:
[MySQLDB @ forum.openoffice.org] <-->{MySQL server software and http server} <--> [your internet browser]
WIth your internet browser you call a web page from forum.openoffice.org which is filled with database data (postings) and some form controls. When you are logged-in, you can use input forms to save new postings to the MySQLDB that stores the entire forum content.

[HSQLDB] <-->{HSQL driver} <--> [Base and office documents]
Even if your HSQLDB is embedded in a Base document, it will be installed to a temporary location once you open a form, a report, a query, acces the list of tables. The embedded HSQLDB will be extracted and a driver file hsqldb.jar that is shipped with the office suite "opens" the extracted database and serves data to your office suite. Every time when you close a form or some grid views after editing database data, the modified database will be wrapped back into the embedding Base document. This database in a single file is elegant but highly unsafe. As the database grows, the extraction and wrapping takes more time. Any error that occurs during the process or closing the laptop lid too hasty may destroy your embedded database because there is not enough time to finish the process.
Another draw back is that embedded HSQL databases still use a HSQL driver of version 1.8 which got its latest update in 2009. This database is somewhat outdated, limited and even more limited because of the way how it is integrated in the office suite.

My macro pulls the database out of the Base document and stores it in a subdirectory "database". If your database document is named "ABCD.odb", the names of the database files start with "ABCD." . If the macro finds driver/hsqldb.jar, it will use this database driver instead the one provided by your office suite. The macro points the connection parameters of the Base document to the files in the database directory and to the alternative driver. When you open this database for the first time, the new HSQLDB driver detects the old database version and converts your database on the fly to a more modern type of HSQLDB. Notice the status bar of this database. It indicates the connection type and the location and the logged-in user "SA" (system admin). I was hoping that this process would fix your database.
This database is a lot more stable because it is not extracted and wrapped back all the time and it offers some useful features. With some effort is even possible to move it to another computer and use it over the network.
The embedded database is still in the Base document but remains unused ballast. You find another macro to delete the embedded database in the same module where you found the Main routine to extract and reconnect. This will make the Base document a bit lighter but has no effect on the performance.
-----------------------------------------------
Now for the draw backs:
1) The whole thing is not easily portable anymore. Do not rename your database document. Don't touch the files in the database directory. You may move the whole directory with Base document, database folder and driver/hsqldb.jar to another place or another machine. But then you have to open the Base document and re-run the connection macro in order to reconnect. The macro will detect that there is a database directory with file names matching the document name and it will ask you to connect the Base document with the found database files.
You can use desktop links to the database, you can move and copy the database document as long as the database document as long as the path to the database files and to the driver remains constant. The database document itself does not contain any database data anymore.
2) Base is not familiar with this type of database to the same degree as with the old embedded HSQL 1.8. For instance, you may find that Base does not allow you to change the type of a table column. In this case you need to resort to SQL language which is easy enough and not part of every day's work. Your forms and reports should work in the same ways.
------------------------------------------
How to rebuild an embedded HSQLDB from here (if you really want):
Create another copy of your original file indicating the embedded HSQL in the status bar.
Open the copy and your new document connected to the stand-alone HSQLDB.
Delete all relations and then all tables from the embedded database.
Drag or copy the table icons from the external connection into the blank embedded HSQLDB. A dialog pops up. Make sure that the exact table names are used and that you copy both structure and data.
Recreate the relations in the relations manager (if any).
If all the names of tables and columns are the same, the queries, forms and reports should work as before.
From now on, you should really, really follow some backup strategy for your office documents, embedded and stand-alone databases and all the other files, at least the ones in your home directory.
------------------------------------------------
Today I tried SHUTDOWN SCRIPT with an embedded HSQL on a Windows box and could not find the script neither. I searched all files that had been created most recently but the script file was not among them.

[GreyEagle06]

Thank you for this thorough explanation.
I think I just completed a college course in DATABASE.

This a lot to digest for an entry level database user. I will keep your instruction for further study and use.

One last question. With all the complication you described with Base, would Access be any better?

Thanks again.

[Villeroy]

MS Access is not a database. MS Access is a set of tools to work with databases. MS Access is a so called database frontend.

[Database] <---> {db driver} <---> [Frontend]

Now for the differences:

MS Access is a commercial product with a mulit-million budget. The professional version of MS Access is a professional development suite which can produce database applications as stand-alone executables for the Windows platform.

MS Access comes with its own JET database engine to be embedded in MS Access documents. Just like Base, you can connect an Access document to any database you have a driver for. Now you can connect an Access document to your new stand-alone HSQLDB and edit database data with MS Access forms.

The binary document format of MS Access with embedded JET database does not require constant extraction and re-wrapping of whole databases. Contrary to that, a Base document is a zip archive (but with .odb file name suffix) which includes some configuration files, Writer documents (forms and reports are just that) and in some cases a database directory with a HSQLDB. HSQLDB is a stand-alone product that is not prepared to be embedded in some other file, therefore the constant extraction/wrapping of the whole thing. However, most Base documents do not include any database because the vast majority of Base documents link a spreadsheet with an address list to a form letter in Writer. The main purpose of database connectivity in OpenOffice is the ability to use a wide variety of data sources with office documents. You can feed serial letters, print labels, business cards and spreadsheets with data from dozends of different types of lists and queries that pull data from such lists. Back in the early days of OpenOffice a very loud crowd demanded that there should be something like a database in a single file and here we are now. Introducing a Base document with an embedded stand-alone product was a huge mistake.

MS Access includes a whole database development suite. I guess that the footprint of the installed product is larger than the footprint of the entire OpenOffice suite. If you install OpenOffice without Base, you save no more than 20 MB.

[GreyEagle06]

Thanks, Villeroy for the analysis.