//*********************************************************************
//
//                 Copyright (c) 2009 by Teradata Corporation
//                         All Rights Reserved
//
//*********************************************************************
//
//  File:       T22005JD.java
//  Header:     none
//  Purpose:    Demonstrate basic Teradata SQL using the JDBC API by
//              creating a table with a nested User Defined Type (UDT),
//              and inserting & selecting data from the table.
//              The program will:
//                -  Connect as user guest/please
//                -  Create a table with the Student UDT which contains a
//                   nested Address UDT. (Student is created using sample
//                   T22001JD and Address is created using T22000JD)
//                -  Create data using class SampStruct defined at the
//                   end of this file
//                -  Insert data into the table
//                -  Select and print the data from the table
//                -  Disconnect
//
//  JDBC API: java.sql.Connection, java.sql.Statement,
//            java.sql.Statement.executeUpdate,
//
//  Version: Created for Teradata Database 13.10
//
//*********************************************************************

import java.sql.*;
import java.io.*;

public class T22005JD
{
    // 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";

        try
        {
            System.out.println("\n Sample T22005JD: \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);
            try
            {
                System.out.println(" User " + sUser + " connected.");
                System.out.println(" Connection to Teradata established. \n");
                // Statements used in table creation
                String sUDTName    = "Student";
                String sTableName  = "StudentTbl";
                String createTable =
                    "CREATE TABLE "+sTableName+
                    " ( studentId   INTEGER, "+
                    "   student    "+sUDTName+" )";

                    String dropTable = "DROP TABLE " + sTableName ;

                    String insertStmt =
                        " INSERT INTO "+sTableName+"( ?, ?)";

                    String selectStmt = " SELECT * FROM "+sTableName+" ORDER BY 2";

                    // Define the data to be inserted into the table. We need to
                    // create two new SampStructs for both Student and Address.
                    Object[][] data = {
                    {"120", new SampStruct("Student", new Object[] {"Will", "Jones",
                        new SampStruct("Address", new Object[] {
                        "123 Main Street", "My City", "NY", "00245"})})},

                    {"130", new SampStruct("Student", new Object[] {"Joe", "Black",
                        new SampStruct("Address", new Object[] {
                        "487 Elm Avenue", "Your City", "AZ", "30246"})})},

                    {"140", new SampStruct("Student", new Object[] {"Joe", "Black",
                        new SampStruct("Address", new Object[] {
                        "287 Elm Avenue", "Your City", "AZ", "30246"})})},

                    {"150", new SampStruct("Student", new Object[] {"Alice", "Frong",
                        new SampStruct("Address", new Object[] {
                        "1050 Oak Street", "Big City", "CA", "90124"})})},

                    {"160", new SampStruct("Student", new Object[] {"Fred", "Join",
                        new SampStruct("Address", new Object[] {
                        "8132 First Street", "Small Town", "CO", "80124"})})}
                    };

                    // Create a statement object from an active connection.
                    Statement stmt = con.createStatement();
                    try
                    {
                        // If the sample table already exists, drop it.
                        dropObject(con, dropTable);
                        // Create the sample table
                        System.out.println(" Creating the table...");
                        stmt.executeUpdate(createTable);
                        try
                        {
                            // Creating a prepared statement object
                            System.out.println(" Preparing: " + insertStmt);
                            PreparedStatement pStmt =
                                con.prepareStatement(insertStmt);
                            try
                            {
                                // Call the following method to insert rows into the
                                // sample table
                                insertRows(con, pStmt, data);

                                // The following code will perform a SELECT query
                                // on the table.
                                stmt = con.createStatement();
                                System.out.println(" Selecting from table "
                                    + sTableName);
                                // Submit a query, creating a result set object
                                ResultSet rs = stmt.executeQuery(selectStmt);

                                // Extract and display result set table data
                                System.out.println(
                                    "\n Displaying the result set returned by the"+
                                    " select to "+sTableName+" : ");
                                System.out.println("\n -------------------------");
                                // iterate through all returned rows and display them
                                while (rs.next())
                                {
                                    // retrieve the student struct from the second
                                    // column
                                    Struct student = (Struct) rs.getObject(2);
                                    // retrieve the student struct attributes
                                    Object[] studentAttributes
                                        = student.getAttributes();

                                    // retrieve the address struct from the 3rd
                                    // student attribute
                                    Struct address = (Struct) studentAttributes[2];
                                    // retrieve the address struct attributes
                                    Object[] addressAttributes
                                        = address.getAttributes();

                                    // retrieve and display first column value
                                    System.out.println("\n  Student Id     : "
                                        + rs.getObject(1));
                                    // display first and last name from student
                                    // attribute values
                                    System.out.println("\n  Student Name   : "
                                        + studentAttributes[0]
                                        + ", " + studentAttributes[1]);
                                    // display student's address attributes values
                                    System.out.println("\n  Student Address: "
                                        + addressAttributes[0]
                                        +", "+addressAttributes[1]
                                        +", "+ addressAttributes[2]
                                        +" "+addressAttributes[3]);
                                    System.out.println(" -------------------------");
                                } // End while
                            }
                            finally
                            {
                                // close the PreparedStatement
                                pStmt.close();
                                System.out.println(
                                    "\n PreparedStatement object closed.\n");
                            }
                        }
                        catch (SQLException ex)
                        {
                            // if an exception is thrown, drop the sample table
                            dropObject(con, dropTable);
                            throw ex;
                        }
                    }
                    finally
                    {
                        // close the statement
                        stmt.close();
                        System.out.println("\n Statement object closed.\n");
                    }
                }
                finally
                {
                    //Close the connection
                    System.out.println(" Closing Connection");
                    con.close();
                    System.out.println(" Sample T22005JD 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 insertRows(Connection con, PreparedStatement pStmt,
            Object[][] data) throws SQLException
        {
            // The following code will perform an INSERT query
            // on the sample table.
            System.out.println(" Inserting Rows");
            for (int i = 0; i < data.length; i++)
            {
                for (int x = 0; x < data[0].length; x++)
                {
                    // Set parameter values indicated by ?
                    pStmt.setObject(x + 1, data[i][x]);
                }
                // Add a row to the batch of insert statements
                pStmt.addBatch();
            }
            // Insert all the rows into the sample table
            pStmt.executeBatch();
        } // End insertRows

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

    // This class is used instead of calling Connection.createStruct, which is
    // only supported in JDK 6.0 or later
    public static class SampStruct implements Struct
    {
        private Object[] m_attributes;
        private String m_sqlTypeName;

        public SampStruct(String sqlTypeName, Object[] attributes)
        {
            m_sqlTypeName = sqlTypeName;
            m_attributes = attributes;
        }

        // Returns attributes
        public Object[] getAttributes() throws SQLException
        {
            return m_attributes;
        }

        // Returns SQLTypeName
        public String getSQLTypeName() throws SQLException
        {
            return m_sqlTypeName;
        }

        // This method is not supported, but needs to be included
        public Object[] getAttributes(java.util.Map map) throws SQLException
        {
            //Unsupported Exception
            throw new SQLException("getAttributes(Map) NOT SUPPORTED");
        }
    } // End of class SampStruct
} //End T22005JD