//********************************************************************* // // Copyright (c) 2004-2008 by Teradata Corporation // All Rights Reserved // //********************************************************************* // // File: T21500JD.java // Header: none // Purpose: Demonstrate workarounds for data type conversions. // The program will: // - Connect as user guest/please // - Drop table conversions if it exists // - Create table conversions with the following columns: // c0 (DATE), // c1 (TIME), // c2 (TIMESTAMP) // - Execute conversions with a PreparedStatement // - Disconnect. // // JDBC API: java.sql.Connection, java.sql.Statement, // java.sql.PreparedStatement, // java.sql.Statement.executeUpdate // // Version: Updated for Teradata V2R6 // // Note: The workarounds demonstrated in this program are no longer // needed when Teradata JDBC Driver 12.0 or later is used in conjunction // with Teradata Database 12.0 or later. The Teradata JDBC Driver now // works with Teradata Database 12.0 and later to provide more precise // classification for DATE, TIME, and TIMESTAMP values bound to // PreparedStatement question-mark parameter markers, // thereby enabling the Teradata Database to provide better implicit // data type conversion for those data types. // //********************************************************************* import java.sql.*; public class T21500JD { // 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"; // Statements used in table creation String sDropTbl = "DROP TABLE conversions"; String sCreateTbl = "CREATE TABLE conversions (colIndex INTEGER PRIMARY KEY NOT NULL, " + "colDate DATE, colTime TIME, colTimestamp TIMESTAMP)"; try { System.out.println("\n Sample T21500JD: \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 procedure: // 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"); } // Create the sample table System.out.println(" Creating table: " + sCreateTbl); stmt.executeUpdate(sCreateTbl); System.out.println(" Sample table created. \n"); // Demonstrate workarounds for data type conversions // Use a PreparedStatement.setDate to bind to a timestamp column. System.out.println(" Binding a date to a timestamp column"); PreparedStatement pstmt = con.prepareStatement( "INSERT INTO conversions (colIndex, colTimestamp)" + " VALUES (1, cast(cast(? as date) as timestamp))"); Date date = new Date(System.currentTimeMillis()); pstmt.setDate(1, date); pstmt.executeUpdate(); // Close the PreparedStatement pstmt.close(); // Use a PreparedStatement.setTime to bind to a timestamp column. System.out.println(" Binding a time to a timestamp column"); pstmt = con.prepareStatement( "INSERT INTO conversions (colIndex, colTimestamp)" + " VALUES (2, cast(cast(? as time) as timestamp))"); Time time = new Time(System.currentTimeMillis()); pstmt.setTime(1, time); pstmt.executeUpdate(); // Close the PreparedStatement pstmt.close(); // Use a PreparedStatement.setTimestamp to bind to a date column. System.out.println(" Binding a timestamp to a date column"); pstmt = con.prepareStatement( "INSERT INTO conversions (colIndex, colDate)" + " VALUES (3, cast(cast(? as timestamp) as date))"); Timestamp timeStamp = new Timestamp(System.currentTimeMillis()); pstmt.setTimestamp(1, timeStamp); pstmt.executeUpdate(); // Close the PreparedStatement pstmt.close(); // Use a PreparedStatement.setTimestamp to bind to a time column. System.out.println(" Binding a timestamp to a time column"); pstmt = con.prepareStatement( "INSERT INTO conversions (colIndex, colTime)" + " VALUES (4, cast(cast(? as timestamp) as time))"); timeStamp = new Timestamp(System.currentTimeMillis()); pstmt.setTimestamp(1, timeStamp); pstmt.executeUpdate(); // Close the PreparedStatement pstmt.close(); // Use a PreparedStatement.setObject(java.sql.Date) to bind to a // timestamp column. System.out.println( " Binding an object (java.sql.Date) to a timestamp column"); pstmt = con.prepareStatement( "INSERT INTO conversions (colIndex, colTimestamp)" + " VALUES (5, cast(cast(? as date) as timestamp))"); date = new Date(System.currentTimeMillis()); pstmt.setObject(1, date); pstmt.executeUpdate(); // Close the PreparedStatement pstmt.close(); // Use a PreparedStatement.setObject(java.sql.Timestamp) to bind // to a date column. System.out.println( " Binding an object (java.sql.Timestamp) to a date column"); pstmt = con.prepareStatement( "INSERT INTO conversions (colIndex, colDate)" + " VALUES (6, cast(cast(? as timestamp) as date))"); timeStamp = new Timestamp(System.currentTimeMillis()); pstmt.setObject(1, timeStamp); pstmt.executeUpdate(); // Close the PreparedStatement pstmt.close(); // Use a PreparedStatement.setObject(java.sql.Timestamp) to bind // to a time column. System.out.println( " Binding an object (java.sql.Timestamp) to a time column"); pstmt = con.prepareStatement( "INSERT INTO conversions (colIndex, colTime)" + " VALUES (7, cast(cast(? as timestamp) as time))"); timeStamp = new Timestamp(System.currentTimeMillis()); pstmt.setObject(1, timeStamp); pstmt.executeUpdate(); // Close the PreparedStatement pstmt.close(); // Display results System.out.println("\n Displaying ResultSet: \n"); ResultSet rs = stmt.executeQuery("SELECT * FROM conversions"); while (rs.next()) { System.out.println(" " + rs.getDate(2) + ", " + rs.getTime(3) + ", " + rs.getTimestamp(4)); } } 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 T21500JD 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 } // End class T21500JD