/*
 *  Derby - Class org.apache.derbyTesting.functionTests.tests.lang.ProcedureInTriggerTest
 *  
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied.  See the License for the
 * specific language governing permissions and limitations
 * under the License.
 */
package org.apache.derbyTesting.functionTests.tests.lang;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import junit.framework.Test;
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.BaseTestSuite;
import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
import org.apache.derbyTesting.junit.JDBC;
import org.apache.derbyTesting.junit.TestConfiguration;

public class ProcedureInTriggerTest extends BaseJDBCTestCase {

    public ProcedureInTriggerTest(String name) {
        super(name);
       
    }

    /**
     * Test triggers that fire procedures with no sql
     * 
     * @throws SQLException
     */
    public void testTriggerNoSql() throws SQLException {
        Statement s = createStatement();
        s.execute("create trigger after_stmt_trig_no_sql AFTER insert on t2 for each STATEMENT call proc_no_sql()");
        //insert 2 rows. check that trigger is fired - procedure should be called once
        zeroArgCount = 0;
        s.execute("insert into t2 values (1,2), (2,4)");
        checkAndResetZeroArgCount(1);
        ResultSet rs = s.executeQuery("SELECT * FROM T2");
        JDBC.assertFullResultSet(rs, new String[][] {{"1","2"},{"2","4"}});
        // Check that a procedure used by a trigger cannot be dropped.
        assertStatementError("X0Y25", s, "drop procedure proc_no_sql");
        s.execute("insert into t2 values (3,6)");
        checkAndResetZeroArgCount(1);
        rs = s.executeQuery("SELECT * FROM T2");  
        JDBC.assertFullResultSet(rs, new String[][] {{"1","2"},{"2","4"},{"3","6"}});
        s.execute("create trigger after_row_trig_no_sql AFTER delete on t2 for each ROW call proc_no_sql()");
        //--- delete all rows. check that trigger is fired - procedure should be called 3 times
        s.execute("delete from t2");
        checkAndResetZeroArgCount(3);
        rs = s.executeQuery("select * from t2");
        JDBC.assertEmpty(rs);
        s.execute("drop trigger after_stmt_trig_no_sql");
        s.execute("drop trigger after_row_trig_no_sql");
        s.execute("create trigger before_stmt_trig_no_sql no cascade BEFORE insert on t2 for each STATEMENT call proc_no_sql()");
        //--- insert 2 rows. check that trigger is fired - procedure should be called once
        s.execute("insert into t2 values (1,2), (2,4)");
        checkAndResetZeroArgCount(1);
        rs = s.executeQuery("select * from t2");
        JDBC.assertFullResultSet(rs, new String[][] {{"1","2"},{"2","4"}});
        // Check that a procedure used by a trigger cannot be dropped.
        assertStatementError("X0Y25", s, "drop procedure proc_no_sql");
        s.execute("insert into t2 values (3,6)");
        checkAndResetZeroArgCount(1);
        // check inserts are successful
        rs = s.executeQuery("SELECT * FROM T2");  
        JDBC.assertFullResultSet(rs, new String[][] {{"1","2"},{"2","4"},{"3","6"}});
        s.execute("create trigger before_row_trig_no_sql no cascade BEFORE delete on t2 for each ROW call proc_no_sql()");
        // delete and check trigger fired
        s.execute("delete from t2");
        checkAndResetZeroArgCount(3);
        // check delete is successful
        rs = s.executeQuery("select * from t2");
        JDBC.assertEmpty(rs);
        s.execute("drop trigger before_stmt_trig_no_sql");
        s.execute("drop trigger before_row_trig_no_sql");
        s.close();
    }
    
    /**
     * Test CONTAINS SQL triggers (neither reads no writes data)
     * procedure does just a getConnection and that's it.
     * 
     * @throws SQLException
     */
    public void testTriggerContainsSql() throws SQLException{
        Statement s = createStatement();
        s.execute("insert into t2 values (1,2), (2,4)");
        s.execute("create trigger after_row_trig_contains_sql AFTER update on t2 for each ROW call proc_contains_sql()");
        // --- update 2 rows. check that trigger is fired - procedure should be called twice
        s.execute("update t2 set x = x*2");
        checkAndResetGetConnectionProcCount(2);
        //--- check updates are successful
        ResultSet rs = s.executeQuery("select * from t2");
        JDBC.assertFullResultSet(rs, new String[][] {{"2","2"},{"4","4"}});
        s.execute("create trigger before_stmt_trig_contains_sql no cascade BEFORE delete on t2 for each STATEMENT call proc_contains_sql()");
        //--- delete 2 rows. check that trigger is fired - procedure should be called once
        s.execute("delete from t2");
        checkAndResetGetConnectionProcCount(1);
        //--- check delete is successful
        rs = s.executeQuery("select * from t2");
        JDBC.assertEmpty(rs);
        s.execute("drop trigger after_row_trig_contains_sql");
        s.execute("drop trigger before_stmt_trig_contains_sql");
        s.close();
    }
   
