//*********************************************************************
//
//                 Copyright (c) 2016 by Teradata Corporation
//                         All Rights Reserved
//
//*********************************************************************
//
//  File:       T21509JD.java
//  Header:     none
//  Purpose:    Demonstrate setting question-mark parameter markers to DATASET
//              STORAGE FORMAT AVRO data types using a structure.
//
//              This shows how to insert, select and utilize the DATASET DBS
//              functions with the DATASET STORAGE FORMAT AVRO values.
//
//              The DATASET STORAGE FORMAT AVRO data type was introduced in Teradata 
//              Database 16.0. The DATASET STORAGE FORMAT AVRO data type stores text 
//              as a BLOB. An AVRO value is sent to the Teradata Database
//              using a Struct containing a byte array, an InputStream attribute.
//              The DATASET AVRO values are returned from the Teradata Database as BLOB values.
//
//              The program will:
//                -  Connect as user guest/please
//                -  Create a table with DATASET STORAGE FORMAT AVRO columns
//                -  Insert the DATASET AVRO values using Structs
//                -  Select from the table and combine one of the AVRO columns
//                   with a parameter marker set to a AVRO value
//                -  Display the results
//                -  Disconnect.
//
//  JDBC API: java.sql.Connection, java.sql.PreparedStatement, java.sql.Struct,
//            java.sql.PreparedStatement.setObject
//
//  Note: When setting a Avro parameter to NULL, create the Struct with the
//        name of Avro and a single null attribute.
//
//*********************************************************************

import java.sql.* ;
import java.io.* ;

