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.
OK, Learning! I had tried upper/lower but not the quotes on lower. All is now working. I want to thank you again very much for your patience with me on this. I understand things much better now and hopefully this exercise will help others. I have a last question for now but I think it should be another thread. It might have a long answer! If I wanted to convert to MySQL how would I do that assuming my preference Ubuntu. What wrapper program would I use?
@dsc3507 We all learned these things through experience and yours was another. If, as you say, all is working, please mark this as a correct answer (click on checkmark symbol upper left corner of my answer).
As far as MySQL, yes a new question would be preferable as it is really a new topic. Also, before asking the question, please look at Chapter 2 - Creating a DB in docs - click here.
While online help is great my old school prefers a printed book. Do you have any recommendations for that?
These are PDF files which you can download and print if you like. There is also a link at the bottom of the section on where to purchase a printed copy but I don’t believe there is a v5 printed copy available yet.
The answer to this question is in all the comments and the kind help from “ratslinger” getting me through all the machinations of getting an embedded DB split and running. I hope this helps others in the same situation.