//********************************************************************* // // Copyright (c) 2006-2008 by Teradata Corporation // All Rights Reserved // //********************************************************************* // // File: T20404JD.java // Header: none // Purpose: Demonstrate basic Teradata SQL using the JDBC API by // updating a lob using the set methods. // The program will: // - Connect as user guest/please // - Select a row from the employee2 table // - Retrieve a CLOB value and update it // - Update the row in the employee2 table // using the updated LOB // - Display updated LOB // - Disconnect. // // JDBC API: java.sql.Connection, java.sql.PreparedStatement, // java.sql.PreparedStatement.executeUpdate, java.sql.Clob // // Version: Supported with TTU 12 Teradata JDBC Driver // //********************************************************************* import java.lang.reflect.*; import java.sql.*; public class T20404JD { // 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," + "LOB_TEMP_TABLE=JdbcLobUpdate"; // Creating strings representing a prepared statement and its // respective parameter values String sUpdate = "UPDATE employee2 " + "SET empResume = ? WHERE empID = ?"; String sSelect = "SELECT empResume FROM employee2 " + "WHERE empID = ?"; String newTraining = "Completed Teradata training"; int Id = 100002; try { System.out.println("\n Sample T20404JD: \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 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 { System.out.println(" Preparing this SQL statement for" + " select:\n " + sSelect); // Creating a prepared statement object from an active connection PreparedStatement pstmt = con.prepareStatement(sSelect); System.out.println(" Prepared statement object created. \n"); try { // The following code will perform a SELECT empResume // from the table for the specified employee id System.out.println(" Selecting employee resume...\n"); System.out.println(" Using setInt() to bind employee id" + " to parameter markers:\n"); System.out.println(" FIRST ? set to: " + Id+"\n"); // Set the employee id pstmt.setInt(1, Id); // Submit a query, creating a result set object ResultSet rs = pstmt.executeQuery(); rs.next(); // Retrieve the resume so we can update it Clob empResume = rs.getClob(1); try { // Update the resume with the additional information System.out.println(" Updating employee resume using"+ " Clob.setString...\n"); empResume.setString(empResume.length() + 1, newTraining, 0, newTraining.length()); // Check to see if updates made to a LOB are made on a copy // or directly to the LOB. The Teradata JDBC driver updates // are made on a copy so it will be necessary to update the // original table if (con.getMetaData().locatorsUpdateCopy()) { // Prepare the update request PreparedStatement pstmtUpd = con.prepareStatement(sUpdate); try { // The following code will perform an UPDATE to the table System.out.println(" Since updates are made to a copy,"+ " attempting to update the original"+ " table...\n"); System.out.println(" Using setClob(), setInt() " + "to bind values to parameter markers:\n"); // Set parameter values indicated by ? (dynamic update) // Set employee resume pstmtUpd.setClob(1, empResume); System.out.println(" FIRST ? set to updated resume "); // Set employee ID number pstmtUpd.setInt(2, Id); System.out.println(" SECOND ? set to: " + Id); // Call without parameter to execute SQL command int RowCount = pstmtUpd.executeUpdate(); System.out.println("\n Update completed successfully: " + RowCount + " row(s) updated."); } finally { pstmtUpd.close () ; } } } finally { callLobFree (empResume) ; } } finally { // Close the statement pstmt.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 T20404JD 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 // The free method is available beginning with Teradata JDBC Driver 14.00.00.08. // If the application updates a Blob or Clob object, then the application // must call free when the application is done with the object; otherwise, // Teradata Database Error 3130 (Response Limit Exceeded) may occur. // With JDK 6.0 and later, the free method is defined in the java.sql.Blob and // Clob interfaces, and the free method can be called directly by the application. // With JDK 5.0 and earlier, reflection must be used to call the free method. public static void callLobFree (Object obj) throws SQLException { if (! (obj instanceof Blob) && ! (obj instanceof Clob)) throw new IllegalArgumentException ( "Unexpected object type " + obj.getClass ().getName ()) ; try { Method meth = obj.getClass ().getMethod ("free", new Class [0]) ; meth.invoke (obj, new Object [0]) ; } catch (Exception ex) { if (ex instanceof InvocationTargetException && ex.getCause () instanceof SQLException) throw (SQLException) ex.getCause () ; // Ignore the lack of a free method in earlier versions of // the Teradata JDBC Driver ex.printStackTrace () ; } } // end callLobFree } // End class T20404JD