Example: Java External Stored Procedure With a Period Parameter | Vantage - Example: Java External Stored Procedure With a Period Parameter - Analytics Database - Teradata Vantage

The following Java external stored procedure takes a Period type parameter and uses JDBC to insert it into a table. The value returned from the SELECT statement is returned as the OUT parameter.

//*********************************************************************
//
//  File:       JXSP_Period.java
//  Header:     none
//  Purpose:    Demonstrate a JXSP pdt_proc1 with SQL access. 
//              The JXSP will:
//               -  Take a Period(DATE) IN Parameter
//               -  Connect as user guest/please
//               -  Insert the Period(DATE) passed in into a table
//               -  Select the Period(DATE) column from the table
//               -  The value returned by SELECT is set as the
//                  OUT parameter value.
//
//  JDBC API: java.sql.Connection, java.sql.Statement,
//            java.sql.Statement.executeUpdate
//
//
//*********************************************************************

import java.sql.*;
import java.io.*;

public class JXSP_Period
{
    // Name of the user able to create, drop, and manipulate tables
    public static String sUser = "guest";
    public static String sPassword = "please";
    
    /* REPLACE PROCEDURE PDT_PROC1(IN P1 PERIOD(DATE), OUT P2 PERIOD(DATE))
       LANGUAGE JAVA
       NO SQL
       PARAMETER STYLE JAVA 
       EXTERNAL NAME 'JXSP_NEWTYPES:JXSP_Period.pdt_proc1';

       CREATE TABLE PerTypes(i int, P period(date)); 

     */

    public static void pdt_proc1(java.sql.Struct p1, java.sql.Struct p2[]) throws SQLException
    {
        // Creation of URL to be passed to the JDBC driver
        String url = "jdbc:teradata://whomooz/TMODE=ANSI,CHARSET=UTF8";
        try
        {
            // Loading the Teradata JDBC driver
            Class.forName("com.teradata.jdbc.TeraDriver");

            // Creating a connection object
            Connection con=DriverManager.getConnection(url,sUser, sPassword);
            try
            {
               String insertStmt =  " INSERT INTO PerTypes ( 1 ,?,)";
               String selectStmt = " SELECT * FROM PerTypes ORDER BY 1";
              
                // Create a statement object from an active connection.
                Statement stmt = con.createStatement();
                                    
               try
               {
                 // Creating a prepared statement object
                  System.out.println("\n Preparing: " + insertStmt);
                  PreparedStatement pStmt =
                           con.prepareStatement(insertStmt);
                  try
                   {
                    // Call the following method to insert rows into the
                    // sample table
              	  insertRows(con, pStmt, p1);// Insert the IN parameter value

                   // The following code will perform a SELECT query
                   // on the table.
                   stmt = con.createStatement();
                                
                   // Submit a query, creating a result set object
                   ResultSet rs = stmt.executeQuery(selectStmt);
                                                              
                 // iterate through all returned rows and display them 
                 // (should be only 1 row)
                 while (rs.next())
                 {
                    // retrieve the PERIOD ( DATE ) Struct
                    Struct perDt = (Struct) rs.getObject(2);
                                     
                    //Set the output value 
                    p2[0] = perDt;

                  } // End while
                }
                finally
                {
                     // close the PreparedStatement
                     pStmt.close();
                     System.out.println(
                                    "\n PreparedStatement object closed.\n");
                 }
                        
               }
               finally
               {
                  // close the statement
                  stmt.close();
                 
               }
              }
              finally
              {
                 //Close the connection
                    System.out.println(" Closing Connection");
                    con.close();
                 
                }
            }
            catch (SQLException ex)
            {
                // An 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.") ;
            }
        }


  public static void insertRows(Connection con, PreparedStatement pStmt,
            Struct period) throws SQLException
  {
   // The following code will perform an INSERT query
   // on the sample table.
   System.out.println("\n Inserting Row...");
   pStmt.setInt (1, 1)  ;
   pStmt.setObject (2, period) ;
   pStmt.executeUpdate () ;
  } // End insertRows


    public static void dropObject(Connection con, String dropQuery)
    {
        try
        {
            // The following code will be used to perform a DROP query
            Statement stmt = con.createStatement();
            System.out.println(" Executing command "+dropQuery+"...");
            stmt.executeUpdate(dropQuery);
            System.out.println(" Command executed successfully.");
        }
        catch (SQLException ex)
        {
            // If the table did not exist, no drop is required.
            // Ignore the raised "no table present" exception by
            // printing out the error message and swallowing the
            // exception.
            System.out.println(" Ignoring exception: " + ex);
        }
    } // End dropObject
    
} // End of class JXSP_Period