    /**
     * Test triggers for procedures that READ SQL DATA
     * @throws SQLException
     */
    public void testTriggerReadsSql() throws SQLException {
        Statement s = createStatement();
        //--- create a row in t1 for use in select in the procedure
        s.execute("insert into t1 values (1, 'one')");
        s.execute("create trigger after_stmt_trig_reads_sql AFTER insert on t2 for each STATEMENT call proc_reads_sql(1)");
        //--- insert 2 rows. check that trigger is fired - procedure should be called once
        selectRowsCount = 0;
        s.execute("insert into t2 values (1,2), (2,4)");
        checkAndResetSelectRowsCount(1);
        //--- check inserts are successful
        ResultSet rs = s.executeQuery("select * from t2");
        JDBC.assertFullResultSet(rs, new String[][] {{"1","2"},{"2","4"}});
        s.execute("drop trigger after_stmt_trig_reads_sql");
        s.execute("create trigger before_row_trig_reads_sql no cascade BEFORE delete on t2 for each ROW call proc_reads_sql(1)");
        //--- delete 2 rows. check that trigger is fired - procedure should be called twice
        s.execute("delete from t2");
        checkAndResetSelectRowsCount(2);
        // --- check delete is successful
        rs = s.executeQuery("select * from t2");
        JDBC.assertEmpty(rs);
        s.execute("drop trigger before_row_trig_reads_sql");
        //--- empty t1
        s.execute("delete from t1");
        s.close();
    }
    
