//*********************************************************************
//
//                 Copyright (c) 2006-2008 by Teradata Corporation
//                         All Rights Reserved
//
//*********************************************************************
//
//  File:       T20703JD.java
//  Header:     none
//  Purpose:    Demonstrate the retrievel of auto-generated keys
//              after a multiple statement INSERT query.
//              The program will:
//                -  Connect as user guest/please
//                -  Executes a multi-statement 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 multi-statement 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.execute,
//            java.sql.Statement.execute,
//            java.sql.Statement.getGeneratedKeys,
//            java.sql.Statement.getUpdateCount,
//            java.sql.Statement.getResultSet,
//            java.sql.Statement.getMoreResults,
//            java.sql.ResultSet, java.sql.ResultSet.getMetaData,
//            java.sql.ResultSetMetaData
//
//  Version: Updated for Teradata V2R6.2
//
//*********************************************************************

import java.sql.*;

public class T20703JD
{
    // 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 multiple SQL INSERT statements
        String sMultiStmtInsertQuery1 =
            "INSERT into books (ISBN, bookTitle, bookAuthor, bookPublisher)"
            + " values('1-76535-785-X', 'Beginning SQL', 'Jones, Howard',"
            + " 'Books Inc.')"
            + ";" +
            "INSERT into books (ISBN, bookTitle, bookPublisher)"
            + " values('1-28435-611-X', 'The White Daisy', 'Book Club')"
            + ";" +
            "INSERT into books (ISBN, bookTitle, bookAuthor)"
            + " values('1-81766-591-X', 'The Giant Whale', 'Dempner, William')" ;

        String sMultiStmtInsertQuery2 =
            "INSERT into books (ISBN, bookTitle, bookAuthor, bookPublisher)"
            + " values(?, ?, ?, ?)"
            + ";" +
            "INSERT into books (ISBN, bookTitle, bookPublisher)"
            + " values(?, ?, ?)"
            + ";" +
            "INSERT into books (ISBN, bookTitle, bookAuthor)"
            + " values(?, ?, ?)" ;
        try
        {
            System.out.println("\n Sample T20703JD: \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 Statement request with the "+
                                   "flag Statement.GET_GENERATED_KEYS. \n");
                // Executing a multi-statement request and retrieving the
                // identity column
                multiStmtRequest(con, sMultiStmtInsertQuery1);

                System.out.println(" Execute a PreparedStatement request "+
                                   "indicating which column indexes should be "+
                                   "made available for retrieval. \n");
                // Executing a multi-statement request and retrieving the
                // columns specified to be returned from getGeneratedKeys
                preparedMultiStmtRequest(con, sMultiStmtInsertQuery2);
            }
            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 T20703JD 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 multiStmtRequest(Connection con, String sMultiStmtInsert)
    throws SQLException
    {
        System.out.println(" Preparing this SQL statement for execution:\n "
                           + sMultiStmtInsert);
        // Creating a statement object from an active connection
        Statement stmt = con.createStatement();
        System.out.println(" Created statement object created. \n" +
                           " Executing query. \n");

        try
        {
            // The following code will perform a multiple statement INSERT
            // query on the table.

            // Executing a multi-statement query and signaling the driver that
            // it should return the identity key, which is automatically
            // generated as a result of executing this request
            if (stmt.execute(sMultiStmtInsert, Statement.RETURN_GENERATED_KEYS))
            {
                // If a ResultSet has been generated, error
                throw new IllegalStateException(
                    "Unexpected results: ResultSet generated.");
            }

            int updateCount ;
            int stmtNum = 1;

            // While there are more update counts to process
            while((updateCount = stmt.getUpdateCount()) != -1)
            {
                // Print the update count
                System.out.println();
                System.out.println(
                    " =====================================================");
                System.out.println(
                    " =====================================================");
                System.out.println();
                System.out.println(" STATEMENT " + stmtNum +
                                   " executed successfully. " + updateCount +
                                   " row(s) inserted. \n");

                System.out.println(" Retrieving generated keys.");
                // Retrieve generated key
                ResultSet rs = stmt.getGeneratedKeys();

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

                // iterate through all returned rows and display them
                while (rs.next())
                {
                    // retrieve and display identity column value
                    int identityCol = rs.getInt(1);
                    System.out.println(" Identity Column Value : " +
                                       identityCol);
                }

                if (stmt.getMoreResults())
                {
                    // If a ResultSet has been generated, error
                    throw new IllegalStateException(
                        "Unexpected results: ResultSet generated.");
                }
                stmtNum ++;
            }
        }
        finally
        {
            // Close the statement
            stmt.close();
            System.out.println("\n Statement object closed.\n");
        }
    }

    private static void preparedMultiStmtRequest(Connection con,
        String sMultiStmtInsert)
    throws SQLException
    {
        // Using an array of column indexes to indicate which columns should be
        // made available for retrieval.
        int columnIndexes[] = {2, 3, 4, 5};

        System.out.println(" Preparing this SQL statement for execution:\n "
                           + sMultiStmtInsert);

        // Creating a prepared statement object from an active connection
        // and passing the columnIndexes
        PreparedStatement pstmt = con.prepareStatement(sMultiStmtInsert,
                                                       columnIndexes);
        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");
            pstmt.setString(1, "1-98675-812-X");
            pstmt.setString(2, "November Cold");
            pstmt.setString(3, "Bark, Andy");
            pstmt.setString(4, "Better Books");
            pstmt.setString(5, "1-62054-787-X");
            pstmt.setString(6, "Wet Day");
            pstmt.setString(7, "Great Books");
            pstmt.setString(8, "1-73165-923-X");
            pstmt.setString(9, "The Shady Tree");
            pstmt.setString(10, "Temin, Samuel");

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

            System.out.println(" Submitting the request to be executed.");
            // Submit a query, returning an update count
            boolean rsExpected = pstmt.execute();

            if (rsExpected)
            {
                // If a ResultSet has been generated, error
                throw new IllegalStateException(
                    "Unexpected results: ResultSet generated.");
            }

            int updateCount ;
            int stmtNum = 1;

            // While there are more update counts to process
            while((updateCount = pstmt.getUpdateCount()) != -1)
            {
                // Print the update count
                System.out.println();
                System.out.println(
                    " =====================================================");
                System.out.println(
                    " =====================================================");
                System.out.println();
                System.out.println(" STATEMENT " + stmtNum +
                                   " executed successfully. " + updateCount +
                                   " row(s) inserted. \n");

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

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

                // iterate through all returned rows and display them
                while (rs.next())
                {
                    // 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));
                    }
                }

                if (pstmt.getMoreResults())
                {
                    // If a ResultSet has been generated, error
                    throw new IllegalStateException(
                        "Unexpected results: ResultSet generated.");
                }
                stmtNum++;
            }
        }
        finally
        {
            // Close the statement
            pstmt.close();
            System.out.println("\n PreparedStatement object closed.\n");
        }
    }
} // End class T20703JD