public class T21509JD
{
    public static void main (String [] args)
    throws ClassNotFoundException
    , IOException
    {
        String url = "jdbc:teradata://whomooz/TMODE=ANSI,CHARSET=UTF8" ; 
        String sUser = "guest" ;
        String sPassword = "please" ;

        // Table creation command and its columns
        String sCreateTable =
            "CREATE TABLE myAvroTable (ID INTEGER, AvroCol DATASET STORAGE FORMAT AVRO)" ;

        String sDropTbl = "DROP TABLE myAvroTable" ;
        String sInsert = "INSERT INTO myAvroTable VALUES (?, ?)" ;
        String sSelect = "SELECT ID, AvroCol.getSchema(), AvroCol.toJSON() FROM myAvroTable" ;
        String sAvroCheck = "SELECT AVRO_CHECK (?)" ;

        try
        {
            System.out.println ("\n Sample T21509JD: \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
                {
                    // Cleanup table
                    // If the sample table already exists, drop it.
                    try
                    {
                        System.out.println(" Dropping table if present: "
                                           + sDropTbl);
                        stmt.executeUpdate(sDropTbl);
                        System.out.println(" Table dropped.\n");
                    }
                    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");
                    }

                    System.out.println (" Attempting to create a sample table : "
                                       + sCreateTable) ;
                    // Sending the create table request to the database,
                    stmt.executeUpdate (sCreateTable) ;
                    System.out.println (" Table created successfully.") ;

                    // Before inserting the Avro data into the table, check it for validity.
                    PreparedStatement pStmt = con.prepareStatement (sAvroCheck) ;
                    try
                    {
                        System.out.println ("\n Check Avro value for validity before inserting it into the table ...") ;

                        // Specify the DATASET STORAGE FORMAT AVRO that will be checked.
                        // A byte array may be used instead and no second attribute is required.

                        // Create a new file object to enable access to the
                        // Avro document

                        File avroFile = new File("sampledata.avro");

                        // Create a new file input stream object to enable
                        // reading of data from the file
                        FileInputStream avroStream = new FileInputStream(avroFile);

                        pStmt.setObject (1, new SampStruct ("DATASET STORAGE FORMAT AVRO",
                            new Object[] {avroStream, new Integer ((int) avroFile.length())})) ;

                        ResultSet rs = pStmt.executeQuery () ;
                        try
                        {
                            rs.next () ;

                            // Displaying results from check
                            System.out.println (" Select successful.") ;
                            System.out.println ("\n Displaying validation results  ") ;
                            System.out.println (" -----------------") ;
                            System.out.println (" Result of validity check on Avro value:  " + rs.getObject (1)) ;
                        }
                        finally
                        {
                            rs.close () ;
                        }
                    }
                    finally
                    {
                        // Close the Prepared Statement
                        pStmt.close () ;
                        System.out.println ("\n Prepared Statement object closed. \n") ;
                    }

                    // Inserting a row of data into the sample table. The
                    // insert request is first prepared and then executed.
                    pStmt = con.prepareStatement (sInsert) ;
                    try
                    {
                        // Setting parameter value
                        pStmt.setInt (1, 1) ;

                        // To set Avro parameter markers, use a Struct with a Avro
                        // attribute value. The value can be set using a single String
                        // atribute or a Reader attribute. If a Reader is used,
                        // the length must be specified as the second attribute.

                        // Create a new file object to enable access to the
                        // Avro document
                        File avroFile = new File("sampledata.avro");

                        // Create byte array to read the data
                        byte [] aby = new byte [(int) avroFile.length ()] ;

                        // Create a new file input stream object to enable
                        // reading of data from the file
                        FileInputStream avroStream = new FileInputStream (avroFile) ;
                        try
                        {
                            avroStream.read (aby, 0, aby.length) ;
                        }
                        finally 
                        {
                            avroStream.close () ;
                        }

                        // The InputStream could have been used as the first attribute, but
                        // then a second attribute indicating the length would be required.
                        pStmt.setObject (2, new SampStruct ("DATASET STORAGE FORMAT AVRO",
                            new Object[] {aby})) ;

                        // Excuting insert statement
                        System.out.println ("\n Inserting Data ...") ;
                        pStmt.executeUpdate () ;
                        System.out.println (" Insert Successful.") ;
                    }
                    finally
                    {
                        // Close the Prepared Statement
                        pStmt.close () ;
                        System.out.println ("\n Prepared Statement object closed. \n") ;
                    }

                    System.out.println ("\n Selecting Data ...") ;
                    ResultSet rs = stmt.executeQuery (sSelect) ;
                    try
                    {
                        rs.next () ;

                        // Displaying results from select
                        System.out.println (" Select successful.") ;
                        System.out.println ("\n Displaying output of the data  ") ;
                        System.out.println (" -----------------") ;
                        System.out.println (" ID                   :  " + rs.getObject (1)) ;
                        System.out.println (" AvroCol.getSchema()  :  " + clobToString (rs.getClob (2))) ;
                        System.out.println (" AvroCol.toJSON()     :  " + clobToString (rs.getClob (3))) ;
                    }
                    finally
                    {
                        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 T21509JD 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

    // Used to convert the clob to a String
    private static String clobToString (Clob clob)
    throws SQLException
    , IOException
    {
        if (clob == null)
            return null ;

        // In JDK 5.0 and later, consider using StringBuilder instead of StringBuffer
        StringBuffer sb = new StringBuffer () ;

        BufferedReader br = new BufferedReader (clob.getCharacterStream ()) ;
        try
        {
            String sLine ;
            while (null != (sLine = br.readLine ()))
                sb.append (sLine) ;
        }
        finally
        {
            br.close () ;
        }

        return sb.toString () ;

    } // End clobToString

    // This class is used instead of calling Connection.createStruct, which is
    // only supported in JDK 6.0 or later
    public static class SampStruct implements Struct
    {
        private Object [] m_attributes ;
        private String m_sqlTypeName ;

        public SampStruct (String sqlTypeName, Object[] attributes)
        {
            m_sqlTypeName = sqlTypeName ;
            m_attributes = attributes ;
        }

        // Returns attributes
        public Object [] getAttributes () throws SQLException
        {
            return m_attributes ;
        }

        // Returns SQLTypeName
        public String getSQLTypeName () throws SQLException
        {
            return m_sqlTypeName ;
        }

        // This method is not supported, but needs to be included
        public Object [] getAttributes (java.util.Map map) throws SQLException
        {
            // Unsupported Exception
            throw new SQLException ("getAttributes (Map) NOT SUPPORTED") ;
        }
    } // End of class SampStruct

} // End class T21509JD