//*********************************************************************
//
//                 Copyright (c) 2006-2008 by Teradata Corporation
//                         All Rights Reserved
//
//*********************************************************************
//
//  File:     T20602JD.java
//  Purpose:  This sample application will:
//            - Create, delete, display and use an External Stored Procedure
//            - The source code for the External Stored Procedure is on the
//              client. This requires the source code to be transferred
//              from the client node to the server node. The JDBC driver
//              uses the the classpath to load all resources. The file
//              "getlastname.c" must be on the classpath in order for it to
//              be transferred to the server node so it can be used with this
//              class.
//
//*********************************************************************
import java.sql.*;

public class T20602JD
{
    public static void main (String[] args)
    throws ClassNotFoundException
    {
        String url       = "jdbc:teradata://whomooz/TMODE=ANSI,CHARSET=UTF8";
        String user      = "guest";
        String password  = "please";
        String query1    = "DROP PROCEDURE GetLastNameXSP";
        String query2    =
            "CREATE PROCEDURE GetLastNameXSP "
            + "(INOUT name VARCHAR(30)) "
            + "LANGUAGE C "
            + "NO SQL "
            + "EXTERNAL NAME 'CS!getlastname!getlastname.c!F!xsp_getlastname'";
        String query3    = "SHOW PROCEDURE GetLastNameXSP";
        String query4     = "{CALL GetLastNameXSP(?)}";

        try
        {
            Class.forName("com.teradata.jdbc.TeraDriver");

            Connection con = DriverManager.getConnection(url, user, password);

            try
            {
                Statement stmt = con.createStatement();

                try
                {
                    try
                    {
                        System.out.println();
                        System.out.println(
                        " Drop the External Stored Procedure if it already exists");
                        stmt.executeUpdate(query1);
                    }
                    catch (SQLException ex)
                    {
                        System.out.println();
                        System.out.println(" Ignoring exception: " + ex);
                    }

                    System.out.println();
                    System.out.println(" Creating the External Stored Procedure");
                    stmt.executeUpdate(query2);

                    // If no errors occured...
                    System.out.println(
                        " External Stored Procedure created successfully.");
                    System.out.println();
                    System.out.println(" Showing the External Stored Procedure");

                    ResultSet rs = stmt.executeQuery(query3);

                    System.out.println();
                    dispResultSet(rs);

                    // IN and OUT parameters, and methods for executing the
                    // call to a stored procedure. Please refer to the JAVA API
                    // or the supporting driver documentation for a complete
                    // list of methods, their implementations, and
                    // return values.
                    // Creating a CallableStatement object for calling the
                    // database stored procedure and preparing the callable
                    // statement for execution
                    CallableStatement cStmt = con.prepareCall(query4);

                    try
                    {

                        // Setting up input parameter value
                        String sName = "John Smith";

                        cStmt.setString(1, sName);

                        // Setting up output parameters for data retrieval by
                        // declaring parameter types.
                        cStmt.registerOutParameter(1, Types.VARCHAR);
                        System.out.println("\n Calling the procedure with '"
                                           + sName + "' ...");

                        // Making a procedure call
                        cStmt.executeUpdate();

                        // Displaying procedure call result, please refer to the
                        // driver manual for a full list of data retrieval methods
                        System.out.println(" Call successful.");
                        System.out.println(
                            "\n Displaying output of the call to "
                            + "GetLastNameXSP(...) : ");
                        System.out.println("\n " + sName);
                        System.out.println(" -----------------");
                        System.out.println(" Employee Last Name : "
                                           + cStmt.getString(1));
                    }
                    finally
                    {

                        // Close the callable statement
                        cStmt.close();
                        System.out.println(
                            "\n CallableStatement object closed. \n");
                    }
                }
                finally
                {
                    stmt.close();
                }
            }
            finally
            {
                System.out.println();
                System.out.println(" Closing Connection");
                con.close();
                System.out.println(" Closed Connection");
            }
            System.out.println(" Sample T20602JD finished.");
        }
        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 dispResultSet (ResultSet rs)
        throws SQLException
    {
        ResultSetMetaData rsmd    = rs.getMetaData();
        int               numCols = rsmd.getColumnCount();

        for (int nRow = 1; rs.next(); nRow++)
        {
            for (int i = 1; i <= numCols; i++)
            {
                if (i == 1) System.out.print(" Row " + nRow + ": ");
                else System.out.print(", ");

                String s = rs.getString(i);

                if (s != null)
                    s = s.replaceAll("\r\n", "\n").replaceAll("\r",
                                     "\n").replaceAll("\n$",
                                      "").replaceAll("\n", "\n        ");

                System.out.print(s);
            }

            System.out.println();
        }
    }    // end dispResultSet
}    // end class T20602JD