//************************************************************************************
//
// Copyright (c) 2006-2008 by Teradata Corporation
// All Rights Reserved
//
// TERADATA CORPORATION CONFIDENTIAL AND TRADE SECRET
//
//************************************************************************************
//
// 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(" Sample T21900JD: ");
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. ");
// 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. ");
try{
//Creating a statement object from an active connection
PreparedStatement pstmt = con.prepareStatement(queryBandStatement);
Statement stmt = con.createStatement();
System.out.println(" Statement object created. ");
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. ");
stmt.close();
System.out.println(" Statement object closed. ");
}
}
finally
{
//Close the connection
System.out.println(" Closing connection to Teradata...");
con.close();
System.out.println(" Connection to Teradata closed. ");
}
System.out.println(" Sample T21900JD 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 T21900JD
|
|
|
|