//********************************************************************* // // Copyright (c) 2004-2008 by Teradata Corporation // All Rights Reserved // //********************************************************************* // // File: T20301JD.java // Header: none // Purpose: Demonstrate basic Teradata SQL using the JDBC API by // performing a SELECT query on the table using // parameter markers. // The program will: // - Connect as user guest/please // - Select a row from table employee // - Display the medatata associated with the // column values of the selected row. // - Disconnect. // // JDBC API: java.sql.Connection, java.sql.PreparedStatement, // java.sql.PreparedStatement.executeQuery, // java.sql.ResultSet, java.sql.ResultSet.getMetaData, // java.sql.ResultSetMetaData // // Version: Updated for Teradata V2R6 // //********************************************************************* import java.sql.*; public class T20301JD { // Name of the user able to create and drop 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"; // Creation of the SQL SELECT statement and its parameter value String sSelect = "SELECT empID, empName, empJob FROM employee" + " WHERE empDept = ? ORDER BY empID"; String selectCond = "Product Development"; try { System.out.println("\n Sample T20301JD: \n"); 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. \n"); // 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. \n"); try { System.out.println(" Preparing this SQL statement for execution:\n " + sSelect); // Creating a prepared statement object from an active connection PreparedStatement pstmt = con.prepareStatement(sSelect); System.out.println(" Prepared statement object created. \n"); try { // Set parameter values indicated by ? (dynamic update) System.out.println(" Using setString() to bind value to " + "the parameter marker:"); pstmt.setString(1, selectCond); System.out.println(" FIRST ? set to: " + selectCond); // The following code will perform a SELECT query // on the table. // Submit a query, creating a result set object ResultSet rs = pstmt.executeQuery(); // Display result set table column meta data displayRSMetaData(rs); // Extract and display result set table data System.out.println(); System.out.println(" DISPLAYING RESULT SET DATA:"); System.out.println(" ---------------------------"); int rowCount = 0; // result set row counter int numData; // placeholder for integer data String charData; // placeholder for character data // iterate through all returned rows and display them while (rs.next()) { rowCount++; // increment retrieved row counter System.out.println(); System.out.println(" ROW " + rowCount + ": "); // retrieve and display first column value numData = rs.getInt(1); System.out.println(" Employee ID: " + numData); // retrieve and display second column value charData = rs.getString(2); System.out.println(" Name: " + charData); // retrieve and display third column value charData = rs.getString(3); System.out.println(" Job Description: " + charData); } System.out.println("\n " + rowCount + " Row(s) returned."); } finally { // Close the statement pstmt.close(); System.out.println("\n PreparedStatement object closed.\n"); } } finally { // Close the connection System.out.println(" Closing connection to Teradata..."); con.close(); System.out.println(" Connection to Teradata closed. \n"); } System.out.println(" Sample T20301JD finished. \n"); } 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 private static void displayRSMetaData(ResultSet rs) throws SQLException { // This code will demonstrate all available methods for // retrieving table column meta data. // Retrieve result set meta data to get information on the columns ResultSetMetaData rsmd = rs.getMetaData(); // Retrieve the number of columns returned int colCount = rsmd.getColumnCount(); System.out.println("\n DISPLAYING RESULT SET COLUMN META DATA:"); System.out.println(" -----------------------------------------"); System.out.println("\n The resulting table has " + colCount + " columns:"); // For every column, display it's meta data. int i = 1; // Initialize loop counter while (i <= colCount) { // Demonstrating all methods for retrieving column meta data System.out.println(); System.out.println(" Column " + i); System.out.println(" ------------ "); // Display the suggested column title for use in // printouts and displays System.out.println(" Column label: " + rsmd.getColumnLabel(i)); // Display the column name System.out.println(" Column name: " + rsmd.getColumnName(i)); // Display the SQL type of a column. System.out.println(" Column type: " + rsmd.getColumnType(i)); // Display the type name of a column System.out.println(" Column type name: " + rsmd.getColumnTypeName(i)); // Display information on whether NULL values are allowed System.out.println(" NULLs allowed: " + rsmd.isNullable(i)); // Display the normal maximum width of a column in characters. System.out.println(" Maximum character width: " + rsmd.getColumnDisplaySize(i)); // Display precision: the number of decimal digits // Note: default value is 0. System.out.println(" Column precision" + " (number of decimal places): " + rsmd.getPrecision(i)); // Display the number of digits to the right of the // decimal point. Note: default value is 0. System.out.println(" Precision to the right of" + " the decimal point: " + rsmd.getScale(i)); // Increment column counter i++; } } // end displayRSMetaData(...) } // End class T20301JD