//*********************************************************************
//
//                 Copyright (c) 2004-2008 by Teradata Corporation
//                         All Rights Reserved
//
//*********************************************************************
//
//  File:       T20802JD.java
//  Header:     none
//  Purpose:    Demonstrate basic Teradata SQL using the JDBC API by creating
//              and executing a macro containing both non-SELECT and
//              SELECT statements.
//              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 T20802JD
{
    // 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 empRestoreDisplay AS (" +
            "SELECT empName, empDept, empJob FROM employee ORDER BY empName" +
            ";" +
            "DELETE FROM employee WHERE empID = 100009" +
            ";" +
            "UPDATE employee" +
            " SET empJob = 'Manager overseeing international contracts' " +
            "WHERE empID = 100002" +
            ";" +
            "UPDATE employee" +
            " SET empJob = 'Software engineer providing technical support' " +
            "WHERE empID = 100005" +
            ";" +
            "UPDATE employee SET empJob = 'Team leader managing market research' " +
            "WHERE empID = 100007" +
            ";" +
            "SELECT empName, empDept, empJob FROM employee ORDER BY empName" +
            ";)";
        String sMacroExec = "EXEC empRestoreDisplay";

        try
        {
            System.out.println("\n Sample T20802JD: \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 non-SELECT and SELECT commands

                    // 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...");
                    // Making a call to execute the created macro.
                    // Both result sets and update counts are expected back
                    boolean firstResult = stmt.execute(sMacroExec);

                    if(!firstResult)
                    {
                        // If an update count or nothing is returned - error.
                        // Result set expected as first result
                        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("\n Displaying results of the " +
                                           "first query:\n");
                        int rowCount = 0;
                        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.");

                        System.out.println("\n Processing updates...\n");
                        // Dummy variable to hold temporary update counts
                        int updResult = 0;
                        // Flag to mark whether an error occured during execution.
                        boolean error = false;
                        // Four update counts are expected
                        for(int i = 1; i <= 4; i++)
                        {
                            // Advance to next result
                            stmt.getMoreResults();
                            // Retrieve current result
                            updResult = stmt.getUpdateCount();
                            // Check for errors
                            if(updResult > 0 || updResult == -2)
                            {
                                System.out.println(" Command " + (i) +
                                " executed successfully. " + updResult +
                                " row(s) updated.");
                            }
                            else
                            {
                                System.out.println(" Command " + (i) +
                                " failed to execute successfully.");
                                error = true;     // Set error flag
                                break;
                            }
                        }

                        if(error)    // If an error was present
                        {
                            System.out.println("\n Update request failed.\n");
                            throw new IllegalStateException(
                                "Error occured during update execution");
                        }

                        // Else proceed
                        // All update statements have been processed successfully
                        System.out.println("\n Updates processed successfully.");

                        if(stmt.getMoreResults() == false)
                        {
                            // If an update count or nothing is returned - error
                            // second Result set is expected
                            throw new IllegalStateException(
                                "Unexpected results: ResultSet expected.");
                        }

                        // Else can process second query
                        // Retrieve the second result set
                        rs = stmt.getResultSet();
                        // Display the result of the first query
                        System.out.println("\n Displaying results of the " +
                                           "second query:\n");
                        // Reset row counter
                        rowCount = 0;
                        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.");

                        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(" 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 T20802JD 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 T20802JD