//*********************************************************************
//
//                 Copyright (c) 2006-2013 by Teradata Corporation
//                         All Rights Reserved
//
//*********************************************************************
//
//  File:       T20206JD.java
//  Header:     none
//  Purpose:    Demonstrate FastLoad of data rows with auto-commit false.
//
//       The program will:
//         -  Connect as user guest/please
//         -  Insert data rows into a table using FastLoad with auto-commit false
//            - if args[0] is omitted or 0 then inject no error
//                            1 then inject contraint error
//                            2 then inject unique primary index error
//         -  Verify the updateCount returned by executeBatch
//         -  Verify that no SQLWarning has occurred
//         -  Verify that the correct number of data rows were inserted (fastloaded)
//         -  Disconnect
//
//  JDBC API: java.sql.Connection, java.sql.PreparedStatement,
//            java.sql.PreparedStatement.executeBatch
//
//  Version: new sample
//
//*********************************************************************

import java.io.*;
import java.sql.*;
import java.util.Arrays;

/**
 * FastLoad with auto-commit false.
 */
public class T20206JD {
    public static final int INJECT_NO_ERROR = 0;
    public static final int INJECT_CONTRAINT_ERROR = 1;
    public static final int INJECT_UNIQUE_PRIMARY_INDEX_ERROR = 2;

    // Name of the user able to create and drop tables
    public static String user = "guest";
    public static String password = "please";

