You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ofbiz.apache.org by Claude Feistel <cf...@integrasphere.com> on 2006/08/16 15:10:36 UTC
XML Inport of GlAccounts List
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
RE: XML Import of GlAccounts List
Posted by Claude Feistel <cf...@integrasphere.com>.
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:jonesde@undersunconsulting.com]
Sent: Wednesday, August 16, 2006 7:58 PM
To: ofbiz-user@incubator.apache.org
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
>
>
>
Re: XML Inport of GlAccounts List
Posted by "David E. Jones" <jo...@undersunconsulting.com>.
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
>
>
>