    /**
     * Test triggers that MODIFY SQL DATA
     * 
     * @throws SQLException
     */
    public void testModifiesSql() throws SQLException {
        Statement s = createStatement();
        s.execute("create trigger after_stmt_trig_modifies_sql_insert_op AFTER insert on t2 for each STATEMENT call proc_modifies_sql_insert_op(1, 'one')");
        //--- insert 2 rows
        s.execute("insert into t2 values (1,2), (2,4)");
        //--- check trigger is fired. insertRow should be called once
        ResultSet rs = s.executeQuery("select * from t1");
        JDBC.assertFullResultSet(rs, new String[][]{{"1","one"}});  
        //--- check inserts are successful
        rs = s.executeQuery("select * from t2");
        JDBC.assertFullResultSet(rs, new String[][] {{"1","2"},{"2","4"}});
        
        s.execute("create trigger after_row_trig_modifies_sql_update_op AFTER update of x on t2 for each ROW call proc_modifies_sql_update_op(2)");
        //--- update all rows
        s.execute("update t2 set x=x*2");
        // --- check row trigger was fired. value of i should be 5
        rs = s.executeQuery("select * from t1");
        JDBC.assertFullResultSet(rs, new String[][]{{"5","one"}});
        //--- check update successful
        rs = s.executeQuery("select * from t2");
        JDBC.assertFullResultSet(rs, new String[][] {{"2","2"},{"4","4"}});
        s.execute("create trigger after_stmt_trig_modifies_sql_delete_op AFTER delete on t2 for each STATEMENT call proc_modifies_sql_delete_op(5)");
        //--- delete from t2
        s.execute("delete from t2");
        // --- check trigger is fired. table t1 should be empty
        rs = s.executeQuery("select * from t1");
        JDBC.assertEmpty(rs);
        // check delete successful
        rs = s.executeQuery("select * from t2");
        JDBC.assertEmpty(rs);
        s.execute("drop trigger after_stmt_trig_modifies_sql_insert_op");
        s.execute("drop trigger after_row_trig_modifies_sql_update_op");
        s.execute("drop trigger after_stmt_trig_modifies_sql_delete_op");
        s.execute("create trigger refer_new_row_trig AFTER insert on t2 REFERENCING NEW as new for each ROW call proc_modifies_sql_insert_op(new.x, 'new')");
        //--- insert a row
        s.execute("insert into t2 values (25, 50)");
        //--- check trigger is fired. insertRow should be called once
        rs = s.executeQuery("select * from t1");
        JDBC.assertFullResultSet(rs, new String[][] {{"25","new"}});
        // --- check inserts are successful
        rs = s.executeQuery("select * from t2");
        JDBC.assertFullResultSet(rs, new String[][] {{"25","50"}});
        s.execute("create trigger refer_old_row_trig AFTER delete on t2 REFERENCING OLD as old for each ROW call proc_modifies_sql_delete_op(old.x)");
        // --- delete a row
        s.execute("delete from t2 where x=25");
        //--- check trigger is fired. deleteRow should be called once
        rs = s.executeQuery("select * from t1");
        JDBC.assertEmpty(rs);
        rs = s.executeQuery("select * from t2");
        JDBC.assertEmpty(rs);
        s.execute("drop trigger refer_new_row_trig");
        s.execute("drop trigger refer_old_row_trig");
        //--- create a before trigger that calls a procedure that modifies sql data. 
        //--- trigger creation should fail
        assertStatementError("42Z9D",s,"create trigger before_trig_modifies_sql no cascade BEFORE insert on t2 for each STATEMENT call proc_modifies_sql_insert_op(1, 'one')");
        //--- in a BEFORE trigger, call a procedure which actually modifies SQL data      
        //--- trigger creation will pass but firing should fail
        s.execute("create trigger bad_before_trig no cascade BEFORE insert on t2 for each STATEMENT call proc_wrongly_defined_as_no_sql(50, 'fifty')");
        //--- try to insert 2 rows
        try {
            s.execute("insert into t2 values (1,2), (2,4)");
        } catch (SQLException se) {
            assertSQLState("38000", se);
            se = se.getNextException();
            assertSQLState("38001", se);
        }
        //--- check trigger is not fired.
        rs = s.executeQuery("select * from t1");
        JDBC.assertEmpty(rs);
        rs = s.executeQuery("select * from t2");
        JDBC.assertEmpty(rs);
        s.execute("drop trigger bad_before_trig");
        //--- procedures which insert/update/delete into trigger table
        s.execute("create trigger insert_trig AFTER update on t1 for each STATEMENT call proc_modifies_sql_insert_op(1, 'one')");
        s.execute("insert into t1 values(2, 'two')");
        s.execute("update t1 set i=i+1");
        //--- Check that update and insert successful. t1 should have 2 rows
        rs = s.executeQuery("select * from t1");
        JDBC.assertFullResultSet(rs, new String[][] {{"3","two"},{"1","one"}});
       s.execute("drop trigger insert_trig");
       s.execute("create trigger update_trig AFTER insert on t1 for each STATEMENT call proc_modifies_sql_update_op(2)");
       s.execute("insert into t1 values (4,'four')");
       //--- Check that insert successful and trigger fired. 
       rs = s.executeQuery("select * from t1");
       String [][] expectedRows = {{"5","two            "},
                                   {"3","one            "},
                                   {"6","four           "}};
       JDBC.assertFullResultSet(rs, expectedRows);
              
       s.execute("drop trigger update_trig");
       s.execute("create trigger delete_trig AFTER insert on t1 for each STATEMENT call proc_modifies_sql_delete_op(3)");
       s.execute("insert into t1 values (8,'eight')");
       //-- Check that insert was successful and trigger was fired
       rs = s.executeQuery("select * from t1");
       expectedRows = new String [][]
                        {{"5","two            "},{"6","four           "},{"8","eight          "}};
       JDBC.assertFullResultSet(rs, expectedRows);
       s.execute("drop trigger delete_trig");
       //--- Procedures with schema name
       s.execute("create trigger call_proc_in_default_schema AFTER insert on t2 for each STATEMENT call APP.proc_no_sql()");
       //--- insert 2 rows. check that trigger is fired - procedure should be called once
       s.execute("insert into t2 values (1,2), (2,4)");
       //--- check inserts are successful
       rs = s.executeQuery("select * from t2");
       JDBC.assertFullResultSet(rs, new String[][] { {"1","2"}, {"2","4"}});
       s.execute("drop trigger call_proc_in_default_schema");
       s.execute("create trigger call_proc_in_default_schema no cascade BEFORE delete on t2 for each ROW call APP.proc_no_sql()");
       //--- delete 2 rows. check that trigger is fired - procedure should be called twice
       s.execute("delete from t2");
       //--- check delete is successful
       rs = s.executeQuery("select * from t2");
      JDBC.assertEmpty(rs);
      s.execute("drop trigger call_proc_in_default_schema");
      s.execute("create trigger call_proc_in_new_schema no cascade BEFORE insert on t2 for each STATEMENT call new_schema.proc_in_new_schema()");
      //--- insert 2 rows. check that trigger is fired - procedure should be called once
      s.execute("insert into t2 values (1,2), (2,4)");
      //--- check inserts are successful
      rs = s.executeQuery("select * from t2");
      JDBC.assertFullResultSet(rs, new String[][] {{"1","2"},{"2","4"}});
      s.execute("drop trigger call_proc_in_new_schema");
      s.execute("create trigger call_proc_in_new_schema AFTER delete on t2 for each ROW call new_schema.proc_in_new_schema()");
      //--- delete 2 rows. check that trigger is fired - procedure should be called twice
      s.execute("delete from t2");
      //--- check delete is successful
      rs = s.executeQuery("select * from t2");
      JDBC.assertEmpty(rs);
      s.execute("drop trigger call_proc_in_new_schema");
      s.close();
    }

  
    /**
     * Some misc negative tests for procedures in triggers.
     * 
     * @throws SQLException
     */
    public void testTriggerNegative() throws SQLException {
        Statement s = createStatement();

        // Insert some test data.
        s.execute("insert into t1 values (5,'two'), (6,'four'), (8,'eight')");

        ResultSet rs;
        assertStatementError("42Y03",s,"create trigger call_non_existent_proc1 AFTER insert on t2 for each ROW call non_existent_proc()");
          rs = s.executeQuery("select count(*) from SYS.SYSTRIGGERS where CAST(triggername AS VARCHAR(128))='CALL_NON_EXISTENT_PROC1'");
          JDBC.assertFullResultSet(rs, new String[][] {{"0"}});
          assertStatementError("42Y03",s,"create trigger call_proc_with_non_existent_proc2 AFTER insert on t2 for each ROW call new_schema.non_existent_proc()");
          rs = s.executeQuery("select count(*) from SYS.SYSTRIGGERS where CAST(triggername AS VARCHAR(128))='CALL_NON_EXISTENT_PROC2'");
          JDBC.assertFullResultSet(rs, new String[][] {{"0"}});
          assertStatementError("42Y07",s,"create trigger call_proc_in_non_existent_schema AFTER insert on t2 for each ROW call non_existent_schema.non_existent_proc()");
          rs = s.executeQuery("select count(*) from SYS.SYSTRIGGERS where CAST(triggername AS VARCHAR(128))='CALL_PROC_IN_NON_EXISTENT_SCHEMA'");
          JDBC.assertFullResultSet(rs, new String[][] {{"0"}});
          assertStatementError("42X50",s,"create trigger call_proc_using_non_existent_method AFTER insert on t2 for each ROW call proc_using_non_existent_method()");
          rs = s.executeQuery("select count(*) from SYS.SYSTRIGGERS where CAST(triggername as VARCHAR(128))='CALL_PROC_WITH_NON_EXISTENT_METHOD'");
          JDBC.assertFullResultSet(rs, new String[][] {{"0"}});
          assertStatementError("42Y03",s,"create trigger call_non_existent_proc1 no cascade BEFORE insert on t2 for each ROW call non_existent_proc()");
          rs = s.executeQuery("select count(*) from SYS.SYSTRIGGERS where CAST(triggername AS VARCHAR(128))='CALL_NON_EXISTENT_PROC1'");
          JDBC.assertFullResultSet(rs, new String[][] {{"0"}});
          assertStatementError("42Y07",s,"create trigger call_proc_in_non_existent_schema no cascade BEFORE insert on t2 for each ROW call non_existent_schema.non_existent_proc()");
          rs = s.executeQuery("select count(*) from SYS.SYSTRIGGERS where CAST(triggername AS VARCHAR(128))='CALL_PROC_IN_NON_EXISTENT_SCHEMA'");
          JDBC.assertFullResultSet(rs, new String[][] {{"0"}});      
          assertStatementError("42X50",s,"create trigger call_proc_using_non_existent_method no cascade BEFORE insert on t2 for each ROW call proc_using_non_existent_method()");
          rs = s.executeQuery("select count(*) from SYS.SYSTRIGGERS where CAST(triggername AS VARCHAR(128))='CALL_PROC_WITH_NON_EXISTENT_METHOD'");
          JDBC.assertFullResultSet(rs, new String[][] {{"0"}});
          //--- triggers must not allow dynamic parameters (?)
          
          assertStatementError("42Y27",s,"create trigger update_trig AFTER insert on t1 for each STATEMENT call proc_modifies_sql_update_op(?)");
          s.execute("insert into t2 values (1,2), (2,4)");
          // --- use procedure with commit
          s.execute("create trigger commit_trig AFTER delete on t2 for each STATEMENT call commit_proc()");
          assertStatementError("38000",s,"delete from t2");
          rs = s.executeQuery("select * from t2");
          JDBC.assertFullResultSet(rs, new String[][] {{"1","2"},{"2","4"}});
          s.execute("drop trigger commit_trig");
          s.execute("create trigger commit_trig no cascade BEFORE delete on t2 for each STATEMENT call commit_proc()");
          // -- should fail
          assertStatementError("38000",s,"delete from t2");
          //--- check delete failed
          rs = s.executeQuery("select * from t2");
          JDBC.assertFullResultSet(rs,new String[][] {{"1","2"}, {"2","4"}});
          s.execute("drop trigger commit_trig");
          //--- use procedure with rollback
          s.execute("create trigger rollback_trig AFTER delete on t2 for each STATEMENT call rollback_proc()");
          assertStatementError("38000",s,"delete from t2");
          //--- check delete failed
          rs = s.executeQuery("select * from t2");
          JDBC.assertFullResultSet(rs, new String[][] {{"1","2"},{"2","4"}});
          s.execute("drop trigger rollback_trig");
          s.execute("create trigger rollback_trig no cascade BEFORE delete on t2 for each STATEMENT call rollback_proc()");
          //--- should fail
          assertStatementError("38000",s,"delete from t2");
          //--- check delete failed
          rs = s.executeQuery("select * from t2");
          JDBC.assertFullResultSet(rs, new String[][] {{"1","2"},{"2","4"}});
          s.execute("drop trigger rollback_trig");
          //--- use procedure which changes isolation level
          s.execute("create trigger set_isolation_trig AFTER delete on t2 for each STATEMENT call set_isolation_proc()");
          assertStatementError("38000",s,"delete from t2");
          //--- check delete failed
          rs = s.executeQuery("select * from t2");
          JDBC.assertFullResultSet(rs, new String[][] {{"1","2"},{"2","4"}});
          s.execute("drop trigger set_isolation_trig");
          s.execute("create trigger set_isolation_trig no cascade BEFORE delete on t2 for each STATEMENT call set_isolation_proc()");
          assertStatementError("38000",s,"delete from t2");
          //--- check delete failed
          rs = s.executeQuery("select * from t2");
          JDBC.assertFullResultSet(rs, new String[][] {{"1","2"},{"2","4"}});
          s.execute("drop trigger set_isolation_trig");
          // --- call procedure that selects from same trigger table
          s.execute("create trigger select_from_trig_table AFTER insert on t1 for each STATEMENT call proc_reads_sql(1)");
          //--- insert 2 rows check that trigger is fired - procedure should be called once
          s.execute("insert into t1 values (10, 'ten')");
          //--- check inserts are successful
          rs = s.executeQuery("select * from t1");
          String [][] expectedRows = { {"5","two"},{"6","four"},{"8","eight"},{"10","ten"}};
          JDBC.assertFullResultSet(rs,expectedRows );
          s.execute("drop trigger select_from_trig_table");
          s.execute("create trigger select_from_trig_table no cascade before delete on t1 for each STATEMENT call proc_reads_sql(1)");
          //--- delete a row. check that trigger is fired - procedure should be called once
          //RESOLVE: How to check
          s.execute("delete from t1 where i=10");
          // --- check delete is successful
          rs = s.executeQuery("select * from t1");
          expectedRows = new String[][] { {"5","two"},{"6","four"},{"8","eight"}};
          JDBC.assertFullResultSet(rs, expectedRows);
          s.execute("drop trigger select_from_trig_table");
          //--- use procedures which alter/drop trigger table and some other table
          s.execute("create trigger alter_table_trig AFTER delete on t1 for each STATEMENT call alter_table_proc()");
          assertStatementError("38000",s,"delete from t1");
          // check delete failed
          rs = s.executeQuery("select * from t1");
          expectedRows = new String[][] { {"5","two"},{"6","four"},{"8","eight"}};
          JDBC.assertFullResultSet(rs, expectedRows);
          s.execute("create trigger drop_table_trig AFTER delete on t2 for each STATEMENT call drop_table_proc()");
          // should fail
         assertStatementError("38000",s,"delete from t2");
         // check delete failed
         rs = s.executeQuery("select * from t2");
         JDBC.assertFullResultSet(rs,new String[][] {{"1","2"}, {"2","4"}});
         s.execute("drop trigger drop_table_trig");
         //--- use procedures which create/drop trigger on trigger table and some other table
         s.execute("create trigger create_trigger_trig AFTER delete on t1 for each STATEMENT call create_trigger_proc()");
         // -- should fail
         assertStatementError("38000",s,"delete from t1");
         //--- check delete failed
         rs = s.executeQuery("select * from t1");
         expectedRows = new String[][] { {"5","two"},{"6","four"},{"8","eight"}};
         JDBC.assertFullResultSet(rs, expectedRows);
         //--- check trigger is not created
         rs = s.executeQuery("select count(*) from SYS.SYSTRIGGERS where  CAST(triggername AS VARCHAR(128))='TEST_TRIG'");
         JDBC.assertFullResultSet(rs, new String[][] {{"0"}});
         s.execute("drop trigger create_trigger_trig");
         //--- create a trigger to test we cannot drop it from a procedure called by a trigger
         s.execute("create trigger test_trig AFTER delete on t1 for each STATEMENT insert into  t1 values(20, 'twenty')");
         s.execute("create trigger drop_trigger_trig AFTER delete on t2 for each STATEMENT call drop_trigger_proc()");
         assertStatementError("38000",s,"delete from t2");
         //--- check delete failed
         rs = s.executeQuery("select * from t2");
         JDBC.assertFullResultSet(rs,new String[][] {{"1","2"}, {"2","4"}});
         //--- check trigger is not dropped
         rs = s.executeQuery("select count(*) from SYS.SYSTRIGGERS where CAST(triggername AS VARCHAR(128))='TEST_TRIG'");
         JDBC.assertFullResultSet(rs, new String[][] {{"1"}});
         s.execute("drop trigger drop_trigger_trig");
         //- use procedures which create/drop index on trigger table and some other table
         s.execute("create trigger create_index_trig AFTER delete on t2 for each STATEMENT call create_index_proc()");
         // -- should fail
         assertStatementError("38000",s,"delete from t2");
         // check delete failed
         rs = s.executeQuery("select * from t2");
         JDBC.assertFullResultSet(rs,new String[][] {{"1","2"}, {"2","4"}});
         // -- check index is not created
         rs = s.executeQuery("select count(*) from SYS.SYSCONGLOMERATES where CAST(CONGLOMERATENAME AS VARCHAR(128))='IX' and ISINDEX");
         JDBC.assertFullResultSet(rs, new String [][] {{"0"}});
         s.execute("drop trigger create_index_trig");
         //--- create an index to test we cannot drop it from a procedure called by a trigger
         s.execute("create index ix on t1(i,b)");
         s.execute("create trigger drop_index_trig AFTER delete on t1 for each STATEMENT call drop_index_proc()");
         assertStatementError("38000",s,"delete from t1");
         // -- check delete failed
         rs = s.executeQuery("select * from t1");
         expectedRows = new String[][] { {"5","two"},{"6","four"},{"8","eight"}};
         JDBC.assertFullResultSet(rs, expectedRows);
         // -- check index is not dropped
         rs = s.executeQuery("select count(*) from SYS.SYSCONGLOMERATES where CAST(CONGLOMERATENAME AS VARCHAR(128))='IX' and ISINDEX");
         JDBC.assertFullResultSet(rs, new String[][] {{"1"}});

         // Clean up objects created by the test case.
         s.execute("drop trigger alter_table_trig");
         s.execute("drop trigger test_trig");
         s.execute("drop trigger drop_index_trig");
         s.execute("drop index ix");

         s.close();
    }
    
    
    
