Re: XML Inport of GlAccounts List

[David E. Jones]

Foreign keys are one of the biggest issues that comes up with  
importing data, or in general with moving data between databases. In  
some cases a proper ordering of the records can resolve the issues,  
but there is such a thing a circular dependencies and with those the  
only way around it something like a "dummy fk" (unless you are using  
initially deferred foreign keys, but that can make it difficult to  
find actual problems since the error occurs on commit instead of on  
the actual violating operation).

The dummy fk option just creates an empty record with a primary key  
only. That way the foreign key will be satisfied. The trick here is  
that you should ONLY use it if you are SURE that all of the data is  
there so you don't end up with empty records lying around that you  
weren't aware of... In other words unless it came from a good clean  
data source you should go through the fk violations (like the one  
listed below) and make SURE that the record referred to will show up  
later on.

-David

P.S. This is covered along with all other such option in the Advanced  
Framework Training package that I just finished a few weeks ago. I  
very highly recommend this. Obviously I'm biased and want to sell  
copies (still about 80 to sell before recovering the production costs  
so we can make this available for free somehow), but it has a lot of  
information and anyone doing development based on OFBiz that goes  
beyond trivial things (and where someone else is around to cover  
trickier bits) should definitely review this material. The place to  
start is the Framework Introduction videos, and there are links from  
that page to details about the advanced package. Here is the  
framework intro resources page where you can download the intro stuff  
for free:

http://incubator.apache.org/ofbiz/VideosFrameworkIntro.html



On Aug 16, 2006, at 7:10 AM, Claude Feistel wrote:

Here is a scenario I tried, which I am sure people are using now:



Export a GlAccount list from a working instance of the ofbiz -  
using the
WebTool XML Data Export

Move the file to a new instance of ofbiz that has no GlAccount list  
loaded

Import the GlAccount list file into ofbiz - using the WebTool XML Data
Import



I didn't check any of the option boxes on the import screen and got  
the
error  below.



Then I checked create dummy FK's and the import ran ok.



What is "dummy FK" and will I be alright with that mode of import?   
Is there
a situation where you would NOT do this to make it work?



Original error:



ERROR: Error parsing entity xml file:
org.ofbiz.entity.GenericEntityException: Exception while inserting the
following entity:
[GenericEntity:GlAccount][accountCode,902000(java.lang.String)] 
[accountName,
STATE INCOME TAX(java.lang.String)][createdStamp,2006-08-16
08:03:35.706(java.sql.Timestamp)][createdTxStamp,2006-08-16
08:03:35.622(java.sql.Timestamp)][glAccountClassId,NON_POSTING 
(java.lang.Str
ing)][glAccountId,902000(java.lang.String)][glResourceTypeId,MONEY 
(java.lang
.String)][lastUpdatedStamp,2006-08-16
08:03:35.706(java.sql.Timestamp)][lastUpdatedTxStamp,2006-08-16
08:03:35.622(java.sql.Timestamp)][parentGlAccountId,900000 
(java.lang.String)
][postedBalance,0.0(java.lang.Double)] (while inserting:
[GenericEntity:GlAccount][accountCode,902000(java.lang.String)] 
[accountName,
STATE INCOME TAX(java.lang.String)][createdStamp,2006-08-16
08:03:35.706(java.sql.Timestamp)][createdTxStamp,2006-08-16
08:03:35.622(java.sql.Timestamp)][glAccountClassId,NON_POSTING 
(java.lang.Str
ing)][glAccountId,902000(java.lang.String)][glResourceTypeId,MONEY 
(java.lang
.String)][lastUpdatedStamp,2006-08-16
08:03:35.706(java.sql.Timestamp)][lastUpdatedTxStamp,2006-08-16
08:03:35.622(java.sql.Timestamp)][parentGlAccountId,900000 
(java.lang.String)
][postedBalance,0.0(java.lang.Double)] (SQL Exception while  
executing the
following:INSERT INTO GL_ACCOUNT (GL_ACCOUNT_ID, GL_ACCOUNT_TYPE_ID,
GL_ACCOUNT_CLASS_ID, GL_RESOURCE_TYPE_ID, GL_XBRL_CLASS_ID,
PARENT_GL_ACCOUNT_ID, ACCOUNT_CODE, ACCOUNT_NAME, DESCRIPTION,  
PRODUCT_ID,
POSTED_BALANCE, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP,  
CREATED_STAMP,
CREATED_TX_STAMP) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
(Cannot add or update a child row: a foreign key constraint fails
(`opentaps/GL_ACCOUNT`, CONSTRAINT `GLACCT_PAR` FOREIGN KEY
(`PARENT_GL_ACCOUNT_ID`) REFERENCES `GL_ACCOUNT` (`GL_ACCOUNT_ID`)))))







Claude Feistel