//*********************************************************************
//
//                 Copyright (c) 2006-2008 by Teradata Corporation
//                         All Rights Reserved
//
//*********************************************************************
//
//  File:       T20912JD.java
//  Header:     none
//  Purpose:    Demonstrate obtaining the user-defined types (UDTs)
//              defined in a particular schema.
//              The program will:
//                -  Connect as user guest/please
//                -  Obtain and display names of optimal set of row-
//                   identifying columns in a table in the target database
//                -  Disconnect.
//
//  JDBC API: java.sql.Connection, java.sql.DatabaseMetaData,
//            java.sql.DatabaseMetaData.getUTDs
//
//  Version: Updated for Teradata V2R6.1
//
//*********************************************************************

import java.sql.*;

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

        // Type name for retrieving optimal set of row-identifying columns
        String typeName = "udt_datatype";
        // Java SQL Type array for retrieving optimal set of row-identifying columns
        int types[] = {Types.DISTINCT, Types.STRUCT};

        try
        {
            System.out.println("\n Sample T20912JD: \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 DatabaseMetaData object from an active
                // connection.
                DatabaseMetaData dbmd = con.getMetaData();
                System.out.println(" DatabaseMetaData object created. \n");

                // The following code obtains the names of the user-defined types
                // (UDTs) matching type name pattern "TESTUDTTYPE" and
                // UDT java SQL types in the database "sysudtlib".
                // Use getUDTs method to generate a result set of the specified
                // UDTs.
                // This method has the following parameters:
                // parameter 1: a String object representing a catalog name
                // parameter 2: a String object representing a schema name
                // pattern (database name)
                // parameter 3: a String object representing a UDT type name
                // pattern (UDT type name)
                // parameter 4: an int array containing the Java SQL types for UDTs
                // The int array can contain the following values:
                //   java.sql.Types.DISTINCT - for UDT DISTINCT type
                //   java.sql.Types.DISTINCT - for UDT DISTINCT type
                //   java.sql.Types.JAVA_OBJECT - for UDT JAVA_OBJECT type
                //   NOTE: UDT JAVA_OBJECT type is not supported in the current
                //   DBS versions.
                // Each column description includes the following columns:
                // TYPE_CAT (String)
                // TYPE_SCHEM (String)
                // TYPE_NAME (String)
                // CLASS_NAME (String)
                // DATA_TYPE (int)
                // REMARKS (String)
                // BASE_TYPE (short)
                // Please refer to the driver manual for a complete
                // description of this method, its implementations,
                // and return values.
                // The call below will obtain the UDTs matching type name pattern
                // "TESTUDTTYPE" and UDT java SQL types in the database "sysudtlib"
                ResultSet rs = dbmd.getUDTs(null, "sysudtlib", typeName, types);
                // Display the retrieved UDT type names
                System.out.println(" DISPLAYING THE UDT NAMES" +
                    " FOR UDT TYPE udt_datatype IN SYSUDTLIB DATABASE:\n");
                System.out.println(" Database Name : Type Name :" +
                                   " Data Type");
                System.out.println(" ---------------------------" +
                                   " ----------");
                while(rs.next())
                {
                    System.out.println(" " + rs.getString("TYPE_SCHEM") +
                                       " : " + rs.getString("TYPE_NAME") +
                                       " : " + rs.getInt("DATA_TYPE"));
                }
            }
            finally
            {
                // Close the connection
                System.out.println("\n Closing connection to Teradata...");
                con.close();
                System.out.println(" Connection to Teradata closed. \n");
            }

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