//************************************************************************************
//
// Copyright (c) 2007-2008 by Teradata Corporation
// All Rights Reserved
//
// TERADATA CONFIDENTIAL AND TRADE SECRET
//
//************************************************************************************
//
// File: T20608JD.java
// Header: none
// Purpose: Demonstrate basic Teradata SQL using the JDBC API by
// creating/executing a Java Stored Procedure that returns
// multiple result sets.
// The program will:
// - Connect as user guest/please
// - Create a stored procedure
// - Execute the stored procedure
// - Obtain and display the multiple result sets
// - Disconnect.
//
// JDBC API: java.sql.Connection, java.sql.Statement,
// java.sql.Statement.executeQuery,
// java.sql.CallableStatement,
// java.sql.CallableStatement.setString,
// java.sql.ResultSet.next
// java.sql.ResultSet.getString
// java.sql.ResultSet.getMetaData
// java.sql.MetaData.getColumnName
// java.sql.MetaData.getColumnCount
//
// Version: Created for Teradata Database 13.0
//
//************************************************************************************
import java.sql.*;
public class T20608JD
{
// 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";
//Create a Java Stored Procedure creation command. Since
//a result set is being returned, it must be specified.
String sProcedure =
"REPLACE PROCEDURE getBookInfoByTitleAndPublisher " +
"(IN BookTitle VARCHAR(30), "+
" IN BookTitleTopic VARCHAR(30)) " +
"LANGUAGE JAVA MODIFIES SQL DATA " +
"PARAMETER STYLE JAVA " +
"DYNAMIC RESULT SETS 2 " +
"EXTERNAL NAME 'SampleJXSP:com.teradata.sample.BookInfo." +
"getBookInfoByTitleAndPublisher'";
String sCall = "{CALL getBookInfoByTitleAndPublisher(?, ?)}";
String sBookTitle = "Advanced SQL";
String sBookPublisher = "Able Books";
try
{
System.out.println(" Sample T20608JD: ");
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
{
// Creating a statement object from an active connection
Statement stmt = con.createStatement();
System.out.println(" Statement object created. ");
try
{
System.out.println(" Attempting to create a procedure : "
+ sProcedure);
// Sending the procedure creation DDL request to the
// database, replacing any old implementations, if present
stmt.executeUpdate(sProcedure);
// If no errors occured...
System.out.println(" Procedure created successfully.");
// The CallableStatement object represents a precompiled
// SQL statement. It provides methods for setting up its
// 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(sCall);
// Setting up input parameter value
cStmt.setString(1, sBookTitle);
cStmt.setString(2, sBookPublisher);
System.out.println(" Calling the procedure with '"
+ sBookTitle + ", "+sBookPublisher+"' ...");
// Making a procedure call
boolean rsReturned = cStmt.execute();
if (rsReturned)
{
ResultSet rs = cStmt.getResultSet();
// Displaying procedure result set, please refer to the
// driver manual for a full list of data retrieval methods
System.out.println(" Call successful.");
System.out.println(
" Displaying the 1st result set returned by procedure"+
" call to getBookInfoByTitleAndPublisher(...) : ");
while (rs.next())
{
System.out.println(" Book Title: " + sBookTitle);
System.out.println(" --------------------------");
for (int colNum = 1;
colNum <= rs.getMetaData().getColumnCount();
colNum++)
{
System.out.println(
" "+rs.getMetaData().getColumnName(colNum)
+ " : " +rs.getString(colNum));
}
}
}
rsReturned = cStmt.getMoreResults();
if (rsReturned)
{
ResultSet rs = cStmt.getResultSet();
// Displaying procedure result set, please refer to the
// driver manual for a full list of data retrieval methods
System.out.println(" Call successful.");
System.out.println(
" Displaying the 2nd result set returned by procedure"+
" call to getBookInfoByTitleAndPublisher(...) : ");
while (rs.next())
{
System.out.println(" Book Publisher: "
+ sBookPublisher);
System.out.println(" --------------------------");
for (int colNum = 1;
colNum <= rs.getMetaData().getColumnCount();
colNum++)
{
System.out.println(
" "+rs.getMetaData().getColumnName(colNum)
+ " : " +rs.getString(colNum));
}
}
}
}
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 T20608JD 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 T20608JD
|
|
|
|