//*********************************************************************
//
//                 Copyright (c) 2006-2008 by Teradata Corporation
//                         All Rights Reserved
//
//*********************************************************************
//
//  File:       T21105JD.java
//  Header:     none
//  Purpose:    Demonstrate use of USEXVIEWS URL Parameter/DataSource Property
//              to determine whether XVIEWS will be used to access Database
//              metadata.
//
//              The program will:
//                -  Connect as user guest/please with USEXVIEWS=ON
//                -  Connect as user guest/please with USEXVIEWS=OFF
//                -  Obtain and display names of all available databases when
//                   USEXVIEWS=ON is set
//                -  Obtain and display names of all available databases when
//                   USEXVIEWS=OFF is set
//                -  Disconnect both connections
//
//  JDBC API: java.sql.Connection, java.sql.Statement, java.sql.DatabaseMetaData
//
//
//  Version: Created for Teradata Database 12.0
//
//*********************************************************************

import java.sql.*;

public class T21105JD
{
    // 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";
        String url_xviewOn = url + ",USEXVIEWS=ON";
        String url_xviewOff = url + ",USEXVIEWS=OFF";

        try
        {
            System.out.println("\n Sample T21105JD: \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 1st  connection object
            Connection con_xviewOn =
                DriverManager.getConnection(url_xviewOn, sUser, sPassword);
            try
            {
                System.out.println(" User " + sUser + " connected.");
                System.out.println(
                    " Connection to Teradata with USEXVIEWS=ON established. \n");

                // Creating 2nd connection object
                Connection con_xviewOff =
                    DriverManager.getConnection(url_xviewOff, sUser, sPassword);
                try
                {
                    System.out.println(" User " + sUser + " connected.");
                    System.out.println(
                        " Connection to Teradata with USEXVIEWS=OFF established.\n");

                    // Creating a DatabaseMetaData object from an active connection.
                    DatabaseMetaData dbmd = con_xviewOn.getMetaData();
                    System.out.println(" DatabaseMetaData object created. \n");

                    // The following code obtains all schema names available in
                    // the database.
                    ResultSet rs = dbmd.getSchemas();

                    // Display the database names
                    System.out.println(
                        " DISPLAYING ALL DATABASE NAMES WITH USEXVIEWS=ON:\n");
                    int rowCount = 0;
                    while (rs.next())
                    {
                        System.out.println(" " + rs.getString("TABLE_SCHEM"));
                        rowCount++;
                    }
                    System.out.println("\n ROWS DISPLAYED: " + rowCount + "\n");

                    // Creating a DatabaseMetaData object from an active connection.
                    dbmd = con_xviewOff.getMetaData();
                    System.out.println(" DatabaseMetaData object created. \n");

                    // The following code obtains all schema names available in
                    // the database.
                    rs = dbmd.getSchemas();

                    // Display the database names
                    System.out.println(
                        " DISPLAYING ALL DATABASE NAMES WITH USEXVIEWS=OFF:\n");
                    rowCount = 0;
                    while (rs.next())
                    {
                        System.out.println(" " + rs.getString("TABLE_SCHEM"));
                        rowCount++;
                    }
                    System.out.println("\n ROWS DISPLAYED: " + rowCount + "\n");
                }
                finally
                {
                    System.out.println("\n Closing connection to Teradata...");
                    con_xviewOff.close();
                    System.out.println(" Connection to Teradata closed. \n");
                }
            }
            finally
            {
                System.out.println("\n Closing connection to Teradata...");
                con_xviewOn.close();
                System.out.println(" Connection to Teradata closed. \n");
            }

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