//*********************************************************************
//
//                 Copyright (c) 2004-2008 by Teradata Corporation
//                         All Rights Reserved
//
//*********************************************************************
//
//  File:       T20701JD.java
//  Header:     none
//  Purpose:    Demonstrate basic Teradata SQL using the JDBC API by
//              creating/executing multi-statement requests containing
//              SELECT statements only.
//              The program will:
//                -  Connect as user guest/please
//                -  Perform a multi-statement request
//                -  Obtain and display the results
//                -  Disconnect.
//
//  JDBC API:  java.sql.Connection,
//             java.sql.Statement,
//             java.sql.Statement.execute,
//             java.sql.Statement.getUpdateCount,
//             java.sql.Statement.getResultSet,
//             java.sql.Statement.getMoreResults,
//             java.sql.ResultSet
//
//  Version:   Updated for Teradata V2R6
//
//*********************************************************************

import java.sql.*;

public class T20701JD
{
    // Name of the user able to create, drop, and manipulate 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";

        // SQL statements to be processed as part of the
        // multi-statement request
        String sMultiSelect = "SELECT empID, empName " +
                              "FROM employee ORDER BY empID" +
                              ";" +
                              "SELECT empDept, empName, empID " +
                              "FROM employee ORDER BY empDept, empName" +
                              ";" +
                              "SELECT empName, empDept, empJob " +
                              "FROM employee ORDER BY empName";

        try
        {
            System.out.println("\n Sample T20701JD: \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
            {
                // Creating a statement object from an active connection
                Statement stmt = con.createStatement();
                System.out.println(" Statement object created. \n");

                try
                {
                    // The following code will demonstrate execution of a
                    // a multi-statement request consisting of SELECT statements
                    // only. Please note that only the Statement.execute method
                    // can be used, for the update batching method does not
                    // return ResultSet objects.

                    System.out.println(" Performing a multi-statement request...");
                    // use Statement.execute method to execute a multi-statement
                    // request. Only result sets are expected back.
                    // As before, use getResultSet or getUpdateCount to
                    // retrieve the result, and getMoreResults to move to any
                    // subsequent results.
                    boolean result = stmt.execute(sMultiSelect);

                    if(!result)
                    {
                        // If an update count or nothing is returned - error
                        throw new IllegalStateException(
                            "Unexpected results: ResultSet expected.");
                    }
                    else
                    {
                        // Retrieve the first result
                        ResultSet rs = stmt.getResultSet();
                        // Display the result of the first query
                        System.out.println("\n Displaying results of the first "
                                           + "query:\n");
                        int rowCount = 0;
                        while(rs.next())
                        {
                            rowCount ++;
                            System.out.println(" Row " + rowCount + " : ");
                            System.out.println(" Column empID : " +
                                               rs.getInt(1));
                            System.out.println(" Column empName : " +
                                               rs.getString(2));
                        }
                        System.out.println("\n " + rowCount + " row(s) returned.");

                        // Advance to next result.
                        // If an update count or nothing is returned - error.
                        if(!stmt.getMoreResults())
                            throw new IllegalStateException(
                                "Unexpected results: ResultSet expected.");

                        // Retrieve the second result
                        rs = stmt.getResultSet();
                        System.out.println("\n Displaying results of the" +
                                           " second query:\n");
                        rowCount = 0;  // reset row counter
                        while(rs.next())
                        {
                            rowCount ++;
                            System.out.println(" Row " + rowCount + " : ");
                            System.out.println(" Column empDept : " +
                                               rs.getString(1));
                            System.out.println(" Column empName : " +
                                               rs.getString(2));
                            System.out.println(" Column empID : " +
                                               rs.getInt(3));
                        }
                        System.out.println("\n " + rowCount + " row(s) returned.");

                        // Advance to next result.
                        // If an update count or nothing is returned - error.
                        if(!stmt.getMoreResults())
                            throw new IllegalStateException(
                                "Unexpected results: ResultSet expected.");

                        // Retrieve the third result
                        rs = stmt.getResultSet();
                        System.out.println("\n Displaying results of the" +
                                           " third query:\n");
                        rowCount = 0;  // reset row counter
                        while(rs.next())
                        {
                            rowCount ++;
                            System.out.println(" Row " + rowCount + " : ");
                            System.out.println(" Column empName : " +
                                               rs.getString(1));
                            System.out.println(" Column empDept : " +
                                               rs.getString(2));
                            System.out.println(" Column empJob : " +
                                               rs.getString(3));
                        }
                        System.out.println("\n " + rowCount + " row(s) returned.");
                        // If more results are present after processing of the
                        // first three queries, an error has occured.
                        if(!((stmt.getMoreResults() == false)
                        && (stmt.getUpdateCount() == -1)))
                            throw new IllegalStateException(
                                "Unexpected results: more results present.");

                        System.out.println("\n Multi-statement request "
                                           + "successfully completed .\n");
                    }
                }
                finally
                {
                    // Close the statement
                    stmt.close();
                    System.out.println(" Statement object closed. \n");
                }
            }
            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 T20701JD finished.");
        }
        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
} // End class T20701JD