//*********************************************************************
//
//                 Copyright (c) 2008 by Teradata Corporation
//                         All Rights Reserved
//
//*********************************************************************
//
//  File:     T21402JD.java
//  Header:   None
//  Purpose:  This sample program demonstrates how to create, delete,
//            and display Java User Defined Functions.
//            This program will:
//            Connect to Teradata as guest user
//              Create the statement object
//              Drop the test table if already exists
//              Drop the Java User Defined Function, if already exists
//              Create test table and insert testdata to that table
//              Create the Java User Defined Function
//              Call the Java User Defined function and display the results
//              Closing the resultset
//              Closing the statement object
//              Closing the connection
//  Version : Created for Teradata Database 13.0
//
//**********************************************************************

import java.sql.*;

public class T21402JD
{
    public static String userName = "guest";
    public static String password = "please";

    public static void main (String [] args)
    throws ClassNotFoundException
    {
        String url = "jdbc:teradata://whomooz/TMODE=ANSI,CHARSET=UTF8";
        String dropFunction = "DROP FUNCTION FindRegEx";

        String dropTable = "DROP TABLE UDFTEST";

        String createFunction = "REPLACE FUNCTION FindRegEx" +
            " (inp VARCHAR(6000), regex VARCHAR(1000))" +
            " RETURNS VARCHAR(6000)" +
            " LANGUAGE JAVA" +
            " NO SQL" +
            " PARAMETER STYLE JAVA" +
            " EXTERNAL NAME" +
            " 'SampleJavaUDF:com.teradata.sample.JavaUDFClass.findRegEx'";

        String createTable = " CREATE TABLE UDFTEST(ID INTEGER, DATA VARCHAR(1000))";

        String insertIntoTable =
            "INSERT INTO UDFTEST VALUES (1, 'Converge on the location.')" +
             ";" +
             "INSERT INTO UDFTEST VALUES (2, 'Engage in conversation.')" +
             ";" +
             "INSERT INTO UDFTEST VALUES (3, 'Conversion may occur.')" +
             ";" +
             "INSERT INTO UDFTEST VALUES (4, 'A converter is needed.')" +
             ";" +
             "INSERT INTO UDFTEST VALUES (5, 'Drive a convertible.')";

        String selectQuery =
            " SELECT FindRegEx(data, '(?i)conver(t|sion)') FROM UDFTEST";

        String showFunction = "SHOW FUNCTION FindRegEx";
        try
        {
            System.out.println () ;
            System.out.println(" Sample T21402JD:");
            System.out.println () ;
            System.out.println(" Loading the Teradata JDBC driver");
            Class.forName ("com.teradata.jdbc.TeraDriver") ;
            System.out.println(" JDBC driver loaded.");
            System.out.println () ;
            System.out.println(
                " Attempting to connect to Teradata via the JDBC driver...");
            Connection con = DriverManager.getConnection (url, userName, password) ;
            System.out.println(" Connection to Teradata established.");
            try
            {
                Statement stmt = con.createStatement ();
                try
                {
                    try
                    {
                        System.out.println () ;
                        System.out.println (
                            " Drop the JAVA UDF if it already exists") ;
                        stmt.executeUpdate (dropFunction) ;
                    }
                    catch(SQLException ex)
                    {
                        System.out.println () ;
                        System.out.println (" Ignoring drop udf exception: "+ ex) ;
                    }

                    try
                    {
                        System.out.println () ;
                        System.out.println (
                            " Drop the Test table if it already exists") ;
                        stmt.executeUpdate (dropTable) ;
                    }
                    catch(SQLException ex)
                    {
                        System.out.println () ;
                        System.out.println (" Ignoring drop table exception: "+ ex) ;
                    }

                    System.out.println () ;
                    System.out.println (" Creating the UDF") ;
                    ResultSet rs = stmt.executeQuery(createFunction) ;

                    System.out.println () ;
                    System.out.println (" Creating the table") ;
                    stmt.executeUpdate (createTable);

                    System.out.println () ;
                    System.out.println (" Showing the JUDF ") ;
                    rs = stmt.executeQuery(showFunction);
                    dispResultSet (rs);

                    System.out.println () ;
                    System.out.println (" Inserting data to table") ;
                    stmt.executeUpdate (insertIntoTable);

                    System.out.println () ;
                    System.out.println (" Calling the UDF ") ;
                    rs = stmt.executeQuery (selectQuery);

                    dispResultSet (rs);
                    System.out.println () ;
                    System.out.println(" Closing the result set ");
                    rs.close();
                    System.out.println(" Result set is closed ");
                    System.out.println () ;
                }
                finally
                {
                    System.out.println(" Closing the Statement object " );
                    stmt.close();
                    System.out.println(" Statement object is closed " );
                }
            }
            finally
            {
                System.out.println(" Closing the Connection ");
                con.close();
                System.out.println(" Connection object is closed ");
            }
            System.out.println () ;
            System.out.println(" Sample T21402JD 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

}