//********************************************************************* // // Copyright (c) 2006-2008 by Teradata Corporation // All Rights Reserved // //********************************************************************* // // File: T21900JD.java // Header: none // Purpose: Demonstrate the use of SET QUERY_BAND statement // // The program will: // - Connect as user guest/please // - Beginning a TRANSACTION // - Execute QUERY_BAND statement // - Display the name/value pairs // - Ending the TRANSACTION // - Disconnect. // // JDBC API: java.sql.Connection, java.sql.Statement, // java.sql.Statement.execute, java.sql.PreparedStatement, // java.sql.PreparedStatement.execute, java.sql.ResultSet // // Version: Updated for Teradata 12.0 // //********************************************************************* import java.sql.*; public class T21900JD { // 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"; String queryBandStatement = "SET QUERY_BAND = ? FOR TRANSACTION"; String retrieveValues = "HELP SESSION"; String queryBandStatementClear = "SET QUERY_BAND = NONE FOR TRANSACTION"; String nameValuePair = "org=Finance;report=Fin123;"; try { System.out.println("\n Sample T21900JD: \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 PreparedStatement pstmt = con.prepareStatement(queryBandStatement); Statement stmt = con.createStatement(); System.out.println(" Statement object created. \n"); try{ con.setAutoCommit(false); System.out.println(" Transaction Begins"); pstmt.setString(1,nameValuePair); pstmt.execute(); System.out.println( " Statement for setting query_band was executed"); ResultSet rs = stmt.executeQuery(retrieveValues); System.out.println(" Retrieving QueryBand Values"); System.out.println(" QueryBand values are expected to be set"); while(rs.next()){ System.out.println(" QueryBand values are \"" + rs.getString("QueryBand") + "\""); } con.commit(); System.out.println(" Transaction ends"); System.out.println(); System.out.println(" Transaction Begins"); System.out.println(" No query_band is active now"); rs = stmt.executeQuery(retrieveValues); System.out.println(" Retrieving QueryBand Values"); System.out.println(" QueryBand value is expected to be empty"); while(rs.next()){ System.out.println(" QueryBand value is \"" + rs.getString("QueryBand") + "\""); } pstmt.setString(1,nameValuePair); pstmt.execute(); System.out.println( " Statement for setting query_band was executed"); rs = stmt.executeQuery(retrieveValues); System.out.println(" Retrieving QueryBand Values"); System.out.println(" QueryBand values are expected to be set"); while(rs.next()){ System.out.println(" QueryBand values are \"" + rs.getString("QueryBand") + "\""); } stmt.execute(queryBandStatementClear); System.out.println( " Statement for clearing query_band was executed"); rs = stmt.executeQuery(retrieveValues); System.out.println(" Retrieving QueryBand Values"); System.out.println(" QueryBand value is expected to be empty"); while(rs.next()){ System.out.println(" QueryBand value is \"" + rs.getString("QueryBand") + "\""); } con.commit(); System.out.println(" Transaction ends"); System.out.println(); //Close the resultset rs.close(); } finally { //Close the statement pstmt.close(); System.out.println(" PrepareStatement object closed. \n"); stmt.close(); System.out.println(" Statement object closed. \n"); } } finally { //Close the connection System.out.println("\n Closing connection to Teradata..."); con.close(); System.out.println(" Connection to Teradata closed. \n"); } System.out.println(" Sample T21900JD 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 T21900JD