//*********************************************************************
//
//                 Copyright (c) 2009 by Teradata Corporation
//                         All Rights Reserved
//
//*********************************************************************
//
//  File:       T20706JD.java
//  Header:     none
//  Purpose:    Demonstrate PreparedStatement batch request and manipulation
//              of BatchUpdateException.
//              The program will:
//                -  Connect as user guest/please
//                -  Execute PreparedStatement batch INSERT query,
//                   which inserts rows into table employee
//                -  Catch BatchUpdateException
//                -  Determine which parameter sets failed and which need to
//                   be resubmitted using PreparedStatement executeUpdate
//                -  Resubmit the necessary parameter sets
//                -  Display failed parameter sets with their errors
//                -  Disconnect
//
//  JDBC API: java.sql.Connection, java.sql.PreparedStatement,
//            java.sql.PreparedStatement.addBatch,
//            java.sql.PreparedStatement.executeBatch,
//            java.sql.BatchUpdateException.getUpdateCounts,
//            java.sql.BatchUpdateException.getNextException
//
//  Version: Created for Teradata Database 13.10
//
//  Notes: Uses table employee created in T20000JD
//         Uses T29900JD to remove rows and drop table
//
//*********************************************************************

import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;

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

        // Creation of the SQL INSERT statement and its parameter values
        String sInsert =
        "INSERT into employee (empId, empName, empDept, empJob) values(?, ?, ?, ?)" ;

        // Expected errors from the following parameter sets
        // 3996 : Right truncation of string data
        // 3604 : Cannot place a null value in a NOT NULL field.
        String sEmps1[][] = {
            {"106", "John Smith", "Accounting", "Administrator"},
            {"107", "Mary Jones", "Testing", "Tester"},
            {null, "Crusman Williams", "Human Resources", "Director"},
            {"115", "Betty Brown", "Human Resources", "Assistant"},
            {"104", "William White", "Human Resources", "Assistant"},
            {"103", "William James Ungunaysheunayesto", "Sales", "Assistant"}};

        // Expected error from the following parameter sets
        // 2801 : Duplicate unique prime key error in guest.employee. (twice)
        String sEmps2[][] = {
            {"106", "John Smith", "Accounting", "Administrator"},
            {"108", "Jim Green", "Testing", "Tester"},
            {"109", "Richie Williams", "Human Resources", "Assistant Director"},
            {"115", "Betty Brown", "Human Resources", "Assistant"}};

        try
        {
            System.out.println("\n Sample T20706JD: \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
            {
                System.out.println(" Execute a PreparedStatement batch request. \n");
                // Execute a PreparedStatement batch request and examine the
                // update count
                executeBatchRequest(con, sInsert, sEmps1);

                // Execute a 2nd PreparedStatement batch request that had
                // different errors
                executeBatchRequest(con, sInsert, sEmps2);
            }
            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 T20706JD 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

    private static void executeBatchRequest(Connection con, String sInsert,
        String[][] aasEmps)
    throws SQLException
    {
        ArrayList failedParameterSets = new ArrayList();
        PreparedStatement pstmt;
        System.out.println(
            " Preparing this SQL statement for execution:\n "
            + sInsert);

        // Creating a prepared statement object from an active connection
        pstmt = con.prepareStatement(sInsert);
        System.out.println(" Prepared statement object created. \n");

        try
        {
            // Set parameter values indicated by ? (dynamic update)
            System.out.println(" Using setString() to bind value to " +
                               "the parameter marker. \n" );
            for (int nEmpCnt = 0; nEmpCnt < aasEmps.length; nEmpCnt++)
            {
                for (int nEmpItem = 0; nEmpItem < aasEmps[nEmpCnt].length;
                     nEmpItem++)
                    pstmt.setString(nEmpItem + 1, aasEmps[nEmpCnt][nEmpItem]);

                pstmt.addBatch();
            }

            try
            {
                // The following code will perform an INSERT
                // on the table.

                System.out.println(" Submitting the batch request to be " +
                                   "executed. \n");
                // Submit a batch request, returning update counts
                int[] updateCount = pstmt.executeBatch();
            }
            catch (BatchUpdateException ex)
            {
                System.out.println(" Exception thrown "+ex.getErrorCode()+":"
                                   +ex.getMessage()+"\n");
                // If the error code is 1338, then this indicates that the new
                // functionality is available.

                // 1338: A failure occurred while executing a PreparedStatement
                // batch request. Details of the failure can be found in the
                // exception chain that is accessible with getNextException.
                if (ex.getErrorCode() == 1338)
                {
                    ArrayList resubmitParameterSets = new ArrayList();
                    // Get the array of update counts to check which parameter
                    // sets failed
                    int[] anUpdateCounts = ex.getUpdateCounts();

                    // Details of the failures are found in the exception chain
                    // Get the first SQL exception
                    SQLException se = ex.getNextException();

                    // BatchUpdateException error code 1138 indicates that each
                    // non-successful update count corresponds to a chained
                    // SQLException, in order.
                    for (int i = 0; i < anUpdateCounts.length; i++)
                    {
                        if (anUpdateCounts[i] == Statement.EXECUTE_FAILED)
                        {
                            // If the error code is negative, then we know this
                            // parameter set failed
                            if (se.getErrorCode() < 0)
                            {
                                // Since this parameter set failed lets add it
                                // as well as the error to a list of failed
                                // parameter sets
                                ArrayList failedParameterSet =
                                    new ArrayList(aasEmps[i].length + 2);
                                failedParameterSet.add(
                                    new Integer(se.getErrorCode()));
                                failedParameterSet.add(se.getMessage());

                                for (int nParam = 0; nParam < aasEmps[i].length
                                ; nParam++)
                                    failedParameterSet.add(aasEmps[i][nParam]);
                                failedParameterSets.add(failedParameterSet);
                            }

                            // If the error code is a non negative number, then
                            // the parameter set must be resubmitted individually
                            // using PreparedStatement executeUpdate method.
                            else
                            {
                                // Add the paramter set to a list of sets that need
                                // to be resubmitted
                                Object[] resubmitParameterSet
                                    = new Object[aasEmps[i].length];
                                System.arraycopy(aasEmps[i], 0,
                                    resubmitParameterSet, 0, aasEmps[i].length);
                                resubmitParameterSets.add(resubmitParameterSet);
                            }
                            se = se.getNextException();
                        }
                    }

                    // We need to resubmit individual requests that were not executed
                    if (resubmitParameterSets.size() > 0)
                        failedParameterSets = resubmitIndividualParamSets(con,
                            sInsert, resubmitParameterSets, failedParameterSets);

                    // Print the failed parameter sets and the exceptions
                    System.out.println(" FAILED PARAMETER SETS: \n");
                    Iterator sets = failedParameterSets.iterator();

                    while (sets.hasNext())
                    {
                        Object[] params = ((ArrayList) sets.next()).toArray();
                        for (int iCnt = 2; iCnt < params.length; iCnt++)
                            System.out.print(params[iCnt]+"  ");

                        System.out.println(
                            "\n Error "+params[0]+": "+params[1]+"\n");
                    }
                }
                else
                {
                    System.out.println(" All parameter sets failed \n"
                                      +" Error "+ex.getErrorCode()+": "
                                      +ex.getMessage()+"\n");
                }
            }
        }
        finally
        {
            // Close the statement
            pstmt.close();
            System.out.println("\n PreparedStatement object closed.\n");
        }
    } // End method executeBatchRequest

    private static ArrayList resubmitIndividualParamSets(Connection con,
                                         String sInsertStmt,
                                         ArrayList resubmitParameterSets,
                                         ArrayList failedParameterSets)
    throws SQLException
    {
        PreparedStatement pstmt;

        System.out.println(
             " Processing Individual Queries. \n");
        System.out.println(
            " Preparing this SQL statement for execution:\n "
            + sInsertStmt);

        // Creating a prepared statement object from an active connection
        pstmt = con.prepareStatement(sInsertStmt);
        System.out.println(" Prepared statement object created. \n");

        try
        {
            // Set parameter values indicated by ? (dynamic update)
            System.out.println(" Using setString() to bind value to " +
                               "the parameter marker. \n" );
            // Loop through each parameter set and resubmit them individually
            for (int nParSetCnt = 0; nParSetCnt < resubmitParameterSets.size()
            ; nParSetCnt++)
            {
                Object[] paramSet = (Object[]) resubmitParameterSets.get(nParSetCnt);
                for (int nParam = 0; nParam < paramSet.length; nParam++)
                    pstmt.setString(nParam + 1, (String) paramSet[nParam]);
                try
                {
                    pstmt.executeUpdate();
                }
                catch (SQLException se)
                {
                    // Add any failures to the list of failed parameter sets
                    ArrayList failedParameterSet
                        = new ArrayList(paramSet.length + 2);
                    failedParameterSet.add(new Integer(se.getErrorCode()));
                    failedParameterSet.add(se.getMessage());

                    for (int nParam = 0; nParam < paramSet.length; nParam++)
                        failedParameterSet.add(paramSet[nParam]);
                    failedParameterSets.add(failedParameterSet);
                }
            }
        }
        finally
        {
            // Close the statement
            pstmt.close();
            System.out.println("\n PreparedStatement object closed.\n");
        }
        return failedParameterSets;
    }
} // End class T20706JD