//********************************************************************* // // Copyright (c) 2011 by Teradata Corporation // All Rights Reserved // //********************************************************************* // // File: T22102JD.java // Header: none // Purpose: Demonstrate processing of INSERT and SELECT statements // for single dimensional array data type. // The program will: // - Connect as user guest/please // - Perform a insertion into the table empPhoneNumbers // - Select from the table empPhoneNumbers // - Disconnect. // // JDBC API: java.sql.Connection, java.sql.PreparedStatement, // java.sql.PreparedStatement.executeUpdate, java.sql.Array // // Version: Updated for Teradata Database 14.0 // //********************************************************************* import java.sql.* ; import java.util.Map ; import java.util.Arrays ; public class T22102JD { // 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 prepared statement and its parameter values, // respectively String sInsert = "INSERT INTO empPhoneNumbers VALUES (?,?)" ; int Id = 100008 ; String [] asPhoneNumbers = { "3104315555" ,"8586593332" ,"8006162950" } ; // Creation of the SQL SELECT statement String sSelect = "SELECT ID, PhoneNumbers FROM empPhoneNumbers " ; try { System.out.println ("\n Sample T22102JD: \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 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 SQL statement for execution:\n " + sInsert) ; PreparedStatement pstmt = con.prepareStatement (sInsert) ; System.out.println (" Prepared statement object created. \n") ; try { int RowCount ; // Return value for row count // The following code will perform an INSERT into the table // using a prepared statement using the // PreparedStatement.executeUpdate method to execute SQL. System.out.println (" Attempting an insertion...\n") ; // Set parameter values indicated by ? (dynamic update) // PreparedStatement.setInt and PreparedStatement.setString // 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 setInt () and setArray () to " + "assign values to parameter markers:\n") ; pstmt.setInt (1, Id) ; System.out.println (" FIRST ? set to: " + Id) ; // Create the array to be inserted into column 2 // Use the class defined at the bottom of this sample Array empPhoneArray = new SampArray ( "CHAR" , asPhoneNumbers) ; pstmt.setArray (2, empPhoneArray) ; System.out.println (" SECOND ? set to:\n " +" SYSUDTLIB.PhoneNumbersArray: " + Arrays.asList(asPhoneNumbers)) ; // Once all of the parameter values have been set, call // executeUpdate to execute the now complete SQL command. // Display row count returned by the method. RowCount = pstmt.executeUpdate (); System.out.println (" Insertion completed successfully: " + RowCount + " row(s) inserted.") ; } finally { // Close the statement pstmt.close () ; System.out.println ("\n PreparedStatement object closed. \n") ; } // Creating a statement object from an active connection Statement stmt = con.createStatement () ; System.out.println (" Statement object created. \n") ; try { // The following code will perform a SELECT query on the table. System.out.println (" Using executeQuery () to execute the " + "following SQL statement:\n " + sSelect) ; // Submit a query, creating a result set object ResultSet rs = stmt.executeQuery (sSelect) ; // Extract and display result set table data System.out.println () ; System.out.println (" DISPLAYING RESULT SET DATA:") ; System.out.println (" ---------------------------") ; int rowCount = 0 ; // result set row counter int id ; // placeholder for id Array array ; // placeholder for arrray Object [] phoneNumbers ; // placeholder for array elements // iterate through all returned rows and display them while (rs.next ()) { rowCount++ ; // increment retrieved row counter System.out.println () ; System.out.println (" ROW " + rowCount + ": ") ; // retrieve and display first column value id = rs.getInt (1) ; System.out.println (" Employee ID: " + id) ; // retrieve and display second column value array = rs.getArray (2) ; // Use the method getArray () to retrieve the array // elements phoneNumbers = (Object []) array.getArray () ; for (int i = 0 ; i < phoneNumbers.length ; i++) System.out.println (" Phone Number " + (i + 1) + " : " + phoneNumbers [i]) ; // Another way to retrieve the array elements is // by using the method Array.getResultSet. The Result // Set contains two columns. The 2nd column contains // the element value and the 1st column stores the // index into the array for that element (index starts // at 1). It contains one row for each array element. System.out.println("Displaying ResultSet from Array.getResultSet:\n"); ResultSet arrayRS = array.getResultSet () ; // iterate through all rows and display them while (arrayRS.next ()) { System.out.println () ; // retrieve and display the index value int index = arrayRS.getInt (1) ; System.out.println (" Array Index: " + index) ; // retrieve and display the element value String phoneNumber = arrayRS.getString (2) ; System.out.println (" Array element: " + phoneNumber) ; } } System.out.println ("\n " + rowCount + " Row (s) returned.") ; } finally { // Close the statement stmt.close () ; System.out.println ("\n Statement 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 T22102JD 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 T22102JD