//************************************************************************************
//
// Copyright (c) 2006-2008 by Teradata Corporation
// All Rights Reserved
//
// TERADATA CORPORATION CONFIDENTIAL AND TRADE SECRET
//
//************************************************************************************
//
// File: T20205JD.java
// Header: none
// Purpose: Demonstrate FastLoad of data rows with auto-commit true.
//
// The program will:
// - Connect as user guest/please
// - Insert data rows into a table using FastLoad with auto-commit true
// - Verify the updateCount returned by executeBatch
// - Verify that no SQLWarning has occurred
// - Verify that the correct number of data rows were inserted (fastloaded)
// - Disconnect
//
// JDBC API: java.sql.Connection, java.sql.PreparedStatement,
// java.sql.PreparedStatement.executeBatch
//
// Version: new sample
//
//************************************************************************************
import java.io.*;
import java.sql.*;
import java.util.Arrays;
/**
* FastLoad with auto-commit true.
*/
public class T20205JD {
// Name of the user able to create and drop tables
public static String user = "guest";
public static String password = "please";
public static void main(String args[])
throws ClassNotFoundException
{
// URL to be passed to the JDBC driver
String url = "jdbc:teradata://whomooz/TMODE=ANSI,CHARSET=UTF8,TYPE=FASTLOAD";
// Size of CHAR column
int strLen = 100;
// Name of sample table
String tableName = "T20205JD";
// CREATE TABLE statement
String createTable = "CREATE TABLE " + tableName + "," +
"NO FALLBACK," +
"NO BEFORE JOURNAL," +
"NO AFTER JOURNAL," +
"CHECKSUM = DEFAULT" +
"(" +
" L_INDEX INTEGER NOT NULL," +
" L_TEXT CHAR(" + strLen + ")" +
" CHARACTER SET UNICODE NOT CASESPECIFIC" +
") " +
"UNIQUE PRIMARY INDEX ( L_INDEX ";
// INSERT statement
String insertTable = "INSERT INTO " + tableName + " VALUES(?,?)";
// SELECT statement
String selectTable = "SELECT COUNT(*) FROM " + tableName;
try {
System.out.println("Sample T20205JD starting: " + new java.util.Date());
System.out.println(" Looking for the Teradata JDBC driver...");
Class.forName("com.teradata.jdbc.TeraDriver");
System.out.println(" Teradata JDBC driver loaded.");
System.out.println(" Attempting to connect to Teradata...");
Connection con = DriverManager.getConnection(url, user, password);
System.out.println(" Connection to Teradata established.");
try {
System.out.println(" Creating a Statement object...");
Statement stmt = con.createStatement();
System.out.println(" Created a Statement object.");
try {
try {
stmt.executeUpdate("DROP TABLE " + tableName);
} catch (SQLException e) {
// Do nothing because the table likely does not exist yet!
System.out.println(" Drop table exception ignored: " + e);
}
System.out.println(" Creating table " + tableName + "...");
stmt.executeUpdate(createTable);
System.out.println(" Created table " + tableName + ".");
System.out.println(" Creating a PreparedStatement object...");
PreparedStatement pstmt = con.prepareStatement(insertTable);
System.out.println(" Created a PreparedStatement object...");
try {
int numRows = 0;
int batchCount = 0;
char[] charBuf = new char[strLen];
Arrays.fill(charBuf, 'a');
String strBuf = new String(charBuf);
// Note that auto-commit is true by default,
// thus an auto-commit mode of 'true' is assumed here!
System.out.println(" Binding data rows...");
pstmt.setInt(1, 1);
pstmt.setString(2, strBuf);
pstmt.addBatch();
batchCount++;
pstmt.setInt(1, 2);
pstmt.setString(2, strBuf);
pstmt.addBatch();
batchCount++;
System.out.println(" Inserting data rows...");
int updateCounts[] = pstmt.executeBatch();
if (updateCounts == null) {
System.out.println(
"ERROR: A null update count was returned!");
} else {
if (updateCounts.length != batchCount) {
System.out.println(
"ERROR: The update count does not match the"
+ " number of rows batched: expected "
+ batchCount + ", got " + updateCounts.length);
}
for (int i = 0; i < updateCounts.length; i++) {
if (updateCounts[i] != 1) {
System.out.println(
"ERROR: The update count for row " + (i+1)
+ " failed: expected 1, got "
+ updateCounts[i]);
}
}
numRows += updateCounts.length;
}
// Note that at this point no further executeBatch is
// permitted unless all rows in the destination table
// are deleted.
SQLWarning w = pstmt.getWarnings();
// Note that there could be multiple SQLWarning objects
// chained together.
while (w != null) {
System.out.println("*** SQLWarning caught ***");
StringWriter sw = new StringWriter();
w.printStackTrace(new PrintWriter(sw, true));
System.out.println("SQL State = " + w.getSQLState()
+ ", Error Code = " + w.getErrorCode() +
"" + sw.toString());
w = w.getNextWarning();
}
System.out.println(" Selecting data rows...");
ResultSet rs = stmt.executeQuery(selectTable);
try {
int rowCount = 0;
ResultSetMetaData rsmd = rs.getMetaData();
if (rs.next()) {
rowCount = rs.getInt(1);
System.out.println(" " + tableName + " row "
+ rsmd.getColumnName(1) + "=" + rowCount);
}
if (rowCount != numRows) {
System.out.println(
"ERROR: Didn't retrieve enough rows: expected "
+ numRows + ", got " + rowCount + "!");
}
} finally {
rs.close();
}
} finally {
System.out.println(" Closing PreparedStatement object...");
pstmt.close();
System.out.println(" PreparedStatement object closed.");
}
} finally {
System.out.println(" Closing Statement object...");
stmt.close();
System.out.println(" Statement object closed.");
}
} finally {
System.out.println(" Closing Connection to Teradata...");
con.close();
System.out.println(" Connection to Teradata closed.");
}
System.out.println("Sample T20205JD finished. " + new java.util.Date());
}
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.") ;
}
}
}
|
|
|
|