Oracle Unique Constraint Error w/ DbUnit

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

Oracle Unique Constraint Error w/ DbUnit

HarleyGuy


Hello Everyone!

Thanks for taking the time to read my question.

The issue I am having is I am getting a constraint error when I attempted to call a store procedure for Oracle. I realize this means we are trying to insert a record that already exist, but let me explain more.


SQL Exception in Testing Version Init
" java.sql.SQLException: ORA-00001: unique constraint (<Schema>.XPK<table_name>) violated
ORA-06512: at "<Stored Procedure Name>", line 270
ORA-06512: at line 1

 

I have attempted to debug this error. I backed the issue down to the DbUnit/JDBC Driver for Oracle. What I did was run the stored procedure from SQL Developer with no problems. All data was created properly. When I remove the DbUnit portion and run with JUnit and JDBC Connetion I get the same error.  When I use DbUnit and it way of connecting, I get the same error.  This is my setup:


database name=Oracle
database version=Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
 
With the Partitioning, OLAP and Data Mining options
database major version=10
database minor version=2
jdbc driver name=Oracle JDBC driver
jdbc driver version=10.2.0.3.0
jdbc driver major version=10
jdbc driver minor version=2

 

This is my connection string

 

       String _jdbcConnection_url = "jdbc:oracle:thin:@<host name>:1522:<SID>";   Yes, the port is correct.
 

 

My Store procedure call is:

 

 String sql = "{call <shema>.<stored procedure>.p_ver_init(?, ?, ?, ?, ?)}";
 CallableStatement cs = connection.getConnection().prepareCall(sql);
 cs.setInt(1, 2008420084);
 cs.setInt(2, 3);
 cs.setInt(3, 4);
 cs.setString(4, "JUnit Test");
 cs.setString(5, "Russ Test Case"); 
 

What is very strange is the second parameter is a version number. When I look at the database, this value is not used. It makes me suspect a caching issue may exist.....

Any suggestion would be greatly appreciated.
 
 
Russ


------------------------------------------------------------------------------
Crystal Reports - New Free Runtime and 30 Day Trial
Check out the new simplified licensing option that enables unlimited
royalty-free distribution of the report engine for externally facing
server and web deployment.
http://p.sf.net/sfu/businessobjects
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: Oracle Unique Constraint Error w/ DbUnit

Matthias Gommeringer
Hi Russ,

sorry that I cannot suggest more, but I think you should first try to get the procedure run with a plain JDBC call (without using any framework) and then integrate it with JUnit/dbunit. Perhaps the procedure call is performed twice and you just did not notice?

regards,
matthias

> -----Ursprüngliche Nachricht-----
> Von: "Russ Ray" <[hidden email]>
> Gesendet: 11.06.09 20:18:39
> An: [hidden email]
> Betreff: [dbunit-user] Oracle Unique Constraint Error w/ DbUnit


