//*********************************************************************
//
//                 Copyright (c) 2006-2008 by Teradata Corporation
//                         All Rights Reserved
//
//*********************************************************************
//
//  File:       T20605JD.java
//  Header:     none
//  Purpose:    Demonstrate basic Teradata SQL using the JDBC API by
//              creating/executing a Java Stored Procedure.
//              The program will:
//                -  Connect as user guest/please
//                -  Create a stored java procedure with LOB parameters
//                -  Execute the stored procedure
//                -  Obtain and display the results
//                -  Disconnect.
//
//  JDBC API: java.sql.Connection, java.sql.Statement,
//            java.sql.Statement.executeUpdate
//
//  Version: Created for Teradata Database 12
//
//*********************************************************************

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

public class T20605JD
{
    // Name of the user able to create, drop, and manipulate 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";

        // Stored procedure creation command and its respective parameters
        String sProcedure =
            "REPLACE PROCEDURE getEmpInfo " +
            "(IN name VARCHAR(30), OUT id INTEGER, OUT dept VARCHAR(50), " +
             "OUT job VARCHAR(300), OUT res CLOB) " +
             "LANGUAGE JAVA MODIFIES SQL DATA " +
             "PARAMETER STYLE JAVA " +
             "EXTERNAL NAME 'SampleJXSP:com.teradata.sample.EmpInfo.getEmpInfo(" +
             "java.lang.String,java.lang.Integer[],java.lang.String[]," +
             "java.lang.String[],java.sql.Clob[])'";

        String sCall = "{CALL getEmpInfo(?,?,?,?,?)}";
        String sName = "Brian Lee";

        try
        {
            System.out.println("\n Sample T20605JD: \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
                {
                    System.out.println(" Attempting to create a procedure : "
                                       + sProcedure);
                    // Sending the procedure creation request to the database,
                    // replacing any old implementations, if present
                    stmt.executeUpdate(sProcedure);
                    System.out.println(" Procedure created successfully.");

                    // Creating a CallableStatement object, representing
                    // a precompiled SQL statement and preparing the callable
                    // statement for execution.
                    CallableStatement cStmt = con.prepareCall(sCall);
                    // Setting up input parameter value
                    cStmt.setString(1, sName);
                    // Setting up output parameters for data retrieval by
                    // declaring parameter types.
                    cStmt.registerOutParameter(2, Types.INTEGER);
                    cStmt.registerOutParameter(3, Types.VARCHAR);
                    cStmt.registerOutParameter(4, Types.VARCHAR);
                    cStmt.registerOutParameter(5, Types.CLOB);
                    System.out.println("\n Calling the procedure with '"
                                       + sName + "' ...");
                    // Making a procedure call
                    cStmt.executeUpdate();
                    // Displaying procedure call result
                    System.out.println(" Call successful.");
                    System.out.println("\n Displaying output of the call to "
                                       + "getEmpInfo(...) : ");
                    System.out.println("\n " + sName);
                    System.out.println(" -----------------");
                    int id = cStmt.getInt(2);
                    System.out.println(" Employee ID : " + id);
                    System.out.println(" Department : " + cStmt.getString(3));
                    System.out.println(" Job Description : " + cStmt.getString(4));
                    System.out.print(" Resume : ");
                    // Writing CLOB data out to a file for review
                    createClobFile(cStmt.getClob(5), (id + "resumeT20605.txt"));
                }
                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 T20605JD 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 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(...)
} // End class T20605JD