//*********************************************************************
//
//                 Copyright (c) 2004-2008 by Teradata Corporation
//                         All Rights Reserved
//
//*********************************************************************
//
//  File:       T20303JD.java
//  Header:     none
//  Purpose:    Demonstrate basic Teradata SQL using the JDBC API by
//              performing a SELECT query on the table using
//              parameter markers.
//              The program will:
//                -  Connect as user guest/please
//                -  Select a row from table employee2,
//                   retrieving LOB column values
//                -  Display the medatata associated with the
//                   column values of the selected row.
//                -  Disconnect.
//
//  JDBC API: java.sql.Connection, java.sql.PreparedStatement,
//            java.sql.PreparedStatement.executeQuery,
//            java.sql.ResultSet, java.sql.ResultSet.getMetaData,
//            java.sql.ResultSetMetaData
//
//  Version: Updated for Teradata V2R6
//
//*********************************************************************

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

public class T20303JD
{
    // Name of the user able to create and drop tables
    public static String sUser = "guest";
    public static String sPassword = "please";
    // Define maximum buffer size for reading in/writing out data
    private static final int BUFFERSIZE = 100;
    // Constant defining the number of initial bytes displayed to the user
    private static final int DISPLAYSIZE = 50;

    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";

        // Creation of the SQL SELECT statement and its parameter value
        String sSelect =
            "SELECT empName, empID, empResume, empPhoto, empRawPhotoData " +
            "FROM employee2 WHERE empDept = ? ORDER BY empName";
        String department = "Product Development";

