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

import java.sql.*;

public class T20204JD
{
    // 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 value
        String sInsertQuery = "INSERT into books "
                       + " (ISBN, bookTitle, bookAuthor, bookPublisher)"
                       + " values(?, ?, ?, ?)";

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

                System.out.println(" Execute a PreparedStatement request "+
                                   "indicating which column names should be "+
                                   "made available for retrieval. \n");
                // Execute a PreparedStatement batch request and retrieve the
                // specified columns using getGeneratedKeys
                requestMultipleColumns(con, sInsertQuery);
            }
            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 T20204JD 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

    static void requestSingleColumn(Connection con, String sInsertQuery)
        throws SQLException
    {
        System.out.println(" Preparing this SQL statement for execution:\n "
                           + sInsertQuery);
        // Creating a prepared statement object from an active connection
        // and indicating that the generated key will be returned.
        PreparedStatement pstmt = con.prepareStatement(sInsertQuery,
            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:");
            pstmt.setString(1, "1-57345-785-X");
            pstmt.setString(2, "SQL for Beginners");
            pstmt.setString(3, "Smith, John");
            pstmt.setString(4, "Able Books");

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

            System.out.println(" Executing query. \n");
            // Submit a query, returning an update count
            int updateCount = pstmt.executeUpdate();
            System.out.println(" Obtained update count: " + updateCount);

            System.out.println(" Retrieving generated keys.");
            // Retrieve generated key
            ResultSet rs = pstmt.getGeneratedKeys();
            // set result set to the first row
            rs.next();

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

            // retrieve and display identity column value
            int identityCol = rs.getInt(1);
            System.out.println(" Identity Column Value: " + identityCol);
        }
        finally
        {
            // Close the statement
            pstmt.close();
            System.out.println("\n PreparedStatement object closed.\n");
        }
    }

    static void requestMultipleColumns(Connection con, String sInsertQuery)
        throws SQLException
    {
        // Specifying the column names that should be returned in a String array
        String columnNames[] = {"bookTitle", "bookAuthor", "identityCol"};

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

        // Creating a prepared statement object from an active connection
        // and passing the columnNames, which is a list of columns to be
        // returned from getGeneratedKeys
        PreparedStatement pstmt = con.prepareStatement(sInsertQuery,
                                                       columnNames);
        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-68345-785-X");
            pstmt.setString(2, "Advanced SQL");
            pstmt.setString(3, "White, Joseph");
            pstmt.setString(4, "Able Books");

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

            System.out.println(" Executing query. \n");
            // Submit a query, returning an update count
            int updateCount = pstmt.executeUpdate();
            System.out.println(" Obtained update count: " + updateCount);

            System.out.println(" Retrieving generated keys.");
            // Retrieve generated key
            ResultSet rs = pstmt.getGeneratedKeys();
            // set the result set to the first row
            rs.next();

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

            // retrieve and display specified column value
            String bookTitle = rs.getString(1);
            String bookAuthor = rs.getString(2);
            int identityCol = rs.getInt(3);
            System.out.println(" Book Title :           " + bookTitle);
            System.out.println(" Book Author:           " + bookAuthor);
            System.out.println(" Identity Column Value: " + identityCol);
        }
        finally
        {
            // Close the statement
            pstmt.close();
            System.out.println("\n PreparedStatement object closed.\n");
        }
    }
} // End class T20204JD