    private static Test basesuite() {
        Test basesuite = new BaseTestSuite(ProcedureInTriggerTest.class);
        Test clean = new CleanDatabaseTestSetup(basesuite) {
        protected void decorateSQL(Statement s) throws SQLException {
            s.execute("create table t1 (i int primary key, b char(15))");
            s.execute("create table t2 (x integer, y integer)");
            s.execute("create procedure proc_no_sql() parameter style java language java NO SQL external name 'org.apache.derbyTesting.functionTests.tests.lang.ProcedureInTriggerTest.zeroArg'");
            s.execute("create procedure proc_contains_sql() parameter style java language java CONTAINS SQL external name 'org.apache.derbyTesting.functionTests.tests.lang.ProcedureInTriggerTest.getConnectionProc'");
            s.execute("create procedure proc_reads_sql(i integer) parameter style java language java READS SQL DATA external name 'org.apache.derbyTesting.functionTests.tests.lang.ProcedureInTriggerTest.selectRows' dynamic result sets 1");
            s.execute("create procedure proc_modifies_sql_insert_op(p1 int, p2 char(10)) parameter style java language java MODIFIES SQL DATA external name 'org.apache.derbyTesting.functionTests.tests.lang.ProcedureInTriggerTest.insertRow'");
            s.execute("create procedure proc_modifies_sql_update_op(p1 int) parameter style java language java  MODIFIES SQL DATA  external name 'org.apache.derbyTesting.functionTests.tests.lang.ProcedureInTriggerTest.updateRow'");
            s.execute("create procedure proc_modifies_sql_delete_op(p1 int) parameter style java language java MODIFIES SQL DATA external name 'org.apache.derbyTesting.functionTests.tests.lang.ProcedureInTriggerTest.deleteRow'");
            s.execute("create procedure alter_table_proc() parameter style java language java external name 'org.apache.derbyTesting.functionTests.tests.lang.ProcedureInTriggerTest.alterTable'");
            s.execute("create procedure drop_table_proc() parameter style java language java external name 'org.apache.derbyTesting.functionTests.tests.lang.ProcedureInTriggerTest.dropTable'");
            s.execute("create procedure commit_proc() parameter style java dynamic result sets 0 language java contains sql external name 'org.apache.derbyTesting.functionTests.tests.lang.ProcedureInTriggerTest.doConnCommit'");
            s.execute("create procedure rollback_proc() parameter style java dynamic result sets 0 language java contains sql external name 'org.apache.derbyTesting.functionTests.tests.lang.ProcedureInTriggerTest.doConnRollback'");
            s.execute("create procedure set_isolation_proc() parameter style java dynamic result sets 0 language java contains sql external name 'org.apache.derbyTesting.functionTests.tests.lang.ProcedureInTriggerTest.doConnectionSetIsolation'");
            s.execute("create procedure create_index_proc() parameter style java dynamic result sets 0 language java  contains sql external name 'org.apache.derbyTesting.functionTests.tests.lang.ProcedureInTriggerTest.createIndex'");
            s.execute("create procedure drop_index_proc() parameter style java dynamic result sets 0 language java contains sql external name 'org.apache.derbyTesting.functionTests.tests.lang.ProcedureInTriggerTest.dropIndex'");
            s.execute("create procedure create_trigger_proc() parameter style java dynamic result sets 0 language java contains sql external name 'org.apache.derbyTesting.functionTests.tests.lang.ProcedureInTriggerTest.createTrigger'");
            s.execute(" create procedure drop_trigger_proc() parameter style java dynamic result sets 0 language java contains sql external name 'org.apache.derbyTesting.functionTests.tests.lang.ProcedureInTriggerTest.dropTrigger'");
            s.execute("create procedure proc_wrongly_defined_as_no_sql(p1 int, p2 char(10)) parameter style java language java NO SQL external name 'org.apache.derbyTesting.functionTests.tests.lang.ProcedureInTriggerTest.insertRow'");
            // create a new schema and put a procedure in it.
            s.execute("create schema new_schema");
            s.execute("create procedure new_schema.proc_in_new_schema() parameter style java language java NO SQL external name 'org.apache.derbyTesting.functionTests.tests.lang.ProcedureInTriggerTest.zeroArg'");
            // procedure which uses non-existant method
            s.execute("create procedure proc_using_non_existent_method() parameter style java language java NO SQL external name 'org.apache.derbyTesting.functionTests.tests.lang.ProcedureInTriggerTest.nonexistentMethod'");
       
            
        }};
        
        return clean;
        }
                