> Hello Everyone!
> Thanks for taking the time to read my question.
> The issue I am having is I am getting a constraint error when I
> attempted to call a store procedure for Oracle. I realize this means
> we are trying to insert a record that already exist, but let me
> explain more.
>
> SQL Exception in Testing Version Init
> " java.sql.SQLException: ORA-00001: unique constraint (<Schema>.XPK<
> table_name>) violated
> ORA-06512: at "<Stored Procedure Name>", line 270
> ORA-06512: at line 1
>
> I have attempted to debug this error. I backed the issue down to the
> DbUnit/JDBC Driver for Oracle. What I did was run the stored
> procedure from SQL Developer with no problems. All data was created
> properly. When I remove the DbUnit portion and run with JUnit and
> JDBC Connetion I get the same error. When I use DbUnit and it way of
> connecting, I get the same error. This is my setup:
>
> database name=Oracle
> database version=Oracle Database 10g Enterprise Edition Release 10.2.
> 0.3.0 - 64bit Production
>
> With the Partitioning, OLAP and Data Mining options
> database major version=10
> database minor version=2
> jdbc driver name=Oracle JDBC driver
> jdbc driver version=10.2.0.3.0
> jdbc driver major version=10
> jdbc driver minor version=2
>
> This is my connection string
>
>  String _jdbcConnection_url = "jdbc:oracle:thin:@<host name>:1522:<
> SID>"; Yes, the port is correct.
>
> My Store procedure call is:
>
>  String sql = "{call <shema>.<stored procedure>.p_ver_init(?, ?, ?, ?,
> ?)}";
>  CallableStatement cs = connection.getConnection().prepareCall(sql);
>  cs.setInt(1, 2008420084);
>  cs.setInt(2, 3);
>  cs.setInt(3, 4);
>  cs.setString(4, "JUnit Test");
>  cs.setString(5, "Russ Test Case");
>
> What is very strange is the second parameter is a version number.
> When I look at the database, this value is not used. It makes me
> suspect a caching issue may exist.....
> Any suggestion would be greatly appreciated.
>
> Russ------------------------------------------------------------------
> ------------ Crystal Reports - New Free Runtime and 30 Day Trial
> Check out the new simplified licensing option that enables unlimited
> royalty-free distribution of the report engine for externally facing
> server and web deployment. http://p.sf.net/sfu/businessobjects________
> _______________________________________ dbunit-user mailing list
> [hidden email] https://lists.sourceforge.net/lists/
>
> listinfo/dbunit-user


___________________________________________________________________
WEB.DE FreeDSL Komplettanschluss mit DSL 6.000 Flatrate und
Telefonanschluss für 17,95 Euro/mtl.! http://produkte.web.de/go/02/


------------------------------------------------------------------------------
Crystal Reports - New Free Runtime and 30 Day Trial
Check out the new simplified licensing option that enables unlimited
royalty-free distribution of the report engine for externally facing
server and web deployment.
http://p.sf.net/sfu/businessobjects
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: Oracle Unique Constraint Error w/ DbUnit

HarleyGuy
Mathias,
 
   Thank you for your suggestion......  How would I detect procedure code is being called twice?
 
Russ

On Thu, Jun 11, 2009 at 6:06 PM, Matthias Gommeringer <[hidden email]> wrote:
Hi Russ,

sorry that I cannot suggest more, but I think you should first try to get the procedure run with a plain JDBC call (without using any framework) and then integrate it with JUnit/dbunit. Perhaps the procedure call is performed twice and you just did not notice?

regards,
matthias

> -----Ursprüngliche Nachricht-----
> Von: "Russ Ray" <[hidden email]>
> Gesendet: 11.06.09 20:18:39
> An: [hidden email]
> Betreff: [dbunit-user] Oracle Unique Constraint Error w/ DbUnit


> Hello Everyone!
> Thanks for taking the time to read my question.
> The issue I am having is I am getting a constraint error when I
> attempted to call a store procedure for Oracle. I realize this means
> we are trying to insert a record that already exist, but let me
> explain more.
>
> SQL Exception in Testing Version Init
> " java.sql.SQLException: ORA-00001: unique constraint (<Schema>.XPK<
> table_name>) violated
> ORA-06512: at "<Stored Procedure Name>", line 270
> ORA-06512: at line 1
>
> I have attempted to debug this error. I backed the issue down to the
> DbUnit/JDBC Driver for Oracle. What I did was run the stored
> procedure from SQL Developer with no problems. All data was created
> properly. When I remove the DbUnit portion and run with JUnit and
> JDBC Connetion I get the same error. When I use DbUnit and it way of
> connecting, I get the same error. This is my setup:
>
> database name=Oracle
> database version=Oracle Database 10g Enterprise Edition Release 10.2.
> 0.3.0 - 64bit Production
>
> With the Partitioning, OLAP and Data Mining options
> database major version=10
> database minor version=2
> jdbc driver name=Oracle JDBC driver
> jdbc driver version=10.2.0.3.0
> jdbc driver major version=10
> jdbc driver minor version=2
>
> This is my connection string
>
>  String _jdbcConnection_url = "jdbc:oracle:thin:@<host name>:1522:<
> SID>"; Yes, the port is correct.
>
> My Store procedure call is:
>
>  String sql = "{call <shema>.<stored procedure>.p_ver_init(?, ?, ?, ?,
> ?)}";
>  CallableStatement cs = connection.getConnection().prepareCall(sql);
>  cs.setInt(1, 2008420084);
>  cs.setInt(2, 3);
>  cs.setInt(3, 4);
>  cs.setString(4, "JUnit Test");
>  cs.setString(5, "Russ Test Case");
>
> What is very strange is the second parameter is a version number.
> When I look at the database, this value is not used. It makes me
> suspect a caching issue may exist.....
> Any suggestion would be greatly appreciated.
>
> Russ------------------------------------------------------------------
> ------------ Crystal Reports - New Free Runtime and 30 Day Trial
> Check out the new simplified licensing option that enables unlimited
> royalty-free distribution of the report engine for externally facing
> server and web deployment. http://p.sf.net/sfu/businessobjects________
> _______________________________________ dbunit-user mailing list
> [hidden email] https://lists.sourceforge.net/lists/
>
> listinfo/dbunit-user


