1 Reply Latest reply on May 22, 2008 12:54 PM by Pete Muir

    DBUnitSeamTest working with Oracle

    Tom Nicholls Newbie

      I've managed to get DBUnit integration testing working in Seam with Oracle.  I know a number of people have been asking how to do this.  Essentially, the problem is that you need to disable foreign key constraint checking before running in the DBUnit dataset and then reenable it afterwards.  This is a one-line update in MySQL for example, but needs some PLSQL with a cursor in Oracle.


      You can subclass DBUnitSeamTest.java as follows:


      package uk.co.codeclass.dbunit;
      
      import java.sql.CallableStatement;
      import java.sql.SQLException;
      
      import org.dbunit.database.IDatabaseConnection;
      import org.jboss.seam.mock.DBUnitSeamTest;
      
      public abstract class DBUnitOracleSeamTest extends DBUnitSeamTest {
      
      
           @Override
           protected void disableReferentialIntegrity(IDatabaseConnection con) {
                try {
                     CallableStatement proc =
                          con.getConnection().prepareCall("{ call change_constraint_state(?) }");
                    proc.setString(1, "disable");
                    proc.execute();
                } catch (SQLException e) {
                     throw new RuntimeException(e);
                }
           }
      
           @Override
           protected void enableReferentialIntegrity(IDatabaseConnection con) {
                try {
                     CallableStatement proc =
                          con.getConnection().prepareCall("{ call change_constraint_state(?) }");
                    proc.setString(1, "enable");
                    proc.execute();
                } catch (SQLException e) {
                     throw new RuntimeException(e);
                }
                
           }
      }




      Obviously, this code is calling a stored procedure within your Oracle schema, and you can use the following script to create it:



      create or replace procedure change_constraint_state (mode varchar2) as
        cursor constraints is 
           select c.table_name, c.constraint_name from user_constraints c, user_tables u where c.table_name=u.table_name and c.constraint_type != 'P';
        
      Begin
        if (mode != 'enable' and mode != 'disable')
        then
          dbms_output.put_line('mode must be enable or disable');
          return;
        end if;
        for rec in constraints loop
          execute immediate 'alter table ' 
                     || rec.table_name
                     || ' '
                     || mode
                     || ' constraint '
                     || rec.constraint_name;
        end loop;
      End change_constraint_state;




      The easiest way to do this (for the uninitiated) is to log in to an SQL command line session (in the correct schema) and paste the script in, typing a / and return on a single line in order to execute the script.  You will only have to create the procedure once.


      Then simply subclass DBUnitOracleSeamTest instead of DBUnitSeamTest.


      The above is tested and works with Oracle XE 10g, but I should see no reason why it shouldn't work with any recent version of Oracle.  There's also no reason why you couldn't create the proc from the Java in the first method and drop it in the second.



      Tom