//*********************************************************************
//
//                     Copyright (c) 2008-2009 by Teradata Corporation
//                               All Rights Reserved
//
//*********************************************************************
//
//  File:     T21401JD.java
//  Header:   None
//  Purpose:  This sample program demonstrates how to install
//            a jar from the client to DBS for Java UDFs.
//            This program will:
//              Connect to Teradata as guest user
//              Drop any Java User Defined Function, if already exists
//              Drop the JUDF jar file if already exists
//              Install new jar file
//              Closing the connection
//  Version:  Updated for Teradata Database 13.10
//
//*********************************************************************

import java.sql.*;

public class T21401JD
{
    public static String sUser = "guest";
    public static String sPassword = "please";

    public static void main (String args [])
    throws ClassNotFoundException
    {
        String url = "jdbc:teradata://whomooz/TMODE=ANSI,CHARSET=UTF8";
        String dropFunctionQuery = "DROP FUNCTION FindRegEx";
        String sRemoveJar = "{call sqlj.remove_jar(?,0)}";
        String sInstallJar = "{call sqlj.install_jar(?,?,0)}";

        try
        {
            System.out.println("\n Sample T21401JD: \n");
            System.out.println(" Looking for the Teradata JDBC driver... ");
            Class.forName("com.teradata.jdbc.TeraDriver");
            System.out.println(" JDBC driver loaded. \n");

            System.out.println(
                " Attempting to connect to Teradata via the JDBC driver...");

            Connection con = DriverManager.getConnection(url, sUser, sPassword);
            System.out.println(" User " + sUser + " connected.");
            System.out.println(" Connection to Teradata established. \n");

            try
            {
                Statement stmt = con.createStatement();
                System.out.println(" Statement object created. \n");

                try
                {
                    try
                    {
                        System.out.println(
                            " Attempting to drop a JUDF : " + dropFunctionQuery);
                        stmt.executeUpdate(dropFunctionQuery);
                        System.out.println(" JUDF has been dropped ");
                    }
                    catch (SQLException e)
                    {
                        System.out.println(" Ignoring Exception: " + e);
                    }
                }
                finally
                {
                    System.out.println(" Closing the Statement object ");
                    stmt.close();
                    System.out.println(" Statement object has been closed ");
                }

                System.out.println(
                    " Attempting to remove SampleJavaUDF.jar : " + sRemoveJar);
                CallableStatement cStmt = con.prepareCall(sRemoveJar);
                try
                {
                    cStmt.setString(1, "SampleJavaUDF");
                    cStmt.executeUpdate();
                    System.out.println(" SampleJavaUDF.jar removed successfully. ");
                }
                catch (SQLException e)
                {
                    System.out.println(" Ignoring Exception: " + e);
                }
                finally
                {
                    System.out.println(" Closing the CallableStatement object ");
                    cStmt.close();
                    System.out.println(" CallableStatement object has been closed ");
                }

                System.out.println(" Installing SampleJavaUDF.jar : " + sInstallJar);
                cStmt = con.prepareCall(sInstallJar);
                try
                {
                    cStmt.setString(1,"cj!SampleJavaUDF.jar");
                    cStmt.setString(2,"SampleJavaUDF");
                    cStmt.executeUpdate();
                    System.out.println(" SampleJavaUDF.jar installed successfully ");
                }
                finally
                {
                    System.out.println(" Closing the CallableStatement object ");
                    cStmt.close();
                    System.out.println(" CallableStatement object has been closed ");
                }
            }
            finally
            {
                System.out.println(" Closing Connection");
                con.close();
                System.out.println(" Connection has been closed ");
            }

            System.out.println(" Sample T21401JD 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.") ;
        }
    }
}