        public static Test suite() { 
            BaseTestSuite suite = new BaseTestSuite();
            if (!JDBC.vmSupportsJSR169()) {
                suite.addTest(basesuite());
                suite.addTest(TestConfiguration.clientServerDecorator(basesuite()));
            }
            return suite;
        }

        /**
         * Tear down the test environment.
         */
        protected void tearDown() throws Exception {
            rollback();

            // Remove all rows in the test tables, so that each test case
            // sees the same initial state.
            Statement stmt = createStatement();
            stmt.execute("truncate table t1");
            stmt.execute("truncate table t2");
            commit();

            // Reset the counters.
            zeroArgCount = 0;
            getConnectionProcCount = 0;
            selectRowsCount = 0;

            super.tearDown();
        }

        private void checkAndResetZeroArgCount(int count) {
            assertEquals(count, zeroArgCount);
            zeroArgCount = 0;
         }      
        
        // PROCEDURES
        private static int zeroArgCount = 0;
        public static void zeroArg() {
            zeroArgCount++;
        }
        
        private static int getConnectionProcCount = 0;
        
        private void checkAndResetGetConnectionProcCount(int count) {
            assertEquals(count, getConnectionProcCount);
            getConnectionProcCount = 0;
         }
        
        public static void getConnectionProc() throws Throwable
        {
                Connection conn = DriverManager.getConnection("jdbc:default:connection");
                Statement s = conn.createStatement();
                conn.close();
                getConnectionProcCount++;
        }
        
