//********************************************************************* // // Copyright (c) 2004-2008 by Teradata Corporation // All Rights Reserved // //********************************************************************* // // Notes: This module will demonstrate 3 kinds of multi-statement // requests: // - with non-SELECT statements only (T20700JD) // - with SELECT statements only (T20701JD) // - with both SELECT/non-SELECT statements (T20702JD) // All changes made in this module will be made to the // employee table only. Furthermore, after the execution // of this module (T20700JD-T20702JD), all changes to the // employee table will be reversed to maintain consistency // with the employee2 table. Changes to the employee2 table // can be made analogously. Please refer to the earlier // examples for help on using UPDATE/INSERT/DELETE/SELECT // statements with LOB values. // // File: T20700JD.java // Header: none // Purpose: Demonstrate basic Teradata SQL using the JDBC API by // creating/executing multi-statement requests containing // non-SELECT statements only. // The program will: // - Connect as user guest/please // - Perform a multi-statement request // - Obtain and display the results // - Disconnect. // // JDBC API: java.sql.Connection, // java.sql.Statement, // java.sql.Statement.addBatch, // java.sql.Statement.executeBatch, // java.sql.Statement.execute, // java.sql.Statement.getUpdateCount, // java.sql.Statement.getResultSet, // java.sql.Statement.getMoreResults, // java.sql.PreparedStatement, // java.sql.PreparedStatement.addBatch, // java.sql.PreparedStatement.executeBatch // // Version: Updated for Teradata V2R6 // //********************************************************************* import java.sql.*; public class T20700JD { // 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 SQL statemens to be processed as part of the // multi-statement request: // Statements to be executed with Statement's update batching String sUpdate1 = "INSERT INTO employee " + "VALUES(100009, 'Jennifer Thompson', " + "'Product Development', 'Administrative assistant')"; String sUpdate2 = "UPDATE employee " + "SET empDept = 'Marketing', " + "empJob = 'Product manager directing industry analysis' " + "WHERE empID = 100005"; String sUpdate3 = "UPDATE employee " + "SET empJob = 'Partner development specialist' " + "WHERE empID = 100002"; String sUpdate4 = "UPDATE employee " + "SET empJob = 'National advertising manager' " + "WHERE empID = 100007"; // Statement to be executed using Statement.execute String sUpdate5 = // Update command 1 "DELETE FROM employee WHERE empID = 100009" + ";" + // Update command 2 "UPDATE employee " + "SET empDept = 'Customer Service', empJob = " + "'Software engineer providing technical applications support'" + "WHERE empID = 100005" + ";" + // Update command 3 "UPDATE employee " + "SET empJob = 'Manager overseeing international contracts' " + "WHERE empID = 100002" + ";" + // Update command 4 "UPDATE employee " + "SET empJob = 'Team leader managing market research' " + "WHERE empID = 100007"; // Statement to be executed using PreparedStatement's update batching String sPSUpdate = "UPDATE employee SET empJob = ? " + "WHERE empID = ?"; try { System.out.println("\n Sample T20700JD: \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 { // The following code will demonstrate three different // methods for performing multi-statement requests, along // with their limitations // Multi-statement requests can be performed by either // executing one SQL statement consisting of multiple // commands or by using update batching with the Statement // and the PreparedStatement classes. // Both of the Statement and the PreparedStatement objects // are created with a list of commands which can be // manipulated with the following methods: // addBatch(), clearBatch(), and executeBatch(). // Method addBatch() adds the specified command to the // current list, clearBatch() clears the current list, and // executeBatch() submits the current list of commands // for execution and if all commands are successfully executed, // returns an array of update counts. // For both objects, the following restriction applies: // only INSERT, UPDATE, and DELETE commands are allowed // to be added to the current batch with the addBatch(...) // method. When the current batch is executed with // executeBatch(), any SQL statement attempting to return // a ResultSet object will generate an exception. // For the PreparedStatement, all SLQ commands added to the // current batch must be of exactly the same kind and // structure, whereas Statement class accepts update // commands of all types. // If a Connection is in auto-commit mode, then all // its SQL statements will be executed and committed // as individual transactions. Otherwise, its SQL statements // are grouped into transactions that are terminated by // either commit() or rollback(). In that case, either all // statements are commited or none at all. // Turn off AutoCommit. con.setAutoCommit(false); // Creating a statement object from an active connection Statement stmt = con.createStatement(); System.out.println(" Statement object created. \n"); try { // The following code will demonstrate performing a // multi-statement request using update batching with // the Statement class. System.out.println("\n Performing first multi-statement" + " request..."); // Clear current command list. Not required in this case, // since the statement has just been created and not yet // used, but necessary when performing multiple batch // updates. The current batch should be cleared to prevent // statements left over from previous use from being // executed more than once. stmt.clearBatch(); // Add SQL commands to the current batch for this // statement object. System.out.println("\n Adding SQL command to the current" + " batch: " + sUpdate1); stmt.addBatch(sUpdate1); System.out.println(" Adding SQL command to the current" + " batch: " + sUpdate2); stmt.addBatch(sUpdate2); System.out.println(" Adding SQL command to the current" + " batch: " + sUpdate3); stmt.addBatch(sUpdate3); System.out.println(" Adding SQL command to the current" + " batch: " + sUpdate4 + "\n"); stmt.addBatch(sUpdate4); // Submit a batch of update commands for execution. // If any errors occur during processing, an SQLException // will be raised. Otherwise, update counts will be returned // for each statement in the order the statements have // been added to the batch. The update counts may be one of // the following values: // -- a number greater or equal to zero, indicating that the // command has been executed successfully and giving the // the number of rows in the database that have been // update as a result of the statement // -- a value of -2, indicating that the command has been // executed successfully, but that the number of rows // affected by the executed statement is unknown. If // a SQL command will attempt to return a result set // a value of -2 will be returned and a // BatchUpdateException will be raised. // -- a value of -3, indicating that the command failed // to execute successfully. int[] updateCounts = stmt.executeBatch(); // Dummy variable to hold temporary update counts int updResult =0; // Flag marking whether an error occured during execution. boolean error = false; // Iterate through update count values, if an error occurred // must rollback all updates, otherwise, can commit changes. // Display update counts and errors (if any). for (int i = 0; i < updateCounts.length; i++) { updResult = updateCounts[i]; if(updResult > 0 || updResult == -2) { System.out.println(" Command " + (i+1) + " executed successfully. " + updateCounts[i] + " row(s) updated."); } else { System.out.println(" Command " + (i+1) + " failed to execute successfully."); error = true; // Set error flag break; } } if(error) // If an error was present { System.out.println( "\n First multi-statement request failed.\n"); throw new IllegalStateException( "Error occured during command execution"); } else { // All statements have been processed successfully, // can commit all changes to the database. System.out.println("\n No errors have occurred. " + "Commit all changes."); con.commit(); System.out.println(" First multi-statement request " + "successfully completed .\n"); } // The following code will display a way of executing a // multi-statement request using the Statement.execute // method. The "execute" method executes a SQL statement // and indicates the form of the first result. It returns // true if the next result is a ResultSet or false if it is // an update count or if there are no more results. // getResultSet or getUpdateCount methods can be used to // retrieve the result, and getMoreResults method can be // used to move to any subsequent results in case multiple // results are returned. The getResultSet returns the // current result as a ResultSet or NULL if the result is // an update count or there are no more results. The // getUpdateCount returns the current result as an // update count or -1 if it is a ResultSet or there are // no more results. // Note: all changes made by the previos multi-statement // request will be reversed as a result of this request System.out.println("\n Performing second multi-statement " + "request...\n"); // Clear current batch to remove statements left over // from previous updates. stmt.clearBatch(); // reset error flag error = false; // Use Statement.execute method to execute a multi-statement // request. Only update counts are expected boolean firstRes = stmt.execute(sUpdate5); // Iterate through update count values, if an error occurred // must rollback all updates, otherwise, can commit changes. // Display update counts and errors (if any). if(firstRes) // If a ResultSet has been generated, error { // a ResultSet has been generated - error throw new IllegalStateException( "Unexpected results: ResultSet generated."); } else // Continue processing and display errors, if any { int count = 0; // processed command counter // While there are more update counts to process while(!((updResult = stmt.getUpdateCount()) == -1)) { count ++; if(updResult > 0 || updResult == -2) { System.out.println(" Command " + (count) + " executed successfully. " + updResult + " row(s) updated."); } else { System.out.println(" Command " + (count) + " failed to execute successfully."); error = true; // Set error flag break; } // Retrieve next result if(stmt.getMoreResults()) { // a ResultSet has been generated - error throw new IllegalStateException( "Unexpected results: ResultSet generated."); } } if(error) // If an error was present { System.out.println( "\n Second multi-statement request failed.\n"); throw new IllegalStateException( "Error occured during command execution"); } else { // All statements have been processed successfully, // can commit all changes to the database. System.out.println("\n No errors have occurred. " + "Commit all changes."); con.commit(); System.out.println(" Second multi-statement request " + "successfully completed .\n"); } } } finally { // Close the statement stmt.close(); System.out.println(" Statement object closed. \n"); } // Creating a PreparedStatement object from an active connection System.out.println("\n Performing third multi-statement request..."); System.out.println("\n Preparing SQL statement for execution:\n " + sPSUpdate); PreparedStatement pstmt = con.prepareStatement(sPSUpdate); System.out.println("\n Prepared statement object created. \n"); try { // The following code will demonstrate performing a // multi-statement request using update batching with // the PreparedStatement class. All commands in the batch // will be of the same exact format. // Prepare update commands, add them to the current batch. // Set parameter values indicated by ? (dynamic update) System.out.println(" Preparing statement 1..."); // Set parameter values pstmt.setString(1, "Partner development specialist"); pstmt.setInt(2, 100002); // Add prepared instruction to the current batch pstmt.addBatch(); System.out.println(" Statement 1 added."); System.out.println(" Preparing statement 2..."); // Set parameter values pstmt.setString(1, "Product manager directing industry analysis"); pstmt.setInt(2, 100005); // Add prepared instruction to the current batch pstmt.addBatch(); System.out.println(" Statement 2 added."); System.out.println(" Preparing statement 3..."); // Set parameter values pstmt.setString(1, "National advertising manager"); pstmt.setInt(2, 100007); // Add prepared instruction to the current batch pstmt.addBatch(); System.out.println(" Statement 3 added.\n"); // Submit a batch of update commands for execution // If any errors occur during processing, an SQLException // will be raised. Otherwise, update counts will be // returned for each statement. int[] updateCounts = pstmt.executeBatch(); // Dummy variable to hold temporary update counts int updResult =0; // Flag to mark whether an error occured during execution. boolean error = false; // Iterate through update count values, if an error occurred // must rollback all updates, otherwise, can commit changes. // Display update counts and errors (if any). for (int i = 0; i < updateCounts.length; i++) { updResult = updateCounts[i]; if(updResult > 0 || updResult == -2) { System.out.println(" Command " + (i+1) + " executed successfully. " + updateCounts[i] + " row(s) updated."); } else { System.out.println(" Command " + (i+1) + " failed to execute successfully."); error = true; // Set error flag break; } } if(error) // If an error was present { System.out.println( "\n Third multi-statement request failed.\n"); throw new IllegalStateException( "Error occured during command execution"); } else { // All statements have been processed successfully, // can commit all changes to the database. System.out.println("\n No errors have occurred. " + "Commit all changes."); con.commit(); System.out.println(" Third multi-statement request " + "successfully completed ."); } } finally { // Close the prepared statement pstmt.close(); System.out.println("\n PreparedStatement 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 T20700JD 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 T20700JD