//************************************************************************************
//
// Copyright (c) 2004-2008 by Teradata Corporation
// All Rights Reserved
//
// TERADATA CORPORATION CONFIDENTIAL AND TRADE SECRET
//
//************************************************************************************
//
// 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(" Sample T21001JD: ");
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. ");
// 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. ");
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 ");
// Creating a DatabaseMetaData object from an active
// connection.
DatabaseMetaData dbmd = con.getMetaData();
System.out.println(" DatabaseMetaData object created. ");
// 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 + "\":");
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(" METHOD 2 ");
// 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. ");
try
{
System.out.println(" Using executeQuery() to execute " +
"the following SQL statement: " + 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(" " + rowCount + " Row(s) returned.");
}
finally
{
// Close the statement
stmt.close();
System.out.println(" Statement object closed.");
}
}
finally
{
// Close the connection
System.out.println(" Closing connection to Teradata...");
con.close();
System.out.println(" Connection to Teradata closed. ");
}
System.out.println(" Sample T21001JD finished. ");
}
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
|
|
|
|