package org.apache.derbyTesting.functionTests.tests.lang;
import java.sql.*;

public class reproOldNewValuesInStoredProcedure {	
	public static void main(String [] args) {
	    Connection conn = null;
		try {

	        Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
	        conn = DriverManager.getConnection("jdbc:derby:db;create=true");
			conn.setAutoCommit(false);
			
	       	loadData(conn);
			runCharTest(conn);

			try {
				DriverManager.getConnection("jdbc:derby:db;shutdown=true");
			} catch (SQLException se) {
				// Should just be the shutdown message.
				System.out.println(se.getMessage());
			}

			System.out.println("\n[ Done. ]\n");

		} catch (Exception e) {
			System.out.println("-= OOPS: " + e.getMessage());
		}
	}

	public static void loadData(Connection conn) throws SQLException{
        Statement s = conn.createStatement();
		try {
			s.execute("drop table testtabl1");
		} catch (SQLException sqle) {}

		s.execute("create table testtabl1 (id int, char1 char(1))");

        s.execute("create procedure proc_display_old_new " +
        		"(p1 char(1), p2 char(1)) parameter style java language "+
        		"java  NO SQL external name "+
        		"'org.apache.derbyTesting.functionTests.tests.lang.reproOldNewValuesInStoredProcedure.proc_display_old_new'");

        s.execute("create trigger test6tr2 no cascade before update of char1 on testtabl1 " +
        		"REFERENCING NEW as n_row old as o_row for each ROW "+
        		"call proc_display_old_new(o_row.char1, n_row.char1)");
		conn.commit();

		PreparedStatement ps = conn.prepareStatement(
		"insert into testtabl1 values (?,?)");

		for (int i = 0; i < 100; i++) {
			ps.setInt(1, i);
			ps.setString(2, "a");
	        ps.executeUpdate();
		}
		conn.commit();
	}
    public static void proc_display_old_new(String p1, String p2) throws SQLException {
    	System.out.println("Inside the procedure called by the BEFORE TRIGGER action");
    	System.out.println("old value of triggering table's char column passed as parameter to store proc " + p1);
    	System.out.println("new value of triggering table's char column passed as parameter to store proc " + p2);
    	System.out.println();

    	System.out.println("Now see if you can look at it through n_row alias of REFERENCING clause");
        Connection conn = DriverManager.getConnection("jdbc:default:connection");
        PreparedStatement ps = conn.prepareStatement("values(n_row.char1)");
        ps.executeQuery();
        conn.close();
}

	public static void runCharTest(Connection conn) throws Exception{
        //Cause the trigger to fire
		PreparedStatement ps = conn.prepareStatement(
		"update testtabl1 set char1 = 'b' where id = 1");
        ps.executeUpdate();
        conn.commit();
	}
}