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

public class beforeTriggerOldNewValues {	
	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.printStackTrace(System.out);
		}
	}

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

		s.execute("create table test6table2 (id int, cl_old clob(2G), " +
				"cl_new clob(2G), char1_old char(1), char1_new char(1))");
		s.execute("create table test6table3 (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.derby1482ShallowCopyBeforeTriggerOnLobColumn.proc_display_old_new'");

        s.execute("create trigger test6tr2 no cascade before update of char1 on test6table3 " +
        		"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 test6table2 (id) values (?)");

		for (int i = 0; i < 100; i++) {
			ps.setInt(1, i);
	        ps.executeUpdate();
		}
		conn.commit();

		ps = conn.prepareStatement(
		"insert into test6table3 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 char is " + p1);
    	System.out.println("new value of char is " + p2);
}

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