        private static int selectRowsCount = 0;
        
        private void checkAndResetSelectRowsCount(int count) {
            assertEquals(count, selectRowsCount);
            selectRowsCount = 0;
         }
        
        public static void selectRows(int p1, ResultSet[] data) throws SQLException {

                

                Connection conn = DriverManager.getConnection("jdbc:default:connection");
                PreparedStatement ps = conn.prepareStatement("select * from t1 where i = ?");
                ps.setInt(1, p1);
                data[0] = ps.executeQuery();
                conn.close();
                selectRowsCount++;
        }
        
        public static void selectRows(int p1, int p2, ResultSet[] data1, ResultSet[] data2) throws SQLException {

                

                Connection conn = DriverManager.getConnection("jdbc:default:connection");
                PreparedStatement ps = conn.prepareStatement("select * from t1 where i = ?");
                ps.setInt(1, p1);
                data1[0] = ps.executeQuery();

                ps = conn.prepareStatement("select * from t1 where i >= ?");
                ps.setInt(1, p2);
                data2[0] = ps.executeQuery();

                if (p2 == 99)
                        data2[0].close();

                // return no results
                if (p2 == 199) {
                        data1[0].close();
                        data1[0] = null;
                        data2[0].close();
                        data2[0] = null;
                }

                // swap results
                if (p2 == 299) {
                        ResultSet rs = data1[0];
                        data1[0] = data2[0];
                        data2[0] = rs;
                }

                conn.close();
                selectRowsCount++;
        }

