//************************************************************************************
//
// Copyright (c) 2006-2008 by Teradata Corporation
// All Rights Reserved
//
// TERADATA CORPORATION CONFIDENTIAL AND TRADE SECRET
//
//************************************************************************************
//
// File: T20602JD.java
// Purpose: This sample application will:
// - Create, delete, display and use an External Stored Procedure
// - The source code for the External Stored Procedure 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
// "getlastname.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 T20602JD
{
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 PROCEDURE GetLastNameXSP";
String query2 =
"CREATE PROCEDURE GetLastNameXSP "
+ "(INOUT name VARCHAR(30)) "
+ "LANGUAGE C "
+ "NO SQL "
+ "EXTERNAL NAME 'CS!getlastname!getlastname.c!F!xsp_getlastname'";
String query3 = "SHOW PROCEDURE GetLastNameXSP";
String query4 = "{CALL GetLastNameXSP(?)}";
try
{
Class.forName("com.teradata.jdbc.TeraDriver");
Connection con = DriverManager.getConnection(url, user, password);
try
{
Statement stmt = con.createStatement();
try
{
try
{
System.out.println();
System.out.println(
" Drop the External Stored Procedure 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 External Stored Procedure");
stmt.executeUpdate(query2);
// If no errors occured...
System.out.println(
" External Stored Procedure created successfully.");
System.out.println();
System.out.println(" Showing the External Stored Procedure");
ResultSet rs = stmt.executeQuery(query3);
System.out.println();
dispResultSet(rs);
// IN and OUT parameters, and methods for executing the
// call to a stored procedure. Please refer to the JAVA API
// or the supporting driver documentation for a complete
// list of methods, their implementations, and
// return values.
// Creating a CallableStatement object for calling the
// database stored procedure and preparing the callable
// statement for execution
CallableStatement cStmt = con.prepareCall(query4);
try
{
// Setting up input parameter value
String sName = "John Smith";
cStmt.setString(1, sName);
// Setting up output parameters for data retrieval by
// declaring parameter types.
cStmt.registerOutParameter(1, Types.VARCHAR);
System.out.println(" Calling the procedure with '"
+ sName + "' ...");
// Making a procedure call
cStmt.executeUpdate();
// Displaying procedure call result, please refer to the
// driver manual for a full list of data retrieval methods
System.out.println(" Call successful.");
System.out.println(
" Displaying output of the call to "
+ "GetLastNameXSP(...) : ");
System.out.println(" " + sName);
System.out.println(" -----------------");
System.out.println(" Employee Last Name : "
+ cStmt.getString(1));
}
finally
{
// Close the callable statement
cStmt.close();
System.out.println(
" CallableStatement object closed. ");
}
}
finally
{
stmt.close();
}
}
finally
{
System.out.println();
System.out.println(" Closing Connection");
con.close();
System.out.println(" Closed Connection");
}
System.out.println(" Sample T20602JD 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", "").replaceAll("\r",
"").replaceAll("$",
"").replaceAll("", " ");
System.out.print(s);
}
System.out.println();
}
} // end dispResultSet
} // end class T20602JD
|
|
|
|