//********************************************************************* // // Copyright (c) 2006-2008 by Teradata Corporation // All Rights Reserved // //********************************************************************* // // File: T20705JD.java // Header: none // Purpose: Demonstrate the ability to keep JDBC resultSets open // when processing a multi-statment request via the method: // // Statement.getMoreResults(Statement.KEEP_CURRENT_RESULT) // // The program will: // - Connect as user guest/please. // - Perform a multi-statement request. // - Obtain all of the results from the request, using // KEEP_CURRENT_RESULT to keep all result sets open. // - Display the results that were obtained, in last-to-first order. // - 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: Created for Teradata Database 12 // //********************************************************************* import java.sql.*; public class T20705JD { // 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 sMultiSelect = "SELECT empID, empName " + "FROM employee ORDER BY empID" + ";" + "SELECT empDept, empName, empID " + "FROM employee ORDER BY empDept, empName" + ";" + "SELECT empName, empDept, empJob " + "FROM employee ORDER BY empName"; try { System.out.println("\n Sample T20705JD: \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( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); System.out.println(" Statement object created. \n"); try { // The following code will demonstrate execution of a // a multi-statement request consisting of SELECT statements // only. Please note that only the Statement.execute method // can be used, for the update batching method does not // return ResultSet objects. System.out.println(" Performing a multi-statement request..."); // use Statement.execute method to execute a multi-statement // request. Only result sets are expected back. // As before, use getResultSet or getUpdateCount to // retrieve the result, and getMoreResults to move to any // subsequent results. boolean result = stmt.execute(sMultiSelect); if(!result) { // If an update count or nothing is returned - error throw new IllegalStateException( "Unexpected results: ResultSet expected."); } else { // Retrieve the first result System.out.println( "\n Retreiving results from first query.\n"); ResultSet rs1 = stmt.getResultSet(); // Retrieve the second result, keeping first result open System.out.println(" Moving to next result.\n"); if (! stmt.getMoreResults(Statement.KEEP_CURRENT_RESULT)) throw new IllegalStateException( "Unexpected results: ResultSet expected."); System.out.println( "\n Retreiving results from second query.\n"); ResultSet rs2 = stmt.getResultSet(); // Retrieve the third result, keeping second result open System.out.println(" Moving to next result.\n"); if (! stmt.getMoreResults(Statement.KEEP_CURRENT_RESULT)) throw new IllegalStateException( "Unexpected results: ResultSet expected."); System.out.println( "\n Retreiving results from third query.\n"); ResultSet rs3 = stmt.getResultSet(); // Display the results of the third query System.out.println("\n Displaying results of the" + " third query:\n"); int rowCount = 0; // reset row counter while(rs3.next()) { rowCount ++; System.out.println(" Row " + rowCount + " : "); System.out.println(" Column empName : " + rs3.getString(1)); System.out.println(" Column empDept : " + rs3.getString(2)); System.out.println(" Column empJob : " + rs3.getString(3)); } System.out.println("\n " + rowCount + " row(s) returned."); // Display the results of the second query System.out.println("\n Displaying results of the second " + "query:\n"); rowCount = 0; // reset row counter while(rs2.next()) { rowCount ++; System.out.println(" Row " + rowCount + " : "); System.out.println(" Column empDept : " + rs2.getString(1)); System.out.println(" Column empName : " + rs2.getString(2)); System.out.println(" Column empID : " + rs2.getInt(3)); } System.out.println("\n " + rowCount + " row(s) returned."); // Display the results of the first query System.out.println("\n Displaying results of the first " + "query:\n"); rowCount = 0; while(rs1.next()) { rowCount ++; System.out.println(" Row " + rowCount + " : "); System.out.println(" Column empID : " + rs1.getInt(1)); System.out.println(" Column empName : " + rs1.getString(2)); } System.out.println("\n " + rowCount + " row(s) returned."); // Advance to next result. // If an update count or nothing is returned - error. if(stmt.getMoreResults()) throw new IllegalStateException( "Unexpected results: ResultSet expected."); // Close open results System.out.println("\n Closing all results.\n"); rs1.close(); rs2.close(); rs3.close(); 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 T20705JD finished."); } 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 T20705JD