___________________________________________________________________
WEB.DE FreeDSL Komplettanschluss mit DSL 6.000 Flatrate und
Telefonanschluss für 17,95 Euro/mtl.! http://produkte.web.de/go/02/


------------------------------------------------------------------------------
Crystal Reports - New Free Runtime and 30 Day Trial
Check out the new simplified licensing option that enables unlimited
royalty-free distribution of the report engine for externally facing
server and web deployment.
http://p.sf.net/sfu/businessobjects
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user


------------------------------------------------------------------------------
Crystal Reports - New Free Runtime and 30 Day Trial
Check out the new simplified licensing option that enables unlimited
royalty-free distribution of the report engine for externally facing
server and web deployment.
http://p.sf.net/sfu/businessobjects
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: Oracle Unique Constraint Error w/ DbUnit

Luiz Eduardo Guida Valmont-2
Hi Russ,

While on JUnit, make sure you override the setUp and tearDown methods. Make sure you clean all tables before running your tests.

As for DbUnit, the default insert operation is CLEAN_INSERT before each test is run. You're running a stored procedure inside your tests. So make sure you insert test data in all tables accessed by your stored procedure.

All in all, you can also perform a "select * from" during setUp() to assess the contents of the tables accessed by the stored procedure. That way you can actually count how many times your tests (and so your procedure) are being run.

Regards,
Luiz

2009/6/11 Russ Ray <[hidden email]>
Mathias,
 
   Thank you for your suggestion......  How would I detect procedure code is being called twice?
 
Russ

On Thu, Jun 11, 2009 at 6:06 PM, Matthias Gommeringer <[hidden email]> wrote:
Hi Russ,

sorry that I cannot suggest more, but I think you should first try to get the procedure run with a plain JDBC call (without using any framework) and then integrate it with JUnit/dbunit. Perhaps the procedure call is performed twice and you just did not notice?

regards,
matthias

> -----Ursprüngliche Nachricht-----
> Von: "Russ Ray" <[hidden email]>
> Gesendet: 11.06.09 20:18:39
> An: [hidden email]
> Betreff: [dbunit-user] Oracle Unique Constraint Error w/ DbUnit


