I am trying to transfer a series of tables from an old OOBase file to a new LOBase one. I’ve set up the tables, forms, queries, and reports in the new file, so I’m (finally!) ready to transfer the data itself. I have the old tables exported into Calc. I can easily paste them into the new tables, but it won’t let me copy over the primary key (probably because it’s an autonumber field). As the primary keys of each table are used as foreign keys in the other tables, I can’t simply allow the new file to assign new keys. I can’t seem to edit my new tables, probably because I’m using a split database ([Wizard] Create a new 'split' HSQL 2.x database (View topic) • Apache OpenOffice Community Forum), so turning off autonumber temporarily doesn’t seem to be an option.
Hello,
Have just tested this and do not find any problem. Used Ubuntu 18.04 Mate with LO v6.3.4.2 and created a new split file with the v2.3.2 HSQLDB from the link. (You provide no specifics on the system you are using).
Performed a very simple test but here is result:
This shows the Table with auto increment set, the data in the Calc file and the table result after copy/paste of data from Calc to the table.
In the paste used Append data and selected Use first line as column names.
With the split file you cannot edit existing columns using the graphic interface once the table is saved. You can delete fields and create new ones. SQL can be used to modify existing items.
You never actually state what if any error you are getting. Make certain the data is in fact numeric and of the type you are using. This is seen during the paste process when going through the Next steps.
You are correct. When I pasted, and got to the “Assign columns” screen, it wouldn’t let me select my primary key in the destination table. I wrongly assumed this meant it wouldn’t copy over. It copied the primary key field just fine.
I am getting a warning “data exception: invalid character value for cast” for each paste. It doesn’t seem to matter which fields I copy over, or how much data. The data was copied from a OOBase table with identical fields (types, sizes, etc.) to the new LOBase table. Any ideas why this would happen?
Ubuntu 18.04, LO 6.0.7.3, HSQLDB 2.3.2 (I think)
Glad the key field worked out OK.
As for the ‘warning’ would need sample data causing this. Don’t recall that particular message. If you wish to proceed, this is best as a new question. If so be specific on the process.
Seems to me that type of message states data was not transferred correctly/completely. Would check there first. If all the data is correctly copied, sample is probably only way to test.
Also should mention that it is not clear as to why in the original question you were using Calc. Should be able to go directly from one DB table to another - have done this with many DB’s and tables. Calc can change the data type if you are not careful.
Thanks for the info. I used Calc because OOBase was being run under a VM, and (originally) LOBase wouldn’t open the old OOBase file. Tried it again just now, and it looks like I’ll be able to try copying directly. Thank you again.