//*********************************************************************
//
//                 Copyright (c) 2005-2009 by Teradata Corporation
//                         All Rights Reserved
//
//*********************************************************************
//
//  File:       T21102JD.java
//  Header:     none
//  Purpose:    Demonstrate use of Transaction Isolation.
//
//              The program will attempt to read from a table while the table
//              is being populated. The test case uses a reader that is using a
//              transaction isolation level of TRANSACTION_READ_UNCOMMITTED.
//
//                - This program will create two connections for user guest/please,
//                  one for creating/populating the table isolationLevels and one
//                  for reading from that table.
//
//                - Ensure that the desired isolation level can be supported.
//
//                - Drop table isolationLevels if it exists.
//
//                - Create table isolationLevels with the following columns:
//                       c0 (integer),
//                       c1 (String)
//
//                - Populate the table.
//
//                - Insert a row into the table without committing it
//
//                - Show that the row can be read when using the transaction
//                  isolation level READ_UNCOMMITTED.
//
//                - Disconnect both connections.
//
//              The results of using TRANSACTION_READ_UNCOMMITTED can be
//              seen in the program output as follows:
//
//              Read operations do not have to wait for write operations to
//              be committed.
//
//  JDBC API: java.sql.Connection, java.sql.Statement, java.sql.DatabaseMetaData
//            java.sql.Statement.executeUpdate
//            java.sql.Connection.setTransactionIsolation
//            java.sql.DatabaseMetaData.supportsTransactionIsolationLevel
//
//  Version: Created for Teradata V2R6.1
//
//*********************************************************************

import java.sql.*;

public class T21102JD
{
    // 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, InterruptedException
    {
        try
        {
            // Creation of URL to be passed to the JDBC driver
            String url = "jdbc:teradata://whomooz/TMODE=ANSI,CHARSET=UTF8";

            // Statements used in table creation/selection
            String sDropTbl = "drop table isolationLevels";
            String sCreateTbl =
                "create table isolationLevels (c_index integer, c_value varchar(16))";
            String sQuerySel = "select * from isolationLevels order by 1";
            String sInsert = "INSERT INTO isolationLevels VALUES(10,'ocean')";

            System.out.println("\n Sample T21102JD:");

            // run the TRANSACTION_READ_UNCOMMITTED test
            System.out.println("\n Start Test case: TRANSACTION_READ_UNCOMMITTED");

            int transactionIsolationLevel = Connection.TRANSACTION_READ_UNCOMMITTED ;

            // Loading the Teradata JDBC driver
            System.out.println(" Looking for 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 for writing
            Connection conWrite = DriverManager.getConnection(url, sUser, sPassword);
            try
            {
                System.out.println(" User " + sUser + " connected.");
                System.out.println(" Writer Connection to Teradata established.");
                conWrite.setAutoCommit(false);

                // Creating a connection object for writing
                Connection conRead =
                    DriverManager.getConnection(url, sUser, sPassword);
                try
                {
                    System.out.println(
                        " Reader Connection to Teradata established. \n");

                    // Ensure that the Teradata Database can support the
                    // requested Transaction Isolation Level.
                    DatabaseMetaData dbmd = conRead.getMetaData();
                    if (!dbmd.supportsTransactionIsolationLevel(
                        transactionIsolationLevel))
                        throw new IllegalStateException(
                            "Cannot Support TransactionIsolationLevel: "
                            + transactionIsolationLevel);

                    // Set the Transaction Isolation level of the reader.
                    conRead.setTransactionIsolation(transactionIsolationLevel);

                    // Creating a statement object from an active connection.
                    Statement stmtWrite = conWrite.createStatement();
                    System.out.println(" Statement object created. \n");

                    try
                    {
                        // Cleanup procedure:
                        // If the sample table already exists, drop it.
                        try
                        {
                            System.out.println(
                                " Dropping table if present: " + sDropTbl);
                            stmtWrite.executeUpdate(sDropTbl);
                            System.out.println(" Table dropped.\n");
                            conWrite.commit();
                        }
                        catch (SQLException ex)
                        {
                            // If the table did not exist, no drop is required.
                            // Ignore the raised "no table present" exception by
                            // printing out the error message and swallowing the
                            // exception.
                            System.out.println(
                                " Drop table exception ignored: " + ex);
                        }

                        // Create the sample table
                        System.out.println(" Creating table: " + sCreateTbl);
                        stmtWrite.executeUpdate(sCreateTbl);
                        System.out.println(" Sample table created. \n");
                        conWrite.commit();

                        // Populate the table
                        System.out.println(" Writer: Inserting row");
                        stmtWrite.executeUpdate(sInsert);

                        // Retrieve and display the contents of the table
                        Statement stmtRd = conRead.createStatement();
                        try
                        {
                             System.out.println(
                                        "\n Reader: running query against table");

                            ResultSet rs = stmtRd.executeQuery(sQuerySel);
                            try
                            {
                                System.out.println(
                                        "\n Reader Displaying ResultSet: ");

                                while (rs.next())
                                    System.out.println(" " + rs.getString(1)
                                        + ", " + rs.getString(2));
                            }
                            finally
                            {
                                rs.close();
                            }
                        }
                        finally
                        {
                            stmtRd.close();
                        }

                        System.out.println("\n Writer: Committing");
                        conWrite.commit();

                    }
                    finally
                    {
                        stmtWrite.close();
                        System.out.println("\n Statement object closed. \n");
                    }
                }
                finally
                {
                    System.out.println(" Closing connection to Teradata...");
                    conRead.close();
                    System.out.println(" Connection to Teradata closed. \n");
                }
            }
            finally
            {
                System.out.println(" Closing connection to Teradata...");
                conWrite.close();
                System.out.println(" Connection to Teradata closed. \n");
            }

            System.out.println(
                "\n End Test case: TRANSACTION_READ_UNCOMMITTED\n\n\n");

            System.out.println(" Sample T21102JD 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

} // End class T21102JD