//*********************************************************************
//
//                  Copyright (c) 2012 by Teradata Corporation
//                            All Rights Reserved
//
//*********************************************************************
//
//      File:     T21502J6.java
//      Header:   None
//      Purpose:  This sample program demonstrates the use of the
//                Teradata Database 14.10 XML data types.
//                This program will:
//                - Connect to the Teradata Database as guest user.
//                - Drop SampleSchools tables if
//                  already existing in the data base.
//                - Create SampleSchools tables with XML data types.
//                - Insert data to the tables.
//                - Select from SampleSchools.
//                - Display the results.
//
//      JDBC API: java.sql.Connection, java.sql.PreparedStatement,
//                java.sql.PreparedStatement.setSQLXML,
//                java.sql.PreparedStatement.addBatch,
//                java.sql.PreparedStatement.executeBatch,
//                java.sql.SQLXML, java.sql.SQLXML.setString,
//                java.sql.SQLXML.getString, java.sql.SQLXML.setResult,
//                java.sql.SQLXML.getSource, java.sql.SQLXML.free
//
//      Version: Created for Teradata Database 14.10
//
//      Requires JDK 6.0 or later
//
//*********************************************************************

import java.sql.* ;
import java.io.Writer ;
import java.io.IOException ;

public class T21502J6
{
    // 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
    ,IOException
    {
        // Creation of URL to be passed to the JDBC driver
        String sUrl = "jdbc:teradata://whomooz/TMODE=ANSI,CHARSET=UTF8" ;

        String sCreateBooks = " CREATE TABLE SampleBooks (Id INTEGER,  " +
        " StoreName VARCHAR (100), Books XML)" ;

        String sDropBooks = " DROP TABLE SampleBooks " ;

        String sInsertBooks =
            " INSERT INTO SampleBooks VALUES" +
            " (?, ?, ?)" ;

        String sSelectBooks =
            " SELECT * FROM SampleBooks" +
            " ORDER BY 1 " ;

        String sSelectFirstBook =
            " SELECT StoreName, Books.xmlextract ('/books/book[1]', NULL)  AS FirstBook FROM SampleBooks " +
            " ORDER BY StoreName " ;

        try
        {
            System.out.println ("\n Sample T21502J6: \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") ;

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

            // Creating a connection object
            Connection con = DriverManager.getConnection (sUrl, sUser, sPassword) ;
            System.out.println (" User " + sUser + " connected.") ;
            System.out.println (" Connection to Teradata established. \n") ;
            try
            {
                // Creating a Statement, drop the table and ignore any exception
                // thrown from the drop since the table may not exist yet.
                Statement stmt = con.createStatement () ;
                try
                {
                    try
                    {
                        stmt.executeUpdate (sDropBooks) ;
                        System.out.println (" SampleBooks Table has been dropped ") ;
                    }
                    catch (SQLException ex)
                    {
                        System.out.println (" Ignoring exception " + ex.getMessage ()) ;
                    }

                    // Creating the table
                    System.out.println (" \n Creating SampleBooks table .... ") ;
                    stmt.executeUpdate (sCreateBooks) ;
                    System.out.println (" SampleBooks table has been created. ") ;

                    // Creating a PreparedStatement object from an active
                    // connection. A PreparedStatement is an object that represents
                    // a precompiled SQL statement and allows for efficient
                    // execution of the precompiled SQL statement multiple times.
                    System.out.println (" Preparing this Insert statement for execution:\n "
                                        + sInsertBooks) ;
                    PreparedStatement pstmt = con.prepareStatement (sInsertBooks) ;
                    System.out.println (" Prepared statement object created. \n") ;

                    try
                    {

                        // Set parameter values indicated by ? (dynamic update)
                        // PreparedStatement.setInt, PreparedStatement.setString and
                        // PreparedStatement.setSQLXML methods will be demonstrated.
                        System.out.println (" Using setInt (), setString () and setSQLXML () to " +
                                             "assign values to parameter markers:\n") ;
                        pstmt.setInt (1, 1) ;

                        pstmt.setString (2, "Lake Book Store") ;

                        // Creating an SQLXML object and using setString to set
                        // the XML value.
                        System.out.println (" Creating SQLXML object and setting the value. ") ;
                        SQLXML xmlBooks = con.createSQLXML () ;
                        xmlBooks.setString (
                            "<books>"
                             +"<book>"
                               +"<title id=\"103\">Gone Today</title>"
                               +"<author>Mel Smith</author>"
                               +"<publisher>Family Books</publisher>"
                               +"<price>5.50</price>"
                             +"</book>"
                             +"<book>"
                               +"<title id=\"555\">In Town</title>"
                               +"<author>Sam Waters</author>"
                               +"<publisher>Family Books</publisher>"
                               +"<price>9.99</price>"
                             +"</book>"
                            +"</books>") ;

                        // Binding the SQLXML object to parameter 3
                        pstmt.setSQLXML (3, xmlBooks) ;

                        // Calling SQLXML.free to free up any resources
                        xmlBooks.free () ;

                        System.out.println (
                            " Inserting first row into SampleBooks. ") ;

                        // Calling executeUpdate to execute the SQL command after
                        // all of the parameter values have been set.
                        pstmt.executeUpdate () ;

                        pstmt.setInt (1, 2) ;

                        pstmt.setString (2, "Children's Book Store") ;

                        // Creating an SQLXML object and using setCharacterStream
                        // to set the XML value.
                        xmlBooks = con.createSQLXML () ;

                        Writer writer = xmlBooks.setCharacterStream () ;
                        String sBooks =
                        "<books>"
                         +"<book>"
                           +"<title id=\"I255\">Here Tomorrow</title>"
                           +"<author>James Locker</author>"
                           +"<publisher>Snow Publishing</publisher>"
                           +"<price>150.50</price>"
                         +"</book>"
                         +"<book>"
                           +"<title id=\"I301\">Out of Town</title>"
                           +"<author>Mary Winder</author>"
                           +"<publisher>Star Publishing</publisher>"
                           +"<price>25.99</price>"
                         +"</book>"
                        +"</books>" ;

                        writer.write (sBooks) ;

                        writer.flush () ;
                        writer.close () ;

                        pstmt.setSQLXML (3, xmlBooks) ;
                        xmlBooks.free () ;

                        System.out.println (
                            " Inserting second row into SampleBooks. ") ;

                        // Calling executeUpdate to execute the SQL command after
                        // all of the parameter values have been set.
                        pstmt.executeUpdate () ;
                    }
                    finally
                    {
                        // Closing the PreparedStatement
                        pstmt.close () ;
                    }

                    // The following methods will perform a SELECT query on the table
                    // and will display the results of the query

                    // Selecting all data in the table
                    executeAndDispResults (con, sSelectBooks) ;

                    // Selecting only the store name and the first book in the XML document
                    executeAndDispResults (con, sSelectFirstBook) ;

                }
                finally
                {
                    // Closing the Statement
                    System.out.println ("\n Closing Statement object ...") ;
                    stmt.close () ;
                    System.out.println (" Connection has been closed.") ;
                }
            }
            finally
            {
                // Closing the connection
                System.out.println ("\n Closing Connection ... ") ;
                con.close () ;
                System.out.println (" Connection has been closed ") ;
            }

            System.out.println ( " Sample T21502J6 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 executeAndDispResults (Connection con, String sQuery) throws SQLException
    {
        // Creating a Statement
        Statement stmt = con.createStatement () ;
        try
        {
            // Selecting data from the table.
            System.out.println ("\n Executing Select query ... ") ;
            ResultSet rs = stmt.executeQuery (sQuery) ;

            try
            {
                // Displaying the results of the SELECT query
                System.out.println () ;
                System.out.println (" Results from " + sQuery ) ;
                ResultSetMetaData rsmd = rs.getMetaData () ;
                int numCols = rsmd.getColumnCount () ;
                System.out.println () ;
                while (rs.next ())
                {
                    for (int nCol = 1 ; nCol <= rs.getMetaData ().getColumnCount () ; nCol ++)
                    {
                        Object ob = rs.getObject (nCol) ;

                        System.out.println (" " + rs.getMetaData ().getColumnName (nCol) + " : "
                                            + (ob instanceof SQLXML
                                               ? rs.getSQLXML (nCol).getString ()
                                               : rs.getObject (nCol))) ;

                        // Freeing up resources held by SQLXML
                        if (ob instanceof SQLXML)
                            ((SQLXML) ob).free () ;
                        System.out.println () ;
                   }
                   System.out.println () ;
               }
               System.out.println ("**********************************************************************") ;
            }
            finally
            {
                // Closing the ResultSet
                rs.close () ;
            }
        }
        finally
        {
            // Closing the Statement
            stmt.close () ;
        }
    } // end dispResultSet

} // end class T21502J6