> Hello Everyone!
> Thanks for taking the time to read my question.
> The issue I am having is I am getting a constraint error when I
> attempted to call a store procedure for Oracle. I realize this means
> we are trying to insert a record that already exist, but let me
> explain more.
>
> SQL Exception in Testing Version Init
> " java.sql.SQLException: ORA-00001: unique constraint (<Schema>.XPK<
> table_name>) violated
> ORA-06512: at "<Stored Procedure Name>", line 270
> ORA-06512: at line 1
>
> I have attempted to debug this error. I backed the issue down to the
> DbUnit/JDBC Driver for Oracle. What I did was run the stored
> procedure from SQL Developer with no problems. All data was created
> properly. When I remove the DbUnit portion and run with JUnit and
> JDBC Connetion I get the same error. When I use DbUnit and it way of
> connecting, I get the same error. This is my setup:
>
> database name=Oracle
> database version=Oracle Database 10g Enterprise Edition Release 10.2.
> 0.3.0 - 64bit Production
>
> With the Partitioning, OLAP and Data Mining options
> database major version=10
> database minor version=2
> jdbc driver name=Oracle JDBC driver
> jdbc driver version=10.2.0.3.0
> jdbc driver major version=10
> jdbc driver minor version=2
>
> This is my connection string
>
>  String _jdbcConnection_url = "jdbc:oracle:thin:@<host name>:1522:<
> SID>"; Yes, the port is correct.
>
> My Store procedure call is:
>
>  String sql = "{call <shema>.<stored procedure>.p_ver_init(?, ?, ?, ?,
> ?)}";
>  CallableStatement cs = connection.getConnection().prepareCall(sql);
>  cs.setInt(1, 2008420084);
>  cs.setInt(2, 3);
>  cs.setInt(3, 4);
>  cs.setString(4, "JUnit Test");
>  cs.setString(5, "Russ Test Case");
>
> What is very strange is the second parameter is a version number.
> When I look at the database, this value is not used. It makes me
> suspect a caching issue may exist.....
> Any suggestion would be greatly appreciated.
>
> Russ------------------------------------------------------------------
> ------------ Crystal Reports - New Free Runtime and 30 Day Trial
> Check out the new simplified licensing option that enables unlimited
> royalty-free distribution of the report engine for externally facing
> server and web deployment. http://p.sf.net/sfu/businessobjects________
> _______________________________________ dbunit-user mailing list
> [hidden email] https://lists.sourceforge.net/lists/
>
> listinfo/dbunit-user


___________________________________________________________________
WEB.DE FreeDSL Komplettanschluss mit DSL 6.000 Flatrate und
Telefonanschluss für 17,95 Euro/mtl.! http://produkte.web.de/go/02/


------------------------------------------------------------------------------
Crystal Reports - New Free Runtime and 30 Day Trial
Check out the new simplified licensing option that enables unlimited
royalty-free distribution of the report engine for externally facing
server and web deployment.
http://p.sf.net/sfu/businessobjects
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user


------------------------------------------------------------------------------
Crystal Reports - New Free Runtime and 30 Day Trial
Check out the new simplified licensing option that enables unlimited
royalty-free distribution of the report engine for externally facing
server and web deployment.
http://p.sf.net/sfu/businessobjects
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user



------------------------------------------------------------------------------
Crystal Reports - New Free Runtime and 30 Day Trial
Check out the new simplified licensing option that enables unlimited
royalty-free distribution of the report engine for externally facing
server and web deployment.
http://p.sf.net/sfu/businessobjects
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: Oracle Unique Constraint Error w/ DbUnit

HarleyGuy
Most Excellent advice!  Thanks a million Luiz!!!!

On Fri, Jun 12, 2009 at 8:39 AM, Luiz Eduardo Guida Valmont <[hidden email]> wrote:
Hi Russ,

While on JUnit, make sure you override the setUp and tearDown methods. Make sure you clean all tables before running your tests.

As for DbUnit, the default insert operation is CLEAN_INSERT before each test is run. You're running a stored procedure inside your tests. So make sure you insert test data in all tables accessed by your stored procedure.

All in all, you can also perform a "select * from" during setUp() to assess the contents of the tables accessed by the stored procedure. That way you can actually count how many times your tests (and so your procedure) are being run.

Regards,
Luiz

2009/6/11 Russ Ray <[hidden email]>
Mathias,
 
   Thank you for your suggestion......  How would I detect procedure code is being called twice?
 
Russ

On Thu, Jun 11, 2009 at 6:06 PM, Matthias Gommeringer <[hidden email]> wrote:
Hi Russ,

sorry that I cannot suggest more, but I think you should first try to get the procedure run with a plain JDBC call (without using any framework) and then integrate it with JUnit/dbunit. Perhaps the procedure call is performed twice and you just did not notice?

regards,
matthias

