//********************************************************************* // // Copyright (c) 2013 by Teradata Corporation // All Rights Reserved // //********************************************************************* // // File: T21506JD.java // Header: none // Purpose: Demonstrate setting question-mark parameter markers to PERIOD // TIMESTAMP with TIME ZONE using setObject. // // Prior to Teradata JDBC 14.10, the Teradata JDBC Driver did not // support setting PERIOD types WITH TIME ZONE. Now they can be // set using a Struct object that contains the TIME or TIMSTAMP // as one attribute value and the TIME ZONE as a second attribute // value. // // The program will: // - Connect as user guest/please // - Create a table with PERIOD(TIMESTAMP WITH TIME ZONE) types // - Insert values into table using setObject // - Select the data from the table and display the results // - Disconnect. // // JDBC API: java.sql.Connection, java.sql.Statement, // java.sql.PreparedStatement.setObject, // java.sql.PreparedStatement.addBatch, // java.sql.PreparedStatement.executeBatch // //********************************************************************* import java.sql.* ; import java.io.* ; import java.util.Calendar ; import java.util.TimeZone ; public class T21506JD { // 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" ; // Table creation command and its columns String sCreateTable = "\nCREATE TABLE SchoolTerms " + "\n(SchoolName VARCHAR (30), " + "\n FallTerm PERIOD(TIMESTAMP WITH TIME ZONE), " + "\n WinterTerm PERIOD(TIMESTAMP WITH TIME ZONE)) " ; String sDropTbl = " DROP TABLE SchoolTerms" ; String sInsert = " INSERT INTO SchoolTerms VALUES (?, ?, ?)" ; String sSelect = " SELECT * " + " FROM SchoolTerms ORDER BY 1" ; Calendar calWest = Calendar.getInstance (TimeZone.getTimeZone ("GMT-08:00")) ; Calendar calEast = Calendar.getInstance (TimeZone.getTimeZone ("GMT-05:00")) ; // Data that will be used to set parameter markers using setObject Object [] [] aaoSchoolTermData = { { "UCLA", new SampStruct ("PERIOD(TIMESTAMP WITH TIME ZONE)", new Object [] { // Start timestamp new SampStruct ("TIMESTAMP WITH TIME ZONE", new Object [] { Timestamp.valueOf ("2013-08-20 09:10:06.974"), calWest}), // End timestamp new SampStruct ("TIMESTAMP WITH TIME ZONE", new Object [] { Timestamp.valueOf ("2013-12-14 01:56:46.974"), calWest}) }), new SampStruct ("PERIOD(TIME WITH TIME ZONE)", new Object [] { new SampStruct ("TIMESTAMP WITH TIME ZONE", new Object [] { Timestamp.valueOf ("2014-01-17 19:16:46.974"), calWest}), new SampStruct ("TIMESTAMP WITH TIME ZONE", new Object [] { Timestamp.valueOf ("2014-05-02 00:16:46.974"), calWest}) } ), } , { "USU", new SampStruct ("PERIOD(TIME WITH TIME ZONE)", new Object [] { new SampStruct ("TIMESTAMP WITH TIME ZONE", new Object [] { Timestamp.valueOf ("2013-09-12 12:43:26.974"), calEast}), new SampStruct ("TIMESTAMP WITH TIME ZONE", new Object [] { Timestamp.valueOf ("2013-12-16 09:30:06.974"), calEast}) } ), new SampStruct ("PERIOD(TIME WITH TIME ZONE)", new Object [] { new SampStruct ("TIMESTAMP WITH TIME ZONE", new Object [] { Timestamp.valueOf ("2014-01-20 02:50:06.974"), calEast}), new SampStruct ("TIMESTAMP WITH TIME ZONE", new Object [] { Timestamp.valueOf ("2014-05-04 07:50:06.974"), calEast}) } ), } } ; try { System.out.println ("\n Sample T21506JD: \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 statement object from an active connection Statement stmt = con.createStatement () ; System.out.println (" Statement object created. \n") ; try { // Cleanup table // If the sample table already exists, drop it. try { System.out.println(" Dropping table if present: " + sDropTbl); stmt.executeUpdate(sDropTbl); System.out.println(" Table dropped.\n"); } 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(" Drop table exception ignored: " + ex); System.out.println(" Table could not be dropped." + " Execution will continue...\n"); } System.out.println (" Attempting to create a sample table : " + sCreateTable) ; // Sending the create table request to the database, stmt.executeUpdate (sCreateTable) ; System.out.println (" Table created successfully.") ; // Inserting a row of data into the sample table. The // insert request is first prepared and then executed. PreparedStatement pStmt = con.prepareStatement (sInsert) ; try { // Setting parameter value for (int nSchoolTerm = 0 ; nSchoolTerm < aaoSchoolTermData.length ; nSchoolTerm ++) { for (int nParam = 1 ; nParam <= aaoSchoolTermData [nSchoolTerm].length ; nParam ++) { // Use setObject to set all parameters including TIME // WITH TIME ZONE and TIMESTAMP WITH TIME ZONE pStmt.setObject (nParam, aaoSchoolTermData [nSchoolTerm] [nParam - 1]) ; } // Adding parameter set to the batch System.out.println ("\n Adding parameter set to the batch ...") ; pStmt.addBatch () ; } // Executing insert batch statements System.out.println ("\n Executing batch statements"); pStmt.executeBatch () ; System.out.println ("\n Selecting Data ...") ; ResultSet rs = stmt.executeQuery (sSelect) ; // Displaying results from select System.out.println (" Select successful.") ; System.out.println ("\n Displaying output of the data in SchoolTerms ") ; while (rs.next ()) { System.out.println (" -----------------") ; System.out.println (" SchoolTerm Name : " + rs.getObject (1)) ; System.out.println (" Fall Term Start : " + ((Struct) rs.getObject (2)).getAttributes()[0]) ; System.out.println (" Fall Term End : " + ((Struct) rs.getObject (2)).getAttributes()[1]) ; System.out.println (" Winter Term Start : " + ((Struct) rs.getObject (3)).getAttributes()[0]) ; System.out.println (" Winter Term End : " + ((Struct) rs.getObject (3)).getAttributes()[1]) ; System.out.println () ; } } finally { // Close the Prepared Statement pStmt.close () ; System.out.println ("\n Prepared Statement object closed. \n") ; } } 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 T21506JD 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.createStruct, which is // only supported in JDK 6.0 or later public static class SampStruct implements Struct { private Object[] m_attributes ; private String m_sqlTypeName ; public SampStruct (String sqlTypeName, Object[] attributes) { m_sqlTypeName = sqlTypeName ; m_attributes = attributes ; } // Returns attributes public Object[] getAttributes () throws SQLException { return m_attributes ; } // Returns SQLTypeName public String getSQLTypeName () throws SQLException { return m_sqlTypeName ; } // This method is not supported, but needs to be included public Object[] getAttributes (java.util.Map map) throws SQLException { //Unsupported Exception throw new SQLException ("getAttributes (Map) NOT SUPPORTED") ; } } // End of class SampStruct } // End class T21506JD