//********************************************************************* // // Copyright (c) 2004-2008 by Teradata Corporation // All Rights Reserved // //********************************************************************* // // File: T20203JD.java // Header: none // Purpose: Demonstrate insertion of LOB values using parameter // markers. // The program will: // - Connect as user guest/please // - Perform a series of insertions into the table using // parameter markers for the values // - Disconnect. // // JDBC API: java.sql.Connection, java.sql.PreparedStatement, // java.sql.PreparedStatement.executeUpdate // // Version: Updated for Teradata V2R6 // //********************************************************************* import java.sql.*; import java.io.*; public class T20203JD { // 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, FileNotFoundException { // 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 employee2 VALUES(?,?,?,?,?,?,?)"; int[] Id = {100002, 100003, 100004, 100005, 100006, 100007, 100008}; String[] Name = {"James Parker", "Mary Jones", "John Walker", "Steven Brown", "Susan Young", "Brian Lee", "Robert Martinez"}; String[] Department = {"Marketing", "Product Development", "Human Resources", "Customer Service", "Product Development", "Marketing", "Human Resources"}; String[] Description = {"Manager coordinating international sales", "Software engineer responsible for regression test plans", "Manager responsible for employee benefits programs", "Software engineer providing technical applications support", "QA engineer overlooking overseas testing", "Team leader managing market research", "Recruiter in charge of university relations"}; // Flag setting signaling that the BLOB data was inserted by // streaming data out of a file byte isRaw = 0; try { System.out.println("\n Sample T20203JD: \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 execution:\n " + sInsert); // Creating a prepared statement object from an active connection PreparedStatement pstmt = con.prepareStatement(sInsert); System.out.println(" Prepared statement object created."); try { int RowCount; // Return value for row count // The following code will perform a series of INSERTions // into the table using a prepared statement. // Retrieving the number of insertions by using ID field // to count, since ID can never be null int len = Id.length; for (int i = 0; i < len; i++) { System.out.println("\n Attempting an insertion...\n"); // Set parameter values indicated by ? (dynamic update) // PreparedStatement.setInt, PreparedStatement.setString // PreparedStatement.setAsciiStream, and // PreparedStatement.setBinaryStream 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 setString() to assign" + " values to parameter markers 1-4:"); // Set employee ID number pstmt.setInt(1, Id[i]); System.out.println(" FIRST ? set to: " + Id[i]); // Set employee name pstmt.setString(2, Name[i]); System.out.println(" SECOND ? set to: " + Name[i]); // Set employee department name pstmt.setString(3, Department[i]); System.out.println(" THIRD ? set to: " + Department[i]); // Set job description pstmt.setString(4, Description[i]); System.out.println(" FOURTH ? set to: " + Description[i]); System.out.println( " Using setAsciiStream() and setBinaryStream()" + " to assign values to parameter markers 5-6:"); // Create a new file object to enable access to the // resume document File res = new File(Id[i] + ".txt"); // Create a new file input stream object to enable // reading of data from the text file FileInputStream empResume = new FileInputStream(res); // Set employee resume CLOB: set the parameter value // to the input stream above, which will have the // specified number of bytes. pstmt.setAsciiStream(5, empResume, (int)(res.length())); System.out.println(" FIFTH ? set to the file: " + Id[i] + ".txt"); // Create a new file object to enable access to the // photograph File photo = new File(Id[i] + ".jpg"); // Create a new file input stream object to enable // reading of data from the binary file FileInputStream empPhoto = new FileInputStream(photo); // Set employee photo BLOB: set the parameter value // to the input stream above, which will have the // specified number of bytes. pstmt.setBinaryStream(6, empPhoto, (int)(photo.length())); System.out.println(" SIXTH ? set to the file: " + Id[i] + ".jpg"); // Set insertion type flag, 0 for insertion from a file pstmt.setByte(7, isRaw); System.out.println(" SEVENTH ? set to : " + isRaw); // Call without parameter to execute the SQL command RowCount = pstmt.executeUpdate(); System.out.println("\n Insertion completed successfully: " + RowCount + " row(s) inserted."); } } 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 T20203JD 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 T20203JD