Import BO's - problems with MySQL and ID's

Gabbitas

Hi All,

I am currently trying to export data from an existing aware installation which is set up with Derby database into a new aware installation set up with MySQL. I am using the import and export in the operation mode.

The problem i have is that when i import business objects into the new installation the data contained in the ID attribute is changed from what is in the .csv file to an auto icremented number which is set by, i assume, MySQL.
This causes problems when i try to import other business objects that are related - the relationship cant be made as aware cant find the original ID's and the import throws errors.

Any help would be much appreciated,

Thanks

Andy

tford

Hi Andy,

Sometimes importing data that already has relationships can be tricky.

A couple of points:

1) There is a file called ExportImport.txt in your AwareIM/docs subdirectory that is sometimes overlooked.

2) You can not use the ID #s from another application (either AwareIM application or other application) to be your ID #s upon importing.

They can be used in the import process, but they can not be used for the ID attribute.

3) For reference the User Guide states the following about using the * :

It is possible to use a particular attribute, rather than ID attribute to indicate update operation. If you add “*” symbol before the name of the object and attribute in the header of the CSV file, Aware IM will use values of this attribute to find instances of business objects to modify (only one instance must be found for each value of the attribute). This may be useful when exchanging data with non-Aware IM systems that do
not have the ID attribute.

4) Consider making an attribute in the new BSV called PriorID for each BO. Then after exporting your previous data to CSV files, edit the CSVs that change the ID attribute to PriorID. You might need to also do other editing of the PriorID to get the relationships to work depending on your needs.

5) After the import is complete, the PriorID attribute can be deleted.

Gabbitas

Hi Tom,

Thanks for your pointers on this one, i didn't realise it was not possible to use the same ID's!

My workaround was this:

1)Exported all data from my derby installation and included original ID's in the exports.

2)Edited all the csv exports to rename the 'ID' attribute as 'PriorID'

3)Using AwareIM configuration tool, Created new 'PriorID' attribute in the relevant BO's in my BSV under the MySQL installation.

4)Using phpMyAdmin i set the MAX_ID (in bas_idgen table) well above the amount of instances i had to import, in my case it was set to 10000 (None of my PriorID's had a value higher than this either and i knew that the basdb was empty at this point)

5)Using the operation mode i imported the edited csv's into the Aware/MySQL setup and let new IDs be genarated for each BO instance i imported.

6)Again using phpMyAdmin i ran a sql command on each table that i had imported my data into:

UPDATE `basdb`.`BusinessSpace_BusinessObject` SET `ID` = `PriorID`;

This changed the ID's back to the original ID's and all relationships were mended without further tweeking.

I didn't think it would take me too long to change from Derby to MySQL but i wasnt expecting the problems i had above! My head hurts so im off for a beer now!

Thanks again for your help,

Andy 😉