//*********************************************************************
//
//                 Copyright (c) 2004-2008 by Teradata Corporation
//                         All Rights Reserved
//
//*********************************************************************
//
//  File:       T20801JD.java
//  Header:     none
//  Purpose:    Demonstrate basic Teradata SQL using the JDBC API by creating
//              and executing a macro containing SELECT statements only.
//              The program will:
//                -  Connect as user guest/please
//                -  Create a macro
//                -  Execute the newly-created macro
//                -  Obtain and display the results
//                -  Disconnect.
//
//  JDBC API:  java.sql.Connection,
//             java.sql.Statement,
//             java.sql.Statement.execute,
//             java.sql.Statement.executeUpdate,
//             java.sql.Statement.getUpdateCount,
//             java.sql.Statement.getResultSet,
//             java.sql.Statement.getMoreResults,
//
//  Version:   Updated for Teradata V2R6
//
//*********************************************************************

import java.sql.*;

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

        // Macro creation/execution commands
        String sMacro =
            "REPLACE MACRO empDisplayVarious AS ("+
            "SELECT empID, empName FROM employee ORDER BY empID" +
            ";" +
            "SELECT empDept, empName, empID FROM employee " +
            "ORDER BY empDept, empName" +
            ";" +
            "SELECT empName, empDept, empJob FROM employee ORDER BY empName" +
            ";)";
        String sMacroExec = "EXEC empDisplayVarious";

        try
        {
            System.out.println("\n Sample T20801JD: \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 statement object from an active connection
                Statement stmt = con.createStatement();
                System.out.println(" Statement object created.");

                try
                {
                    // The following code will create and execute a macro
                    // consisting of SELECT commands only

                    // Turn off AutoCommit to allow for proper error-handling
                    con.setAutoCommit(false);

                    System.out.println("\n Attempting to create a macro : "
                                       + sMacro);
                    // Sending the procedure creation request to the database,
                    // replacing any old implementations, if present
                    stmt.executeUpdate(sMacro);
                    // No errors were generated, can commit changes
                    con.commit();
                    System.out.println(" Macro created successfully.");

                    System.out.println("\n Attempting to execute the macro...\n");
                    // Making a call to execute the created macro
                    // Only result sets are expected back
                    boolean firstResult = stmt.execute(sMacroExec);

                    if(!firstResult)
                    {
                        // If an update count or nothing is returned - error
                        throw new IllegalStateException(
                            "Unexpected results: ResultSet expected.");
                    }
                    else
                    {
                        // Retrieve the first result
                        ResultSet rs = stmt.getResultSet();
                        // Display the result of the first query
                        System.out.println(" Displaying results of the" +
                                           " first query:\n");
                        int rowCount = 0;    // returned rows counter
                        while (rs.next())
                        {
                            rowCount++;
                            System.out.println(" Row " + rowCount + " : ");
                            System.out.println(" Column empID : " +
                                               rs.getInt(1));
                            System.out.println(" Column empName : " +
                                               rs.getString(2));
                        }
                        System.out.println("\n " + rowCount +
                                           " row(s) returned.");

                        // If an update count or nothing is returned - error
                        // Advance to next result
                        if (!stmt.getMoreResults())
                            throw new IllegalStateException(
                                "Unexpected results: ResultSet expected.");

                        // Retrieve the second result
                        rs = stmt.getResultSet();
                        System.out.println("\n Displaying results of the " +
                                           "second query:\n");
                        rowCount = 0; // reset row counter
                        while (rs.next())
                        {
                            rowCount++;
                            System.out.println(" Row " + rowCount + " : ");
                            System.out.println(" Column empDept : " +
                                               rs.getString(1));
                            System.out.println(" Column empName : " +
                                               rs.getString(2));
                            System.out.println(" Column empID : " +
                                               rs.getInt(3));
                        }
                        System.out.println("\n " + rowCount +
                                           " row(s) returned.");

                        // If an update count or nothing is returned - error
                        // Advance to next result
                        if (!stmt.getMoreResults())
                            throw new IllegalStateException(
                                "Unexpected results: ResultSet expected.");

                        // Retrieve the third result
                        rs = stmt.getResultSet();
                        System.out.println("\n Displaying results of the " +
                                           " third query:\n");
                        rowCount = 0; // reset row counter
                        while (rs.next()) {
                            rowCount++;
                            System.out.println(" Row " + rowCount + " : ");
                            System.out.println(" Column empName : " +
                                               rs.getString(1));
                            System.out.println(" Column empDept : " +
                                               rs.getString(2));
                            System.out.println(" Column empJob : " +
                                               rs.getString(3));
                        }
                        System.out.println("\n " + rowCount + " row(s) returned.");

                        // Check for more results being returned, none are
                        // expected
                        if (! ( (stmt.getMoreResults() == false)
                        && (stmt.getUpdateCount() == -1)))
                            throw new IllegalStateException(
                                "Unexpected results: more results present.");

                        // All statements have been processed successfully,
                        // can commit all changes to the database.
                        System.out.println("\n No errors have occurred. " +
                                           "Commit all changes.");
                        con.commit();
                        System.out.println(
                                    "\n Call to macro successfully completed.");
                    }
                }
                finally
                {
                    // Close the statement
                    stmt.close();
                    System.out.println("\n Statement object closed. \n");
                }
            }
            finally
            {
                // Close the connection
                System.out.println(" Closing connection to Teradata...");
                con.close();
                System.out.println(" Connection to Teradata closed. \n");
            }

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