//*********************************************************************
//
//                 Copyright (c) 20011 by Teradata Corporation
//                         All Rights Reserved
//
//*********************************************************************
//
//  File:       T22100JD.java
//  Header:     none
//  Purpose:    Demonstrate the creation of a single dimensional array and
//              a table containing a column with this array type.
//              The program will:
//                -  Connect as user guest/please
//                -  Drop table array table if it exists
//                -  Drop array data type if it exists
//                -  Create PhoneNumbersArray as VARARRAY(5) of CHAR(10)
//                -  Create a table with the following columns:
//                       id (INTEGER),
//                       phonenumbers_array (PhoneNumbersArray),
//                -  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 T22100JD
{
    // 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 PhoneNumbersArray" ;
        String sCreateArray =
            "CREATE TYPE PhoneNumbersArray AS VARRAY (5) OF CHAR (10)" ;

        // Statements used in table creation
        String sDropTbl = "DROP TABLE empPhoneNumbers";
        String sCreateTbl =
            "CREATE TABLE empPhoneNumbers "
                +"( ID INTEGER NOT NULL "
                +", PhoneNumbers PhoneNumbersArray NOT NULL "
                +")" ;


        try
        {
            System.out.println ("\n Sample T22100JD: \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 table already exists, drop it.
                    // It needs to be dropped before the array type to
                    // eliminate array dependencies
                    dropObject (con, sDropTbl) ;


                    // 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 table
                    System.out.println (" Creating table: " + sCreateTbl) ;
                    stmt.executeUpdate (sCreateTbl) ;
                    System.out.println (" Sample table 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 T22100JD 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 table or type did not exist, no drop is required.
            // Ignore the raised "no table/type present" exception
            // by printing out the error message and swallowing the
            // exception.
            System.out.println(" Ignoring exception: " + ex);
        }
    } // End dropObject

} // End class T22100JD