//************************************************************************************
//
// Copyright (c) 2004-2008 by Teradata Corporation
// All Rights Reserved
//
// TERADATA CORPORATION CONFIDENTIAL AND TRADE SECRET
//
//************************************************************************************
//
// 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(" Sample T20702JD: ");
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
Statement stmt = con.createStatement();
System.out.println(" Statement object created. ");
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(" Displaying results of the first " +
"query:");
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(" " + rowCount + " row(s) returned.");
System.out.println(" Processing updates...");
// 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(" Update request failed.");
throw new IllegalStateException(
"Error occured during update execution");
}
// Else proceed; all update statements have been
// processed successfully.
System.out.println(" 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(" Displaying results of the " +
"second query:");
// 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(" " + 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(" No errors have occurred. " +
"Commit all changes.");
con.commit();
System.out.println(" Multi-statement request "
+ "successfully completed .");
}
}
finally
{
// Close the statement
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 T20702JD 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 T20702JD
|
|
|
|