//*********************************************************************
//
//                 Copyright (c) 2006-2008 by Teradata Corporation
//                         All Rights Reserved
//
//*********************************************************************
//
//  File:       T20704JD.java
//  Header:     none
//  Purpose:    Demonstrate the retrievel of auto-generated keys
//              after an PreparedStatement batch INSERT query.
//              The program will:
//                -  Connect as user guest/please
//                -  Executes a PreparedStatement batch INSERT query,
//                   which inserts rows into table books indicating the
//                   generated key flag RETURN_GENERATED_KEYS
//                -  Retrieve the generated key
//                -  Display the generated keys result set
//                -  Executes a PreparedStatement batch INSERT query,
//                   which inserts rows into table books indicating the
//                   column names that will be made available
//                -  Retrieve the columns using getGeneratedKeys
//                -  Display the columns
//                -  Disconnect.
//
//  JDBC API: java.sql.Connection, java.sql.PreparedStatement,
//            java.sql.PreparedStatement.executeBatch,
//            java.sql.PreparedStatement.addBatch,
//            java.sql.PreparedStatement.getGeneratedKeys,
//            java.sql.ResultSet, java.sql.ResultSet.getMetaData,
//            java.sql.ResultSetMetaData
//
//  Version: Updated for Teradata V2R6.2
//
//*********************************************************************

import java.sql.*;

public class T20704JD
{
    // 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 books (ISBN, bookTitle, bookAuthor) values(?, ?, ?)" ;
        String sBooks[][] = { {"1-09786-701-X", "In the Dust", "Low, Anne"},
                              {"1-52392-872-X", "Outside", "Willis, Ted"},
                              {"1-77841-414-X", "Sunny Day", "Gile, Jeni"}};

        try
        {
            System.out.println("\n Sample T20704JD: \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 "
                                  +"with the flag GET_GENERATED_KEYS. \n");
                // Execute a PreparedStatement batch request and retrieve the
                // identity column
                executeBatchRequest(con, sInsert, sBooks, false);

                System.out.println(" Execute a PreparedStatement batch request "
                                   +"indicating which column names should be "
                                   +"made available for retrieval. \n");
                // Execute a PreparedStatement batch request and retrieve the
                // specified columns using getGeneratedKeys
                executeBatchRequest(con, sInsert, sBooks, true);
            }
            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 T20704JD 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[][] sBooks, boolean useColNames)
    throws SQLException
    {
        PreparedStatement pstmt;
        System.out.println(
            " Preparing this SQL statement for execution:\n "
            + sInsert);

        if (useColNames)
        {
            // Using an array of column names to indicate which columns should
            // be made available for retrieval.
            String columnNames[] = {"bookPublisher", "identityCol"};

            // Creating a prepared statement object from an active connection
            // and passing the columnNames
            pstmt = con.prepareStatement(sInsert, columnNames);
        }
        else
        {
            // Creating a prepared statement object from an active connection
            // and passing the columnIndexes
            pstmt = con.prepareStatement(sInsert,
                                         Statement.RETURN_GENERATED_KEYS);
        }
        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 bookNum = 0; bookNum < sBooks.length; bookNum++)
            {

                for (int bookItem = 0; bookItem < sBooks[bookNum].length;
                     bookItem++)
                    pstmt.setString(bookItem + 1, sBooks[bookNum][bookItem]);

                pstmt.addBatch();
            }

            // The following code will perform an INSERT query
            // 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();

            int insertNum = 1;

            System.out.println(" Retrieving generated keys. \n");
            // Retrieve generated keys
            ResultSet rs = pstmt.getGeneratedKeys();

            // iterate through all returned rows and display them
            while (rs.next())
            {
                // Print the update count
                System.out.println(
                    " =====================================================");
                System.out.println(
                    " =====================================================");
                System.out.println();
                System.out.println(" INSERT " + insertNum +
                                   " executed successfully. " +
                                   updateCount[insertNum - 1] +
                                   " row(s) inserted. \n");

                // Extract and display generated keys
                System.out.println();
                System.out.println(" DISPLAYING GENERATED KEY FOR STATEMENT " +
                                   insertNum + ":");
                System.out.println(
                    " -----------------------------------------");

                // retrieve and display specified column values along
                // with their column name
                for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++)
                {
                    System.out.println(" " + rs.getMetaData().getColumnName(i)+
                                       ":  " + rs.getString(i));
                }
                insertNum++;
            }
        }
        finally
        {
            // Close the statement
            pstmt.close();
            System.out.println("\n PreparedStatement object closed.\n");
        }
    }
} // End class T20704JD