DbUnit - XML dataset - autogenerated keys

classic Classic list List threaded Threaded
2 messages Options
tcs
Reply | Threaded
Open this post in threaded view
|

DbUnit - XML dataset - autogenerated keys

tcs
Hi,

I have two database tables, Site and Location. The PERSISTENT_ID (which is the Primary Key) value for both is auto generated.  The LOCATION table has a column 'Site' which references the SITE table's PERSISTENT_ID.

I created my XML dataset file as follows:
  <?xml version="1.0" encoding="UTF-8" ?>
<a class="b" onfocus="h()" onclick="return false" href="#">- <dataset>
  <SITE PERSISTENT_ID="999100" DESCRIPTION="TC Testing DbUnit" SHORTNAME="Testing DbUnit" />
  <LOCATION PERSISTENT_ID="998100" AREA="DbUnit-Location" SITE="999100" CREATEDTIMESTAMP="2006-11-04" LASTUPDATEDTIMESTAMP="2006-02-24" />
  </dataset>


When I try to import my dataset to my database using DbUnit, DbUnit fails for the 'Location' entry.
  The exception is: 
java.sql.SQLException: ORA-02291: integrity constraint (DEV.LOCATION_SITE) violated - parent key not found

What I see in the database is that the Persistent_ID of the new 'SITE' entry is not '
999100' as I specified in my dataset XML file.  Its something else.  I think when DbUnit tries to add the LOCATION entry, the SITE column value is not found, and the exception is thrown.

How do I go about inserting data from the dataset which references auto-generated keys. 

I don't need to use XML dataset.  The dataset can be of any type including Java objects. 

Any help will be greatly appreciated.

thanks,









HO HO HO, if you've been nice this year, email Santa! Visit asksanta.ca to learn more!
-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2005.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: DbUnit - XML dataset - autogenerated keys

George Dinwiddie
Tahura Chaudhry wrote:

> When I try to import my dataset to my database using DbUnit, DbUnit
> fails for the 'Location' entry.  The exception is:
> java.sql.SQLException: ORA-02291: integrity constraint
> (DEV.LOCATION_SITE) violated - parent key not found
>
> What I see in the database is that the Persistent_ID of the new 'SITE'
> entry is not '*999100' *as I specified in my dataset XML file.  Its
> something else.  I think when DbUnit tries to add the LOCATION entry,
> the SITE column value is not found, and the exception is thrown.
>
> How do I go about inserting data from the dataset which references
> auto-generated keys.

Tahura, I'm guessing that you're using a trigger to auto-generate the
key.  DbUnit really needs to insert it's own key, for many reasons
beyond the integrity constraint you found.  It won't be able to find its
data for updates or deletions, for instance.

My solution has been to modify the trigger to generate a key if none is
passed, but allow for manual keys to be specified.  See
http://idiacomputing.com/moin/PrimaryKeyGeneration#head-bf8960bfd6672e3be60c75caa52e705263354513 
for the trigger source.

I generally use negative numbers for the specified keys to avoid
collisions with the sequence number keys, and to make the test data obvious.

  - George

--
  ----------------------------------------------------------------------
   * George Dinwiddie *                      http://blog.gdinwiddie.com
   Software Development                    http://www.idiacomputing.com
   Consultant and Coach                    http://www.agilemaryland.org
  ----------------------------------------------------------------------


-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2005.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user