//************************************************************************************
//
// Copyright (c) 2004-2008 by Teradata Corporation
// All Rights Reserved
//
// TERADATA CORPORATION CONFIDENTIAL AND TRADE SECRET
//
//************************************************************************************
//
// File: T20302JD.java
// Header: none
// Purpose: Demonstrate basic Teradata SQL using the JDBC API by
// performing a SELECT query on the table without using
// parameter markers.
// The program will:
// - Connect as user guest/please
// - Select a row from table employee2,
// retrieving LOB column values
// - Display the medatata associated with the
// column values of the selected row.
// - Disconnect.
//
// JDBC API: java.sql.Connection,
// java.sql.Statement, java.sql.Statement.executeQuery,
// java.sql.ResultSet, java.sql.ResultSet.getMetaData,
// java.sql.ResultSetMetaData
//
// Version: Updated for Teradata V2R6
//
//************************************************************************************
import java.sql.*;
import java.io.*;
public class T20302JD
{
// Name of the user able to create and drop tables
public static String sUser = "guest";
public static String sPassword = "please";
// Define maximum buffer size for reading in/writing out data
private static final int BUFFERSIZE = 100;
// Constant defining the number of initial bytes displayed to the user
private static final int DISPLAYSIZE = 50;
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";
// Creation of the SQL SELECT statement
String sSelect =
"SELECT empName, empID, empResume, empPhoto, empRawPhotoData " +
"FROM employee2 WHERE empDept = 'Product Development' " +
"ORDER BY empName";
try
{
System.out.println(" Sample T20302JD: ");
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
{
// The following code will perform a SELECT query.
// Clob/Blob values will be retrieved from the database
// and written to the specified files.
// For those values of empPhoto which were inserted using
// binary literals, writing the information out into a jpeg
// format is meaningless. For these cases, a character
// representation of the binary data (in hex format)
// will be displayed instead and written into a text file.
// A given number of first bytes of data will be displayed,
// bounded from above by the DISPLAYSIZE constant.
System.out.println(" Using executeQuery() to execute the " +
"following SQL statement: " + sSelect);
// Submit a query, creating a result set object
ResultSet rs = stmt.executeQuery(sSelect);
// Display table column meta data
displayRSMetaData(rs);
// Extract and display result set table data
System.out.println();
System.out.println(" DISPLAYING RESULT SET DATA:");
System.out.println(" ---------------------------");
int rowCount = 0;
while(rs.next())
{
// Advance row counter
rowCount++;
// Extract column values
String name = rs.getString("empName");
int id = rs.getInt("empID");
Clob clob = rs.getClob("empResume");
Blob blob = rs.getBlob("empPhoto");
int isRaw =
new Byte(rs.getByte("empRawPhotoData")).intValue();
// Create file names for writing out BLOB/CLOB values.
// Refer to the note above for file format explanation.
String cfilename = id + "resumeT20302.txt";
String bfilename = id + "photoT20302.jpg";
System.out.println(" ROW " + rowCount);
System.out.println(" ----------");
System.out.println(" COLUMN empName : " + name);
System.out.println(" COLUMN empID : " + id);
System.out.print(" COLUMN empResume : ");
createClobFile(clob, cfilename);
System.out.print(" COLUMN empPhoto : ");
createBlobFile(blob, bfilename, isRaw);
System.out.println(" COLUMN empRawPhotoData : " + isRaw);
}
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 T20302JD 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 displayRSMetaData(ResultSet rs) throws SQLException
{
// This code will demonstrate all available methods for
// retrieving table column meta data.
// Retrieve result set meta data to get information on the columns
ResultSetMetaData rsmd = rs.getMetaData();
// Retrieve the number of columns returned
int colCount = rsmd.getColumnCount();
System.out.println(" DISPLAYING RESULT SET COLUMN META DATA:");
System.out.println(" -----------------------------------------");
System.out.println(" The resulting table has " + colCount + " columns:");
// For every column, display it's meta data.
int i = 1; // Initialize loop counter
while (i <= colCount)
{
// Demonstrating all methods for retrieving column meta data
System.out.println();
System.out.println(" Column " + i);
System.out.println(" ------------ ");
// Display the suggested column title for use in
// printouts and displays
System.out.println(" Column label: "
+ rsmd.getColumnLabel(i));
// Display the column name
System.out.println(" Column name: "
+ rsmd.getColumnName(i));
// Display the SQL type of a column.
System.out.println(" Column type: "
+ rsmd.getColumnType(i));
// Display the type name of a column
System.out.println(" Column type name: "
+ rsmd.getColumnTypeName(i));
// Display information on whether NULL values are allowed
System.out.println(" NULLs allowed: "
+ rsmd.isNullable(i));
// Display the normal maximum width of a column in characters.
System.out.println(" Maximum character width: "
+ rsmd.getColumnDisplaySize(i));
// Display precision: the number of decimal digits
// Note: default value is 0.
System.out.println(" Column precision"
+ " (number of decimal places): "
+ rsmd.getPrecision(i));
// Display the number of digits to the right of the
// decimal point. Note: default value is 0.
System.out.println(" Precision to the right of"
+ " the decimal point: "
+ rsmd.getScale(i));
// Increment column counter
i++;
}
} // end displayRSMetaData(...)
private static void createClobFile(Clob clob, String filename)
throws SQLException
{
// This function will read in the Clob data one character at a time
// and write it to the file designated by the string 'filename'
try
{
System.out.println("Clob of length = " + clob.length() + " bytes. ");
// Display the first |DISPLAYSIZE| characters
// Set the character data retrieval limit for the display of the
// initial DISPLAYSIZE bytes and retrieve data.
int dispsize = (int)(Math.min(clob.length(), (long)DISPLAYSIZE));
String display = clob.getSubString(1, dispsize);
System.out.println(" First " + DISPLAYSIZE + " bytes (if available): "
+ display);
// Create a file writer object to send clob data to a file
FileWriter fos = new FileWriter(filename);
// Create a character stream reader object to retrieve data
Reader c = clob.getCharacterStream();
// Initializing a buffer for reading in clob data
char[] charBuffer = new char[BUFFERSIZE];
// Initialize a dummy variable representing number of characters
// read into the buffer
int charCount = 0;
// While data is available, read it into a buffer and read out
// to the designated file
while ( (charCount = c.read(charBuffer, 0, BUFFERSIZE)) != -1)
{
// Write out the retrieved character data
fos.write(charBuffer, 0, charCount);
}
// Display success message
System.out.println(" File creation completed successfully. Please "
+ "refer to the following file: " + filename);
// close file/data streams
c.close();
fos.close();
}
catch (IOException ex)
{
System.out.println(" Error writing Clob : " + ex.getMessage());
ex.printStackTrace();
}
} // End createClobFile(...)
private static void createBlobFile(Blob blob, String filename, int litDisplay)
throws SQLException
{
// This function will read in the Blob data one byte at a time
// and write it to the file designated by the string 'filename'.
// A format in which the binary data will be stored to the file
// will depend on the manner in which that data was inserted.
// The litDisplay flag will be used to decide whether a literal
// representation of the binary data is required, in which case,
// this function will write the character representation of the binary data
// (in hexadecimal format) into the file designated by the string 'filename'
// If all parameter values are valid
if ((litDisplay == 0) || (litDisplay == 1))
{
try
{
// Display blob zize information
System.out.println(" Blob of length = " + blob.length() + " bytes.");
// Display the first |DISPLAYSIZE| characters
// Set the character data retrieval limit for the display of the
// initial DISPLAYSIZE bytes and retrieve data
int dispsize = (int)(Math.min(blob.length(), (long)DISPLAYSIZE));
byte [] disp = blob.getBytes(1, dispsize);
String dispStr = "";
for (int i = 0; i < dispsize; i++)
{
int val = disp[i] & 0xFF;
// Display the character representation of retrieved data
if (val < 16)
{ dispStr += '0' + Integer.toHexString(val).toUpperCase();}
else
{ dispStr += Integer.toHexString(val).toUpperCase();}
}
System.out.println(" First " + DISPLAYSIZE + " bytes (if available): "
+ dispStr);
// If a conversion into character representation is required
if (litDisplay == 1)
{
writeBlobCharRep(blob, filename + ".txt");
}
else // If a straightforward transfer of binary data is required
{
// Create a file output stream object to send blob data to a file
FileOutputStream fos = new FileOutputStream(filename);
// Create a binary input stream object to retrieve data
InputStream b = blob.getBinaryStream();
// Initializing a buffer for reading in blob data
byte[] byteBuffer = new byte[BUFFERSIZE];
// Initialize a dummy variable representing number of bytes
// read into the buffer
int byteCount = 0;
// While data is available, read it into a buffer and read out
// to the designated file
while ( (byteCount = b.read(byteBuffer, 0, BUFFERSIZE)) != -1)
{
// Write out the retrieved character data
fos.write(byteBuffer, 0, byteCount);
}
// Display success message
System.out.println(" File creation completed successfully."
+ " Please refer to the following file: " + filename);
// close file/data streams
b.close();
fos.close();
}
}
catch (IOException ex)
{
System.out.println(" Error writing Blob : " + ex.getMessage());
ex.printStackTrace();
}
}
else // if some other parameter value was passed
{
throw new IllegalArgumentException(
" Invalid parameter value. litDisplay must hold a value of 0 or 1.");
}
} // End createBlobFile(...)
private static void writeBlobCharRep(Blob blob, String filename)
throws SQLException
{
// This function will read in the Blob data one byte at a time
// and will write the character representation of the binary data
// (in hexadecimal format) into the file designated by the string 'filename'
try
{
// Create a file writer object to send character data to a file
FileWriter fos = new FileWriter(filename);
// Create a binary input stream object to retrieve data
InputStream b = blob.getBinaryStream();
// Initialize a buffer for reading in blob data
byte[] byteBuffer = new byte[BUFFERSIZE];
// Initialize a string to hold character representation of
// binary data
String byteStr = "";
// Initialize a dummy variable representing number of bytes
// read into the buffer
int byteCount = 0;
// While data is available, read it into a buffer and write out
// its character representatin
while ( (byteCount = b.read(byteBuffer, 0, BUFFERSIZE)) != -1)
{
for (int i = 0; i < byteCount; i++)
{
// Isolate the integer value of the byte read
// Note that a conversion to an unsigned integer
// has been made.
int val = byteBuffer[i] & 0xFF;
if (val < 16)
{ byteStr += '0' + Integer.toHexString(val).toUpperCase();}
else
{ byteStr += Integer.toHexString(val).toUpperCase();}
}
// Write out the representation of the retrieved binary data
fos.write(byteStr);
}
// Display success message
System.out.println(" File creation completed successfully. Please "
+ "refer to the following file: " + filename);
// close file/data streams
b.close();
fos.close();
}
catch (IOException ex)
{
System.out.println(" Error writing Blob : " + ex.getMessage());
ex.printStackTrace();
}
} // End writeBlobCharRep(...)
} // End class T20302JD
|
|
|
|