//********************************************************************* // // Copyright (c) 2010 by Teradata Corporation // All Rights Reserved // //********************************************************************* // // File: T20208JD.java // Header: none // Purpose: Demonstrate FastLoad of one CSV data file. // // The program will: // - Connect as user guest/please // - Insert rows from a CSV data file 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 T20208JD { // Name of the user able to create and drop tables public static String user = "guest"; public static String password = "please"; private static final int MAX_FASTLOAD_ERROR_TABLE_ARRAY_LENGTH = 64000; private static final String LINE_SEP = System.getProperty ("line.separator") ; private static final int FULL_HEX_LENGTH = 56 ; private static final int SEG_SIZE = 16 ; 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 = "T20208JD"; // Names of sample FastLoad error tables String tableNameErr1 = tableName + "_ERR_1"; String tableNameErr2 = tableName + "_ERR_2"; // Name of sample CSV data file String dataFile = "FastLoad1.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 T20208JD 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 " + dataFile); InputStream dataStream = new FileInputStream(new File(dataFile)); 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 { verifySQLWarningDoesNotExistInCon( " Checking connection for warnings", conFld); System.out.println(" Streaming " + dataFile); pstmtFld.setAsciiStream(1, dataStream, -1); pstmtFld.executeUpdate(); verifySQLWarningDoesNotExistInPstmt( " Checking prepared statement for warnings", pstmtFld, tableName); } 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."); } 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 " + dataFile); dataStream.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); 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); 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 T20208JD 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 public static void verifySQLWarningDoesNotExistInCon (String msg, Connection con) throws SQLException { System.out.println(msg); SQLWarning w = con.getWarnings(); while (w != null) { StringWriter sw = new StringWriter(); w.printStackTrace(new PrintWriter(sw, true)); System.out.println("ERROR: Found SQLWarning in " + con + " when none was expected: " + "SQL State = " + w.getSQLState() + ", Error Code = " + w.getErrorCode() + "\n" + sw.toString()); w = w.getNextWarning(); } con.clearWarnings(); } public static void verifySQLWarningDoesNotExistInPstmt ( String msg, PreparedStatement pstmt, String table) throws SQLException { System.out.println(msg); SQLWarning w = pstmt.getWarnings(); while (w != null) { StringWriter sw = new StringWriter(); w.printStackTrace(new PrintWriter(sw, true)); System.out.println("ERROR: Found SQLWarning in " + pstmt + " when none was expected for table " + table + ": " + "SQL State = " + w.getSQLState() + ", Error Code = " + w.getErrorCode() + "\n" + sw.toString()); w = w.getNextWarning(); } pstmt.clearWarnings(); } public static void selectFastLoadError1TableRows ( Statement stmt, String tableNameErr1, int rowLimit) throws SQLException { String selectQuery = "LOCKING " + tableNameErr1 + " FOR ACCESS" + " SELECT TOP " + rowLimit + " ErrorCode,ErrorFieldName,BYTES(DataParcel)" + " AS ActualDataParcelLength,CAST(DataParcel AS VARBYTE(" + MAX_FASTLOAD_ERROR_TABLE_ARRAY_LENGTH + ")) FROM " + tableNameErr1; selectFastLoadErrorTableRows(stmt, selectQuery, tableNameErr1); } public static void selectFastLoadError2TableRows ( Statement stmt, String tableNameErr2, int rowLimit) throws SQLException { String selectQuery = "LOCKING " + tableNameErr2 + " FOR ACCESS SELECT TOP " + rowLimit + " * FROM " + tableNameErr2; selectFastLoadErrorTableRows(stmt, selectQuery, tableNameErr2); } private static void selectFastLoadErrorTableRows ( Statement stmt, String selectQuery, String tableNameErr) throws SQLException { System.out.println(selectQuery); ResultSet rs = stmt.executeQuery(selectQuery); try { ResultSetMetaData rsmd = rs.getMetaData(); for (int i = 1; rs.next(); i++) { String colNames[] = new String[rsmd.getColumnCount()]; Object colValues[] = new Object[rsmd.getColumnCount()]; for (int j = 1; j <= rsmd.getColumnCount(); j++) { colNames[j-1] = rsmd.getColumnName(j); colValues[j-1] = rs.getObject(j); } String msg = null; for (int j = 0; j < rsmd.getColumnCount(); j++) { if (colValues[j] instanceof byte[]) { byte[] bytes = (byte[])colValues[j]; int len = ((bytes == null) ? 0 : bytes.length); len = len < MAX_FASTLOAD_ERROR_TABLE_ARRAY_LENGTH ? len : MAX_FASTLOAD_ERROR_TABLE_ARRAY_LENGTH; if (j == 0) msg = "\n" + formatPartialByteArray( colNames[j], bytes, 0, len); else msg += "\n" + formatPartialByteArray( colNames[j], bytes, 0, len); } else { String str = String.valueOf(colValues[j]); int len = str.length() < MAX_FASTLOAD_ERROR_TABLE_ARRAY_LENGTH ? str.length() : MAX_FASTLOAD_ERROR_TABLE_ARRAY_LENGTH; String truncationStr = ""; if (colValues[j] != null && len != 0 && str.length() > MAX_FASTLOAD_ERROR_TABLE_ARRAY_LENGTH) truncationStr = " (only showing string length " + len + " of " + str.length() + ")"; if (j == 0) msg = "\n" + colNames[j] + "=" + str.substring(0, len) + truncationStr; else msg += "\n" + colNames[j] + "=" + str.substring(0, len) + truncationStr; } } System.out.println("Row " + i + " in FastLoad table " + tableNameErr + " contains the following data: " + msg); } } finally { rs.close(); } } private static String formatPartialByteArray ( String sTitle, byte [] bytes, int nOffset, int nDumpLength) { StringBuffer stringBuffer = new StringBuffer () ; stringBuffer.append (sTitle + ": ") ; if (bytes == null) { stringBuffer.append ("null byte array") ; } else if (nOffset >= bytes.length) { stringBuffer.append ("offset " + nOffset + " is beyond the end of byte array length " + bytes.length) ; } else { stringBuffer.append ( "byte array length " + bytes.length + " (0x" + Integer.toHexString (bytes.length) + "), offset " + nOffset + " (0x" + Integer.toHexString (nOffset) + "), dump length " + nDumpLength + " (0x" + Integer.toHexString (nDumpLength) + ")") ; if (nOffset + nDumpLength > bytes.length) { nDumpLength = bytes.length - nOffset ; stringBuffer.append (" --> limited to " + nDumpLength + " (0x" + Integer.toHexString (nDumpLength) + ")") ; } stringBuffer.append (LINE_SEP) ; int bytesWritten = 0; for (int i = 0; i < nDumpLength - SEG_SIZE; i += SEG_SIZE) { stringBuffer.append ( formatByteArrayLine (bytes, nOffset + i, SEG_SIZE)) ; bytesWritten += SEG_SIZE; } if (bytesWritten < nDumpLength) stringBuffer.append (formatByteArrayLine (bytes, nOffset + bytesWritten, nDumpLength - bytesWritten)) ; } return stringBuffer.toString(); } private static String formatByteArrayLine ( byte [] bytes, int srcPos, int dumpLength) { StringBuffer stringBuffer = new StringBuffer(150); // build the offset in the data stream: stringBuffer.append (leadingZeros ( Integer.toHexString (srcPos), 5) + " ") ; // build the data as hex values: for (int i = 0; i < dumpLength; i++) stringBuffer.append (rightJustify ( Integer.toHexString (bytes [srcPos + i]), 2) + " ") ; if (stringBuffer.length() < FULL_HEX_LENGTH) { String chars = " "; char[] fillChars = chars.toCharArray(); stringBuffer.append(fillChars, 0, FULL_HEX_LENGTH - stringBuffer.length()); } // build the data as a (forced) ASCII interpretation: stringBuffer.append(" |"); for (int i = 0; i < dumpLength; i++) { char c= (char) bytes[srcPos + i]; if (!isPrintableAscii(c)) c = '.'; stringBuffer.append(c); } stringBuffer.append ("|" + LINE_SEP) ; return stringBuffer.toString () ; } private static boolean isPrintableAscii (char c) { return c >= '\u0020' && c < '\u007F' ; } private static String rightJustify (String str, int width) { if (str.length() > 2) str = str.substring(str.length() - 2); return leadingZeros (str, width) ; } private static String leadingZeros (String str, int width) { for (int i = width - str.length(); i > 0; i--) str = "0" + str; return str; } } // end class T20208JD