//*********************************************************************
//
//                 Copyright (c) 2006-2008 by Teradata Corporation
//                         All Rights Reserved
//
//*********************************************************************
//
//  File:       T20205JD.java
//  Header:     none
//  Purpose:    Demonstrate FastLoad of data rows with auto-commit true.
//
//     The program will:
//       -  Connect as user guest/please
//       -  Insert data rows into a table using FastLoad with auto-commit true
//       -  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 true.
 */
public class T20205JD {
    // 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 = "T20205JD";

        // 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 T20205JD 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;
                        int batchCount = 0;
                        char[] charBuf = new char[strLen];
                        Arrays.fill(charBuf, 'a');
                        String strBuf = new String(charBuf);

                        // Note that auto-commit is true by default,
                        // thus an auto-commit mode of 'true' is assumed here

                        System.out.println(" Binding data rows...");
                        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(
                                    "ERROR: 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(
                                        "ERROR: The update count for row " + (i+1)
                                        + " failed: expected 1, got "
                                        + updateCounts[i]);
                                }
                            }
                            numRows += updateCounts.length;
                        }

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

                        // 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();
                        }

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