XML Inport of GlAccounts List

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

XML Inport of GlAccounts List

Claude Feistel
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

 

Reply | Threaded
Open this post in threaded view
|

Re: XML Inport of GlAccounts List

David E Jones-2

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
>
>
>

Reply | Threaded
Open this post in threaded view
|

RE: XML Import of GlAccounts List

Claude Feistel
Very helpful info David, thanks!

I am still assessing the needs for both the operational support of ofbiz in
production situations, and also the development of new parts for specific
situations.  But I already conclude that it is a fascinating opportunity.  

Claude Feistel

-----Original Message-----
From: David E. Jones [mailto:[hidden email]]
Sent: Wednesday, August 16, 2006 7:58 PM
To: [hidden email]
Subject: Re: XML Inport of GlAccounts List


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
>
>
>