//*********************************************************************
//
//                 Copyright (c) 2006-2008 by Teradata Corporation
//                         All Rights Reserved
//
//*********************************************************************
//
//  File:       T21800JD.java
//  Header:     none
//  Purpose:    Demonstrate how to use JDBC updatable result set using
//              a non-prepared Statement obtained from the method
//              Connection.createStatement(int type, int concurrency).
//
//              The program will:
//                -  Connect as user guest/please
//
//                -  If sample tables for this program exist,
//                   drop the following tables:
//                      updatableRS_jointable1
//                      updatableRS_jointable2
//
//                -  Create the following sample tables:
//                   updatableRS_jointable1 with the following columns:
//                      colIndex1 INT not null primary key,
//                      colByteInt BYTEINT,
//                      colSmallInt SMALLINT,
//                      colInt INT,
//                      colReal REAL,
//                      colFloat FLOAT,
//                      colDoublePrecision DOUBLE PRECISION,
//                      colDecimal DECIMAL(18,2),
//                      colNumeric NUMERIC,
//                      colChar CHAR(100),
//                      colVarChar VARCHAR(100),
//                      colByte BYTE(30),
//                      colVarByte VARBYTE(13)
//
//                   updatableRS_jointable2 with the following columns:
//                      colIndex2 INT not null primary key,
//                      colDate DATE,
//                      colTime TIME,
//                      colTimestamp TIMESTAMP
//
//                -  Insert data into these two sample tables with
//                   java.sql.PreparedStatement.executeUpdate
//
//                -  Create a Statement with the following parameters:
//                      java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,
//                      java.sql.ResultSet.CONCUR_UPDATABLE
//
//                -  Statement.executeQuery with following joined query:
//                      select * from updatableRS_jointable1 t1 join
//                      updatableRS_jointable2 t2 on t1.colIndex1=t2.colIndex2
//                      order by t1.colIndex1;
//
//                -  Call the updater methods to update column values in
//                   the current row:
//                   java.sql.ResultSet.updateXXX
//
//                -  Call ResultSet.refreshRow() to clear the new contents
//                   of the current row of this ResultSet object
//                   java.sql.ResultSet.refreshRow
//
//                -  Call the updater methods to update column values in
//                   the current row:
//                   java.sql.ResultSet.updateXXX
//
//                -  Call ResultSet.updateRow() to Update the underlying
//                   database with the new contents of the current row of
//                   this ResultSet object
//                   java.sql.ResultSet.updateRow
//
//                -  Call ResultSet.moveToInsertRow() to move the cursor to
//                   the insert row
//                   java.sql.ResultSet.moveToInsertRow
//
//                -  Call the updater methods to update column values in
//                   the insert row:
//                   java.sql.ResultSet.updateXXX
//
//                -  Call ResultSet.insertRow() to insert the contents of
//                   the insert row into the database
//                   java.sql.ResultSet.insertRow
//
//                -  Call ResultSet.moveToCurrentRow() to move the cursor back
//                   to the remembered cursor position, usually the current row
//                   java.sql.ResultSet.moveToCurrentRow
//
//                -  Call ResultSet.deleteRow() to delete the current row
//                   java.sql.ResultSet.deleteRow
//
//                -  Disconnect.
//
//  JDBC API: java.sql.Connection, java.sql.Statement,
//            java.sql.Statement.executeQuery, java.sql.ResultSet.updateRow,
//            java.sql.ResultSet.insertRow, java.sql.ResultSet.deleteRow
//            java.sql.ResultSet.refreshRow, java.sql.ResultSet.moveToInsertRow
//            java.sql.ResultSet.moveToCurrentRow
//
//  Version: Updated for Teradata V2R6.2
//
//*********************************************************************

import java.sql.*;

