//********************************************************************* // // Copyright (c) 2004-2008 by Teradata Corporation // All Rights Reserved // //********************************************************************* // // File: T21001JD.java // Header: none // Purpose: Demonstrate searching for a specific database // The program will: // - Connect as user guest/please // - Obtain and display names of all databases matching // a specified name // - Disconnect. // // JDBC API: java.sql.Connection, java.sql.DatabaseMetaData, // java.sql.DatabaseMetaData.getTables // // Version: Updated for Teradata V2R6 // //********************************************************************* import java.sql.*; public class T21001JD { // 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"; // Database name to be searched for String dbName = "guest"; // SQL query for a specific database by name. String dbSpecSearch = "SELECT DISTINCT " + "CAST(DBC.DATABASES.DATABASENAME AS VARCHAR(30)) AS TABLE_SCHEM " + "FROM DBC.DATABASES " + "WHERE UPPER(TRIM(DBC.DATABASES.DATABASENAME)) " + "= UPPER(TRIM('" + dbName + "')) " + "ORDER BY DATABASENAME"; try { System.out.println("\n Sample T21001JD: \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 { // In order to search for a specific database by name, // two methods are available. // DatabaseMetaData.getTables(..) method can be used to // search for a specific database by specifying a database // name in the schemaPattern parameter and excluding // all other parameters from the search. The side effect // of using this method is that if a database matching // the given name has been found, a row will be returned // for every table in that database. In essence, the query // performed is for all tables in that specific database, // returning the tables if such a database is present. // Another method is executing a SQL query looking for that // database. Both methods will be shown. System.out.println(" METHOD 1 \n"); // 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 databases // matching the specified name using the getTables method. // Use getTables to generate a result set of database names. // Please note that this method will only return information // on databases and tables to which the user has access to. ResultSet rs = dbmd.getTables(null, dbName, null, null); // Display the database names and related information System.out.println(" DISPLAYING ALL DATABASES MATCHING \"" + dbName + "\":\n"); System.out.println(" Database Name : Table Name : Table Type"); System.out.println(" ---------------------------------------"); while (rs.next()) { System.out.println(" " + rs.getString("TABLE_SCHEM") + " : " + rs.getString("TABLE_NAME") + " : " + rs.getString("TABLE_TYPE")); } System.out.println("\n METHOD 2 \n"); // The following code will execute a SQL query which will // search for a specific database by name. // Creating a statement object from an active connection Statement stmt = con.createStatement(); System.out.println(" Statement object created. \n"); try { System.out.println(" Using executeQuery() to execute " + "the following SQL statement:\n " + dbSpecSearch); // Submit a query, creating a result set object rs = stmt.executeQuery(dbSpecSearch); // Extract and display result set table data System.out.println(); System.out.println(" DISPLAYING ALL DATABASES " + "MATCHING \"" + dbName + "\":"); System.out.println(" -------------------------" + "-----------------"); int rowCount = 0; // result set row counter // iterate through all returned rows and display them while (rs.next()) { rowCount++; // increment retrieved row counter System.out.println(" " + rs.getString("TABLE_SCHEM")); } System.out.println("\n " + rowCount + " Row(s) returned."); } finally { // Close the statement stmt.close(); System.out.println("\n Statement object closed."); } } 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 T21001JD 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 T21001JD