//********************************************************************* // // Copyright (c) 2006-2008 by Teradata Corporation // All Rights Reserved // //********************************************************************* // // File: T21400JD.java // Purpose: This sample application will: // - Create, delete, and display User Defined Functions // - The source code for the User Defined Function is on the // client. This requires the source code to be transferred // from the client node to the server node. The JDBC driver // uses the the classpath to load all resources. The file // "Judf01.c" must be on the classpath in order for it to // be transferred to the server node so it can be used with // this class. // //********************************************************************* import java.sql.* ; public class T21400JD { public static void main (String [] args) throws ClassNotFoundException { String url = "jdbc:teradata://whomooz/TMODE=ANSI,CHARSET=UTF8"; String user = "guest" ; String password = "please" ; String query1 = "DROP FUNCTION Judf01" ; String query2 = "CREATE FUNCTION Judf01(INTEGER) " + "RETURNS INTEGER " + "LANGUAGE C " + "NO SQL " + "PARAMETER STYLE TD_GENERAL " + "EXTERNAL Name " + "'CS!Judf01!Judf01.c!F!Judf01'"; String query3 = "show function Judf01" ; String query4 = "Select Judf01(10)"; try { System.out.println () ; System.out.println(" Sample T21400JD:"); System.out.println () ; System.out.println(" Loading the Teradata JDBC driver"); Class.forName ("com.teradata.jdbc.TeraDriver") ; System.out.println(" JDBC driver loaded."); System.out.println () ; System.out.println( " Attempting to connect to Teradata via the JDBC driver..."); Connection con = DriverManager.getConnection (url, user, password) ; System.out.println(" Connection to Teradata established."); try { Statement stmt = con.createStatement () ; try { try { System.out.println () ; System.out.println (" Drop the UDF if it already exists") ; stmt.executeUpdate (query1) ; } catch (SQLException ex) { System.out.println () ; System.out.println (" Ignoring exception: "+ ex) ; } System.out.println () ; System.out.println (" Creating the UDF") ; ResultSet rs = stmt.executeQuery (query2) ; System.out.println () ; System.out.println ( " Showing the output from compiling the UDF") ; System.out.println () ; dispResultSet (rs) ; System.out.println () ; System.out.println(" Showing the UDF"); rs = stmt.executeQuery (query3) ; System.out.println () ; dispResultSet (rs) ; System.out.println(); System.out.println(" Calling the UDF"); System.out.println( " Displaying the result set after calling the UDF "); rs = stmt.executeQuery(query4); dispResultSet (rs) ; System.out.println () ; System.out.println(" Closing the result set "); rs.close(); System.out.println(" Result set is closed "); System.out.println () ; } finally { System.out.println(" Closing the Statement Object "); stmt.close () ; System.out.println(" Statement Object is closed "); } } finally { System.out.println () ; System.out.println (" Closing Connection") ; con.close () ; System.out.println (" Closed Connection") ; } System.out.println () ; System.out.println(" Sample T21400JD 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 private static void dispResultSet (ResultSet rs) throws SQLException { ResultSetMetaData rsmd = rs.getMetaData () ; int numCols = rsmd.getColumnCount () ; for (int nRow = 1 ; rs.next () ; nRow++) { for (int i = 1 ; i <= numCols ; i++) { if (i == 1) System.out.print (" Row " + nRow + ": ") ; else System.out.print (", ") ; String s = rs.getString (i) ; if (s != null) s = s.replaceAll ("\r\n", "\n") .replaceAll ("\r", "\n") .replaceAll ("\n$", "") .replaceAll ("\n", "\n ") ; System.out.print (s) ; } System.out.println () ; } } // end dispResultSet } // end class T21400JD