public class T21800JD
{
    // Name of the user able to create, drop, and manipulate tables
    public static String sUser = "guest";
    public static String sPassword = "please";
    // object data arrays for populating sample tables
    public static Object data[] = null;
    public static Object updateData1[] = null;
    public static Object updateData2[] = null;

    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";
        // Strings used in table dropping
        String sDropTbl1 = "DROP TABLE updatableRS_jointable1";
        String sDropTbl2 = "DROP TABLE updatableRS_jointable2";

        // Strings used in table creation
        String sCreateTbl1 = "CREATE TABLE updatableRS_jointable1(" +
                                "colIndex1 INT not null primary key, " +
                                "colByteInt BYTEINT, " +
                                "colSmallInt SMALLINT, " +
                                "colInt INT, " +
                                "colReal REAL, " +
                                "colFloat FLOAT, " +
                                "colDoublePrecision DOUBLE PRECISION, " +
                                "colDecimal DECIMAL(18,2), " +
                                "colNumeric NUMERIC, " +
                                "colChar CHAR(100), " +
                                "colVarChar VARCHAR(100), " +
                                "colByte BYTE(30), " +
                                "colVarByte VARBYTE(13))";
        String sCreateTbl2 = "CREATE TABLE updatableRS_jointable2(" +
                                "colIndex2 INT not null primary key, " +
                                "colDate DATE, " +
                                "colTime TIME, " +
                                "colTimestamp TIMESTAMP)";
        // Strings used in data insertion
        String sInsertData1 = "INSERT INTO updatableRS_jointable1(" +
                                "colIndex1, " +
                                "colByteInt, " +
                                "colSmallInt, " +
                                "colInt, " +
                                "colReal, " +
                                "colFloat, " +
                                "colDoublePrecision, " +
                                "colDecimal, " +
                                "colNumeric, " +
                                "colChar, " +
                                "colVarChar, " +
                                "colByte, " +
                                "colVarByte) " +
                                "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)";
        String sInsertData2 = "INSERT INTO updatableRS_jointable2(" +
                                "colIndex2, " +
                                "colDate, " +
                                "colTime, " +
                                "colTimestamp) " +
                                "VALUES(?,?,?,?)";
        // String used in data selection from joined tables
        String sSelectJoinedData =
            "SELECT * FROM updatableRS_jointable1 t1 " +
            "JOIN updatableRS_jointable2 t2 ON t1.colIndex1 = t2.colIndex2 " +
            "ORDER BY t1.colIndex1";
        // Populating object data arrays
        data = getData();
        updateData1 = getUpdateData1();
        updateData2 = getUpdateData2();

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

            System.out.println("\n Sample T21800JD: \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
            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 tables already exists, drop them.
                    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 thrown "no table exists" exception
                        System.out.println(" Drop table exception ignored: " + ex);
                        System.out.println(" Table could not be dropped."
                                           + " Execution will continue...\n");
                    }

