//*********************************************************************
//
//                 Copyright (c) 2004-2008 by Teradata Corporation
//                         All Rights Reserved
//
//*********************************************************************
//
//  File:       T20904JD.java
//  Header:     none
//  Purpose:    Demonstrate obtaining table index meta data
//              The program will:
//                -  Connect as user guest/please
//                -  Obtain and display all table index meta data
//                   for a specific table
//                -  Disconnect.
//
//  JDBC API: java.sql.Connection, java.sql.DatabaseMetaData,
//            java.sql.DatabaseMetaData.getTableTypes
//
//  Version: Updated for Teradata V2R6
//
//*********************************************************************

import java.sql.*;

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

        // Table name for which index meta data will be obtained
        String tableName = "employee";

        try
        {
            System.out.println("\n Sample T20904JD: \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 a descriptions of the
                // indices and statistics for a specific table using
                // the getIndexInfo(...) method.
                // Each index column description includes the following:
                // TABLE_CAT (String)
                // TABLE_SCHEM (String)
                // TABLE_NAME (String)
                // NON_UNIQUE (boolean)
                // INDEX_QUALIFIER (String)
                // INDEX_NAME (String)
                // TYPE (short)
                // ORDINAL_POSITION (short)
                // COLUMN_NAME (String)
                // ASC_OR_DESC (String)
                // CARDINALITY (int)
                // PAGES (int)
                // FILTER_CONDITION (String)
                // The results are ordered by NON_UNIQUE, TYPE, INDEX_NAME,
                // and ORDINAL_POSITION. Please refer to the driver manual
                // for a complete description of methods, their
                // implementations, and return values.

                // The call below will obtain all indexes for the above table
                // regardless of catalogs, schemas, or index uniqueness.
                // The 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 table name
                // pattern (table name)
                // parameter 4: a boolean value representing index
                // uniqueness
                // parameter 5: a boolean value reflecting whether
                // approximate values are allowed in the results.
                // Retrieve all table index meta data for the specified
                // table:
                ResultSet rs = dbmd.getIndexInfo(null, null, tableName,
                                                 false,false);
                // Display the retrieved index meta data
                System.out.println(" DISPLAYING INDEX META DATA FOR" +
                                   " TABLE \"" + tableName + "\" :");
                while(rs.next())
                {
                    System.out.println();
                    System.out.println(" Table catalog: " +
                                       rs.getString("TABLE_CAT"));
                    System.out.println(" Table schema: " +
                                       rs.getString("TABLE_SCHEM"));
                    System.out.println(" Table name: " +
                                       rs.getString("TABLE_NAME"));
                    System.out.println(" Can index values be unique: " +
                                       rs.getBoolean("NON_UNIQUE"));
                    System.out.println(" Index catalog: " +
                                       rs.getString("INDEX_QUALIFIER"));
                    System.out.println(" Index name: " +
                                       rs.getString("INDEX_NAME"));
                    System.out.println(" Index type: " + rs.getShort("TYPE"));
                    System.out.println(" Column sequence number within index: " +
                                       rs.getShort("ORDINAL_POSITION"));
                    System.out.println(" Column name: " +
                                       rs.getString("COLUMN_NAME"));
                    System.out.println(" Column sort sequence: " +
                                       rs.getString("ASC_OR_DESC"));
                    System.out.println(" Cardinality: " +
                                       rs.getInt("CARDINALITY"));
                    System.out.println(" Number of pages used: " +
                                       rs.getInt("PAGES"));
                    System.out.println(" Filter condition: " +
                                       rs.getString("FILTER_CONDITION"));
                }
            }
            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 T20904JD 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 T20904JD