Porting database to 5.3

I am using Ubuntu 14.04 and a 4.2.8.2 embedded hsqldb database. I want to port over to LO 5.3. I have tried on both Linux 16.04 and win7 with 5.3 loaded and I get the same error on both:

-SQL Status: S1000
Error code: -40
General error: org.hsqldb.lib.FileSystemRuntimeException: java.io.IOException:

Java is up to date and loaded and checked in options.

when selecting the table. Is there a fix or way to do this?

Here is the script file in question from the last comment -

SET DATABASE COLLATION “Latin1_General”

CREATE SCHEMA PUBLIC AUTHORIZATION DBA

CREATE CACHED TABLE “chs68”(“ID” INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,“FirstName” VARCHAR(255),“LastName” VARCHAR(255),“SpouseName” VARCHAR(255),“Address” VARCHAR(255),“City” VARCHAR(255),“State” VARCHAR(255),“PostalCode” VARCHAR(255),“HomePhone” VARCHAR(255),“WorkPhone” VARCHAR(255),“CellPhone” VARCHAR(255),“EmailAddress” VARCHAR(255),“NumberAttending” VARCHAR(255),“GuestsID” VARCHAR(255),“Address2” VARCHAR(255),“NewLastname” VARCHAR(255),“SendMail” VARCHAR(255),“PlanAttend2008” VARCHAR(255),“MyElementary” VARCHAR(255),“MyJunior” VARCHAR(255),“class photo” VARCHAR(100),“PersonalComment” VARCHAR(255),“Deceased” BOOLEAN,“Contact2008” BOOLEAN,“Contact2018” BOOLEAN,“EmailOK” BOOLEAN,“Comments” VARCHAR(255),“Committee” BOOLEAN,“NeverFound” BOOLEAN,“EmailNotConfirmed” BOOLEAN,“PlanAttend2018” BOOLEAN)

SET TABLE “chs68” INDEX’59664 544’

ALTER TABLE “chs68” ALTER COLUMN “ID” RESTART WITH 544

CREATE USER SA PASSWORD “”

GRANT DBA TO SA

SET WRITE_DELAY 60

and here is the script once the new DB was working under split wizzard -

SET DATABASE UNIQUE NAME HSQLDB5CBE196A76

SET DATABASE GC 0

SET DATABASE DEFAULT RESULT MEMORY ROWS 0

SET DATABASE EVENT LOG LEVEL 0

SET DATABASE TRANSACTION CONTROL LOCKS

SET DATABASE DEFAULT ISOLATION LEVEL READ COMMITTED

SET DATABASE TRANSACTION ROLLBACK ON CONFLICT TRUE

SET DATABASE TEXT TABLE DEFAULTS ‘’

SET DATABASE DEFAULT TABLE TYPE CACHED

SET DATABASE SQL NAMES FALSE

SET DATABASE SQL REFERENCES FALSE

SET DATABASE SQL SIZE TRUE

SET DATABASE SQL TYPES FALSE

SET DATABASE SQL TDC DELETE TRUE

SET DATABASE SQL TDC UPDATE TRUE

SET DATABASE SQL TRANSLATE TTI TYPES TRUE

SET DATABASE SQL CONCAT NULLS TRUE

SET DATABASE SQL UNIQUE NULLS TRUE

SET DATABASE SQL CONVERT TRUNCATE TRUE

SET DATABASE SQL AVG SCALE 0

SET DATABASE SQL DOUBLE NAN TRUE

SET FILES WRITE DELAY 500 MILLIS

SET FILES BACKUP INCREMENT TRUE

SET FILES CACHE SIZE 10000

SET FILES CACHE ROWS 50000

SET FILES SCALE 32

SET FILES LOB SCALE 32

SET FILES DEFRAG 0

SET FILES NIO TRUE

SET FILES NIO SIZE 256

SET FILES LOG TRUE

SET FILES LOG SIZE 50

CREATE USER SA PASSWORD DIGEST ‘d41d8cd98f00b204e9800998ecf8427e’

ALTER USER SA SET LOCAL TRUE

CREATE SCHEMA PUBLIC AUTHORIZATION DBA

ALTER SEQUENCE SYSTEM_LOBS.LOB_ID RESTART WITH 1

SET DATABASE DEFAULT INITIAL SCHEMA PUBLIC

GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.SQL_IDENTIFIER TO PUBLIC

GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.YES_OR_NO TO PUBLIC

GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.TIME_STAMP TO PUBLIC

GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CARDINAL_NUMBER TO PUBLIC

GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CHARACTER_DATA TO PUBLIC

GRANT DBA TO SA

You state Java is checked in options. Does this mean you can see the version in the window below Use Java runtime environment? If this is not seen:

image description

you do not have the correct Java installed. The Java bit size must match LO. 32-bit LO uses 32-bit JRE and 64-bit LO needs 64-bit JRE.