                    try
                    {
                        System.out.println(" Dropping table if present: "
                                           + sDropTbl2);

                        stmt.executeUpdate(sDropTbl2);
                        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 thrown "no table exists" 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");
                    // Close the Statement
                    stmt.close();

                    System.out.println(" Inserting data into sample tables...");
                    // insert data into table 1
                    PreparedStatement pStmt1 = null;
                    pStmt1 = con.prepareStatement(sInsertData1);
                    int j = 1;
                    pStmt1.setInt(j++, 1); //colIndex1
                    pStmt1.setByte(j++, ((Byte) data[0]).byteValue()); //colByteInt
                    pStmt1.setShort(j++,
                        ((Short) data[1]).shortValue()); //colSmallInt
                    pStmt1.setInt(j++, ((Integer) data[2]).intValue()); //colInt
                    pStmt1.setLong(j++, ((Long) data[3]).longValue()); //colReal
                    pStmt1.setFloat(j++, ((Double) data[4]).floatValue()); //colFloat
                    pStmt1.setDouble(j++,
                        ((Double) data[5]).doubleValue()); //colDoublePrecision
                    pStmt1.setBigDecimal(j++,
                        (java.math.BigDecimal) data[6]); //colDecimal
                    pStmt1.setInt(j++, ((Integer) data[7]).intValue()); //colNumeric
                    pStmt1.setString(j++, (String) data[8]); //colChar
                    pStmt1.setString(j++, (String) data[9]); //colVarChar
                    pStmt1.setBytes(j++, (byte[]) data[10]); //colByte
                    pStmt1.setBytes(j++, (byte[]) data[11]); //colVarByte
                    if (pStmt1.executeUpdate() == 0) {
                        System.out.println(" No data inserted into sample table 1");
                        throw new IllegalStateException(
                            "Data insertion into table 1 failed");
                    }
                    else
                        System.out.println(" Data inserted into sample table 1.");
                    pStmt1.close();

                    // insert data into table 2
                    PreparedStatement pStmt2 = null;
                    pStmt2 = con.prepareStatement(sInsertData2);
                    int k = 1;
                    pStmt2.setInt(k++, 1); //colIndex2
                    pStmt2.setDate(k++, (Date) data[12]); //colDate
                    pStmt2.setTime(k++, (Time) data[13]); //colTime
                    pStmt2.setTimestamp(k++, (Timestamp) data[14]); //colTimestamp
                    if (pStmt2.executeUpdate() == 0) {
                        System.out.println(" No data inserted into sample table 2.");
                        throw new IllegalStateException(
                            "Data insertion into table 2 failed");
                    }
                    else
                        System.out.println(" Data inserted into sample table 2.");
                    pStmt2.close();

                    ResultSet rs = null;
                    System.out.println(
                        " Selecting data from joined sample tables...");
                    stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                               ResultSet.CONCUR_UPDATABLE);
                    rs = stmt.executeQuery(sSelectJoinedData);
                    System.out.println(" Result set is fetched from database.");
                    // move the cursor to the first row
                    rs.next();
                    // Call the updater methods to update column values in the
                    // current row
                    System.out.println(
                        " Calling updater methods to update initial new" +
                        " contents on the current row...");
                    updateRowData1(rs);
                    System.out.println(
                        " Initial new contents updated for the current row.");
                    // Call refreshRow() to clear the new contents of the current row
                    System.out.println(
                        " Calling refreshRow() to clear the new contents on...");
                    rs.refreshRow();
                    System.out.println(
                        " Initial new contents on the current row cleared.");
                    // Call the updater methods again to update column values in the
                    // current row
                    System.out.println(
                        " Calling updater methods again to update new" +
                        " contents on the current row...");
                    updateRowData2(rs);
                    System.out.println(
                        " New contents updated again for the current row.");
                    // Call updateRow() to Update the underlying database with the
                    // new contents of the current row of this ResultSet object
                    System.out.println(
                        " Calling updateRow() to update the current row...");
                    rs.updateRow();
                    System.out.println(" The current row updated.");
                    // Call moveToInsertRow() to move the cursor to the insert row
                    System.out.println(
                        " Calling moveToInsertRow() to move the cursor" +
                        " to the insert row...");
                    rs.moveToInsertRow();
                    System.out.println(" The cursor moved to the insert row.");
                    // Call the updater methods to update column values in the
                    // insert row
                    System.out.println(" Calling updater methods to update new" +
                                       " contents on the insert row...");
                    insertRowData(rs);
                    System.out.println(" New contents updated for the insert row.");
                    // Call insertRow() to insert the contents of the insert row
                    // into database
                    System.out.println(" Calling insertRow() to insert a new row...");
                    rs.insertRow();
                    System.out.println(" A second row inserted.");
                    // Call moveToCurrentRow() to move the cursor back to the
                    // remembered cursor position, usually the current row
                    System.out.println(
                        " Calling moveToCurrentRow() to move the cursor" +
                        " back to the current row...");
                    rs.moveToCurrentRow();
                    System.out.println(" The cursor moved back to the current row.");
                    // Call deleteRow() to delete the current row
                    System.out.println(
                        " Calling deleteRow() to delete the first row...");
                    rs.deleteRow();
                    System.out.println(" The first row deleted.");
                    // Close the result set
                    rs.close();
                }
                finally
                {
                   // Close the statement
                    stmt.close();
                    System.out.println("\n Statement object closed. \n");
                }
            }
            finally
            {
                // Close the connection
                System.out.println("\n Closing connection to Teradata...");
                con.close();
                System.out.println(" Connection to Teradata closed. \n");
            }

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

    // Method for calling updater methods to update initial new contents on the
    // current row
    static void updateRowData1(ResultSet rs) throws SQLException
    {
        int j = 2; //skip the column colIndex1
        rs.updateByte(j++, ((Byte) updateData1[0]).byteValue()); //colByteInt
        rs.updateShort(j++, ((Short) updateData1[1]).shortValue()); //colSmallInt
        rs.updateInt(j++, ((Integer) updateData1[2]).intValue()); //colInt
        rs.updateLong(j++, ((Long) updateData1[3]).longValue()); //colReal
        rs.updateFloat(j++, ((Double) updateData1[4]).floatValue()); //colFloat
        rs.updateDouble(j++,
            ((Double) updateData1[5]).doubleValue()); //colDoublePrecision
        rs.updateBigDecimal(j++, (java.math.BigDecimal) updateData1[6]); //colDecimal
        rs.updateInt(j++, ((Integer) updateData1[7]).intValue()); //colNumeric
        rs.updateString(j++, (String) updateData1[8]); //colChar
        rs.updateString(j++, (String) updateData1[9]); //colVarChar
        rs.updateBytes(j++, (byte[]) updateData1[10]); //colByte
        rs.updateBytes(j++, (byte[]) updateData1[11]); //colVarByte
        j++; //skip the column colIndex2
        rs.updateDate(j++, (Date) updateData1[12]); //colDate
        rs.updateTime(j++, (Time) updateData1[13]); //colTime
        rs.updateTimestamp(j++, (Timestamp) updateData1[14]); //colTimestamp
    }

    // Method for calling updater methods to update new contents on the current row
    static void updateRowData2(ResultSet rs) throws SQLException
    {
        int j = 2; //skip the column colIndex1
        rs.updateByte(j++, ((Byte) updateData2[0]).byteValue()); //colByteInt
        rs.updateShort(j++, ((Short) updateData2[1]).shortValue()); //colSmallInt
        rs.updateInt(j++, ((Integer) updateData2[2]).intValue()); //colInt
        rs.updateLong(j++, ((Long) updateData2[3]).longValue()); //colReal
        rs.updateFloat(j++, ((Double) updateData2[4]).floatValue()); //colFloat
        rs.updateDouble(j++,
            ((Double) updateData2[5]).doubleValue()); //colDoublePrecision
        rs.updateBigDecimal(j++, (java.math.BigDecimal) updateData2[6]); //colDecimal
        rs.updateInt(j++, ((Integer) updateData2[7]).intValue()); //colNumeric
        rs.updateString(j++, (String) updateData2[8]); //colChar
        rs.updateString(j++, (String) updateData2[9]); //colVarChar
        rs.updateBytes(j++, (byte[]) updateData2[10]); //colByte
        rs.updateBytes(j++, (byte[]) updateData2[11]); //colVarByte
        j++; //skip the column colIndex2
        rs.updateDate(j++, (Date) updateData2[12]); //colDate
        rs.updateTime(j++, (Time) updateData2[13]); //colTime
        rs.updateTimestamp(j++, (Timestamp) updateData2[14]); //colTimestamp
    }

    // Method for calling updater methods to update contents on the insert row
    static void insertRowData(ResultSet rs) throws SQLException
    {
        int j = 1;
        rs.updateInt(j++, 2); //colIndex1
        rs.updateByte(j++, ((Byte) data[0]).byteValue()); //colByteInt
        rs.updateShort(j++, ((Short) data[1]).shortValue()); //colSmallInt
        rs.updateInt(j++, ((Integer) data[2]).intValue()); //colInt
        rs.updateLong(j++, ((Long) data[3]).longValue()); //colReal
        rs.updateFloat(j++, ((Double) data[4]).floatValue()); //colFloat
        rs.updateDouble(j++, ((Double) data[5]).doubleValue()); //colDoublePrecision
        rs.updateBigDecimal(j++, (java.math.BigDecimal) data[6]); //colDecimal
        rs.updateInt(j++, ((Integer) data[7]).intValue()); //colNumeric
        rs.updateString(j++, (String) data[8]); //colChar
        rs.updateString(j++, (String) data[9]); //colVarChar
        rs.updateBytes(j++, (byte[]) data[10]); //colByte
        rs.updateBytes(j++, (byte[]) data[11]); //colVarByte
        rs.updateInt(j++, 2); //colIndex2
        rs.updateDate(j++, (Date) data[12]); //colDate
        rs.updateTime(j++, (Time) data[13]); //colTime
        rs.updateTimestamp(j++, (Timestamp) data[14]); //colTimestamp
    }

    // Original data used to insert into sample tables
    // Also it is the insertion data when calling insertRow()
    static Object[] getData()
    {
        byte[] b1 = "testBinarytestBinarytestBinary".getBytes();
        byte[] b2 = "binary Column".getBytes();
        Object[] data = new Object[15];
        long ms = new Long("1081361243974").longValue();
        data[0] = new Byte("49");
        data[1] = new Short( (short) 79);
        data[2] = new Integer(199);
        data[3] = new Long((long) 1999999);
        data[4] = new Double((float) 2999999);
        data[5] = new Double((double) 959);
        data[6] = new java.math.BigDecimal("59.58");
        data[7] = new Integer(29);
        data[8] = new String("A");
        data[9] = new String("ABCDEFG");
        data[10] = b1;
        data[11] = b2;
        data[12] = new Date(ms);
        data[13] = new Time(ms);
        data[14] = new Timestamp(ms);

        return data;
    }

    // Initial update contents values before calling refreshRow()
    static Object[] getUpdateData1()
    {
        byte[] b1 = "initUpdateinitUpdateinitUpdate".getBytes();
        byte[] b2 = "initialupdate".getBytes();
        Object[] data = new Object[15];
        long ms = new Long("1081492779998").longValue();
        data[0] = new Byte("50");
        data[1] = new Short( (short) 80);
        data[2] = new Integer(200);
        data[3] = new Long((long) 2000000);
        data[4] = new Double((float) 3000000);
        data[5] = new Double((double) 960);
        data[6] = new java.math.BigDecimal("69.58");
        data[7] = new Integer(30);
        data[8] = new String("B");
        data[9] = new String("MNOPQRS");
        data[10] = b1;
        data[11] = b2;
        data[12] = new Date(ms);
        data[13] = new Time(ms);
        data[14] = new Timestamp(ms);

        return data;
    }

    // New update contents values when calling updateRow()
    static Object[] getUpdateData2() {
        byte[] b1 = "testUpdatetestUpdatetestUpdate".getBytes();
        byte[] b2 = "binary update".getBytes();
        Object[] data = new Object[15];
        long ms = new Long("1081682779998").longValue();
        data[0] = new Byte("51");
        data[1] = new Short( (short) 81);
        data[2] = new Integer(201);
        data[3] = new Long((long) 2000001);
        data[4] = new Double((float) 3000001);
        data[5] = new Double((double) 961);
        data[6] = new java.math.BigDecimal("70.58");
        data[7] = new Integer(31);
        data[8] = new String("C");
        data[9] = new String("TUVWXYZ");
        data[10] = b1;
        data[11] = b2;
        data[12] = new Date(ms);
        data[13] = new Time(ms);
        data[14] = new Timestamp(ms);

        return data;
    }
} // End class T21800JD