        // select all rows from a table
        public static void selectRows(String table, ResultSet[] rs)
                throws SQLException
        {
                Connection conn = DriverManager.getConnection("jdbc:default:connection");
                Statement stmt = conn.createStatement();
                rs[0] = stmt.executeQuery("SELECT * FROM " + table);
                conn.close();
                selectRowsCount++;
        }


        public static void insertRow(int p1) throws SQLException {
                insertRow(p1, "int");
        }

        public static void insertRow(int p1, String p2) throws SQLException {
                Connection conn = DriverManager.getConnection("jdbc:default:connection");
                PreparedStatement ps = conn.prepareStatement("insert into t1 values (?, ?)");
                ps.setInt(1, p1);
                ps.setString(2, p2);
                ps.executeUpdate();
                ps.close();
                conn.close();
        }
        
        public static void updateRow(int p1) throws SQLException {
                Connection conn = DriverManager.getConnection("jdbc:default:connection");
                PreparedStatement ps = conn.prepareStatement("update t1 set i=i+?");
                ps.setInt(1, p1);
                ps.executeUpdate();
                ps.close();
                conn.close();
        }
        
        public static void deleteRow(int p1) throws SQLException {
                Connection conn = DriverManager.getConnection("jdbc:default:connection");
                PreparedStatement ps = conn.prepareStatement("delete from t1 where i=?");
                ps.setInt(1, p1);
                ps.executeUpdate();
                ps.close();
                conn.close();
        }

