//*********************************************************************
//
//                 Copyright (c) 2006-2009 by Teradata Corporation
//                         All Rights Reserved
//
//*********************************************************************
//
//  File:       T20603JD.java
//
//  Header:      none
//  Purpose:    This sample application installs a jar from the client to
//              the DBS server for Java Stored Procedures. The JDBC driver uses the
//              classpath to load all resources. The file "SampleJXSP.jar" must be on
//              the classpath in order for it to be transferred to the server node so
//              it can be installed by this sample class using the JDBC driver .
//
//              The program will:
//                -  Connect as user guest/please
//                -  Remove any stored procedures
//                   which may access the jar about
//                   to be installed
//                -  Remove old jar file if it is installed
//                -  Install new jar file
//                -  Disconnect.
//
//  JDBC API: java.sql.Connection, java.sql.Statement,
//            java.sql.Statement.executeUpdate
//
//  Version: Updated for Teradata Database 13.10
//
//*********************************************************************

import java.sql.*;

public class T20603JD
{
    // Name of the user able to create, drop, and manipulate tables
    public static String sUser = "guest";
    public static String sPassword = "please";

    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";
        String[] sDropProcedures =
        {
              "DROP procedure getDeptJobInfo",
              "DROP procedure getEmpInfo",
              "DROP procedure getBookInfoByAuthor",
              "DROP procedure getBookInfoByTitleAndPublisher"
        };
        String sRemoveJar = "{call sqlj.remove_jar(?,0)}";
        String sInstallJar = "{call sqlj.install_jar(?,?,0)}";

        try
        {
           System.out.println("\n Sample T20603JD: \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
                {
                    for (int i = 0; i < sDropProcedures.length; i++)
                    {
                        try
                        {
                            System.out.println(
                                " Attempting to drop a stored procedure : " +
                                sDropProcedures[i]);
                            // Attempt to drop stored procedure in case it
                            // exists. This is done to make sure no resource
                            // refers to the jar file that will be removed and
                            // installed.
                            stmt.executeUpdate(sDropProcedures[i]);
                        }
                        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 SampleJXSP.jar : " + sRemoveJar);
                CallableStatement cStmt = con.prepareCall(sRemoveJar);
                try
                {
                    cStmt.setString(1, "SampleJXSP");
                    cStmt.executeUpdate();
                    System.out.println(" SampleJXSP.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 SampleJXSP.jar ");
                cStmt = con.prepareCall(sInstallJar);
                try
                {
                    cStmt.setString(1,"cj!SampleJXSP.jar");
                    cStmt.setString(2,"SampleJXSP");
                    cStmt.executeUpdate();
                    System.out.println(" SampleJXSP.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 T20603JD finished. \n");
        }
        catch (SQLException ex)
        {
            // Note that there could be multiple SQLException 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.") ;
        }
    }
}