//*********************************************************************
//
//                 Copyright (c) 2009 by Teradata Corporation
//                         All Rights Reserved
//
//*********************************************************************
//
//  File:       T22001JD.java
//  Header:     none
//  Purpose:    Demonstrate basic Teradata SQL using the JDBC API by
//              creating a nested User Defined Type (UDT).
//              The program will:
//                -  Connect as user guest/please
//                -  Create a student type which includes an address type
//                -  Create the associated methods and functions using
//                   the files: StudentConstructor.c, StudentOrdering.c,
//                   StudentFromSql.c and StudentToSql.c
//                -  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 T22001JD
{
    // Name of the user able to create, drop, and manipulate tables
    public static String sUser = "guest";
    public static String sPassword = "please";
    public static int COMPILE_WARNINGS =  5607;
    public static int REFERENCED_BY_OBJECT = 6839;

    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";

        boolean bPassed = true;
        try {
            System.out.println("\n Sample T22001JD: \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 {
                // Commands associated with the creation of the sample UDT
                // and it's associated methods and functions
                String sUDTName = "Student";
                String sFromSqlMethodName = "StudentFromSql";
                String sToSqlFunctionName = "StudentToSql" ;
                String sTransformGroupName = "StudentIO";
                String sOrderingMethodName = "StudentOrdering";
                String dropUDT = "DROP TYPE " + sUDTName;
                String createUDT = "CREATE TYPE " + sUDTName +
                    " AS ("+
                    " FirstName       VARCHAR(50) CHARACTER SET LATIN , " +
                    " LastName        VARCHAR(50) CHARACTER SET LATIN ,  " +
                    " StudentAddress  Address)  " +
                    " NOT FINAL " +

                    " CONSTRUCTOR METHOD " + sUDTName +
                    "( FirstName       VARCHAR(50) CHARACTER SET LATIN , " +
                    "  LastName        VARCHAR(50) CHARACTER SET LATIN ,  " +
                    "  StudentAddress  Address)  " +
                    " RETURNS " + sUDTName +
                    " Specific " + sUDTName + "Constructor " +
                    " SELF AS RESULT " +
                    " NO SQL " +
                    " PARAMETER STYLE TD_GENERAL " +
                    " DETERMINISTIC " +
                    " LANGUAGE C " +
                    " RETURNS NULL ON NULL INPUT, " +

                    " INSTANCE METHOD " + sFromSqlMethodName + "() " +
                    " RETURNS VARCHAR(155) " +
                    " SPECIFIC " + sFromSqlMethodName +
                    " NO SQL " +
                    " PARAMETER STYLE TD_GENERAL " +
                    " DETERMINISTIC " +
                    " LANGUAGE C " +
                    " RETURNS NULL ON NULL INPUT, " +

                    " INSTANCE METHOD " + sOrderingMethodName + "() " +
                    " RETURNS VARCHAR(150) " +
                    " SPECIFIC " + sOrderingMethodName +
                    " NO SQL " +
                    " PARAMETER STYLE TD_GENERAL " +
                    " DETERMINISTIC " +
                    " LANGUAGE C " +
                    " RETURNS NULL ON NULL INPUT";

                String createConstructorMethod =
                    "CREATE CONSTRUCTOR METHOD " + sUDTName +
                    "( FirstName       VARCHAR(50) CHARACTER SET LATIN , " +
                    "  LastName        VARCHAR(50) CHARACTER SET LATIN ,  " +
                    "  StudentAddress  Address)  " +
                    " RETURNS " + sUDTName +
                    " FOR " + sUDTName +
                    " EXTERNAL NAME" +
                " 'CS!StudentConstructor!StudentConstructor.c!F!StudentConstructor'";

                String createFromSqlMethod =
                    " CREATE METHOD " + sFromSqlMethodName + "() " +
                    " RETURNS VARCHAR (155) " +
                    " FOR " + sUDTName +
                    " EXTERNAL NAME" +
                    " 'CS!StudentFromSql!StudentFromSql.c!F!StudentFromSql'";

                String createToSqlFunction =
                    " CREATE FUNCTION SYSUDTLIB." + sToSqlFunctionName +
                    "( p1 VARCHAR(150) ) " +
                    " RETURNS " + sUDTName +
                    " NO SQL " +
                    " PARAMETER STYLE TD_GENERAL " +
                    " DETERMINISTIC " +
                    " LANGUAGE C " +
                    " EXTERNAL NAME" +
                    " 'CS!StudentToSql!StudentToSql.c!F!StudentToSql'";

                String dropToSqlFunction =
                    " DROP FUNCTION SYSUDTLIB." + sToSqlFunctionName;

                String createTransform =
                    " CREATE TRANSFORM FOR " + sUDTName +
                    " " + sTransformGroupName +
                    " (TO SQL WITH SPECIFIC FUNCTION SYSUDTLIB." +
                    sToSqlFunctionName + ", " +
                    " FROM SQL WITH SPECIFIC METHOD SYSUDTLIB." +
                    sFromSqlMethodName + ")";

                String dropTransform =
                    " DROP TRANSFORM " + sTransformGroupName + " FOR " +
                    sUDTName;

                String createOrderMethod =
                    " CREATE METHOD " + sOrderingMethodName + "() " +
                    " RETURNS VARCHAR(150) " +
                    " FOR " + sUDTName +
                    " EXTERNAL NAME" +
                    " 'CS!StudentOrdering!StudentOrdering.c!F!StudentOrdering'";

                String createOrdering =
                    " CREATE ORDERING FOR " + sUDTName +
                    " ORDER FULL BY MAP WITH SPECIFIC METHOD " +
                    sOrderingMethodName + "";

                String dropOrdering =
                    " DROP ORDERING FOR " + sUDTName;


                // Create a statement object from an active connection.
                Statement stmt = con.createStatement();
                try
                {
                    // call the method to drop the UDT with all it's
                    // associated methods and functions
                    dropUDT(con, dropUDT, dropToSqlFunction,
                            dropTransform, dropOrdering);

                    // Create the UDT - before the UDT can be used in an
                    // object, it must have certain methods & functions
                    // created as well.
                    System.out.println("\n Creating Type "+sUDTName+"...");
                    ResultSet rs = stmt.executeQuery(createUDT);
                    try
                    {
                        dispResultSet(rs, stmt.getWarnings());
                        System.out.println(
                            " Type "+sUDTName+" created successfully.");

                        // Create the constructor method. Structured types support
                        // a special kind of method called a Constructor method.
                        // There is a default constructor method, but one can also
                        // be writen to initialize data attributes and perform
                        // other tasks after the UDT is instantiated.
                        System.out.println("\n Creating Constructor Method");
                        //This method is dropped when type Student is dropped
                        rs = stmt.executeQuery(createConstructorMethod);
                        dispResultSet(rs, stmt.getWarnings());
                        System.out.println(
                            " Constructor Method created successfully.");

                        // Create the method From-SQL. This is a transform routine,
                        // which determines how the referenced UDT column will be
                        // represented outside of the Teradata Database and what
                        // values will be returned in response to queries that
                        // reference the UDT.
                        System.out.println("\n Creating From-SQL Method...");
                        //This method is dropped when type Student is dropped
                        rs = stmt.executeQuery(createFromSqlMethod);
                        dispResultSet(rs, stmt.getWarnings());
                        System.out.println(" From-SQL Method created successfully.");

                        // Create the function To-SQL. The UDF is implicitly invoked
                        // for IMPORT operations. It maps a predefined client type
                        // to a UDT. The UDF transform routine can only have one
                        // declared parameter, which must be a predefined type.
                        // The result data type must be a UDT.
                        System.out.println("\n Creating To-SQL Function...");
                        rs = stmt.executeQuery(createToSqlFunction);
                        dispResultSet(rs, stmt.getWarnings());
                        try
                        {
                            System.out.println(
                                " To-SQL Function created successfully.");

                            // Once the transform routines are compiled and debugged,
                            // the transform group must be associated with the UDT
                            // and registered in the Teradata Database dictionary.
                            System.out.println("\n Register the Transform Group...");
                            stmt.executeUpdate(createTransform);
                            try
                            {
                                System.out.println(" The Transform Group has been"+
                                                   " registered successfully.");
                                // The last of the required UDT constructs is the
                                // ordering routine. An ordering specifies how UDTs
                                // are relationally compared, sorted or grouped with
                                // other UDTs. Orderings do not specify what can be
                                // compared so much as what value is compared.
                                System.out.println("\n Creating Ordering Method...");
                                //This method is dropped when type Student is dropped
                                rs = stmt.executeQuery(createOrderMethod);
                                dispResultSet(rs, stmt.getWarnings());
                                System.out.println(
                                    " Ordering Method created successfully.");

                                // Once the ordering routine is compiled and debugged,
                                // it too must be associated with the UDT and
                                // registered in the Teradata Database dictionary.
                                System.out.println("\n Register Ordering Method...");
                                stmt.executeUpdate(createOrdering);
                                System.out.println(
                                    " Ordering Method registered successfully.");
                            }
                            catch (SQLException se)
                            {
                                // if an exception is thrown, call the method to
                                // unregister the Transform Group
                                dropObject(con, dropTransform);
                                throw se;
                            }
                        }
                        catch (SQLException se)
                        {
                            // if an exception is thrown, call the method to drop
                            // the To-SQL Function
                            dropObject(con, dropToSqlFunction);
                            throw se;
                        }
                    }
                    catch (SQLException se)
                    {
                        // if an exception is thrown, call the method to drop the UDT
                        dropObject(con, dropUDT);
                        throw se;
                    }
                }
                finally
                {
                    // close the statement
                    stmt.close();
                }
            }
            finally
            {
                // close the connection
                System.out.println("\n Closing Connection");
                con.close();
                System.out.println(" Sample T22001JD 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

    // Drops the UDT with it's associated methods and functions. They must be
    // dropped in a specific order.
    public static void dropUDT(Connection con, String dropUDT,
        String dropToSqlFunction, String dropTransform, String dropOrdering)
    throws SQLException
    {
        dropObject(con, dropTransform);
        dropObject(con, dropOrdering);
        dropObject(con, dropToSqlFunction);
        dropObject(con, dropUDT);
    } // end dropUDT

    // Drops an object and ignores any exceptions thrown
    public static void dropObject(Connection con, String dropQuery)
    throws SQLException
    {
        try
        {
            Statement stmt = con.createStatement();
            System.out.println("\n Executing command "+dropQuery+"...");
            stmt.executeUpdate(dropQuery);
            System.out.println(" Command executed successfully.");
        }
        catch (SQLException ex)
        {
            // If it is referenced by another object, then throw
            // the exception because the object cannot be dropped.
            if (ex.getErrorCode() == REFERENCED_BY_OBJECT) throw 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

    private static void dispResultSet (ResultSet rs, SQLWarning warnings)
    throws SQLException
    {
        if (warnings == null ) return;
        // For this sample, we will not display the warnings only errors.
        // However, just remove the 'if' statement, if you want to see the warnings.
        if (warnings.getErrorCode() == COMPILE_WARNINGS) return;
        System.out.println(" Warning Code: "+warnings.getErrorCode()
                           +" : "+warnings.getMessage());

        ResultSetMetaData rsmd = rs.getMetaData () ;
        int numCols = rsmd.getColumnCount () ;
        for (int nRow = 1 ; rs.next () ; nRow++)
        {
            for (int i = 1 ; i <= numCols ; i++)
            {
                if (i == 1)
                    System.out.print (" Row " + nRow + ": ") ;
                else
                    System.out.print (", ") ;

                String s = rs.getString (i) ;
                if (s != null)
                    s = s.replaceAll ("\r\n", "\n")
                        .replaceAll ("\r", "\n")
                        .replaceAll ("\n$", "")
                        .replaceAll ("\n", "\n        ") ;

                System.out.print (s) ;
            }
            System.out.println () ;
        }
    } // end dispResultSet
} // end class T22001JD