//********************************************************************* // // Copyright (c) 2004-2008 by Teradata Corporation // All Rights Reserved // //********************************************************************* // // File: T20908JD.java // Header: none // Purpose: Demonstrate obtaining procedure names // The program will: // - Connect as user guest/please // - Obtain and display names of all procedures in // all databases // - Disconnect. // // JDBC API: java.sql.Connection, java.sql.DatabaseMetaData, // java.sql.DatabaseMetaData.getProcedures // // Version: Updated for Teradata V2R6 // //********************************************************************* import java.sql.*; public class T20908JD { // Name of the user able to create, drop, and manipulate 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"; // Wildcard used for procedure search String wildcard = "%"; try { System.out.println("\n Sample T20908JD: \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 { // Creating a DatabaseMetaData object from an active // connection. DatabaseMetaData dbmd = con.getMetaData(); System.out.println(" DatabaseMetaData object created. \n"); // The following code obtains the names of all procedures // in all databases. // Use getProcedures to generate a result set of procedures. // This method has the following parameters: // parameter 1: a String object representing a catalog name // parameter 2: a String object representing a schema name // pattern (database name) // parameter 3: a String object representing a procedure // name pattern (procedure name) // Each procedure description will contain the following: // PROCEDURE_CAT (String) // PROCEDURE_SCHEM (String) // PROCEDURE_NAME (String) // Reserved (Reserved for future use.) // Reserved (Reserved for future use.) // Reserved (Reserved for future use.) // REMARKS (String) // PROCEDURE_TYPE (Short) // Results will be ordered by PROCEDURE_SCHEM, and // PROCEDURE_NAME. // Note: macros are regarded as procedures. // Please refer to the driver manual for a complete // description of this method, its implementations, // and return values. // The call below will obtain all procedure names in all // databases, using a search with a wildcard for procedure // name. The % character represents any sequence of zero // or more characters. ResultSet rs = dbmd.getProcedures(null, null, wildcard); // Display the procedure names System.out.println(" DISPLAYING ALL PROCEDURE NAMES IN ALL " + "DATABASES:\n"); System.out.println(" Database Name : Procedure Name : " + "Procedure Type"); System.out.println(" ---------------------------------" + "--------------"); while(rs.next()) { System.out.println(" " + rs.getString("PROCEDURE_SCHEM") + " : " + rs.getString("PROCEDURE_NAME") + " : " + rs.getShort("PROCEDURE_TYPE")); } } finally { // Close the connection System.out.println("\n Closing connection to Teradata..."); con.close(); System.out.println(" Connection to Teradata closed. \n"); } System.out.println(" Sample T20908JD 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 } // End class T20908JD