//*********************************************************************
//
//                 Copyright (c) 2006-2008 by Teradata Corporation
//                         All Rights Reserved
//
//*********************************************************************
//
//  File:       T20207JD.java
//  Header:     none
//  Purpose:    Demonstrate FastLoad of data rows with auto-commit false
//              and verify FastLoad sessions.
//
//        The program will:
//          -  Connect as user guest/please
//          -  Insert data rows into a table using FastLoad with auto-commit false
//          -  Verify the updateCount returned by executeBatch
//          -  Verify that no SQLWarning has occurred
//          -  Verify the sessions used to FastLoad
//          -  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 and verify FastLoad sessions.
 */
public class T20207JD
{
    // 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 Parameter for SESSIONS
        int sessions = 1;

        // URL to be passed to the JDBC driver
        String url =
            "jdbc:teradata://whomooz/TMODE=ANSI,CHARSET=UTF8,TYPE=FASTLOAD,SESSIONS="
            + sessions;

        // Size of CHAR column
        int strLen = 100;

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

        // CREATE TABLE statement
        String createTable = "CREATE TABLE " + tableName + "," +
                                "NO FALLBACK," +
                                "NO BEFORE JOURNAL," +
                                "NO AFTER JOURNAL," +
                                "CHECKSUM = DEFAULT" +
                                "(" +
                                " L_INDEX INTEGER 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 T20207JD 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.");

            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 strBuf = new String(charBuf);

                        con.setAutoCommit(false);

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

                        pstmt.setInt(1, 2);
                        pstmt.setString(2, strBuf);
                        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();
                        // 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();
                        }

                        // Note that FastLoad sessions can only be verified while
                        // FastLoad is active with auto-commit set to false,
                        // i.e. a setXXX method must have been called and commit
                        // must NOT have been performed yet
                        verifyFastLoadSessions(con, pstmt.hashCode(), sessions);

                        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 T20207JD 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.") ;
        }
    }

    private static void verifyFastLoadSessions(Connection con, int hashCode,
        int sessions)
    throws SQLException
    {
        System.out.println(" Verifying FastLoad SESSIONS for PreparedStatement="
            + hashCode + "...");

        // Note that the method for verifying FastLoad SESSIONS may change at any
        // time without prior notice

        String amps = con.nativeSQL("{fn teradata_amp_count()}");
        System.out.println("  AMP count = " + amps);
        int consExpected = sessions < Integer.parseInt(amps) ? sessions
            : Integer.parseInt(amps);

        String lsnList = con.nativeSQL("{fn teradata_logon_sequence_number()}");
        System.out.println("  LSN(s) = " + lsnList);

        String lsnArray[] = lsnList.split(",");
        if ((lsnArray.length & 1) == 1) {
            System.out.println(
                "ERROR: Expected an even count of delimited strings, indicating pairs"
                + " of PreparedStatement hash codes and LSNs, but received an odd"
                + " count instead!");
            throw new IllegalStateException (
                "Received invalid list from teradata_logon_sequence_number") ;
        }

        for (int i = 0; i < lsnArray.length; i += 2) {
            int hashCodeForLSN = Integer.parseInt(lsnArray[i]);
            int lsnForHashCode = Integer.parseInt(lsnArray[i+1]);
            if (hashCodeForLSN == hashCode)
            {
                System.out.println("  LogonSequenceNo=" + lsnForHashCode
                    + " for PreparedStatement=" + hashCode);

                String selectSessionCount = "SELECT COUNT(*) FROM DBC.SessionInfo"
                    + " WHERE Partition='FASTLOAD' AND LogonSequenceNo=?";

                PreparedStatement pstmt1 = con.prepareStatement(selectSessionCount);
                try {
                    byte bytes[] = new byte[4];
                    bytes[1] = (byte) ((lsnForHashCode >> 24) & 0xFF);
                    bytes[0] = (byte) ((lsnForHashCode >> 16) & 0xFF);
                    bytes[3] = (byte) ((lsnForHashCode >> 8) & 0xFF);
                    bytes[2] = (byte) (lsnForHashCode & 0xFF);
                    pstmt1.setBytes(1, bytes);
                    ResultSet rs1 = pstmt1.executeQuery();
                    try {
                        int consFound = 0;
                        if (rs1.next()) {
                            ResultSetMetaData rsmd = rs1.getMetaData();
                            consFound = rs1.getInt(1);
                            System.out.println("  FastLoadConnection session "
                                + rsmd.getColumnName(1) + "=" + consFound
                                + " for LogonSequenceNo=" + lsnForHashCode);
                        }
                        if (consFound != consExpected) {
                            System.out.println(
                                "ERROR: Did not detect a correct count of"
                                + " FastLoadConnection session(s): expected "
                                + consExpected + ", got " + consFound);
                        }
                    } finally {
                        rs1.close();
                    }

                    String selectSessionNums =
                        "SELECT SessionNo,Partition FROM DBC.SessionInfo"
                            + " WHERE LogonSequenceNo=?"
                            + " ORDER BY SessionNo,Partition";

                    PreparedStatement pstmt2 =
                        con.prepareStatement(selectSessionNums);

                    try {
                        pstmt2.setBytes(1, bytes);
                        ResultSet rs2 = pstmt2.executeQuery();
                        try {
                            ResultSetMetaData rsmd = rs2.getMetaData();
                            while (rs2.next()) {
                                System.out.println("  Found " + rsmd.getColumnName(2)
                                    + "=" + rs2.getObject(2) + " with "
                                    + rsmd.getColumnName(1) + "=" + rs2.getObject(1)
                                    + " for LogonSequenceNo=" + lsnForHashCode);
                            }
                        } finally {
                            rs2.close();
                        }
                    } finally {
                        pstmt2.close();
                    }
                } finally {
                    pstmt1.close();
                }
                break;
            }
        }
        con.clearWarnings();
    }
}