//*********************************************************************
//
//                 Copyright (c) 2005-2008 by Teradata Corporation
//                         All Rights Reserved
//
//*********************************************************************
//
//  File:       T21600JD.java
//  Header:     none
//  Purpose:    Demonstrate how to use JDBC escape clauses.
//
//              The program will:
//
//                -  Connect as user guest/please
//
//                -  Drop the following tables if they exist:
//                      escape_clauses
//                      escape_join
//
//                -  Create table escape_clauses and escape_join with
//                   the following columns:
//                      colIndex SMALLINT,
//                      colSmallInt SMALLINT,
//                      colFloat FLOAT,
//                      colChar1 CHAR(10)
//                      colChar2 CHAR(32),
//                      colDate DATE,
//                      colTimeStamp TIMESTAMP(0),
//                      colTime TIME(0)
//
//                -  Create table escape_join with the following columns:
//                     colIndex smallint,
//                     colSmallInt smallint
//
//                -  Insert data using date literals and scalar functions with
//                   java.sql.Statement.executeUpdate
//
//                -  Select data using JDBC LIKE predicate escape characters with
//                   java.sql.Statement.executeQuery
//
//                -  Select data using JDBC outer join escape clause with
//                   java.sql.Statement.executeQuery
//
//                -  Disconnect.
//
//  JDBC API: java.sql.Connection, java.sql.Statement,java.sql.Statement.executeQuery,
//            java.sql.Statement.executeUpdate
//
//  Version: Updated for Teradata V2R61
//
//*********************************************************************

import java.sql.*;

public class T21600JD
{
    // 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";
        String sDropTbl1 = "DROP TABLE escape_clauses";
        String sDropTbl2 = "DROP TABLE escape_join";

        // Statements used in table creation
        String sCreateTbl1 = "CREATE TABLE escape_clauses("
            + "colIndex SMALLINT,"
            + "colSmallInt SMALLINT,"
            + "colFloat FLOAT,"
            + "colChar1 CHAR(10),"
            + "colChar2 CHAR(32),"
            + "colDate DATE,"
            + "colTimeStamp TIMESTAMP(0),"
            + "colTime TIME(0))";
        String sCreateTbl2 =
            "CREATE TABLE escape_join( colIndex SMALLINT, colSmallInt SMALLINT)";

        try
        {
            Statement  stmt = null;
            Connection con  = null;

            System.out.println("\n Sample T21600JD: \n");
            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
            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.
                stmt = con.createStatement();

                try
                {
                    // Cleanup procedures:
                    // If the sample table escape_clauses already exists, drop it.
                    try
                    {
                        System.out.println(" Dropping table if present: "
                                           + sDropTbl1);

                        stmt.executeUpdate(sDropTbl1);
                        System.out.println(" Table dropped.\n");
                        // Close the Statement
                        stmt.close();
                    }
                    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);
                        System.out.println(" Table could not be dropped."
                                           + " Execution will continue...\n");
                    }