        public static void alterTable() throws SQLException {
                Connection conn = DriverManager.getConnection("jdbc:default:connection");
                PreparedStatement ps = conn.prepareStatement("alter table t1 add column test integer");
                ps.execute();
                ps.close();
                conn.close();
        }

        public static void dropTable() throws SQLException {
                Connection conn = DriverManager.getConnection("jdbc:default:connection");
                PreparedStatement ps = conn.prepareStatement("drop table t1");
                ps.execute();
                ps.close();
                conn.close();
        }               
        public static int doConnCommitInt() throws Throwable
        {
                Connection conn = DriverManager.getConnection("jdbc:default:connection");
                conn.commit();
                return 1;
        }

        public static void doConnCommit() throws Throwable
        {
                Connection conn = DriverManager.getConnection("jdbc:default:connection");
                conn.commit();
        }        

        public static void doConnRollback() throws Throwable
        {
                Connection conn = DriverManager.getConnection("jdbc:default:connection");
                conn.rollback();
        }

        public static void doConnectionSetIsolation() throws Throwable
        {
                Connection conn = DriverManager.getConnection("jdbc:default:connection");
                conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
        }
        
        public static void createIndex() throws SQLException {
                Connection conn = DriverManager.getConnection("jdbc:default:connection");
                PreparedStatement ps = conn.prepareStatement("create index ix on t1(i,b)");
                ps.execute();
                ps.close();
                conn.close();
        }

        public static void dropIndex() throws SQLException {
                Connection conn = DriverManager.getConnection("jdbc:default:connection");
                PreparedStatement ps = conn.prepareStatement("drop index ix");
                ps.execute();
                ps.close();
                conn.close();
        }
        
        public static void createTrigger() throws SQLException {
                Connection conn = DriverManager.getConnection("jdbc:default:connection");
                PreparedStatement ps = conn.prepareStatement("create trigger test_trig" +
                                " AFTER delete on t1 for each STATEMENT insert into" +
                                " t1 values(20, 'twenty')");
                ps.execute();
                ps.close();
                conn.close();
        }
        

        public static void dropTrigger() throws SQLException {
                Connection conn = DriverManager.getConnection("jdbc:default:connection");
                PreparedStatement ps = conn.prepareStatement("drop trigger test_trig");
                ps.execute();
                ps.close();
                conn.close();
        }
        
}