Re: [jira] Commented: (DERBY-133) Autocommit turned false and rollbacks

Works for me with the attached testcase - can you clarify what you are 
doing differently?

--
Jeremy


Anil Venkobarao wrote:
> Hello Rick,
>  
> The sample code contains the foriegn key constraint being built.
>  
> ALTER TABLE salary ADD CONSTRAINT salary_fk1
>
> > FOREIGN KEY (empid)
> > REFERENCES employee(empid)
package tests;

import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

import util.DerbyTestBase;
import junit.framework.TestCase;

import org.apache.derby.jdbc.EmbeddedDriver;

/**
 * @version $Revision$ $Date$
 */
public class MissingFKTest extends TestCase  {
    static {
        new EmbeddedDriver();
    }
    private Connection c;

    public void testDummy() throws SQLException {
        Connection c1 = 
DriverManager.getConnection("jdbc:derby:bar;create=true");
        c1.setAutoCommit(false);
        Statement s = c1.createStatement();
        try {
            s.execute("INSERT INTO employee values(100, 'John', 100)");
            s.execute("INSERT INTO salary values(100, '01/01/2003')");
            try {
                s.execute("INSERT INTO salary values(200, '01/01/2003')");
                fail();
            } catch (SQLException e) {
                // ok
            }
            c1.commit();
            ResultSet rs = s.executeQuery("SELECT COUNT(*) FROM salary");
            rs.next();
            assertEquals(1, rs.getInt(1));
        } catch(SQLException e) {
            e.printStackTrace();
            fail();
        } finally{
            c1.rollback();
            s.close();
            c1.close();
        }
    }

    protected void setUp() throws Exception {
        super.setUp();
        c = DriverManager.getConnection("jdbc:derby:bar;create=true");
        Statement s = c.createStatement();
        s.execute("CREATE TABLE employee( empid INTEGER NOT NULL primary 
key,full_name VARCHAR(30) NOT NULL, salary DECIMAL(10,2) NOT NULL )");
        s.execute("CREATE TABLE salary(empid INTEGER NOT NULL,pay_date DATE NOT 
NULL)");
        s.execute("ALTER TABLE salary ADD CONSTRAINT salary_fk1 FOREIGN KEY 
(empid) REFERENCES employee(empid)");
        s.close();
    }

    protected void tearDown() throws Exception {
        Statement s = c.createStatement();
        s.execute("DROP TABLE salary");
        s.execute("DROP TABLE employee");
        s.close();
        c.close();
        super.tearDown();
    }
}