//*********************************************************************
//
//                 Copyright (c) 2011 by Teradata Corporation
//                         All Rights Reserved
//
//*********************************************************************
//
//  File:       T22103JD.java
//  Header:     none
//  Purpose:    Demonstrate stored procedure calls with multi-dimensional
//              array parameters.
//              The program will:
//                -  Connect as user guest/please
//                -  Call a stored procedure
//                -  Display the procedure's OUT and INOUT parameters
//                -  Disconnect.
//
//  JDBC API: java.sql.Connection, java.sql.CallableStatement,
//            java.sql.CallableStatement.executeUpdate, java.sql.Array
//
//  Version: Updated for Teradata Database 14.0
//
//*********************************************************************

import java.sql.* ;
import java.util.Map ;
import java.util.Arrays ;

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

        // Strings representing a callable statement and its parameter values,
        // respectively
        String sCallProc = "Call IntArraySP (?,?,?)" ;
        Integer [] [] anIntValues =
            {
                { new Integer(5), new Integer (6), new Integer (7)}
               ,{ new Integer(8), new Integer (9), new Integer (10)}
           } ;


        try
        {
            System.out.println ("\n Sample T22103JD: \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 Callable Statement object from an active
                // connection. A CallableStatement is an object that represents
                // a precompiled SQL statement that enables the application to
                // call a stored procedure.
                System.out.println (" Preparing this SQL statement for execution:\n "
                                   + sCallProc) ;
                CallableStatement cstmt = con.prepareCall (sCallProc) ;
                System.out.println (" Callable statement object created. \n") ;

                try
                {

                    // The following code will perform a CALL to a procedure
                    // using a callable statement. CallableStatement.executeUpdate
                    // method is used to call the procedure.
                    System.out.println (" Attempting a call...\n") ;

                    // Set parameter values indicated by ?.
                    // CallableStatement.setObject and CallableStatement.setArray
                    // methods will be demonstrated. Please refer to the
                    // supporting driver documentation for the full list of
                    // methods, their implementations, and their return values.
                    System.out.println (" Using setObject () and setArray () to " +
                                        "assign values to parameter markers:\n") ;

                    // Create the Arrays using the sample at the bottom. In JDK
                    // 6.0, Connection.createArrayOf () can be used.
                    Array p1Array = new SampArray ("INTEGER", anIntValues) ;
                    cstmt.setObject (1, p1Array) ;
                    System.out.print (" FIRST ? set to : '"
                                        + p1Array.getBaseTypeName () + "' [ " ) ;
                    for (int i = 0 ; i < anIntValues.length ; i++)
                        System.out.print (" " + Arrays.asList( anIntValues [i])) ;

                    System.out.println(" ]");

                    // Set the INOUT parameter to null.
                    cstmt.setArray (2, null) ;
                    System.out.println ("\n SECOND ? set to null\n") ;

                   cstmt.registerOutParameter (2, Types.ARRAY) ;
                   cstmt.registerOutParameter (3, Types.ARRAY) ;

                    // Once all of the parameter values have been set, call
                    // executeUpdate to execute the now complete SQL command.
                    cstmt.executeUpdate ();

                   // Extract and display the OUT and INOUT parameters
                   System.out.println () ;
                   System.out.println (" DISPLAYING OUTPUT parameters:") ;
                   System.out.println (" ---------------------------") ;

                   // retrieve and display the INOUT parameter p2
                   Array p2Array = cstmt.getArray (2) ;

                   // Use the method Array.getArray () to retrieve the array
                   // elements
                   Object [] [] aoP2IntArray  = (Object [] []) p2Array.getArray () ;

                   // Display the array elements
                   for (int i = 0 ; i < aoP2IntArray.length ; i++)
                   {
                       for (int j = 0; j < aoP2IntArray[0].length; j++) {
                           System.out.println(" INOUT p2 Array [" + i + "] "
                                              + "[ " + j + "] " + " : "
                                              + aoP2IntArray[i][j]);
                       }
                   }

                   // retrieve and display the OUT parameter p3
                   Array p3Array = cstmt.getArray (3) ;
                   // this value should be null so we cannot retrieve
                   // the array elements
                   System.out.println("\n OUT p3 Array : "+p3Array) ;
                }
                finally
                {
                    // Close the callable statement
                    cstmt.close () ;
                    System.out.println ("\n CallableStatement 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 T22103JD 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

    // This class is used instead of calling Connection.createArrayOf, which is
    // only supported in JDK 6.0 or later
    public static class SampArray implements java.sql.Array
    {
        private Object m_oElements ;
        private String m_sBaseTypeName ;
        private int m_nBaseType ;

        public SampArray (String sBaseTypeName, Object oElements)
        {
            m_sBaseTypeName = sBaseTypeName ;
            m_oElements = oElements ;
        }

        // Returns base type
        public int getBaseType ()
        throws SQLException
        {
            return m_nBaseType ;
        }

        // Returns base type name
        public String getBaseTypeName ()
        throws SQLException
        {
            return m_sBaseTypeName ;
        }

        // Returns array elements
        public Object getArray ()
        throws SQLException
        {
            return m_oElements ;
        }

        // This method is not supported in this sample, but need to be included
        public Object getArray (long index, int count)
        throws SQLException
        {
            throw new SQLException ("getArray (long, int) "
                                   + "NOT SUPPORTED IN THIS SAMPLE") ;
        }

        // This method is not supported in this sample, but need to be included
        public Object getArray (long index, int count, Map map)
        throws SQLException
        {
            throw new SQLException ("getArray (long, int, Map) "
                                   + "NOT SUPPORTED IN THIS SAMPLE") ;
        }

        // This method is not supported in this sample, but need to be included
        public Object getArray (Map map)
        throws SQLException
        {
            //Unsupported Exception
            throw new SQLException ("getArray (Map) "
                                   + "NOT SUPPORTED IN THIS SAMPLE") ;
        }

        // This method is not supported in this sample, but need to be included
        public ResultSet getResultSet ()
        throws SQLException
        {
            //Unsupported Exception
            throw new SQLException ("getResultSet () "
                                   + "NOT SUPPORTED IN THIS SAMPLE") ;
        }

        // This method is not supported in this sample, but need to be included
        public ResultSet getResultSet (long index, int count)
        throws SQLException
        {
            //Unsupported Exception
            throw new SQLException ("getResultSet (long index, int count) "
                                   + "NOT SUPPORTED IN THIS SAMPLE") ;
        }

        // This method is not supported in this sample, but need to be included
        public ResultSet getResultSet (long index, int count, Map map)
        throws SQLException
        {
            //Unsupported Exception
            throw new SQLException ("getResultSet (Map) "
                                   + "NOT SUPPORTED IN THIS SAMPLE") ;
        }

        // This method is not supported in this sample, but need to be included
        public ResultSet getResultSet (java.util.Map map)
        throws SQLException
        {
            //Unsupported Exception
            throw new SQLException ("getResultSet (Map) "
                                   + "NOT SUPPORTED IN THIS SAMPLE") ;
        }

        // This method is supported in JDK 6.0 and later releases only.
        // It is included here if using the JDK 6.0 compiler
        public void free ()
        {
        }

    } // End of class SampArray
} // End class T22103JD