//*********************************************************************
//
//                 Copyright (c) 2004-2008 by Teradata Corporation
//                         All Rights Reserved
//
//*********************************************************************
//
//  Notes:      This module will demonstrate 3 kinds of macros:
//                - containing non-SELECT statements only (T20800JD)
//                - containing SELECT statements only     (T20801JD)
//                - containing SELECT/non-SELECT statements (T20802JD)
//              All changes made in this module will be made to the
//              employee table only. Furthermore, after the execution
//              of this module (T20800JD-T20802JD), all changes to the
//              employee table will be reversed to maintain consistency
//              with the employee2 table. Changes to the employee2 table
//              can be made analogously. Please refer to the earlier
//              examples for help on using UPDATE/INSERT/DELETE/SELECT
//              statements with LOB values.
//
//  File:       T20800JD.java
//  Header:     none
//  Purpose:    Demonstrate basic Teradata SQL using the JDBC API by creating
//              and executing a macro containing non-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 T20800JD
{
    // Name of the user able to create and drop 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 empUpdate AS (" +
            "INSERT INTO employee VALUES(100009, 'Jennifer Thompson', " +
            "'Product Development', 'Administrative assistant')" +
            ";" +
            "UPDATE employee SET empJob = 'Partner development specialist' " +
            "WHERE empID = 100002" +
            ";" +
            "UPDATE employee" +
            " SET empJob = 'Product manager directing industry analysis' " +
            "WHERE empID = 100005" +
            ";" +
            "UPDATE employee SET empJob = 'National advertising manager' " +
            "WHERE empID = 100007" +
            ";)";
        String sMacroExec = "EXEC empUpdate";

        try
        {
            System.out.println("\n Sample T20800JD: \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
                    // containing non-SELECT statements 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 update
                    // counts are expected back.
                    boolean firstResult = stmt.execute(sMacroExec);
                    // Displaying macro call result
                    boolean error = false;  // Create error flag
                    int updResult = 0;      // Create update counter value holder
                    // Iterate through update count values, if an error occurred
                    // must rollback all updates, otherwise, can commit changes.
                    // Display update counts and errors (if any).
                    if(firstResult)
                    {
                        // If a ResultSet has been generated - error
                        throw new IllegalStateException(
                            "Unexpected results: ResultSet generated.");
                    }
                    else    // Continue processing, display errors, if any
                    {
                        // Process the expected 4 update counts
                        for(int i = 1; i <= 4; i++)
                        {
                           // Retrieve current result
                           updResult = stmt.getUpdateCount();
                           // Advance to next result
                           stmt.getMoreResults();
                           // 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 Macro execution failed.\n");
                            throw new IllegalStateException(
                                " Error occured during macro command execution");
                        }
                        else
                        {
                            // 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 T20800JD 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 T20800JD