Yes, I have 1.8.0_131 in that window and checked. Also LO info:
Version: 5.3.3.2 (x64) Build ID: 3d9a8b4b4e538a85e0782bd6c2d430bafe583448 CPU Threads: 2; OS Version: Windows 6.1; UI Render: default; Layout Engine: new; Locale: en-US (en_US); Calc: group
I guess my question is should this work? Should one be able to bring a DB from an older version of LO to a newer and no have problems, at least not total failure problems?

Today I still have & use Base files created in Ubuntu 14.04 with LO 4.2.8 on a Mint 18 system using LO 4.3.x (and other distros) without problem. I can’t be sure, but I seem to recall a problem with Base around v4.1.x or 4.2.x but cannot find anything right now. Are you using any macros or connected to an event on DB open? Can’t tell much without your .odb.

I might add some additional info - the messages I get are:

The connection to the data source “chs68-50th” could not be established. AND
org.hsqldb.lib.FileSystemRuntimeException: java.io.IOException:

I am moving this version 4 LO database to a new directory on a different computer. I have updated the database entry in options. I have also created a new simple test database in the new code and it seems to work fine. So I suspect that discounts any java issue.

Please note: currently using on Mint 18 with LO v5.3.2.2 and not v4.3!

I am not doing anything unusual. No macros, no events. I think I will create a very simple test DB in the old system and see if it to will not read in the new version and report back.

Yes, that makes a difference. When you first open this .odb, in the lower left corner does it state Embedded database? If so, by any chance, in your LO v4.2.8.2 did you have any additional paths set?

OK, I created a very simple 5 field 1 record database in the 4.1 LO base. Moved it over and it plays fine. I am not sure that proves anything other than you can port. Obviously it does not like something in my larger 540 record database. One thing I do is link images but I did move over the image directory in the same path under the database. It would be great if there was some kind of test for a database for any possible errors. This might be very hard to find. Any tips on what to do?

Yes, in answer to your question it does read embedded database. Am I out of luck because of that? What determines an embedded database when it is created? I am not storing any photos in the database at least I don’t think so . I started out doing that an quickly realized it was not a good idea and deleted that field and went to linked photos. Maybe I should copy the database and try deleting the photo links?

Linked images would not create a problem. Embedded would be a big problem. The next thing I would try is to split (you did say this was embedded) out the database. On this post (click here, in my answer, I have created a procedure to take a broken DB and possibly create a working split DB from it. It is not difficult. Just follow step-by-step and use a COPY of your odb

FYI - An embedded DB means that the data is zipped into the .odb file when closed and unzipped when the .odb is opened. This is a potential for trouble as this process has been known to corrupt data. A split DB will greatly reduce this type of problem as the data is resident in its own sub-directory. This is similar to other DB’s such as MySQL and PostgreSQL where data is stored separately. Should the above split work, I suggest you retain that set-up.

Thank you very much for your answer and help. The current database is not broken at least not in the old 4.1 environment but I will split it and try and get it working on 5.3. I can practice on a copy and not mess with the one that is working. Unfortunately I won’t get to this until Sunday at the earliest but I will get back with a result. Thanks again.

One other quick questions. How does a database get embedded to begin with? Was it an option I selected at creation or was that the standard back then?

LO Base even now, come with, and uses by default, the HSQLDB v1.8 (somewhat outdated) embedded database. This is OK for learning, small DB’s and posting samples. When dealing with important information or large amounts of data, many switch to , minimally, a split DB. Others, like myself, use an external DB such as MySQL, PostgreSQL, SQLite or Debian. There is a current project to switch to the Firebird embedded DB (available currently under experimental features).

To add to what Ratslinger said, I just noticed that the next version of Debian Linux called Stretch, or 9.0, and scheduled for release today (and upon which many other distros are based, e.g. Ubuntu) will now default to MariaDB 10.1 rather than MySQL. Yes, they are close cousins, but if you were to start out today you might want to start with MariaDB rather than MySQL.

Thanks for clearing up about the embedded database. Question - the latest split wizard hsqldb.jar v2.3.2 has both hsqldb.jar and sqltool.jar built-in. Do these two files still need to be in the driver directory?

I forgot exactly how it works. Just ran a quick test. It does already places those in the driver directory. That is all you need.

I actually had to create a driver directory and copy them there for it to work. Otherwise it complained it could not find them. Now the next problem. It is complaining about an error in the script. It is not that long but I don’t think it will fit in a comment. How do I transfer that file to you so you can look t it?

The exact errors -

The connection to the data source “Split_HSQLDB_2.3.2_Wizard_v3d” could not be established.
SQL Status: S1000
Error code: -25
error in script file line: /home/doug/Documents/CHS68_split/database/CHS68-split 3