//************************************************************************************
//
// Copyright (c) 2004-2008 by Teradata Corporation
// All Rights Reserved
//
// TERADATA CORPORATION CONFIDENTIAL AND TRADE SECRET
//
//************************************************************************************
//
// File: T20802JD.java
// Header: none
// Purpose: Demonstrate basic Teradata SQL using the JDBC API by creating
// and executing a macro containing both non-SELECT and
// SELECT statements.
// The program will:
// - Connect as user guest/please
// - Create a macro
// - Execute the newly-created macro
// - Obtain and display the results
// - Disconnect.
//
// JDBC API: java.sql.Connection,
// java.sql.Statement,
// java.sql.Statement.execute,
// java.sql.Statement.executeUpdate,
// java.sql.Statement.getUpdateCount,
// java.sql.Statement.getResultSet,
// java.sql.Statement.getMoreResults,
//
// Version: Updated for Teradata V2R6
//
//************************************************************************************
import java.sql.*;
public class T20802JD
{
// 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";
// Macro creation/execution commands
String sMacro =
"REPLACE MACRO empRestoreDisplay AS (" +
"SELECT empName, empDept, empJob FROM employee ORDER BY empName" +
";" +
"DELETE FROM employee WHERE empID = 100009" +
";" +
"UPDATE employee" +
" SET empJob = 'Manager overseeing international contracts' " +
"WHERE empID = 100002" +
";" +
"UPDATE employee" +
" SET empJob = 'Software engineer providing technical support' " +
"WHERE empID = 100005" +
";" +
"UPDATE employee SET empJob = 'Team leader managing market research' " +
"WHERE empID = 100007" +
";" +
"SELECT empName, empDept, empJob FROM employee ORDER BY empName" +
"";
String sMacroExec = "EXEC empRestoreDisplay";
try
{
System.out.println(" Sample T20802JD: ");
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 create and execute a macro
// consisting of non-SELECT and SELECT commands
// Turn off AutoCommit to allow for proper error-handling
con.setAutoCommit(false);
System.out.println(" Attempting to create a macro : "
+ sMacro);
// Sending the procedure creation request to the database,
// replacing any old implementations, if present
stmt.executeUpdate(sMacro);
// No errors were generated, can commit changes
con.commit();
System.out.println(" Macro created successfully.");
System.out.println(" Attempting to execute the macro...");
// Making a call to execute the created macro.
// Both result sets and update counts are expected back
boolean firstResult = stmt.execute(sMacroExec);
if(!firstResult)
{
// If an update count or nothing is returned - error.
// Result set expected as first result
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;
// Four update counts are expected
for(int i = 1; i <= 4; 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
// second Result set is expected
throw new IllegalStateException(
"Unexpected results: ResultSet expected.");
}
// Else can process second query
// Retrieve the second 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(" Call to macro " +
"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 T20802JD 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 T20802JD
|
|
|
|