//*********************************************************************
//
//                 Copyright (c) 2007-2008 by Teradata Corporation
//                         All Rights Reserved
//
//*********************************************************************
//
//  File:       T20608JD.java
//  Header:     none
//  Purpose:    Demonstrate basic Teradata SQL using the JDBC API by
//              creating/executing a Java Stored Procedure that returns
//              multiple result sets.
//              The program will:
//                -  Connect as user guest/please
//                -  Create a stored procedure
//                -  Execute the stored procedure
//                -  Obtain and display the multiple result sets
//                -  Disconnect.
//
//  JDBC API: java.sql.Connection, java.sql.Statement,
//            java.sql.Statement.executeQuery,
//            java.sql.CallableStatement,
//            java.sql.CallableStatement.setString,
//            java.sql.ResultSet.next
//            java.sql.ResultSet.getString
//            java.sql.ResultSet.getMetaData
//            java.sql.MetaData.getColumnName
//            java.sql.MetaData.getColumnCount
//
//  Version: Created for Teradata Database 13.0
//
//*********************************************************************

import java.sql.*;

public class T20608JD
{
    // 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";

        //Create a Java Stored Procedure creation command. Since
        //a result set is being returned, it must be specified.
        String sProcedure =
            "REPLACE PROCEDURE getBookInfoByTitleAndPublisher " +
            "(IN BookTitle VARCHAR(30), "+
            " IN BookTitleTopic VARCHAR(30)) " +
            "LANGUAGE JAVA MODIFIES SQL DATA " +
            "PARAMETER STYLE JAVA " +
            "DYNAMIC RESULT SETS 2 " +
            "EXTERNAL NAME 'SampleJXSP:com.teradata.sample.BookInfo." +
            "getBookInfoByTitleAndPublisher'";

        String sCall = "{CALL getBookInfoByTitleAndPublisher(?, ?)}";
        String sBookTitle = "Advanced SQL";
        String sBookPublisher = "Able Books";
        try
        {
            System.out.println("\n Sample T20608JD: \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 DDL request to the
                    // database, replacing any old implementations, if present
                    stmt.executeUpdate(sProcedure);
                    // If no errors occured...
                    System.out.println(" Procedure created successfully.");

                    // The CallableStatement object represents a precompiled
                    // SQL statement. It provides methods for setting up its
                    // 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(sCall);
                    // Setting up input parameter value
                    cStmt.setString(1, sBookTitle);
                    cStmt.setString(2, sBookPublisher);
                    System.out.println("\n Calling the procedure with '"
                                       + sBookTitle + ", "+sBookPublisher+"' ...");
                    // Making a procedure call
                    boolean rsReturned = cStmt.execute();
                    if (rsReturned)
                    {
                        ResultSet rs = cStmt.getResultSet();
                        // Displaying procedure result set, please refer to the
                        // driver manual for a full list of data retrieval methods
                        System.out.println(" Call successful.");
                        System.out.println(
                            "\n Displaying the 1st result set returned by procedure"+
                            " call to getBookInfoByTitleAndPublisher(...) : ");
                        while (rs.next())
                        {
                            System.out.println("\n Book Title: " + sBookTitle);
                            System.out.println(" --------------------------");
                            for (int colNum = 1;
                                 colNum <= rs.getMetaData().getColumnCount();
                                 colNum++)
                            {
                                System.out.println(
                                    " "+rs.getMetaData().getColumnName(colNum)
                                    + " : " +rs.getString(colNum));
                            }
                        }
                    }
                    rsReturned = cStmt.getMoreResults();
                    if (rsReturned)
                    {
                        ResultSet rs = cStmt.getResultSet();
                        // Displaying procedure result set, please refer to the
                        // driver manual for a full list of data retrieval methods
                        System.out.println(" Call successful.");
                        System.out.println(
                            "\n Displaying the 2nd result set returned by procedure"+
                            " call to getBookInfoByTitleAndPublisher(...) : ");
                        while (rs.next())
                        {
                            System.out.println("\n Book Publisher: "
                                + sBookPublisher);
                            System.out.println(" --------------------------");
                            for (int colNum = 1;
                                 colNum <= rs.getMetaData().getColumnCount();
                                 colNum++)
                            {
                                System.out.println(
                                    " "+rs.getMetaData().getColumnName(colNum)
                                    + " : " +rs.getString(colNum));
                            }
                        }
                    }
                }
                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 T20608JD 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
} // End class T20608JD