//*********************************************************************
//
//                  Copyright (c) 2008 by Teradata Corporation
//                            All Rights Reserved
//
//*********************************************************************
//
//      File:     T21501JD.java
//      Header:   None
//      Purpose:  This sample program demonstrates the use of the
//                Teradata Database 13.0 Geospatial data types.
//                This program will:
//                - Connect to the Teradata Database as guest user.
//                - Drop any SampleCities, SampleCities tables if
//                  already existing in the data base.
//                - Create SampleCities and SampleStreets tables with
//                  Geospatial data types.
//                - Insert data to the tables.
//                - Find which streets are within which cities using
//                  the ST_Within method.
//                - Display the results.
//
//      JDBC API: java.sql.Connection, java.sql.Statement,
//                java.sql.Statement.executeUpdate,
//                java.sql.Statement.executeQuery,
//                java.sql.ResultSet
//
//      Version: Created for Teradata Database 13.0
//
//*********************************************************************

import java.sql.*;

public class T21501JD
{
    public static String sUser = "guest";
    public static String sPassword = "please";

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

        String createCities = " CREATE TABLE SampleCities(id INTEGER,  " +
        " CityName VARCHAR(40),CityShape ST_GEOMETRY)";

        String createStreets = " CREATE TABLE SampleStreets(id INTEGER, " +
        " StreetName VARCHAR(40), StreetShape ST_GEOMETRY)";

        String dropCities = " DROP TABLE SampleCities ";

        String dropStreets = " DROP TABLE SampleStreets ";

        String insertCities =
            " INSERT INTO SampleCities VALUES " +
            " (1, 'El Segundo', 'POLYGON((1 1, 1 3, 6 3, 6 0, 1 1))')" +
            " ; " +
            " INSERT INTO SampleCities VALUES " +
            " (2, 'San Diego ', 'POLYGON((10 10, 10 20, 20 20, 20 15, 10 10))')";

        String insertStreets =
            " INSERT INTO SampleStreets VALUES " +
            " (1, 'Main Street', 'LINESTRING(2 2, 3 2, 4 1)')" +
            " ; " +
            " INSERT INTO SampleStreets VALUES" +
            " (2, 'Coast Blvd', 'LINESTRING(12 12, 18 17)') " ;

        String selectTable =
            " SELECT CityName,StreetName FROM SampleCities, SampleStreets " +
            " WHERE StreetShape.ST_Within(CityShape) = 1 " +
            " ORDER BY CityName,StreetName ";

        try
        {
            System.out.println("\n Sample T21501JD: \n");
            System.out.println(" Looking for the Teradata JDBC driver... ");
            Class.forName("com.teradata.jdbc.TeraDriver");
            System.out.println(" JDBC driver loaded. \n");

            System.out.println(
            " Attempting to connect to Teradata via the JDBC driver...");

            Connection con = DriverManager.getConnection(url, sUser, sPassword);
            System.out.println(" User " + sUser + " connected.");
            System.out.println(" Connection to Teradata established. \n");
            try
            {
                Statement stmt = con.createStatement();
                try
                {
                    System.out.println(" Dropping SampleCities table if exists ");
                    stmt.executeUpdate(dropCities);
                    System.out.println(" SampleCities Table has been dropped ");
                }
                catch(SQLException ex)
                {
                    System.out.println(" Ignoring exception " + ex.getMessage());
                }

                try
                {
                    System.out.println("\n Dropping SampleStreets table if exists ");
                    stmt.executeUpdate(dropStreets);
                    System.out.println(" SampleStreets Table has been dropped ");
                }
                catch(SQLException ex)
                {
                    System.out.println(" Ignoring exception " + ex.getMessage());
                }

                try
                {
                    System.out.println(" \n Creating SampleCities table .... ");
                    stmt.executeUpdate(createCities);
                    System.out.println(" SampleCities table has been created. ");
                    System.out.println("\n Creating SampleStreets table .... ");
                    stmt.executeUpdate(createStreets);
                    System.out.println(" SampleCities table has been created.  ");
                    System.out.println("\n Inserting data to SampleCities table.. ");
                    stmt.executeUpdate(insertCities);
                    System.out.println(" Data has been inserted to SampleCities. ");
                    System.out.println("\n Inserting data to SampleStreets table..");
                    stmt.executeUpdate(insertStreets);
                    System.out.println(" Data has been inserted to SampleStreets.");
                    System.out.println("\n Executing Select query using " +
                                       " ST_Within method .. ");

                    ResultSet rs = stmt.executeQuery(selectTable);
                    try
                    {
                        dispResultSet (rs);
                    }
                    finally
                    {
                        System.out.println(" Closing Resultset object ...");
                        rs.close();
                        System.out.println(" Resultset has been closed. ");
                    }
                }
                finally
                {
                    System.out.println("\n Closing Statement object ...");
                    stmt.close();
                    System.out.println(" Connection has been closed.");
                }
            }
            finally
            {
                System.out.println("\n Closing Connection ... ");
                con.close();
                System.out.println(" Connection has been closed ");
            }

            System.out.println(" Sample T21501JD 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

    private static void dispResultSet (ResultSet rs) throws SQLException
    {
        System.out.println("\nCity Name               Street Name");
        System.out.println("*********               ***********");
        ResultSetMetaData rsmd = rs.getMetaData () ;
        int numCols = rsmd.getColumnCount () ;
        for (int nRow = 1 ; rs.next () ; nRow++)
        {
            for (int i = 1 ; i <= numCols ; i++)
            {
                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.print("              ");
            }
            System.out.println () ;
       }
       System.out.println () ;

    } // end dispResultSet

} // end class T21501JD