//*********************************************************************
//
//                 Copyright (c) 20011 by Teradata Corporation
//                         All Rights Reserved
//
//*********************************************************************
//
//  File:       T22101JD.java
//  Header:     none
//  Purpose:    Demonstrate the creation of a multi-dimensional array and
//              an SQL stored procedure with the array as a parameter.
//              The program will:
//                -  Connect as user guest/please
//                -  Drop procedure if it exists
//                -  Drop array type if it exists
//                -  Create IntArray as VARARRAY(4)(3) of INTEGER
//                -  Create a procedure with the following parameters:
//                       IN    p1 IntArray
//                       INOUT p2 IntArray
//                       OUT   p3 IntArray
//                -  Disconnect.
//
//  JDBC API: java.sql.Connection, java.sql.Statement,
//            java.sql.Statement.executeUpdate
//
//  Version: Updated for Teradata Database 14.0
//
//*********************************************************************

import java.sql.*;

public class T22101JD
{
    // Name of the user able to create, drop, and manipulate tables
    public static String sUser = "guest";
    public static String sPassword = "please";

    public static void main(String args[])
    throws ClassNotFoundException
    {
        // Creation of URL to be passed to the JDBC driver
        String url = "jdbc:teradata://whomooz/TMODE=ANSI,CHARSET=UTF8";

        // Statements used in array creation
        String sDropArray = "DROP TYPE IntArray" ;
        String sCreateArray =
            "CREATE TYPE IntArray AS VARRAY (4) (3) OF INTEGER" ;

        // Statements used in SQL Stored Procedure creation
        String sDropProc = "DROP PROCEDURE IntArraySP";
        String sCreateProc =
            "REPLACE PROCEDURE intArraySP "
            + "( IN    p1  IntArray "
            + " ,INOUT p2  IntArray "
            + " ,OUT   p3  IntArray) "
            +"BEGIN "
            + " SET  p3 =  p2 ; "
            + " SET  p2 =  p1 ; "
            +"END ; " ;


        try
        {
            System.out.println ("\n Sample T22101JD: \n") ;
            System.out.println (" Looking for the Teradata JDBC driver... ") ;
            // Loading 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
            {
                // Creating a statement object from an active connection.
                Statement stmt = con.createStatement () ;
                System.out.println (" Statement object created. \n") ;

                try
                {
                    // Cleanup procedure:
                    // If the sample procedure already exists, drop it.
                    // It needs to be dropped before the array type to
                    // eliminate array dependencies
                    dropObject (con, sDropProc) ;


                    // Cleanup procedure:
                    // If the array type already exists, drop it.
                    dropObject (con, sDropArray) ;

                    // Create the sample array
                    System.out.println (" Creating array: " + sCreateArray) ;
                    stmt.executeUpdate (sCreateArray) ;
                    System.out.println (" Sample array created. \n") ;

                    // Create the sample SQL stored procedure
                    System.out.println (" Replacing procedure: " + sCreateProc) ;
                    stmt.executeUpdate (sCreateProc) ;
                    System.out.println (" Sample procedure created. \n") ;
                }
                finally
                {
                    // Close the statement
                    stmt.close();
                    System.out.println("\n Statement object closed. \n");
                }
            }
            finally
            {
                // Close the connection
                System.out.println(" Closing connection to Teradata...");
                con.close();
                System.out.println(" Connection to Teradata closed. \n");
            }

            System.out.println(" Sample T22101JD 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

    public static void dropObject(Connection con, String dropQuery)
    {
        try
        {
            // The following code will be used to perform a DROP query
            Statement stmt = con.createStatement();
            System.out.println(" Executing command "+dropQuery);
            stmt.executeUpdate(dropQuery);
            System.out.println(" Command executed successfully.");
        }
        catch (SQLException ex)
        {
            // If the Object did not exist, no drop is required.
            // Ignore the raised "no object present" exception by
            // printing out the error message and swallowing the
            // exception.
            System.out.println(" Ignoring exception: " + ex);
        }
    } // End dropObject

} // End class T22101JD