I have given you some karma. You can post a copy of your file in your question.
Thanks - added.
Nothing seems to be liking your file. I have found a problem from a while back with HSQL and a similar error. It does not help your situation. Here is my last suggestion. Create a new empty Split DB. Create a new directory. Place a copy of the wizard within. Run the wizard to create a new split DB.
Now open your embedded DB using LO v.4.2 which you said works. Copy the table from the old embedded & paste into new split. Then see if you can open the table in the new split DB.
OK - before I do that there is something I did not tell you . I am trying to do this split in the 4.2 code. I can easily move the directory over to a 5.3 system and try again there. That might be the problem?
No, that won’t make any difference. The split DB is using its own HSQL (in driver directory). The Embedded is using the HSQL in LO 4.2. The problem seems to lie in the 4.2 HSQLDB. What I am hoping to get around here is the script problem. By doing this process, the split should create a new script for the copied table.
OK, I have the new DB open but I have not created the table. Do I need to manually create the table with the same specs as the old one?
No. Just open both old & new .odb then right click on table name in old .odb & select Copy. Then right click in table name area in new split db & select Paste. Once pasted, try to open the table in new split DB.
OK It worked! I copied all data over - queries, reports, forms and all seems to work. So I can move (or copy) this directory over to another system and run it from there? Can I change the names of the files in the data directory - mydb.data etc. as long as I change them all and of course keep the extent? Can I change the name of the split wizzard to match a more meaningful name for this database? What is the best way to make backups? Before I just copied the one odb.
For whatever its worth I added the new script to the question for you to view.
So far, so good. There are things you need to know about split DB’s. First you can’t change existing field definitions. You can add/delete but not change fields. Second, and the reason this was a last choice, a copied table loses its auto-increment field if there was one. If you had one, usually for the primary key, it doesn’t work right now. To fix, see this post.
As for you questions:
You should be able to move the entire directory anywhere. Backups are the same. The entire folder becomes the database with the Base files in the .odb. I believe you can change the data directory names and .odb without a problem. Make a copy and try it on the copy. It has been quite a while since I worked with that (I mostly use MySQL now).
Please note after all this frustrating effort, you’ll have a much better set-up, greatly reduced chance of data loss/corruption, newer DB engine which has more SQL capabilities and ability to upgrade HSQLDB engine by just changing the driver files.
IMPORTANT! Before proceeding with any changes, let me know if you do have the auto increment problem.
OK to summarize the ID field auto increment change I would do this (my table name is CHS68) -
ALTER TABLE CHS68 ALTER COLUMN ID SET GENERATED BY DEFAULT AS IDENTITY
Next get the highest value already in use in the table and add 1 to that for use in the next statement:
ALTER TABLE CHS68 ALTER COLUMN ID RESTART WITH XXX
where XXX = that highest value + 1.
Then delete the column for ID as per example in XML file. I have already identified that but not changed.
Correct?
Just saw your question. Yes, if I try to enter another record I get the integrity error that the previous questioner had.
OK! It appears you have found all necessary information. Of course XXX is an actual number ( one higher than the current ID). It appears all looks good to go.
Just to be clear, second statement is:
ALTER TABLE CHS68 ALTER COLUMN ID RESTART WITH XXX
where XXX is highest ID +1
When entering the first line I get -
1: user lacks privilege or object not found: PUBLIC.CHS68
Here is a portion of the script showing table name -
CREATE CACHED TABLE PUBLIC.“chs68”(“ID” INTEGER NOT NULL PRIMARY KEY,“FirstName” VARCHAR(255),“LastName” VARCHAR(255),“SpouseName” VARCHAR(255),“Address” VARCHAR(255),“City” VARCHAR(255),“State” VARCHAR(2
Case is critical in SQL. In your earlier comment you stated you table name was CHS68 but based upon the script and last comment, it is chs68. so the two statements you need are:
ALTER TABLE "chs68" ALTER COLUMN ID SET GENERATED BY DEFAULT AS IDENTITY
and
ALTER TABLE "chs68" ALTER COLUMN ID RESTART WITH XXX
where XXX is highest ID +1
ID is OK as is since it is capitalized. This is the benefit of using all caps in table & field names. You don’t need surrounding quotes.