        try
        {
            System.out.println("\n Sample T20303JD: \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
            {
                System.out.println(" Preparing this SQL statement for" +
                                   " execution:\n " + sSelect);
                // Creating a prepared statement object from an active connection
                PreparedStatement pstmt = con.prepareStatement(sSelect);
                System.out.println(" Prepared statement object created. \n");

                try
                {
                    // Set parameter values indicated by ? (dynamic update)
                    System.out.println(" Using setString() to bind value "
                                       + "to the parameter marker:");
                    pstmt.setString(1, department);
                    System.out.println(" FIRST ? set to: " + department);

                    // The following code will perform a SELECT query.
                    // Clob/Blob values will be retrieved from the database
                    // and written to the specified files.
                    // For those values of empPhoto which were inserted using
                    // binary literals, writing the information out into a jpeg
                    // format is meaningless. For these cases, a character
                    // representation of the binary data (in hex format)
                    // will be displayed instead and written into a text file.
                    // A given number of first bytes of data will be displayed,
                    // bounded from above by the DISPLAYSIZE constant.

                    // Submit a query, creating a result set object
                    ResultSet rs = pstmt.executeQuery();
                    // Display result set table column meta data
                    displayRSMetaData(rs);

                    // Extract and display result set table data
                    System.out.println();
                    System.out.println(" DISPLAYING RESULT SET DATA:");
                    System.out.println(" ---------------------------");

                    int rowCount = 0;
                    while(rs.next())
                    {
                        // Advance row counter
                        rowCount++;

                        // Extract column values
                        String name = rs.getString("empName");
                        int id = rs.getInt("empID");
                        Clob clob = rs.getClob("empResume");
                        Blob blob = rs.getBlob("empPhoto");
                        int isRaw =
                            new Byte(rs.getByte("empRawPhotoData")).intValue();
                        // Create file names for writing out BLOB/CLOB values.
                        // Refer to the note above for file format explanation.
                        String cfilename = id + "resumeT20303.txt";
                        String bfilename = id + "photoT20303.jpg";

                        System.out.println("\n ROW " + rowCount);
                        System.out.println(" ----------");
                        System.out.println(" COLUMN empName : " + name);
                        System.out.println(" COLUMN empID : " + id);
                        System.out.print(" COLUMN empResume : ");
                        createClobFile(clob, cfilename);
                        System.out.print(" COLUMN empPhoto : ");
                        createBlobFile(blob, bfilename, isRaw);
                        System.out.println(" COLUMN empRawPhotoData : " + isRaw);
                    }
                    System.out.println("\n " + rowCount + " Row(s) returned.");
                }
                finally
                {
                    // Close the statement
                    pstmt.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 T20303JD 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

    private static void displayRSMetaData(ResultSet rs) throws SQLException
    {
        // This code will demonstrate all available methods for
        // retrieving table column meta data.

        // Retrieve result set meta data to get information on the columns
        ResultSetMetaData rsmd = rs.getMetaData();
        // Retrieve the number of columns returned
        int colCount = rsmd.getColumnCount();
        System.out.println("\n DISPLAYING RESULT SET COLUMN META DATA:");
        System.out.println(" -----------------------------------------");
        System.out.println("\n The resulting table has " + colCount + " columns:");
        // For every column, display it's meta data.

        int i = 1; // Initialize loop counter
        while (i <= colCount)
        {
            // Demonstrating all methods for retrieving column meta data
            System.out.println();
            System.out.println(" Column " + i);
            System.out.println(" ------------ ");
            // Display the suggested column title for use in
            // printouts and displays
            System.out.println(" Column label: "
                               + rsmd.getColumnLabel(i));
            // Display the column name
            System.out.println(" Column name: "
                               + rsmd.getColumnName(i));
            // Display the SQL type of a column.
            System.out.println(" Column type: "
                               + rsmd.getColumnType(i));
            // Display the type name of a column
            System.out.println(" Column type name: "
                               + rsmd.getColumnTypeName(i));
            // Display information on whether NULL values are allowed
            System.out.println(" NULLs allowed: "
                               + rsmd.isNullable(i));
            // Display the normal maximum width of a column in characters.
            System.out.println(" Maximum character width: "
                               + rsmd.getColumnDisplaySize(i));
            // Display precision: the number of decimal digits
            // Note: default value is 0.
            System.out.println(" Column precision"
                               + " (number of decimal places): "
                               + rsmd.getPrecision(i));
            // Display the number of digits to the right of the
            // decimal point. Note: default value is 0.
            System.out.println(" Precision to the right of"
                               + " the decimal point: "
                               + rsmd.getScale(i));
            // Increment column counter
            i++;
        }
    } // end displayRSMetaData(...)


    private static void createClobFile(Clob clob, String filename)
        throws SQLException
    {
        // This function will read in the Clob data one character at a time
        // and write it to the file designated by the string 'filename'
        try
        {
            System.out.println("Clob of length = " + clob.length() + " bytes. ");
            // Display the first |DISPLAYSIZE| characters
            // Set the character data retrieval limit for the display of the
            // initial DISPLAYSIZE bytes and retrieve data.
            int dispsize = (int)(Math.min(clob.length(), (long)DISPLAYSIZE));
            String display = clob.getSubString(1, dispsize);
            System.out.println(" First " + DISPLAYSIZE + " bytes (if available): "
                               + display);

            // Create a file writer object to send clob data to a file
            FileWriter fos = new FileWriter(filename);
            // Create a character stream reader object to retrieve data
            Reader c = clob.getCharacterStream();

            // Initializing a buffer for reading in clob data
            char[] charBuffer = new char[BUFFERSIZE];
            // Initialize a dummy variable representing number of characters
            // read into the buffer
            int charCount = 0;
            // While data is available, read it into a buffer and read out
            // to the designated file
            while ( (charCount = c.read(charBuffer, 0, BUFFERSIZE)) != -1)
            {
                // Write out the retrieved character data
                fos.write(charBuffer, 0, charCount);
            }
            // Display success message
            System.out.println(" File creation completed successfully. Please "
                               + "refer to the following file: " + filename);
            // close file/data streams
            c.close();
            fos.close();
        }
        catch (IOException ex)
        {
            System.out.println(" Error writing Clob : " + ex.getMessage());
            ex.printStackTrace();
        }
    } // End createClobFile(...)


    private static void createBlobFile(Blob blob, String filename, int litDisplay)
    throws SQLException
    {
        // This function will read in the Blob data one byte at a time
        // and write it to the file designated by the string 'filename'.
        // A format in which the binary data will be stored to the file
        // will depend on the manner in which that data was inserted.
        // The litDisplay flag will be used to decide whether a literal
        // representation of the binary data is required, in which case,
        // this function will write the character representation of the binary data
        // (in hexadecimal format) into the file designated by the string 'filename'

        // If all parameter values are valid
        if ((litDisplay == 0) || (litDisplay == 1))
        {
            try
            {
                // Display blob zize information
                System.out.println(" Blob of length = " + blob.length() + " bytes.");
                // Display the first |DISPLAYSIZE| characters
                // Set the character data retrieval limit for the display of the
                // initial DISPLAYSIZE bytes and retrieve data
                int dispsize = (int)(Math.min(blob.length(), (long)DISPLAYSIZE));
                byte [] disp = blob.getBytes(1, dispsize);
                String dispStr = "";
                for (int i = 0; i < dispsize; i++)
                {
                    int val = disp[i] & 0xFF;
                    // Display the character representation of retrieved data
                    if (val < 16)
                    {    dispStr += '0' + Integer.toHexString(val).toUpperCase();}
                    else
                    {    dispStr += Integer.toHexString(val).toUpperCase();}
                }
                System.out.println(" First " + DISPLAYSIZE + " bytes (if available): "
                                   + dispStr);

                // If a conversion into character representation is required
                if (litDisplay == 1)
                {
                    writeBlobCharRep(blob, filename + ".txt");
                }
                else // If a straightforward transfer of binary data is required
                {
                    // Create a file output stream object to send blob data to a file
                    FileOutputStream fos = new FileOutputStream(filename);
                    // Create a binary input stream object to retrieve data
                    InputStream b = blob.getBinaryStream();

                    // Initializing a buffer for reading in blob data
                    byte[] byteBuffer = new byte[BUFFERSIZE];
                    // Initialize a dummy variable representing number of bytes
                    // read into the buffer
                    int byteCount = 0;
                    // While data is available, read it into a buffer and read out
                    // to the designated file
                    while ( (byteCount = b.read(byteBuffer, 0, BUFFERSIZE)) != -1)
                    {
                        // Write out the retrieved character data
                        fos.write(byteBuffer, 0, byteCount);
                    }
                    // Display success message
                    System.out.println(" File creation completed successfully."
                        + " Please refer to the following file: " + filename);
                    // close file/data streams
                    b.close();
                    fos.close();
                }
            }
            catch (IOException ex)
            {
                System.out.println(" Error writing Blob : " + ex.getMessage());
                ex.printStackTrace();
            }
        }
        else // if some other parameter value was passed
        {
            throw new IllegalArgumentException(
                " Invalid parameter value. litDisplay must hold a value of 0 or 1.");
        }
    } // End createBlobFile(...)


    private static void writeBlobCharRep(Blob blob, String filename)
        throws SQLException
    {
        // This function will read in the Blob data one byte at a time
        // and will write the character representation of the binary data
        // (in hexadecimal format) into the file designated by the string 'filename'
        try
        {
            // Create a file writer object to send character data to a file
            FileWriter fos = new FileWriter(filename);
            // Create a binary input stream object to retrieve data
            InputStream b = blob.getBinaryStream();

            // Initialize a buffer for reading in blob data
            byte[] byteBuffer = new byte[BUFFERSIZE];
            // Initialize a string to hold character representation of
            // binary data
            String byteStr = "";
            // Initialize a dummy variable representing number of bytes
            // read into the buffer
            int byteCount = 0;
            // While data is available, read it into a buffer and write out
            // its character representatin
            while ( (byteCount = b.read(byteBuffer, 0, BUFFERSIZE)) != -1)
            {
                for (int i = 0; i < byteCount; i++)
                {
                    // Isolate the integer value of the byte read
                    // Note that a conversion to an unsigned integer
                    // has been made.
                    int val = byteBuffer[i] & 0xFF;
                    if (val < 16)
                    {   byteStr += '0' + Integer.toHexString(val).toUpperCase();}
                    else
                    {   byteStr += Integer.toHexString(val).toUpperCase();}
                }
                // Write out the representation of the retrieved binary data
                fos.write(byteStr);
            }
            // Display success message
            System.out.println(" File creation completed successfully. Please "
                               + "refer to the following file: " + filename);
            // close file/data streams
            b.close();
            fos.close();
        }
        catch (IOException ex)
        {
            System.out.println(" Error writing Blob : " + ex.getMessage());
            ex.printStackTrace();
        }
    } // End writeBlobCharRep(...)

} // End class T20303JD