//********************************************************************* // // Copyright (c) 2006-2008 by Teradata Corporation // All Rights Reserved // //********************************************************************* // // File: T20913JD.java // Header: none // Purpose: Demonstrate obtaining attributes of the user-defined types // The program will: // - Connect as user guest/please // - Obtain and display names of attributes of of the given // type for a user-defined type (UDT) that is available // in the given database and catalog // - Disconnect. // // JDBC API: java.sql.Connection, java.sql.DatabaseMetaData, // java.sql.DatabaseMetaData.getAttributes // // Version: Updated for Teradata V2R6.1 // //********************************************************************* import java.sql.*; public class T20913JD { // 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"; // Type name for retrieving attributes of the user-defined types String typeName = "udt_struct_datatype"; // Attribute name for retrieving attributes of the user-defined types String attributeName1 = "lastname"; String attributeName2 = "udt_attribute"; try { System.out.println("\n Sample T20913JD: \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 attribute names of the user- // defined types (UDTs) matching type name pattern "TESTUDTTYPE" // and attribute name pattern "TESTATTRIBUTENAME" in the // database "sysudtlib". // Use getAttributes method to generate a result set of the given // attribute of the given user-defined type (UDT). // 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 UDT type name // pattern (UDT type name) // parameter 4: a String object representing a UDT type attribute // name pattern (UDT type attribute name) // Each column description includes the following columns: // TYPE_CAT (String) // TYPE_SCHEM (String) // TYPE_NAME (String) // ATTR_NAME (String) // DATA_TYPE (int) // ATTR_TYPE_NAME (String) // ATTR_SIZE (int) // DECIMAL_DIGITS (int) // NUM_PREC_RADIX (int) // NULLABLE (int) // REMARKS (String) // ATTR_DEF (String) // SQL_DATA_TYPE (int) // SQL_DATETIME_SUB (int) // CHAR_OCTET_LENGTH (int) // ORDINAL_POSITION (int) // IS_NULLABLE (String) // SCOPE_CATALOG (String) // SCOPE_SCHEMA (String) // SCOPE_TABLE (String) // SOURCE_DATA_TYPE (short) // Please refer to the driver manual for a complete // description of this method, its implementations, // and return values. // The call below will obtain the attributes matching type name // pattern "TESTUDTTYPE" and attribute name pattern // "TESTATTRIBUTENAME" in the database "sysudtlib" ResultSet rs1 = dbmd.getAttributes(null, "sysudtlib", typeName, attributeName1); // Display the retrieved attribute names System.out.println(" DISPLAYING THE ATTRIBUTE lastname" + " FOR UDT TYPE udt_struct_datatype IN SYSUDTLIB DATABASE:\n"); System.out.println(" Database Name : Type Name :" + " Attribute Name : Data Type"); System.out.println(" ---------------------------" + " ---------------------------"); while(rs1.next()) { System.out.println(" " + rs1.getString("TYPE_SCHEM") + " : " + rs1.getString("TYPE_NAME") + " : " + rs1.getString("ATTR_NAME") + " : " + rs1.getInt("DATA_TYPE")); } ResultSet rs2 = dbmd.getAttributes(null, "sysudtlib", typeName, attributeName2); // Display the retrieved attribute names System.out.println("\n DISPLAYING THE UDT ATTRIBUTE udt_attribute" + " FOR UDT TYPE udt_struct_datatype IN SYSUDTLIB DATABASE:\n"); System.out.println(" Database Name : Type Name :" + " Attribute Name : Data Type"); System.out.println(" ---------------------------" + " ---------------------------"); while(rs2.next()) { System.out.println(" " + rs2.getString("TYPE_SCHEM") + " : " + rs2.getString("TYPE_NAME") + " : " + rs2.getString("ATTR_NAME") + " : " + rs2.getInt("DATA_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 T20913JD 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 T20913JD