OFBiz › OFBiz - User

XML Inport of GlAccounts List

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

3 messages Options

Claude Feistel

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

David E Jones-2

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

Claude Feistel

RE: XML Import of GlAccounts List

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: