//********************************************************************* // // Copyright (c) 2008 by Teradata Corporation // All Rights Reserved // //********************************************************************* // // File: T20307JD.java // Header: none // Purpose: Demonstrate FastExport of data rows with parameter marker. // // The program will: // - Connect as user guest/please // - Insert data rows into a table // - Select data rows from a table using FastExport with parameter marker // - Verify that no SQLWarning has occurred // - Disconnect // // JDBC API: java.sql.Connection, java.sql.PreparedStatement, // java.sql.PreparedStatement.executeQuery // // Version: new sample // //********************************************************************* import java.io.*; import java.sql.*; import java.util.Arrays; /** * FastExport with parameter marker. */ public class T20307JD { // Name of the user able to select a table 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=FASTEXPORT"; // Name of sample program String progName = T20307JD.class.getName(); // Size of CHAR column int strLen = 100; // Name of sample table String tableName = progName; // CREATE TABLE statement String createTable = "CREATE TABLE " + tableName + "," + "NO FALLBACK," + "NO BEFORE JOURNAL," + "NO AFTER JOURNAL," + "CHECKSUM = DEFAULT" + "(" + " L_INDEX INTEGER NOT NULL," + " L_SMALLINT SMALLINT," + " L_DATE DATE," + " L_TEXT VARCHAR(" + strLen + ")" + " CHARACTER SET UNICODE NOT CASESPECIFIC" + ") " + "UNIQUE PRIMARY INDEX ( L_INDEX )"; // INSERT statement String insertTable = "INSERT INTO " + tableName + " VALUES(?,?,?,?)"; // SELECT statement String selectTable = "SELECT L_INDEX, L_SMALLINT, L_DATE, L_TEXT FROM " + tableName + " WHERE L_SMALLINT=?"; try { System.out.println( "Sample " + progName + " 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 for INSERT..."); PreparedStatement pstmt1 = con.prepareStatement(insertTable); System.out.println( " Created a PreparedStatement object for INSERT..."); try { con.clearWarnings(); System.out.println( " Creating a PreparedStatement object for SELECT..."); PreparedStatement pstmt2 = con.prepareStatement(selectTable); System.out.println( " Created a PreparedStatement object for SELECT..."); try { SQLWarning w = con.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() + "\n" + sw.toString()); w = w.getNextWarning(); } int rowsInserted = 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..."); pstmt1.setInt(1, 1); pstmt1.setShort(2, (short)11); pstmt1.setDate(3, Date.valueOf("2008-01-18")); pstmt1.setString(4, strBuf); pstmt1.addBatch(); batchCount++; pstmt1.setInt(1, 2); pstmt1.setShort(2, (short)22); pstmt1.setDate(3, Date.valueOf("2008-01-17")); pstmt1.setString(4, strBuf); pstmt1.addBatch(); batchCount++; System.out.println(" Inserting data rows..."); int updateCounts[] = pstmt1.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]); } } rowsInserted += updateCounts.length; } System.out.println(" Binding parameter marker..."); pstmt2.setShort(1, (short)11); // Check SQLWarning after binding parameters, but before // executeQuery(). w = pstmt2.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() + "\n" + sw.toString()); w = w.getNextWarning(); } System.out.println(" Selecting data rows..."); ResultSet rs = pstmt2.executeQuery(); try { int rowsSelected = 0; ResultSetMetaData rsmd = rs.getMetaData(); for (int i = 1; rs.next(); i++, rowsSelected++) { for (int j = 1; j <= rsmd.getColumnCount(); j++) { System.out.println(" " + tableName + " row " + i + ", col " + j + ": " + rsmd.getColumnName(j) + "=" + rs.getObject(j)); } } if (rowsSelected != 1) { System.out.println("ERROR: Didn't retrieve" + " expected rows: expected 1, got " + rowsSelected); } } finally { rs.close(); } // Check SQLWarning after executeQuery(). w = pstmt2.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() + "\n" + sw.toString()); w = w.getNextWarning(); } } finally { System.out.println( " Closing PreparedStatement object for SELECT..."); pstmt2.close(); System.out.println( " PreparedStatement object for SELECT closed."); } } finally { System.out.println( " Closing PreparedStatement object for INSERT..."); pstmt1.close(); System.out.println( " PreparedStatement object for INSERT 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 " + progName + " 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.") ; } } }