db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Chowdhury Lincoln" <Lincoln.Chowdh...@nrw.qld.gov.au>
Subject Question (Derby 10.2) copy in, copy out practices for database with identity columns with relationship and constraints on those columns
Date Wed, 19 Sep 2007 04:59:32 GMT
Question (Derby 10.2) copy in, copy out practices for database with
identity columns with relationship and constraints on those columns.

I am using:
Sun Application Server,
Toplink JPA, 
EJB 3
Derby 10.2 Database
to develop a JEE 5 application.

Created Entities with generated identity columns. These entities have
relationships and constraints. The tables and constraints are created
when I deploy the EJB project to the application server (the appserver
manages the connections).

What I am trying to achieve:
-	Replicate the Table data in various environment (Development,
Test, etc) and be able also apply schema modifications before applying
the changes.
o	Copy data out (Export) from an environment (database) to file
o	Copy data in (Import) to another environment (database) from
file
o	Modify schema then import the data

What I have tried:
-	used SYSCS_UTIL.SYSCS_EXPORT_TABLE to export the data to file
-	used SYSCS_UTIL.SYSCS_IMPORT_DATA to import the data from file
into the tables
Problems I am facing with this approach:
-	I can't force the db to insert the identity column from the file
as a result, the relationship between the table and another table (which
uses the id as foreign key) is not maintained
EG:
Two tables:
1.	Order {id - generated identity, name}
2.	OrderLine {id, orderID, name}

OrderLine.orderID - > Order.id (foreign key).

When I try to copy both these tables from one db to another, the
relationship is lost. Ie. The Order.id in the destination table is not
the same as the source table.

Question: 
How do I solve this problem?
What are the best practices for dealing with identity columns and
copying out/in table data?

Thanking you in anticipation.

************************************************************************
The information in this email together with any attachments is
intended only for the person or entity to which it is addressed
and may contain confidential and/or privileged material.
Any form of review, disclosure, modification, distribution
and/or publication of this email message is prohibited, unless
as a necessary part of Departmental business.
If you have received this message in error, you are asked to
inform the sender as quickly as possible and delete this message
and any copies of this message from your computer and/or your
computer system network.
************************************************************************


Mime
View raw message