//*********************************************************************
//
//                 Copyright (c) 2004-2016 by Teradata Corporation
//                         All Rights Reserved
//
//*********************************************************************
//
//  File:       T29900JD.java
//  Header:     none
//  Purpose:    Clean up the example environment
//              The program will:
//                -  Connect as user guest/please
//                -  Delete all stored procedures
//                -  Delete all macros
//                -  Delete all indexes
//                -  Delete all data from all tables
//                -  Delete all tables
//                -  Delete all UDTs
//                -  Disconnect.
//
//  JDBC API: java.sql.Connection, java.sql.Statement,
//            java.sql.Statement.executeUpdate
//
//  Version: Updated for Teradata Database 14.10
//
//*********************************************************************

import java.sql.*;

public class T29900JD
{
    public static void main (String args[])
    throws ClassNotFoundException
    {
        try
        {
            String url = "jdbc:teradata://whomooz/TMODE=ANSI,CHARSET=UTF8";
            String sUser = "guest";
            String sPassword = "please";

            String [] dropCommands = {
                "DROP PROCEDURE getEmpInfo",
                "DROP PROCEDURE getDeptJobInfo",
                "DROP PROCEDURE GetLastNameXSP",
                "DROP PROCEDURE getBookInfoByAuthor",
                "DROP PROCEDURE getBookInfoByTitleAndPublisher",
                "DROP PROCEDURE intArraySP",
                "DROP FUNCTION Judf01" ,
                "DROP FUNCTION FindRegEx",
                "DROP MACRO empUpdate",
                "DROP MACRO empDisplayVarious",
                "DROP MACRO empRestoreDisplay",
                "DROP INDEX (empName) ON employee",
                "DROP INDEX (empName, empDept) ON employee",
                "DROP INDEX (empName) ON employee2",
                "DROP INDEX (empName, empDept) ON employee2",
                "DROP TABLE employee",
                "DROP TABLE employee2",
                "DROP TABLE conversions",
                "DROP TABLE escape_clauses",
                "DROP TABLE escape_join",
                "DROP TABLE books",
                "DROP TABLE JdbcLobUpdate",
                "DROP TABLE EmployeeTbl",
                "DROP TABLE StudentTbl",
                "DROP TABLE PerTypes",
                "DROP TABLE EmpPhoneNumbers",
                "DROP TABLE SampleBooks",
                "DROP TABLE SampleSchools",
                "DROP TABLE Events",
                "DROP TABLE Tasks",
                "DROP TABLE MyJSONTable",
                "DROP TRANSFORM StudentIO FOR Student",
                "DROP ORDERING FOR Student",
                "DROP FUNCTION SYSUDTLIB.StudentToSql",
                "DROP TYPE Student",
                "DROP TRANSFORM AddressIO FOR Address",
                "DROP ORDERING FOR Address",
                "DROP FUNCTION SYSUDTLIB.AddressToSql",
                "DROP TYPE Address",
                "DROP TYPE PhoneNumbersArray",
                "DROP TYPE IntArray",
                "DROP TABLE myAvroTable",
                "DROP TABLE myCSVTable",
                "DROP SCHEMA myCSVSchema"
            } ;

            String removeJar = "{call sqlj.remove_jar(?,0)}";
            String [] jarNames = {"SampleJXSP", "SampleJavaUDF"};

            System.out.println("\n Sample T29900JD: \n");

            // Loading the Teradata JDBC driver
            System.out.println(" Looking for the Teradata JDBC driver... ");
            Class.forName("com.teradata.jdbc.TeraDriver");
            System.out.println(" JDBC driver loaded. \n");

            // Attempting to connect to Teradata
            System.out.println(
                " Attempting to connect to Teradata via the JDBC driver...");

            // Creating a connection object
            Connection con = DriverManager.getConnection(url, sUser, sPassword);
            System.out.println(" User " + sUser + " connected.");
            System.out.println(" Connection to Teradata established. \n");

            try
            {
                Statement stmt = con.createStatement();
                System.out.println(" Statement object created. \n");

                try
                {
                    // The following code will perform cleanup of all database objects
                    // created during the learning examples.

                    System.out.println(
                        " Cleaning up all created database objects.\n");

                    for (int i = 0 ; i < dropCommands.length ; i++)
                    {
                        System.out.println(
                            "\n Executing the following SQL statement: "
                            + dropCommands [i]);
                        try
                        {
                            stmt.executeUpdate(dropCommands [i]);
                            System.out.println(" Command executed successfully.");
                        }
                        catch (SQLException ex)
                        {
                            System.out.println(" Ignoring exception: " + ex);
                        }
                    } // end for
                }
                finally
                {
                    // Close the statement
                    stmt.close();
                    System.out.println("\n Statement object closed.");
                }

                System.out.println(
                        "\n Preparing the following SQL statement: "
                        + removeJar);

                CallableStatement cs = con.prepareCall(removeJar);
                System.out.println(" CallableStatement object created.");

                try
                {
                    for (int i = 0 ; i < jarNames.length ; i++)
                    {
                        System.out.println("\n Removing the following jar file: "
                            + jarNames [i]);

                        cs.setString(1, jarNames [i]);

                        try
                        {
                            cs.executeUpdate();
                            System.out.println(" Command executed successfully.");
                        }
                        catch (SQLException ex)
                        {
                            System.out.println(" Ignoring exception: " + ex);
                        }
                    } // end for
                }
                finally
                {
                    cs.close();
                    System.out.println(" CallableStatement object closed.");
                }

                System.out.println("\n Cleanup complete.");
            }
            finally
            {
                // Close the connection
                System.out.println("\n Closing connection to Teradata...");
                con.close();
                System.out.println(" Connection to Teradata closed.");
            }

            System.out.println("\n Sample T29900JD finished. \n");
        }
        catch (SQLException ex)
        {
            // A SQLException was generated.  Catch it and display
            // the error information.
            // Note that there could be multiple error objects chained
            // together.
            System.out.println();
            System.out.println("*** SQLException caught ***");

            while (ex != null)
            {
                System.out.println(" Error code: " + ex.getErrorCode());
                System.out.println(" SQL State: " + ex.getSQLState());
                System.out.println(" Message: " + ex.getMessage());
                ex.printStackTrace();
                System.out.println();
                ex = ex.getNextException();
            }

            throw new IllegalStateException ("Sample failed.") ;
        }
    } // End main
} // End class T29900JD