> -----Ursprüngliche Nachricht-----
> Von: "Russ Ray" <[hidden email]>
> Gesendet: 11.06.09 20:18:39
> An: [hidden email]
> Betreff: [dbunit-user] Oracle Unique Constraint Error w/ DbUnit


> Hello Everyone!
> Thanks for taking the time to read my question.
> The issue I am having is I am getting a constraint error when I
> attempted to call a store procedure for Oracle. I realize this means
> we are trying to insert a record that already exist, but let me
> explain more.
>
> SQL Exception in Testing Version Init
> " java.sql.SQLException: ORA-00001: unique constraint (<Schema>.XPK<
> table_name>) violated
> ORA-06512: at "<Stored Procedure Name>", line 270
> ORA-06512: at line 1
>
> I have attempted to debug this error. I backed the issue down to the
> DbUnit/JDBC Driver for Oracle. What I did was run the stored
> procedure from SQL Developer with no problems. All data was created
> properly. When I remove the DbUnit portion and run with JUnit and
> JDBC Connetion I get the same error. When I use DbUnit and it way of
> connecting, I get the same error. This is my setup:
>
> database name=Oracle
> database version=Oracle Database 10g Enterprise Edition Release 10.2.
> 0.3.0 - 64bit Production
>
> With the Partitioning, OLAP and Data Mining options
> database major version=10
> database minor version=2
> jdbc driver name=Oracle JDBC driver
> jdbc driver version=10.2.0.3.0
> jdbc driver major version=10
> jdbc driver minor version=2
>
> This is my connection string
>
>  String _jdbcConnection_url = "jdbc:oracle:thin:@<host name>:1522:<
> SID>"; Yes, the port is correct.
>
> My Store procedure call is:
>
>  String sql = "{call <shema>.<stored procedure>.p_ver_init(?, ?, ?, ?,
> ?)}";
>  CallableStatement cs = connection.getConnection().prepareCall(sql);
>  cs.setInt(1, 2008420084);
>  cs.setInt(2, 3);
>  cs.setInt(3, 4);
>  cs.setString(4, "JUnit Test");
>  cs.setString(5, "Russ Test Case");
>
> What is very strange is the second parameter is a version number.
> When I look at the database, this value is not used. It makes me
> suspect a caching issue may exist.....
> Any suggestion would be greatly appreciated.
>
> Russ------------------------------------------------------------------
> ------------ Crystal Reports - New Free Runtime and 30 Day Trial
> Check out the new simplified licensing option that enables unlimited
> royalty-free distribution of the report engine for externally facing
> server and web deployment. http://p.sf.net/sfu/businessobjects________
> _______________________________________ dbunit-user mailing list
> [hidden email] https://lists.sourceforge.net/lists/
>
> listinfo/dbunit-user


___________________________________________________________________
WEB.DE FreeDSL Komplettanschluss mit DSL 6.000 Flatrate und
Telefonanschluss für 17,95 Euro/mtl.! http://produkte.web.de/go/02/


------------------------------------------------------------------------------
Crystal Reports - New Free Runtime and 30 Day Trial
Check out the new simplified licensing option that enables unlimited
royalty-free distribution of the report engine for externally facing
server and web deployment.
http://p.sf.net/sfu/businessobjects
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user


------------------------------------------------------------------------------
Crystal Reports - New Free Runtime and 30 Day Trial
Check out the new simplified licensing option that enables unlimited
royalty-free distribution of the report engine for externally facing
server and web deployment.
http://p.sf.net/sfu/businessobjects
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user



------------------------------------------------------------------------------
Crystal Reports - New Free Runtime and 30 Day Trial
Check out the new simplified licensing option that enables unlimited
royalty-free distribution of the report engine for externally facing
server and web deployment.
http://p.sf.net/sfu/businessobjects
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user



------------------------------------------------------------------------------
Crystal Reports - New Free Runtime and 30 Day Trial
Check out the new simplified licensing option that enables unlimited
royalty-free distribution of the report engine for externally facing
server and web deployment.
http://p.sf.net/sfu/businessobjects
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user