//********************************************************************* // // Copyright (c) 2004-2008 by Teradata Corporation // All Rights Reserved // //********************************************************************* // // File: T20702JD.java // Header: none // Purpose: Demonstrate basic Teradata SQL using the JDBC API by // creating/executing multi-statement requests containing // both non-SELECT and SELECT statements. // 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.execute, // java.sql.Statement.getUpdateCount, // java.sql.Statement.getResultSet, // java.sql.Statement.getMoreResults, // java.sql.ResultSet // // Version: Updated for Teradata V2R6 // //********************************************************************* import java.sql.*; public class T20702JD { // 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"; // SQL statements to be processed as part of the multi-statement request String sMultiStmt = // Command 1 - review changes made "SELECT empName, empDept, empJob " + "FROM employee ORDER BY empName" + ";" + // Command 2 - reverse changes "UPDATE employee " + "SET empJob = 'Manager overseeing international contracts'" + "WHERE empID = 100002" + ";" + // Command 3 - reverse changes "UPDATE employee " + "SET empJob = 'Software engineer providing " + "technical applications support' WHERE empID = 100005" + ";" + // Command 4 - reverse changes "UPDATE employee " + "SET empJob = 'Team leader managing market research' " + "WHERE empID = 100007" + ";" + // Command 5 - review change reversal "SELECT empName, empDept, empJob FROM employee ORDER BY empName"; try { System.out.println("\n Sample T20702JD: \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 { // The following code will demonstrate execution of a // a multi-statement request consisting of SELECT and // non-SELECT statements. Please note that only the // Statement.execute method can be used, for the // update batching method does not return ResultSet objects. // Turn off AutoCommit, since updates have to be checked con.setAutoCommit(false); System.out.println(" Performing a multi-statement request..."); // Use Statement.execute method to execute a multi-statement // request. Both ResultSets and update counts are expected // to be returned. // As before, use getResultSet or getUpdateCount to // retrieve the result, and getMoreResults to move to any // subsequent results. boolean result = stmt.execute(sMultiStmt); if(!result) { // If an update count or nothing is returned - error throw new IllegalStateException( "Unexpected results: ResultSet expected."); } else { // Retrieve the first result ResultSet rs = stmt.getResultSet(); // Display the result of the first query System.out.println("\n Displaying results of the first " + "query:\n"); int rowCount = 0; while(rs.next()) { rowCount ++; System.out.println(" Row " + rowCount + " : "); System.out.println(" Column empName : " + rs.getString(1)); System.out.println(" Column empDept : " + rs.getString(2)); System.out.println(" Column empJob : " + rs.getString(3)); } System.out.println("\n " + rowCount + " row(s) returned."); System.out.println("\n Processing updates...\n"); // Dummy variable to hold temporary update counts int updResult = 0; // Flag to mark whether an error occured during execution. boolean error = false; // Three update counts are expected for(int i = 1; i <= 3; i++) { // Advance to next result stmt.getMoreResults(); // Retrieve current result updResult = stmt.getUpdateCount(); // Check for errors if(updResult > 0 || updResult == -2) { System.out.println(" Command " + (i) + " executed successfully. " + updResult + " row(s) updated."); } else { System.out.println(" Command " + (i) + " failed to execute successfully."); error = true; // Set error flag break; } } if(error) // If an error was present { System.out.println("\n Update request failed.\n"); throw new IllegalStateException( "Error occured during update execution"); } // Else proceed; all update statements have been // processed successfully. System.out.println("\n Updates processed successfully."); if(stmt.getMoreResults() == false) { // If an update count or nothing is returned - error // a ResultSet is expected throw new IllegalStateException( "Unexpected results: ResultSet expected."); } // Else can process second query, retrieve result set rs = stmt.getResultSet(); // Display the result of the first query System.out.println("\n Displaying results of the " + "second query:\n"); // Reset row counter rowCount = 0; while(rs.next()) { rowCount ++; System.out.println(" Row " + rowCount + " : "); System.out.println(" Column empName : " + rs.getString(1)); System.out.println(" Column empDept : " + rs.getString(2)); System.out.println(" Column empJob : " + rs.getString(3)); } System.out.println("\n " + rowCount + " row(s) returned."); if(!((stmt.getMoreResults() == false) && (stmt.getUpdateCount() == -1))) throw new IllegalStateException( "Unexpected results: more results present."); // 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("\n Multi-statement request " + "successfully completed .\n"); } } finally { // Close the statement stmt.close(); System.out.println(" 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 T20702JD 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 T20702JD