//********************************************************************* // // Copyright (c) 2010 by Teradata Corporation // All Rights Reserved // //********************************************************************* // // File: T20209JD.java // Header: none // Purpose: Demonstrate FastLoad of two CSV data files. // // The program will: // - Connect as user guest/please // - Insert rows from two CSV data files into a table using FastLoad // - Verify that no SQLWarning has occurred // - Print the row count of the target table // - Verify that no rows are found in the FastLoad error tables // - Disconnect // // JDBC API: java.sql.Connection, java.sql.PreparedStatement, // java.sql.PreparedStatement.setAsciiStream // java.sql.PreparedStatement.executeUpdate // //********************************************************************* import java.sql.*; import java.io.*; public class T20209JD { // 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 , IOException { // URLs to be passed to the JDBC driver String urlStd = "jdbc:teradata://whomooz/TMODE=ANSI,CHARSET=UTF8"; String urlFld = "jdbc:teradata://whomooz/TMODE=ANSI,CHARSET=UTF8,TYPE=FASTLOADCSV"; // Size of VARCHAR column int strLen = 10; // Name of sample table String tableName = "T20209JD"; // Names of sample FastLoad error tables String tableNameErr1 = tableName + "_ERR_1"; String tableNameErr2 = tableName + "_ERR_2"; // Names of sample CSV data files String dataFile1 = "FastLoad1.csv"; String dataFile2 = "FastLoad2.csv"; // CREATE TABLE statement String createTable = "CREATE TABLE " + tableName + "," + "NO FALLBACK," + "NO BEFORE JOURNAL," + "NO AFTER JOURNAL," + "CHECKSUM = DEFAULT" + "(" + " L_INDEX INTEGER," + " L_TIMESTAMP TIMESTAMP(1)," + " L_TEXT VARCHAR(" + strLen + ")" + " CHARACTER SET UNICODE NOT CASESPECIFIC" + ") " + "PRIMARY INDEX ( L_INDEX )"; // INSERT statement String insertTable = "INSERT INTO " + tableName + " VALUES(?,?,?)"; try { System.out.println("Sample T20209JD 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 connection to Teradata."); Connection conStd = DriverManager.getConnection(urlStd, user, password); System.out.println(" Connection to Teradata established."); try { System.out.println(" Creating a Statement object."); Statement stmtStd = conStd.createStatement(); System.out.println(" Created a Statement object."); try { try { stmtStd.executeUpdate("DROP TABLE " + tableNameErr1); } catch (SQLException e) { // Do nothing because the table likely does not exist yet System.out.println(" Drop table exception ignored: " + e); } try { stmtStd.executeUpdate("DROP TABLE " + tableNameErr2); } catch (SQLException e) { // Do nothing because the table likely does not exist yet System.out.println(" Drop table exception ignored: " + e); } try { stmtStd.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 + "."); stmtStd.executeUpdate(createTable); System.out.println(" Created table " + tableName + "."); System.out.println(" Opening " + dataFile1); InputStream dataStream1 = new FileInputStream(new File(dataFile1)); try { System.out.println(" Opening " + dataFile2); InputStream dataStream2 = new FileInputStream(new File(dataFile2)); try { System.out.println( " Attempting connection to Teradata with" + " FastLoadCSV."); Connection conFld = DriverManager.getConnection( urlFld, user, password); System.out.println( " Connection to Teradata with FastLoadCSV" + " established."); try { System.out.println( " Creating a PreparedStatement object with" + " FastLoadCSV."); PreparedStatement pstmtFld = conFld.prepareStatement(insertTable); System.out.println( " Created a PreparedStatement object with" + " FastLoadCSV."); try { T20208JD.verifySQLWarningDoesNotExistInCon( " Checking connection for warnings", conFld); conFld.setAutoCommit(false); System.out.println(" Streaming " + dataFile1); pstmtFld.setAsciiStream(1, dataStream1, -1); pstmtFld.executeUpdate(); T20208JD.verifySQLWarningDoesNotExistInPstmt( " Checking prepared statement for warnings", pstmtFld, tableName); System.out.println(" Streaming " + dataFile2); pstmtFld.setAsciiStream(1, dataStream2, -1); pstmtFld.executeUpdate(); T20208JD.verifySQLWarningDoesNotExistInPstmt( " Checking prepared statement for warnings", pstmtFld, tableName); conFld.commit(); T20208JD.verifySQLWarningDoesNotExistInCon( " Checking connection for warnings", conFld); } catch (SQLException e) { SQLException ex = e; while (e != null) { StringWriter sw = new StringWriter(); e.printStackTrace(new PrintWriter(sw, true)); System.out.println( "SQL State = " + e.getSQLState() + ", Error Code = " + e.getErrorCode() + "\n" + sw.toString()); e = e.getNextException(); } throw ex; } catch (Throwable e) { e.printStackTrace( new PrintWriter(System.out, true)); IllegalStateException ex = new IllegalStateException ( "Sample failed.") ; ex.initCause (e) ; throw ex; } finally { System.out.println( " Closing PreparedStatement object with" + " FastLoadCSV."); pstmtFld.close(); System.out.println( " PreparedStatement object closed with" + "FastLoadCSV."); } T20208JD.verifySQLWarningDoesNotExistInCon( " Checking connection for warnings", conFld); } finally { System.out.println( " Closing Connection to Teradata with" + " FastLoadCSV."); conFld.close(); System.out.println( " Connection to Teradata with FastLoadCSV" + " closed."); } } finally { System.out.println(" Closing " + dataFile2); dataStream2.close(); } } finally { System.out.println(" Closing " + dataFile1); dataStream1.close(); } String selectTable = "SELECT COUNT(*) FROM " + tableName; System.out.println(selectTable); ResultSet rs = stmtStd.executeQuery(selectTable); try { int rowCount = 0; ResultSetMetaData rsmd = rs.getMetaData(); if (rs.next()) { rowCount = rs.getInt(1); System.out.println(tableName + "." + rsmd.getColumnName(1) + "=" + rowCount); } } finally { rs.close(); } selectTable = "SELECT COUNT(*) FROM " + tableNameErr1; System.out.println(selectTable); rs = stmtStd.executeQuery(selectTable); try { int rowCount = 0; ResultSetMetaData rsmd = rs.getMetaData(); if (rs.next()) { rowCount = rs.getInt(1); System.out.println(tableNameErr1 + "." + rsmd.getColumnName(1) + "=" + rowCount); } if (rowCount != 0) { System.out.println( "ERROR: Row count expected = 0, got " + rowCount); T20208JD.selectFastLoadError1TableRows( stmtStd, tableNameErr1, 10); } else { String dropStmt = "DROP TABLE " + tableNameErr1; System.out.println(dropStmt); stmtStd.executeUpdate(dropStmt); } } finally { rs.close(); } selectTable = "SELECT COUNT(*) FROM " + tableNameErr2; System.out.println(selectTable); rs = stmtStd.executeQuery(selectTable); try { int rowCount = 0; ResultSetMetaData rsmd = rs.getMetaData(); if (rs.next()) { rowCount = rs.getInt(1); System.out.println(tableNameErr2 + "." + rsmd.getColumnName(1) + "=" + rowCount); } if (rowCount != 0) { System.out.println( "ERROR: Row count expected = 0, got " + rowCount); T20208JD.selectFastLoadError2TableRows( stmtStd, tableNameErr2, 10); } else { String dropStmt = "DROP TABLE " + tableNameErr2; System.out.println(dropStmt); stmtStd.executeUpdate(dropStmt); } } finally { rs.close(); } } finally { System.out.println(" Closing Statement object."); stmtStd.close(); System.out.println(" Statement object closed."); } } finally { System.out.println(" Closing Connection to Teradata."); conStd.close(); System.out.println(" Connection to Teradata closed."); } System.out.println("Sample T20209JD finished. " + new java.util.Date()); } catch (SQLException e) { while (e != null) { StringWriter sw = new StringWriter(); e.printStackTrace(new PrintWriter(sw, true)); System.out.println("SQL State = " + e.getSQLState() + ", Error Code = " + e.getErrorCode() + "\n" + sw.toString()); e = e.getNextException(); } throw new IllegalStateException ("Sample failed.") ; } } // end main } // end class T20209JD