                    // If the sample table  escape_join already exists, drop it.
                    try
                    {
                        System.out.println(" Dropping table if present: "
                                           + sDropTbl2);

                        stmt.executeUpdate(sDropTbl2);
                        System.out.println(" Tables dropped.\n");
                        // Close the Statement
                        stmt.close();
                    }
                    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);
                        System.out.println(" Table could not be dropped."
                                           + " Execution will continue...\n");
                    }


                    // Create the sample tables
                    System.out.println(" Creating table: " + sCreateTbl1);
                    stmt.executeUpdate(sCreateTbl1);
                    System.out.println(" Creating table: " + sCreateTbl2);
                    stmt.executeUpdate(sCreateTbl2);
                    System.out.println(" Sample tables created. \n");

                    // Demonstrate JDBC escape clauses
                    System.out.println(" Loading table data ...");

                    stmt = con.createStatement();

                    stmt.setEscapeProcessing(true);

                    // Use CONCAT scalar function and date literals
                    System.out.println(
                        "\n Insert STRING using  CONCAT scalar function ");
                    System.out.println(" Insert data using date literals ");
                    stmt.executeUpdate(
                        "INSERT INTO escape_clauses VALUES("
                        + "1, 10101, 2.6, 'first', "
                        + "{fn CONCAT('_first ', 'element ')}, "
                        + "{d '1997-10-16'},  {ts '1997-10-16 11:11:11'}, "
                        + "{t '11:11:11'})");

                    // Use the following scalar functions:
                    //  LTRIM
                    //  RTRIM
                    //  CURDATE
                    //  NOW
                    //  CURTIME
                    System.out.println(
                        "\n Insert STRINGS using scalar functions LTRIM and RTRIM ");
                    System.out.println(
                        " Insert DATE with the scalar function CURDATE ");
                    System.out.println(
                        " Insert TIMESTAMP with the scalar function NOW ");
                    System.out.println(
                        " Insert TIME with the scalar function  CURTIME ");
                    stmt.executeUpdate(
                        "INSERT INTO escape_clauses VALUES ("
                        + "2, 2014, 4.7, { fn LTRIM( '    second')}, "
                        + "{fn RTRIM( 'second element    ')}, "
                        + "{fn CURDATE()}, {fn NOW() }, { fn CURTIME()})");

                    // Use the following scalar functions:
                    //  SUBSTRING
                    //  UCASE
                    //  CONVERT
                    //  CONVERT
                    System.out.println(
                        "\n Insert STRING 'third' using scalar function SUBSTRING ");
                    System.out.println(
                        " Make column value upper case with the scalar "
                        + "function UCASE ");
                    System.out.println(
                        " Cast current TIMESTAMP to the DATE column with the scalar "
                        + "function CONVERT ");
                    System.out.println(
                        " Cast TIMESTAMP to the TIME column with the scalar "
                        + "function CONVERT ");
                    stmt.executeUpdate(
                        "INSERT INTO escape_clauses VALUES ("
                        + "3, 30302, 5.2, {fn SUBSTRING('third element',1, 5)},"
                        + "{fn UCASE( '    third element')}, "
                        + "{fn CONVERT({fn now()}, date)}, "
                        + "{ts '1999-05-30 02:30:15'}, "
                        + "{fn CONVERT({fn now()}, time(0))})");

                    // Use the following scalar functions:
                    //  TIMESTAMPADD
                    //  ABS
                    System.out.println(
                        "\n Make column value positive with the scalar "
                        + "function ABS ");
                    System.out.println(
                        " Add a year to the current date with scalar "
                        + "function TIMESTAMPADD ");
                    stmt.executeUpdate(
                        "INSERT INTO escape_clauses VALUES ("
                        + "4, 10101, {fn ABS(-2.7) }, 'fourth','_fourth element', "
                        + "{d '1997-10-16'}, "
                        + "{fn TIMESTAMPADD(SQL_TSI_YEAR, 1, {fn NOW() })}, "
                        + "{t '11:11:11'})");

                   // Close the Statement
                   stmt.close();

                    // Display results
                    System.out.println("\n Displaying ResultSet: \n");

                    ResultSet rs = stmt.executeQuery(
                        "SELECT * FROM escape_clauses ORDER BY 1");

                    while (rs.next())
                    {
                        System.out.println(" " + rs.getInt(1) + " "
                                               + rs.getFloat(3) + " "
                                               + rs.getString(4) + " "
                                               + rs.getString(5) + " "
                                               + rs.getDate(6) + " "
                                               + rs.getTimestamp(7) + " "
                                               + rs.getTime(8));
                    }

                    rs.close();
                   // Close the Statement
                   stmt.close();

                    // Use LIKE predicate escape characters
                    System.out.println(
                        "\n Select data using LIKE predicate escape characters ");

                    rs = stmt.executeQuery(
                        "SELECT colIndex, colChar2 FROM escape_clauses  "
                        + " WHERE colChar2 LIKE 'Z_%' {escape 'Z'} ORDER BY 1");

                    // Display results
                    System.out.println("\n Displaying ResultSet: \n");

                    while (rs.next())
                    {
                        System.out.println(" " + rs.getInt(1) + " "
                            + rs.getString(2));
                    }

                    rs.close();
                    // Close the Statement
                    stmt.close();

                    // Insert data for join table
                    System.out.println("\n Insert data for join table ");
                    stmt.executeUpdate("INSERT INTO escape_join VALUES (1, 10101)");
                    stmt.executeUpdate("INSERT INTO escape_join VALUES (2, 20101)");
                    stmt.executeUpdate("INSERT INTO escape_join VALUES (3, 30101)");

                    // Use outer join escape clause
                    System.out.println(
                        " Select data using outer join escape clause ");

                    rs = stmt.executeQuery(
                        "SELECT escape_clauses.colIndex, escape_clauses.colSmallInt, "
                        + "escape_join.colIndex FROM {oj escape_clauses "
                        + "LEFT OUTER JOIN escape_join ON "
                        + "escape_join.colSmallInt = escape_clauses.colSmallInt} "
                        + "ORDER BY 1");

                    // Display results
                    System.out.println("\n Displaying ResultSet: \n");

                    while (rs.next())
                    {
                        System.out.println(" " + rs.getInt(1) + " " + rs.getInt(2)
                            + " " + rs.getInt(3));
                    }

                    rs.close();
                }
                finally
                {
                   // Close the statement
                    stmt.close();
                    System.out.println("\n 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 T21600JD 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 T21600JD