    public static void main(String args[])
    throws ClassNotFoundException
    {
        // URL to be passed to the JDBC driver
        String url = "jdbc:teradata://whomooz/TMODE=ANSI,CHARSET=UTF8,TYPE=FASTLOAD";

        // Size of CHAR column
        int strLen = 100;

        // Name of sample table
        String tableName = "T20206JD";

        // CREATE TABLE statement
        String createTable = "CREATE TABLE " + tableName + "," +
                                "NO FALLBACK," +
                                "NO BEFORE JOURNAL," +
                                "NO AFTER JOURNAL," +
                                "CHECKSUM = DEFAULT" +
                                "(" +
                                " L_INDEX INTEGER CHECK(L_INDEX BETWEEN 1 AND 10) NOT NULL," +
                                " L_TEXT CHAR(" + strLen + ")" +
                                " CHARACTER SET UNICODE NOT CASESPECIFIC" +
                                ") " +
                              "UNIQUE PRIMARY INDEX ( L_INDEX )";

        // INSERT statement
        String insertTable = "INSERT INTO " + tableName + " VALUES(?,?)";

        // SELECT statement
        String selectTable = "SELECT COUNT(*) FROM " + tableName;

        try {
            System.out.println("Sample T20206JD starting: " + new java.util.Date());
            System.out.println(" Looking for the Teradata JDBC driver...");
            Class.forName("com.teradata.jdbc.TeraDriver");
            System.out.println(" Teradata JDBC driver loaded.");

            int injectError = INJECT_NO_ERROR;
            if (args.length > 0) {
                injectError = Integer.parseInt(args[0]);
                if (injectError != INJECT_NO_ERROR)
                    System.out.println(" Will inject error type: " + args[0]);
            }

            System.out.println(" Attempting to connect to Teradata...");
            Connection con = DriverManager.getConnection(url, user, password);
            System.out.println(" Connection to Teradata established.");

            try {
                System.out.println(" Creating a Statement object...");
                Statement stmt = con.createStatement();
                System.out.println(" Created a Statement object.");

                try {
                    try {
                        stmt.executeUpdate("DROP TABLE " + tableName);
                    } catch (SQLException e) {
                        // Do nothing because the table likely does not exist yet
                        System.out.println(" Drop table exception ignored: " + e);
                    }
                    System.out.println(" Creating table " + tableName + "...");
                    stmt.executeUpdate(createTable);
                    System.out.println(" Created table " + tableName + ".");

                    con.clearWarnings();

                    System.out.println(" Creating a PreparedStatement object...");
                    PreparedStatement pstmt = con.prepareStatement(insertTable);
                    System.out.println(" Created a PreparedStatement object...");

                    try {
                        SQLWarning w = con.getWarnings();
                        // Note that there could be multiple SQLWarning objects
                        // chained together
                        while (w != null) {
                            System.out.println("*** SQLWarning caught ***");
                            StringWriter sw = new StringWriter();
                            w.printStackTrace(new PrintWriter(sw, true));
                            System.out.println("SQL State = " + w.getSQLState()
                                + ", Error Code = " + w.getErrorCode()
                                + "\n" + sw.toString());
                            w = w.getNextWarning();
                        }

                        int numRows = 0;
                        char[] charBuf = new char[strLen];

                        Arrays.fill(charBuf, 'a');
                        String strBuf1 = new String(charBuf);

                        Arrays.fill(charBuf, 'b');
                        String strBuf2 = new String(charBuf);

                        Arrays.fill(charBuf, 'c');
                        String strBuf3 = new String(charBuf);

                        Arrays.fill(charBuf, 'd');
                        String strBuf4 = new String(charBuf);

                        con.setAutoCommit(false);

                        System.out.println(" Binding data rows...");
                        int batchCount = 0;
                        pstmt.setInt(1, 1);
                        pstmt.setString(2, strBuf1);
                        pstmt.addBatch();
                        batchCount++;

                        pstmt.setInt(1, 2);
                        pstmt.setString(2, strBuf2);
                        pstmt.addBatch();
                        batchCount++;

                        // Check SQLWarning after binding data rows, but before
                        // executeBatch().
                        w = pstmt.getWarnings();
                        // Note that there could be multiple SQLWarning objects
                        // chained together
                        while (w != null) {
                            System.out.println("*** SQLWarning caught ***");
                            StringWriter sw = new StringWriter();
                            w.printStackTrace(new PrintWriter(sw, true));
                            System.out.println("SQL State = " + w.getSQLState()
                                + ", Error Code = " + w.getErrorCode()
                                + "\n" + sw.toString());
                            w = w.getNextWarning();
                        }

                        System.out.println(" Inserting data rows...");
                        int updateCounts[] = pstmt.executeBatch();
                        if (updateCounts == null) {
                            System.out.println(
                                "ERROR: A null update count was returned!");
                        } else {
                            if (updateCounts.length != batchCount) {
                                System.out.println(
                                    "WARNING: The update count does not match the"
                                    + " number of rows batched: expected "
                                    + batchCount + ", got " + updateCounts.length);
                            }
                            for (int i = 0; i < updateCounts.length; i++) {
                                if (updateCounts[i] != 1) {
                                    System.out.println(
                                        "WARNING: The update count for row " + (i+1)
                                        + " failed: expected 1, got "
                                        + updateCounts[i]);
                                }
                            }
                            numRows += updateCounts.length;
                        }

                        // Check SQLWarning after executeBatch().
                        w = pstmt.getWarnings();
                        while (w != null) {
                            System.out.println("*** SQLWarning caught ***");
                            StringWriter sw = new StringWriter();
                            w.printStackTrace(new PrintWriter(sw, true));
                            System.out.println("SQL State = " + w.getSQLState()
                                + ", Error Code = " + w.getErrorCode() +
                                "\n" + sw.toString());
                            w = w.getNextWarning();
                        }

                        System.out.println(" Binding data rows...");
                        batchCount = 0;
                        pstmt.setInt(1, injectError == INJECT_CONTRAINT_ERROR ? 30 : 3);
                        pstmt.setString(2, strBuf3);
                        pstmt.addBatch();
                        batchCount++;

                        pstmt.setInt(1, injectError == INJECT_UNIQUE_PRIMARY_INDEX_ERROR ? 2 : 4);
                        pstmt.setString(2, strBuf4);
                        pstmt.addBatch();
                        batchCount++;

                        // Check SQLWarning after binding data rows, but before
                        // executeBatch().
                        w = pstmt.getWarnings();
                        // Note that there could be multiple SQLWarning objects
                        // chained together
                        while (w != null) {
                            System.out.println("*** SQLWarning caught ***");
                            StringWriter sw = new StringWriter();
                            w.printStackTrace(new PrintWriter(sw, true));
                            System.out.println("SQL State = " + w.getSQLState()
                                + ", Error Code = " + w.getErrorCode()
                                + "\n" + sw.toString());
                            w = w.getNextWarning();
                        }

                        System.out.println(" Inserting data rows...");
                        updateCounts = pstmt.executeBatch();
                        if (updateCounts == null) {
                            System.out.println(
                                "ERROR: A null update count was returned!");
                        } else {
                            if (updateCounts.length != batchCount) {
                                System.out.println(
                                    "WARNING: The update count does not match the"
                                    + " number of rows batched: expected "
                                    + batchCount + ", got " + updateCounts.length);
                            }
                            for (int i = 0; i < updateCounts.length; i++) {
                                if (updateCounts[i] != 1) {
                                    System.out.println(
                                        "WARNING: The update count for row " + (i+1)
                                        + " failed: expected 1, got "
                                        + updateCounts[i]);
                                }
                            }
                            numRows += updateCounts.length;
                        }

                        // Check SQLWarning after executeBatch().
                        w = pstmt.getWarnings();
                        while (w != null) {
                            System.out.println("*** SQLWarning caught ***");
                            StringWriter sw = new StringWriter();
                            w.printStackTrace(new PrintWriter(sw, true));
                            System.out.println("SQL State = " + w.getSQLState()
                                + ", Error Code = " + w.getErrorCode()
                                +"\n" + sw.toString());
                            w = w.getNextWarning();
                        }

                        con.commit();
                        // Note that at this point no further executeBatch is
                        // permitted unless all rows in the destination table are
                        // deleted

                        // Check SQLWarning after commit().
                        w = con.getWarnings();
                        // Note that there could be multiple SQLWarning objects
                        // chained together
                        while (w != null) {
                            System.out.println("*** SQLWarning caught ***");
                            StringWriter sw = new StringWriter();
                            w.printStackTrace(new PrintWriter(sw, true));
                            System.out.println("SQL State = " + w.getSQLState()
                                + ", Error Code = " + w.getErrorCode()
                                + "\n" + sw.toString());
                            w = w.getNextWarning();
                        }

                        con.setAutoCommit(true);

                        System.out.println(" Selecting data rows...");
                        ResultSet rs = stmt.executeQuery(selectTable);
                        try {
                            int rowCount = 0;
                            ResultSetMetaData rsmd = rs.getMetaData();
                            if (rs.next()) {
                                rowCount = rs.getInt(1);
                                System.out.println("  " + tableName + " row "
                                    + rsmd.getColumnName(1) + "=" + rowCount);
                            }
                            if (rowCount != numRows) {
                                System.out.println(
                                    "ERROR: Didn't retrieve enough rows: expected "
                                    + numRows + ", got " + rowCount);
                            }
                        } finally {
                            rs.close();
                        }
                    } catch (SQLException e) {
                        try {
                            System.out.println(
                                " Rolling back due to exception: " + e);
                            con.rollback();

                            // Check SQLWarning after rollback().
                            SQLWarning w = con.getWarnings();
                            // Note that there could be multiple SQLWarning objects
                            // chained together
                            while (w != null) {
                                System.out.println("*** SQLWarning caught ***");
                                StringWriter sw = new StringWriter();
                                w.printStackTrace(new PrintWriter(sw, true));
                                System.out.println("SQL State = " + w.getSQLState()
                                    + ", Error Code = " + w.getErrorCode()
                                    + "\n" + sw.toString());
                                w = w.getNextWarning();
                            }
                        } catch (SQLException ex) {
                            System.out.println(
                                " Chaining exception from rollback: " + ex);
                            e.setNextException(ex);
                        }
                        throw e;
                    } finally {
                        System.out.println(" Closing PreparedStatement object...");
                        pstmt.close();
                        System.out.println(" PreparedStatement object closed.");
                    }
                } finally {
                    System.out.println(" Closing Statement object...");
                    stmt.close();
                    System.out.println(" Statement object closed.");
                }
            } finally {
                System.out.println(" Closing Connection to Teradata...");
                con.close();
                System.out.println(" Connection to Teradata closed.");
            }

            System.out.println("Sample T20206JD finished. " + new java.util.Date());
        }
        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.") ;
        }
    }
}