Teradata Download Center / Teradata JDBC Driver / Teradata JDBC Sample Programs

Teradata JDBC Driver Learning Examples

tnolan — Thu, 04 Sep 2008 14:35:18 GMT

This documentation represents proprietary information of Teradata Corporation, and is protected by US, foreign and international intellectual property laws, including US Federal copyright law, and as such, may not be disclosed to third parties, or otherwise copied or duplicated in any form, in whole or in part, without the prior written consent of Teradata Corporation.

The purpose of this documentation is to provide examples of programs that can be used as a guide for purposes of developing JDBC applications solely for use with the Teradata Database, and is provided as an illustration of how JDBC functions operate with the Teradata Database.

This documentation, including the example programs, are provided on an "AS-IS" basis, and under no circumstances will Teradata be liable for any damages (including direct, indirect, consequential, special, punitive, incidental damages) resulting from any use even if notified of the possibility of any damages. Teradata reserves the right to change or withdraw any of the information provided in this documentation without notice.

The learning examples are split into distinct modules. Every module consists of one or more examples within a general theme, exemplifying a specific aspect of the JDBC driver. All examples are thoroughly documented to explain newly encountered features as they are used.

The modules are as follows:

Module	Description
0	Sets up the environment, creating necessary tables and indexes
1	Shows how to obtain result set meta data for a SQL statement
2	Illustrates performing insertions into the sample tables
3	Illustrates performing selections from the sample tables
4	Illustrates performing updates to the sample tables
5	Illustrates performing deletions from the sample tables
6	Illustrates creation and execution of stored procedures
7	Illustrates execution of multi-statement requests
8	Illustrates creation and execution of macros
9	Illustrates obtaining various information about the database
10	Illustrates searching for specific information about the database
11	Illustrates obtaining and modifying client access product options
12	Illustrates using the J2SE 5.0 (JDK 1.5) JdbcRowSetImpl
13	Illustrates using DataSources
14	Illustrates creation and execution of User-Defined Functions (UDFs)
15	Illustrates Teradata-specific data type conversions
16	Illustrates the use of JDBC Escape Syntax
18	Illustrates the use of JDBC Updatable Result Set
19	Illustrates the use of SET QUERY_BAND statement
99	Cleans up the environment

The table below shows the example breakdown into modules, with the respective example file name and description.

Module	File Name	Program Description
0	T20000JD.java	Set up the example environment (users, databases, tables, indexes)
0	T20001JD.java	Set up the example environment (users, databases, tables, indexes)
0	T20002JD.java	Set up the example environment (users, databases, tables, indexes)
0	T20003JD.java	Set up the example environment (users, databases, global temporary tables, indexes)
1	T20100JD.java	Execute a SQL statement and obtain any result set column meta data
1	T20101JD.java	Execute a SQL statement and obtain any result set column meta data
2	T20200JD.java	Insert non LOB columns into a row in a table, without using parameter markers
2	T20201JD.java	Insert non LOB columns into a row in a table, using parameter markers
2	T20202JD.java	Insert LOB columns into a row in a table, without using parameter markers
2	T20203JD.java	Insert LOB columns into a row in a table, using parameter markers
2	T20204JD.java	Insert data into a table and display the generated keys
2	T20205JD.java	Insert data into a table using FastLoad with auto-commit true
2	T20206JD.java	Insert data into a table using FastLoad with auto-commit false
2	T20207JD.java	Insert data into a table using FastLoad with auto-commit false and verify FastLoad sessions
3	T20300JD.java	Select non-LOB columns from a specific row, from a table, without using parameter markers for the predicate values. Also obtain column meta data.
3	T20301JD.java	Select non-LOB columns from a specific row, from a table, using parameter markers for the predicate values. Also obtain column meta data.
3	T20302JD.java	Select LOB columns from a specific row, from a table, without using parameter markers for the predicate values. Also obtain column meta data.
3	T20303JD.java	Select LOB columns from a specific row, from a table, using parameter markers for the predicate values. Also obtain column meta data.
3	T20304JD.java	Obtain result set metadata prior to executing the prepared statement
3	T20305JD.java	Obtain parameter metadata prior to executing the prepared statement
3	T20306JD.java	Select data from a table using FastExport without parameter marker
3	T20307JD.java	Select data from a table using FastExport with parameter marker
3	T20308JD.java	Select data from a table using FastExport with parameter marker and verify FastExport sessions
4	T20400JD.java	Update a non-LOB column from a specific row, from a table, without using parameter markers for the update and predicate values
4	T20401JD.java	Update a non-LOB column from a specific row, from a table, using parameter markers for the update and predicate values
4	T20402JD.java	Update a LOB column from a specific row, from a table, without using parameter markers for the update and predicate values
4	T20403JD.java	Update a LOB column from a specific row, from a table, using parameter markers for the update and predicate values
4	T20404JD.java	Update a LOB column from a specific row, from a table, using the set methods for the update and predicate values
5	T20500JD.java	Delete a specific row, from a table, without using parameter markers for predicate values
5	T20501JD.java	Delete a specific row, from a table, using parameter markers for the predicate values
6	T20600JD.java	Create (if allowed) and execute a stored procedure and obtain its results (no LOB parameters)
6	T20601JD.java	Create (if allowed) and execute a stored procedure and obtain its results (LOB parameters)
6	T20602JD.java	Create (if allowed) and execute an external stored procedure and obtain its results
6	T20603JD.java	Install (if allowed) a jar file for sample Java Stored Procedures
6	T20604JD.java	Create (if allowed) and execute a Java Stored Procedure (no LOB parameters)
6	T20605JD.java	Create (if allowed) and execute a Java Stored Procedure (with a LOB parameter)
6	T20606JD.java	Create (if allowed) and execute a SQL stored procedure and retrieve its dynamic result set
6	T20607JD.java	Create (if allowed) and execute a Java stored procedure and retrieve its dynamic result set
6	T20608JD.java	Create (if allowed) and execute a Java stored procedure and retrieve its multiple dynamic result sets
7	T20700JD.java	Execute a multi-statement request that contains only non-SELECT statements, and obtain the results
7	T20701JD.java	Execute a multi-statement request that contains only SELECT statements, and obtain the results
7	T20702JD.java	Execute a multi-statement request that contains both SELECT and non-SELECT statements, and obtain the results
7	T20703JD.java	Execute a multi-statement INSERT request and display the generated keys
7	T20704JD.java	Execute a multi-statement batch INSERT request and display the generated keys
7	T20705JD.java	Execute a multi-statement request and make all results available simultaneously
8	T20800JD.java	Execute a macro that contains only non-SELECT statements, and obtain the results
8	T20801JD.java	Execute a macro that contains only SELECT statements, and obtain the results.
8	T20802JD.java	Execute a macro that contains both SELECT and non-SELECT statements, and obtain the results.
9	T20900JD.java	Obtain meta data about the client access product
9	T20901JD.java	Obtain a list of reserved words
9	T20902JD.java	Obtain a list of all supported SQL data types
9	T20903JD.java	Obtain a list of all table types
9	T20904JD.java	Obtain table index meta data
9	T20905JD.java	Obtain the names of all databases
9	T20906JD.java	Obtain the names of all tables in all databases
9	T20907JD.java	Obtain the names of all columns in all tables in all databases
9	T20908JD.java	Obtain the names of all procedures in all databases
9	T20909JD.java	Obtain the names of all procedure columns in all databases
9	T20910JD.java	Obtain a list of primary key columns for a specific table
9	T20911JD.java	Obtain an optimal set of columns that uniquely identifies a row for a specific table
9	T20912JD.java	Obtain a list of the user-defined types (UDTs) defined in a particular schema
9	T20913JD.java	Obtain a description of the given attribute of the given type for a user-defined type (UDT) that is available in the given schema and catalog
9	T20914JD.java	Demonstrate use of SET SESSION RATE and MONITOR SESSION.
10	T21000JD.java	Search for a specific table type
10	T21001JD.java	Search for specific databases by name
10	T21002JD.java	Search for specific databases by wildcard characters
10	T21003JD.java	Search for specific tables by name (a table here can actually any object that Teradata thinks of as in the "TVM" category, such as stored procedures and UDFs)
10	T21004JD.java	Search for specific tables by wildcard characters
10	T21005JD.java	Search for specific columns by name
10	T21006JD.java	Search for specific columns by wildcard characters
10	T21007JD.java	Search for specific procedures by name
10	T21008JD.java	Search for specific procedures by name by wildcard characters
10	T21009JD.java	Search for specific procedure columns by name
10	T21010JD.java	Search for specific procedure columns by wildcard characters
11	T21100JD.java	Getting and setting connection options
11	T21101JD.java	Getting and setting statement options
11	T21102JD.java	Use transaction isolation level TRANSACTION_READ_UNCOMMITTED
11	T21104JD.java	Use URL/DataSource Parameter NEW_PASSWORD to automatically handle Database Password expiration
11	T21105JD.java	Use URL/DataSource Parameter USEXVIEWS
12	T21200JD.java	Create and use JdbcRowSetImpl, given a connection URL, user, and password
12	T21201JD.java	Create and use JdbcRowSetImpl, given an existing connection
12	T21202JD.java	Create and use JdbcRowSetImpl, given an existing result set
13	T21300JD.java	Programmatically create a DataSource (see note below)
13	T21301JD.java	Programmatically create a ConnectionPoolDataSource (see note below)
13	T21302JD.java	Obtain a connection from a DataSource (see note below)
14	T21400JD.java	Create and execute a User-Defined Function (UDF)
14	T21401JD.java	Install a jar file for sample Java User Defined Function (JUDF)
14	T21402JD.java	Create and execute a Java User Defined Function (JUDF)
15	T21500JD.java	Perform Teradata-specific data type conversions
16	T21600JD.java	Use JDBC Escape Syntax
18	T21800JD.java	Use JDBC Updatable Result Set from a non-prepared Statement
18	T21801JD.java	Use JDBC Updatable Result Set from a PreparedStatement
18	T21802JD.java	Use JDBC Updatable Result Set from a CallableStatement
19	T21900JD.java	Use SET QUERY_BAND statement
19	T21901JD.java	Use SET QUERY_BAND statement with PROXYUSER
99	T29900JD.java	Clean up the example environment (user, database, tables, indexes)

A sample application to test a connection is also provided, sample1.java.

Use the following procedure to run the Teradata JDBC Driver Learning Examples:

Download a supported JDK to your development platform.
Download and copy samples.jar, terajdbc4.jar, and tdgssconfig.jar to your development platform. Note: tdgssjava.jar is also required for TTU 8.2/Teradata JDBC Driver 3.4 or earlier releases. The commands below assume that all the files are copied to the same directory on your development platform.
Go to the directory where samples.jar is located and unjar the sample applications:
jar xvf samples.jar
Modify the sample application sample1.java with your Teradata Database information.
You need to change the following:
- Name of the Teradata Database.
- Userid and password for the Teradata Database.
Compile the modified sample application by entering the following:
javac sample1.java
Run the application on Windows by entering the following:
java -classpath .;terajdbc4.jar;tdgssconfig.jar sample1
Or run the application on UNIX by entering the following:
java -classpath .:terajdbc4.jar:tdgssconfig.jar sample1
With TTU 8.2/Teradata JDBC Driver 3.4 or earlier releases, run the application on Windows by entering the following:
java -classpath .;terajdbc4.jar;tdgssjava.jar;tdgssconfig.jar sample1
With TTU 8.2/Teradata JDBC Driver 3.4 or earlier releases, run the application on UNIX by entering the following:
java -classpath .:terajdbc4.jar:tdgssjava.jar:tdgssconfig.jar sample1

Note: The "." entry, representing the current directory, on the classpath in the commands above is what enables Java to locate the sample1.class file.

Repeat steps 4, 5, and 6 for each of the other Learning Example programs.

Note: The Java Stored Procedure samples in Module 6 must be compiled and placed into the SampleJXSP.jar file before they can be installed on the Teradata Database. The steps listed below assume that you have already unjarred the contents of samples.jar

Compile the Java Stored Procedure samples by entering the following:
javac -d . BookInfo.java DeptJobInfo.java EmpInfo.java
Jar the Java Stored Procedure samples by entering the following:
jar cvf SampleJXSP.jar com/teradata/sample/BookInfo.class com/teradata/sample/DeptJobInfo.class com/teradata/sample/EmpInfo.class
Modify the sample application T20603JD.java with your Teradata Database information.
You need to change the following:
- Name of the Teradata Database.
- Userid and password for the Teradata Database.
Compile the modified sample application by entering the following:
javac T20603JD.java
Run the application on Windows by entering the following:
java -classpath .;terajdbc4.jar;tdgssconfig.jar T20603JD
Or run the application on UNIX by entering the following:
java -classpath .:terajdbc4.jar:tdgssconfig.jar T20603JD
As shown above, tdgssjava.jar must also be listed on the classpath for TTU 8.2/Teradata JDBC Driver 3.4 or earlier releases.

Note:The Java User Defined Function sample in module 14 must be compiled and placed into the SampleJavaUDF.jar file before they are installed on the Teradata database. The steps listed below assume that you have already unjarred the contents of samples.jar

Compile the Java User Defined Function sample by entering the following:
javac -d . JavaUDFClass.java
Jar the Java User Defined Function sample by entering the following:
jar cvf SampleJavaUDF.jar com/teradata/sample/JavaUDFClass.class
Modify the sample application T21401JD.java with your Teradata Database information.
You need to change the following:
- Name of the Teradata Database.
- Userid and password for the Teradata Database.
Compile the modified sample application by entering the following:
javac T21401JD.java
Run the application on Windows by entering the following:
java -classpath .;terajdbc4.jar;tdgssconfig.jar T21401JD
Or run the application on UNIX by entering the following:
java -classpath .:terajdbc4.jar:tdgssconfig.jar T21401JD
As shown above, tdgssjava.jar must also be listed on the classpath for TTU 8.2/Teradata JDBC Driver 3.4 or earlier releases.

Note: The Module 13 DataSource Learning Examples require the com.sun.jndi.fscontext.RefFSContextFactory class, which is included in the fscontext.jar file of the J2EE SDK. In order to run the Module 13 DataSource Learning Examples, you need to download and install the J2EE SDK from Sun and include fscontext.jar and providerutil.jar on your classpath.

sample1.java

tnolan — Thu, 04 Sep 2008 14:18:30 GMT

T20000JD.java

tnolan — Thu, 04 Sep 2008 14:14:32 GMT

T20001JD.java

tnolan — Thu, 04 Sep 2008 14:14:18 GMT

T20002JD.java

tnolan — Thu, 04 Sep 2008 14:13:53 GMT

T20003JD.java

tnolan — Thu, 04 Sep 2008 14:13:29 GMT

T20100JD.java

tnolan — Thu, 04 Sep 2008 14:13:13 GMT

T20101JD.java

tnolan — Thu, 04 Sep 2008 14:12:58 GMT

T20200JD.java

tnolan — Thu, 04 Sep 2008 14:11:12 GMT

T20201JD.java

tnolan — Thu, 04 Sep 2008 14:10:56 GMT

T20202JD.java

tnolan — Thu, 04 Sep 2008 14:10:21 GMT

T20203JD.java

tnolan — Thu, 04 Sep 2008 14:10:08 GMT

T20204JD.java

tnolan — Thu, 04 Sep 2008 14:09:55 GMT

//************************************************************************************
//
// Copyright (c) 2006-2008 by Teradata Corporation
// All Rights Reserved
//
// TERADATA CORPORATION CONFIDENTIAL AND TRADE SECRET
//
//************************************************************************************
//
// File: T20204JD.java
// Header: none
// Purpose: Demonstrate the retrievel of auto-generated keys
// after an INSERT query.
// The program will:
// - Connect as user guest/please
// - Insert a row into table books indicating the
// generated key flag RETURN_GENERATED_KEYS
// - Retrieve the generated key
// - Display the generated keys result set
// - Insert a row into table books indicating the
// column names that will be made available
// - Retrieve the generated keys
// - Display the generated keys result set
// - Disconnect.
//
// JDBC API: java.sql.Connection, java.sql.PreparedStatement,
// java.sql.PreparedStatement.executeUpdate,
// java.sql.PreparedStatement.getGeneratedKeys,
// java.sql.ResultSet, java.sql.ResultSet.getMetaData,
//
// Version: Updated for Teradata V2R6
//
//************************************************************************************

import java.sql.*;

public class T20204JD
{
// Name of the user able to create and drop 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";

// Creation of the SQL INSERT statement and its parameter value
String sInsertQuery = "INSERT into books "
+ " (ISBN, bookTitle, bookAuthor, bookPublisher)"
+ " values(?, ?, ?, ?)";

try
{
System.out.println(" Sample T20204JD: ");
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
{
System.out.println(" Execute a PreparedStatement request "+
"with the flag GET_GENERATED_KEYS. ");
// Execute a PreparedStatement request and retrieve the
// identity column
requestSingleColumn(con, sInsertQuery);

System.out.println(" Execute a PreparedStatement request "+
"indicating which column names should be "+
"made available for retrieval. ");
// Execute a PreparedStatement batch request and retrieve the
// specified columns using getGeneratedKeys
requestMultipleColumns(con, sInsertQuery);
}
finally
{
// Close the connection
System.out.println(" Closing connection to Teradata...");
con.close();
System.out.println(" Connection to Teradata closed. ");
}

System.out.println(" Sample T20204JD 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

static void requestSingleColumn(Connection con, String sInsertQuery)
throws SQLException
{
System.out.println(" Preparing this SQL statement for execution: "
+ sInsertQuery);
// Creating a prepared statement object from an active connection
// and indicating that the generated key will be returned.
PreparedStatement pstmt = con.prepareStatement(sInsertQuery,
Statement.RETURN_GENERATED_KEYS);
System.out.println(" Prepared statement object created. ");

try
{
// Set parameter values indicated by ? (dynamic update)
System.out.println(" Using setString() to bind value to " +
"the parameter marker:");
pstmt.setString(1, "1-57345-785-X");
pstmt.setString(2, "SQL for Beginners");
pstmt.setString(3, "Smith, John");
pstmt.setString(4, "Able Books");

// The following code will perform an INSERT query
// on the table.

System.out.println(" Executing query. ");
// Submit a query, returning an update count
int updateCount = pstmt.executeUpdate();
System.out.println(" Obtained update count: " + updateCount);

System.out.println(" Retrieving generated keys.");
// Retrieve generated key
ResultSet rs = pstmt.getGeneratedKeys();
// set result set to the first row
rs.next();

// Extract and display generated keys
System.out.println();
System.out.println(" DISPLAYING GENERATED KEY:");
System.out.println(" ---------------------------");

// retrieve and display identity column value
int identityCol = rs.getInt(1);
System.out.println(" Identity Column Value: " + identityCol);
}
finally
{
// Close the statement
pstmt.close();
System.out.println(" PreparedStatement object closed.");
}
}

static void requestMultipleColumns(Connection con, String sInsertQuery)
throws SQLException
{
// Specifying the column names that should be returned in a String array
String columnNames[] = {"bookTitle", "bookAuthor", "identityCol"};

System.out.println(" Preparing this SQL statement for execution: "
+ sInsertQuery);

// Creating a prepared statement object from an active connection
// and passing the columnNames, which is a list of columns to be
// returned from getGeneratedKeys
PreparedStatement pstmt = con.prepareStatement(sInsertQuery,
columnNames);
System.out.println(" Prepared statement object created. ");

try
{
// Set parameter values indicated by ? (dynamic update)
System.out.println(" Using setString() to bind value to " +
"the parameter marker. ");
pstmt.setString(1, "1-68345-785-X");
pstmt.setString(2, "Advanced SQL");
pstmt.setString(3, "White, Joseph");
pstmt.setString(4, "Able Books");

// The following code will perform an INSERT query
// on the table.

System.out.println(" Executing query. ");
// Submit a query, returning an update count
int updateCount = pstmt.executeUpdate();
System.out.println(" Obtained update count: " + updateCount);

System.out.println(" Retrieving generated keys.");
// Retrieve generated key
ResultSet rs = pstmt.getGeneratedKeys();
// set the result set to the first row
rs.next();

// Extract and display generated keys
System.out.println();
System.out.println(" DISPLAYING GENERATED KEY:");
System.out.println(" ---------------------------");

// retrieve and display specified column value
String bookTitle = rs.getString(1);
String bookAuthor = rs.getString(2);
int identityCol = rs.getInt(3);
System.out.println(" Book Title : " + bookTitle);
System.out.println(" Book Author: " + bookAuthor);
System.out.println(" Identity Column Value: " + identityCol);
}
finally
{
// Close the statement
pstmt.close();
System.out.println(" PreparedStatement object closed.");
}
}
} // End class T20204JD

T20205JD.java

tnolan — Thu, 04 Sep 2008 14:09:42 GMT

T20206JD.java

tnolan — Thu, 04 Sep 2008 14:09:28 GMT

//************************************************************************************
//
// Copyright (c) 2006-2008 by Teradata Corporation
// All Rights Reserved
//
// TERADATA CORPORATION CONFIDENTIAL AND TRADE SECRET
//
//************************************************************************************
//
// File: T20206JD.java
// Header: none
// Purpose: Demonstrate FastLoad of data rows with auto-commit false.
//
// The program will:
// - Connect as user guest/please
// - Insert data rows into a table using FastLoad with auto-commit false
// - Verify the updateCount returned by executeBatch
// - Verify that no SQLWarning has occurred
// - Verify that the correct number of data rows were inserted (fastloaded)
// - Disconnect
//
// JDBC API: java.sql.Connection, java.sql.PreparedStatement,
// java.sql.PreparedStatement.executeBatch
//
// Version: new sample
//
//************************************************************************************

import java.io.*;
import java.sql.*;
import java.util.Arrays;

/**
* FastLoad with auto-commit false.
*/
public class T20206JD {
// Name of the user able to create and drop tables
public static String user = "guest";
public static String password = "please";

public static void main(String args[])
throws ClassNotFoundException
{
// URL to be passed to the JDBC driver
String url = "jdbc:teradata://whomooz/TMODE=ANSI,CHARSET=UTF8,TYPE=FASTLOAD";

// Size of CHAR column
int strLen = 100;

// Name of sample table
String tableName = "T20206JD";

// CREATE TABLE statement
String createTable = "CREATE TABLE " + tableName + "," +
"NO FALLBACK," +
"NO BEFORE JOURNAL," +
"NO AFTER JOURNAL," +
"CHECKSUM = DEFAULT" +
"(" +
" L_INDEX INTEGER NOT NULL," +
" L_TEXT CHAR(" + strLen + ")" +
" CHARACTER SET UNICODE NOT CASESPECIFIC" +
") " +
"UNIQUE PRIMARY INDEX ( L_INDEX ";

// INSERT statement
String insertTable = "INSERT INTO " + tableName + " VALUES(?,?)";

// SELECT statement
String selectTable = "SELECT COUNT(*) FROM " + tableName;

try {
System.out.println("Sample T20206JD starting: " + new java.util.Date());
System.out.println(" Looking for the Teradata JDBC driver...");
Class.forName("com.teradata.jdbc.TeraDriver");
System.out.println(" Teradata JDBC driver loaded.");

System.out.println(" Attempting to connect to Teradata...");
Connection con = DriverManager.getConnection(url, user, password);
System.out.println(" Connection to Teradata established.");

try {
System.out.println(" Creating a Statement object...");
Statement stmt = con.createStatement();
System.out.println(" Created a Statement object.");

try {
try {
stmt.executeUpdate("DROP TABLE " + tableName);
} catch (SQLException e) {
// Do nothing because the table likely does not exist yet!
System.out.println(" Drop table exception ignored: " + e);
}
System.out.println(" Creating table " + tableName + "...");
stmt.executeUpdate(createTable);
System.out.println(" Created table " + tableName + ".");

System.out.println(" Creating a PreparedStatement object...");
PreparedStatement pstmt = con.prepareStatement(insertTable);
System.out.println(" Created a PreparedStatement object...");

try {
int numRows = 0;
char[] charBuf = new char[strLen];
Arrays.fill(charBuf, 'a');
String strBuf = new String(charBuf);

con.setAutoCommit(false);

System.out.println(" Binding data rows...");
int batchCount = 0;
pstmt.setInt(1, 1);
pstmt.setString(2, strBuf);
pstmt.addBatch();
batchCount++;

pstmt.setInt(1, 2);
pstmt.setString(2, strBuf);
pstmt.addBatch();
batchCount++;

System.out.println(" Inserting data rows...");
int updateCounts[] = pstmt.executeBatch();
if (updateCounts == null) {
System.out.println(
"ERROR: A null update count was returned!");
} else {
if (updateCounts.length != batchCount) {
System.out.println(
"WARNING: The update count does not match the"
+ " number of rows batched: expected "
+ batchCount + ", got " + updateCounts.length);
}
for (int i = 0; i < updateCounts.length; i++) {
if (updateCounts[i] != 1) {
System.out.println(
"WARNING: The update count for row " + (i+1)
+ " failed: expected 1, got "
+ updateCounts[i]);
}
}
numRows += updateCounts.length;
}

SQLWarning w = pstmt.getWarnings();
while (w != null) {
System.out.println("*** SQLWarning caught ***");
StringWriter sw = new StringWriter();
w.printStackTrace(new PrintWriter(sw, true));
System.out.println("SQL State = " + w.getSQLState()
+ ", Error Code = " + w.getErrorCode() +
"" + sw.toString());
w = w.getNextWarning();
}

System.out.println(" Binding data rows...");
batchCount = 0;
pstmt.setInt(1, 3);
pstmt.setString(2, strBuf);
pstmt.addBatch();
batchCount++;

pstmt.setInt(1, 4);
pstmt.setString(2, strBuf);
pstmt.addBatch();
batchCount++;

System.out.println(" Inserting data rows...");
updateCounts = pstmt.executeBatch();
if (updateCounts == null) {
System.out.println(
"ERROR: A null update count was returned!");
} else {
if (updateCounts.length != batchCount) {
System.out.println(
"WARNING: The update count does not match the"
+ " number of rows batched: expected "
+ batchCount + ", got " + updateCounts.length);
}
for (int i = 0; i < updateCounts.length; i++) {
if (updateCounts[i] != 1) {
System.out.println(
"WARNING: The update count for row " + (i+1)
+ " failed: expected 1, got "
+ updateCounts[i]);
}
}
numRows += updateCounts.length;
}

w = pstmt.getWarnings();
while (w != null) {
System.out.println("*** SQLWarning caught ***");
StringWriter sw = new StringWriter();
w.printStackTrace(new PrintWriter(sw, true));
System.out.println("SQL State = " + w.getSQLState()
+ ", Error Code = " + w.getErrorCode()
+"" + sw.toString());
w = w.getNextWarning();
}

con.commit();
// Note that at this point no further executeBatch is
// permitted unless all rows in the destination table are
// deleted!

con.setAutoCommit(true);

System.out.println(" Selecting data rows...");
ResultSet rs = stmt.executeQuery(selectTable);
try {
int rowCount = 0;
ResultSetMetaData rsmd = rs.getMetaData();
if (rs.next()) {
rowCount = rs.getInt(1);
System.out.println(" " + tableName + " row "
+ rsmd.getColumnName(1) + "=" + rowCount);
}
if (rowCount != numRows) {
System.out.println(
"ERROR: Didn't retrieve enough rows: expected "
+ numRows + ", got " + rowCount);
}
} finally {
rs.close();
}
} catch (SQLException e) {
try {
System.out.println(
" Rolling back due to exception: " + e);
con.rollback();
} catch (SQLException ex) {
System.out.println(
" Chaining exception from rollback: " + ex);
e.setNextException(ex);
}
throw e;
} finally {
System.out.println(" Closing PreparedStatement object...");
pstmt.close();
System.out.println(" PreparedStatement object closed.");
}
} finally {
System.out.println(" Closing Statement object...");
stmt.close();
System.out.println(" Statement object closed.");
}
} finally {
System.out.println(" Closing Connection to Teradata...");
con.close();
System.out.println(" Connection to Teradata closed.");
}

System.out.println("Sample T20206JD finished. " + new java.util.Date());
}
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.") ;
}
}
}

T20207JD.java

tnolan — Thu, 04 Sep 2008 14:09:01 GMT

//************************************************************************************
//
// Copyright (c) 2006-2008 by Teradata Corporation
// All Rights Reserved
//
// TERADATA CORPORATION CONFIDENTIAL AND TRADE SECRET
//
//************************************************************************************
//
// File: T20207JD.java
// Header: none
// Purpose: Demonstrate FastLoad of data rows with auto-commit false
// and verify FastLoad sessions.
//
// The program will:
// - Connect as user guest/please
// - Insert data rows into a table using FastLoad with auto-commit false
// - Verify the updateCount returned by executeBatch
// - Verify that no SQLWarning has occurred
// - Verify the sessions used to FastLoad
// - Verify that the correct number of data rows were inserted (fastloaded)
// - Disconnect
//
// JDBC API: java.sql.Connection, java.sql.PreparedStatement,
// java.sql.PreparedStatement.executeBatch
//
// Version: new sample
//
//************************************************************************************

import java.io.*;
import java.sql.*;
import java.util.Arrays;

/**
* FastLoad with auto-commit false and verify FastLoad sessions.
*/
public class T20207JD
{
// Name of the user able to create and drop tables
public static String user = "guest";
public static String password = "please";

public static void main(String args[])
throws ClassNotFoundException
{
// URL Parameter for SESSIONS
int sessions = 1;

// URL to be passed to the JDBC driver
String url =
"jdbc:teradata://whomooz/TMODE=ANSI,CHARSET=UTF8,TYPE=FASTLOAD,SESSIONS="
+ sessions;

// Size of CHAR column
int strLen = 100;

// Name of sample table
String tableName = "T20207JD";

// CREATE TABLE statement
String createTable = "CREATE TABLE " + tableName + "," +
"NO FALLBACK," +
"NO BEFORE JOURNAL," +
"NO AFTER JOURNAL," +
"CHECKSUM = DEFAULT" +
"(" +
" L_INDEX INTEGER NOT NULL," +
" L_TEXT CHAR(" + strLen + ")" +
" CHARACTER SET UNICODE NOT CASESPECIFIC" +
") " +
"UNIQUE PRIMARY INDEX ( L_INDEX ";

// INSERT statement
String insertTable = "INSERT INTO " + tableName + " VALUES(?,?)";

// SELECT statement
String selectTable = "SELECT COUNT(*) FROM " + tableName;

try {
System.out.println("Sample T20207JD starting: " + new java.util.Date());
System.out.println(" Looking for the Teradata JDBC driver...");
Class.forName("com.teradata.jdbc.TeraDriver");
System.out.println(" Teradata JDBC driver loaded.");

System.out.println(" Attempting to connect to Teradata...");
Connection con = DriverManager.getConnection(url, user, password);
System.out.println(" Connection to Teradata established.");

try {
System.out.println(" Creating a Statement object...");
Statement stmt = con.createStatement();
System.out.println(" Created a Statement object.");

try {
try {
stmt.executeUpdate("DROP TABLE " + tableName);
} catch (SQLException e) {
// Do nothing because the table likely does not exist yet!
System.out.println(" Drop table exception ignored: " + e);
}
System.out.println(" Creating table " + tableName + "...");
stmt.executeUpdate(createTable);
System.out.println(" Created table " + tableName + ".");

System.out.println(" Creating a PreparedStatement object...");
PreparedStatement pstmt = con.prepareStatement(insertTable);
System.out.println(" Created a PreparedStatement object...");

try {
int numRows = 0;
char[] charBuf = new char[strLen];
Arrays.fill(charBuf, 'a');
String strBuf = new String(charBuf);

con.setAutoCommit(false);

System.out.println(" Binding data rows...");
int batchCount = 0;
pstmt.setInt(1, 1);
pstmt.setString(2, strBuf);
pstmt.addBatch();
batchCount++;

pstmt.setInt(1, 2);
pstmt.setString(2, strBuf);
pstmt.addBatch();
batchCount++;

System.out.println(" Inserting data rows...");
int updateCounts[] = pstmt.executeBatch();
if (updateCounts == null) {
System.out.println(
"ERROR: A null update count was returned!");
} else {
if (updateCounts.length != batchCount) {
System.out.println(
"WARNING: The update count does not match the"
+ " number of rows batched: expected "
+ batchCount + ", got " + updateCounts.length);
}
for (int i = 0; i < updateCounts.length; i++) {
if (updateCounts[i] != 1) {
System.out.println(
"WARNING: The update count for row " + (i+1)
+ " failed: expected 1, got "
+ updateCounts[i]);
}
}
numRows += updateCounts.length;
}

SQLWarning w = pstmt.getWarnings();
while (w != null) {
System.out.println("*** SQLWarning caught ***");
StringWriter sw = new StringWriter();
w.printStackTrace(new PrintWriter(sw, true));
System.out.println("SQL State = " + w.getSQLState()
+ ", Error Code = " + w.getErrorCode()
+ "" + sw.toString());
w = w.getNextWarning();
}

// Note that FastLoad sessions can only be verified while
// FastLoad is active with auto-commit set to false,
// i.e. a setXXX method must have been called and commit
// must NOT have been performed yet!
verifyFastLoadSessions(con, pstmt.hashCode(), sessions);

con.commit();
// Note that at this point no further executeBatch is
// permitted unless all rows in the destination table are
// deleted!

con.setAutoCommit(true);

System.out.println(" Selecting data rows...");
ResultSet rs = stmt.executeQuery(selectTable);
try {
int rowCount = 0;
ResultSetMetaData rsmd = rs.getMetaData();
if (rs.next()) {
rowCount = rs.getInt(1);
System.out.println(" " + tableName + " row "
+ rsmd.getColumnName(1) + "=" + rowCount);
}
if (rowCount != numRows) {
System.out.println(
"ERROR: Didn't retrieve enough rows: expected "
+ numRows + ", got " + rowCount);
}
} finally {
rs.close();
}
} catch (SQLException e) {
try {
System.out.println(
" Rolling back due to exception: " + e);
con.rollback();
} catch (SQLException ex) {
System.out.println(
" Chaining exception from rollback: " + ex);
e.setNextException(ex);
}
throw e;
} finally {
System.out.println(" Closing PreparedStatement object...");
pstmt.close();
System.out.println(" PreparedStatement object closed.");
}
} finally {
System.out.println(" Closing Statement object...");
stmt.close();
System.out.println(" Statement object closed.");
}
} finally {
System.out.println(" Closing Connection to Teradata...");
con.close();
System.out.println(" Connection to Teradata closed.");
}

System.out.println("Sample T20207JD finished. " + new java.util.Date());
}
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.") ;
}
}

private static void verifyFastLoadSessions(Connection con, int hashCode,
int sessions)
throws SQLException
{
System.out.println(" Verifying FastLoad SESSIONS for PreparedStatement="
+ hashCode + "...");

// Note that the method for verifying FastLoad SESSIONS may change at any
// time without prior notice!

String amps = con.nativeSQL("{fn teradata_amp_count()}");
System.out.println(" AMP count = " + amps);
int consExpected = sessions < Integer.parseInt(amps) ? sessions
: Integer.parseInt(amps);

String lsnList = con.nativeSQL("{fn teradata_logon_sequence_number()}");
System.out.println(" LSN(s) = " + lsnList);

String lsnArray[] = lsnList.split(",");
if ((lsnArray.length & 1) == 1) {
System.out.println(
"ERROR: Expected an even count of delimited strings, indicating pairs"
+ " of PreparedStatement hash codes and LSNs, but received an odd"
+ " count instead!");
throw new IllegalStateException (
"Received invalid list from teradata_logon_sequence_number") ;
}

for (int i = 0; i < lsnArray.length; i += 2) {
int hashCodeForLSN = Integer.parseInt(lsnArray[i]);
int lsnForHashCode = Integer.parseInt(lsnArray[i+1]);
if (hashCodeForLSN == hashCode)
{
System.out.println(" LogonSequenceNo=" + lsnForHashCode
+ " for PreparedStatement=" + hashCode);

String selectSessionCount = "SELECT COUNT(*) FROM DBC.SessionInfo"
+ " WHERE Partition='FASTLOAD' AND LogonSequenceNo=?";

PreparedStatement pstmt1 = con.prepareStatement(selectSessionCount);
try {
byte bytes[] = new byte[4];
bytes[1] = (byte) ((lsnForHashCode >> 24) & 0xFF);
bytes[0] = (byte) ((lsnForHashCode >> 16) & 0xFF);
bytes[3] = (byte) ((lsnForHashCode >> 8) & 0xFF);
bytes[2] = (byte) (lsnForHashCode & 0xFF);
pstmt1.setBytes(1, bytes);
ResultSet rs1 = pstmt1.executeQuery();
try {
int consFound = 0;
if (rs1.next()) {
ResultSetMetaData rsmd = rs1.getMetaData();
consFound = rs1.getInt(1);
System.out.println(" FastLoadConnection session "
+ rsmd.getColumnName(1) + "=" + consFound
+ " for LogonSequenceNo=" + lsnForHashCode);
}
if (consFound != consExpected) {
System.out.println(
"ERROR: Did not detect a correct count of"
+ " FastLoadConnection session(s): expected "
+ consExpected + ", got " + consFound);
}
} finally {
rs1.close();
}

String selectSessionNums =
"SELECT SessionNo,Partition FROM DBC.SessionInfo"
+ " WHERE LogonSequenceNo=?"
+ " ORDER BY SessionNo,Partition";

PreparedStatement pstmt2 =
con.prepareStatement(selectSessionNums);

try {
pstmt2.setBytes(1, bytes);
ResultSet rs2 = pstmt2.executeQuery();
try {
ResultSetMetaData rsmd = rs2.getMetaData();
while (rs2.next()) {
System.out.println(" Found " + rsmd.getColumnName(2)
+ "=" + rs2.getObject(2) + " with "
+ rsmd.getColumnName(1) + "=" + rs2.getObject(1)
+ " for LogonSequenceNo=" + lsnForHashCode);
}
} finally {
rs2.close();
}
} finally {
pstmt2.close();
}
} finally {
pstmt1.close();
}
break;
}
}
}
}

T20300JD.java

tnolan — Thu, 04 Sep 2008 14:07:00 GMT

T20301JD.java

tnolan — Thu, 04 Sep 2008 14:06:47 GMT

T20302JD.java

tnolan — Thu, 04 Sep 2008 14:06:34 GMT

//************************************************************************************
//
// 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

T20303JD.java

tnolan — Thu, 04 Sep 2008 14:06:21 GMT

//************************************************************************************
//
// Copyright (c) 2004-2008 by Teradata Corporation
// All Rights Reserved
//
// TERADATA CORPORATION CONFIDENTIAL AND TRADE SECRET
//
//************************************************************************************
//
// File: T20303JD.java
// Header: none
// Purpose: Demonstrate basic Teradata SQL using the JDBC API by
// performing a SELECT query on the table 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.PreparedStatement,
// java.sql.PreparedStatement.executeQuery,
// java.sql.ResultSet, java.sql.ResultSet.getMetaData,
// java.sql.ResultSetMetaData
//
// Version: Updated for Teradata V2R6
//
//************************************************************************************

import java.sql.*;
import java.io.*;

public class T20303JD
{
// 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 and its parameter value
String sSelect =
"SELECT empName, empID, empResume, empPhoto, empRawPhotoData " +
"FROM employee2 WHERE empDept = ? ORDER BY empName";
String department = "Product Development";

try
{
System.out.println(" Sample T20303JD: ");
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
{
System.out.println(" Preparing this SQL statement for" +
" execution: " + sSelect);
// Creating a prepared statement object from an active connection
PreparedStatement pstmt = con.prepareStatement(sSelect);
System.out.println(" Prepared statement object created. ");

try
{
// Set parameter values indicated by ? (dynamic update)
System.out.println(" Using setString() to bind value "
+ "to the parameter marker:");
pstmt.setString(1, department);
System.out.println(" FIRST ? set to: " + department);

// 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.

// Submit a query, creating a result set object
ResultSet rs = pstmt.executeQuery();
// Display result set 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 + "resumeT20303.txt";
String bfilename = id + "photoT20303.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
pstmt.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 T20303JD 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 T20303JD

T20304JD.java

tnolan — Thu, 04 Sep 2008 14:06:06 GMT

//************************************************************************************
//
// Copyright (c) 2005-2008 by Teradata Corporation
// All Rights Reserved
//
// TERADATA CORPORATION CONFIDENTIAL AND TRADE SECRET
//
//************************************************************************************
//
// File: T20304JD.java
// Header: none
// Purpose: Demonstrate basic Teradata SQL using the JDBC API by
// performing a SELECT query on the table using
// parameter markers.
// The program will:
// - Connect as user guest/please
// - Prepare a statement which selects a row
// from table employee
// - Display the medatata associated with the
// column values of the prepared statement
// - Execute the select statement
// - Display the results
// - Disconnect
//
// JDBC API: java.sql.Connection, java.sql.PreparedStatement,
// java.sql.PreparedStatement.getMetaData,
// java.sql.PreparedStatement.executeQuery,
// java.sql.ResultSet,
// java.sql.ResultSetMetaData
//
// Version: Updated for Teradata V2R6
//
//************************************************************************************

import java.sql.*;

public class T20304JD
{
// Name of the user able to create and drop 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";

// Creation of the SQL SELECT statement and its parameter value
String sSelect = "SELECT empID, empName, empJob FROM employee"
+ " WHERE empDept = ? ORDER BY empID";
String selectCond = "Product Development";

try
{
System.out.println(" Sample T20304JD: ");
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
{
System.out.println(" Preparing this SQL statement for execution: "
+ sSelect);
// Creating a prepared statement object from an active connection
PreparedStatement pstmt = con.prepareStatement(sSelect);
System.out.println(" Prepared statement object created. ");

try
{
System.out.println(" Display the PreparedStatement meta data"
+ " before executing the request");
displayRSMetaData(pstmt.getMetaData());
System.out.println();

// Set parameter values indicated by ? (dynamic update)
System.out.println(" Using setString() to bind value to " +
"the parameter marker:");
pstmt.setString(1, selectCond);
System.out.println(" FIRST ? set to: " + selectCond);

// The following code will perform a SELECT query
// on the table.

// Submit a query, creating a result set object
ResultSet rs = pstmt.executeQuery();

// Extract and display result set table data
System.out.println();
System.out.println(" DISPLAYING RESULT SET DATA:");
System.out.println(" ---------------------------");

int rowCount = 0; // result set row counter
int numData; // placeholder for integer data
String charData; // placeholder for character data

// iterate through all returned rows and display them
while (rs.next())
{
rowCount++; // increment retrieved row counter
System.out.println();
System.out.println(" ROW " + rowCount + ": ");
// retrieve and display first column value
numData = rs.getInt(1);
System.out.println(" Employee ID: " + numData);
// retrieve and display second column value
charData = rs.getString(2);
System.out.println(" Name: " + charData);
// retrieve and display third column value
charData = rs.getString(3);
System.out.println(" Job Description: " + charData);
}
System.out.println(" " + rowCount + " Row(s) returned.");
}
finally
{
// Close the statement
pstmt.close();
System.out.println(" PreparedStatement 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 T20304JD 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(ResultSetMetaData rsmd) throws SQLException
{
// This code will demonstrate all available methods for
// retrieving table column meta data.

// Retrieve the number of columns returned
int colCount = rsmd.getColumnCount();
System.out.println(
" DISPLAYING RESULT SET COLUMN META DATA BEFORE EXECUTING STATEMENT:");
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(...)
} // End class T20304JD

T20305JD.java

tnolan — Thu, 04 Sep 2008 14:05:53 GMT

T20306JD.java

tnolan — Thu, 04 Sep 2008 14:05:38 GMT

T20307JD.java

tnolan — Thu, 04 Sep 2008 14:05:22 GMT

T20308JD.java

tnolan — Thu, 04 Sep 2008 13:59:54 GMT

//************************************************************************************
//
// Copyright (c) 2008 by Teradata Corporation
// All Rights Reserved
//
// TERADATA CORPORATION CONFIDENTIAL AND TRADE SECRET
//
//************************************************************************************
//
// File: T20308JD.java
// Header: none
// Purpose: Demonstrate FastExport of data rows with parameter marker and
// verify FastExport sessions.
//
// The program will:
// - Connect as user guest/please
// - Insert data rows into a table
// - Select data rows from a table using FastExport with parameter marker
// - Verify that no SQLWarning has occurred
// - Verify the sessions used to FastExport
// - Disconnect
//
// JDBC API: java.sql.Connection, java.sql.PreparedStatement,
// java.sql.PreparedStatement.executeQuery
//
// Version: new sample
//
//************************************************************************************

import java.io.*;
import java.sql.*;
import java.util.Arrays;

/**
* FastExport with parameter marker and verify FastExport sessions.
*/
public class T20308JD
{
// Name of the user able to select a table
public static String user = "guest";
public static String password = "please";

public static void main(String args[])
throws ClassNotFoundException
{
// URL Parameter for SESSIONS
int sessions = 1;

// URL to be passed to the JDBC driver
String url =
"jdbc:teradata://whomooz/TMODE=ANSI,CHARSET=UTF8,TYPE=FASTEXPORT,SESSIONS="
+ sessions;

// Name of sample program
String progName = T20308JD.class.getName();

// Size of CHAR column
int strLen = 100;

// Name of sample table
String tableName = progName;

// CREATE TABLE statement
String createTable = "CREATE TABLE " + tableName + "," +
"NO FALLBACK," +
"NO BEFORE JOURNAL," +
"NO AFTER JOURNAL," +
"CHECKSUM = DEFAULT" +
"(" +
" L_INDEX INTEGER NOT NULL," +
" L_SMALLINT SMALLINT," +
" L_DATE DATE," +
" L_TEXT VARCHAR(" + strLen + ")" +
" CHARACTER SET UNICODE NOT CASESPECIFIC" +
") " +
"UNIQUE PRIMARY INDEX ( L_INDEX ";

// INSERT statement
String insertTable = "INSERT INTO " + tableName + " VALUES(?,?,?,?)";

// SELECT statement
String selectTable = "SELECT L_INDEX, L_SMALLINT, L_DATE, L_TEXT FROM "
+ tableName + " WHERE L_SMALLINT=?";

try {
System.out.println(
"Sample " + progName + " starting: " + new java.util.Date());
System.out.println(" Looking for the Teradata JDBC driver...");
Class.forName("com.teradata.jdbc.TeraDriver");
System.out.println(" Teradata JDBC driver loaded.");

System.out.println(" Attempting to connect to Teradata...");
Connection con = DriverManager.getConnection(url, user, password);
System.out.println(" Connection to Teradata established.");

try {
System.out.println(" Creating a Statement object...");
Statement stmt = con.createStatement();
System.out.println(" Created a Statement object.");

try {
try {
stmt.executeUpdate("DROP TABLE " + tableName);
} catch (SQLException e) {
// Do nothing because the table likely does not exist yet!
System.out.println(" Drop table exception ignored: " + e);
}
System.out.println(" Creating table " + tableName + "...");
stmt.executeUpdate(createTable);
System.out.println(" Created table " + tableName + ".");

System.out.println(
" Creating a PreparedStatement object for INSERT...");
PreparedStatement pstmt1 = con.prepareStatement(insertTable);
System.out.println(
" Created a PreparedStatement object for INSERT...");

try {
con.clearWarnings();

System.out.println(
" Creating a PreparedStatement object for SELECT...");
PreparedStatement pstmt2 = con.prepareStatement(selectTable);
System.out.println(
" Created a PreparedStatement object for SELECT...");

try {
SQLWarning w = con.getWarnings();
// Note that there could be multiple SQLWarning objects
// chained together!
while (w != null) {
System.out.println("*** SQLWarning caught ***");
StringWriter sw = new StringWriter();
w.printStackTrace(new PrintWriter(sw, true));
System.out.println("SQL State = " + w.getSQLState()
+ ", Error Code = " + w.getErrorCode()
+ "" + sw.toString());
w = w.getNextWarning();
}

int rowsInserted = 0;
int batchCount = 0;
char[] charBuf = new char[strLen];
Arrays.fill(charBuf, 'a');
String strBuf = new String(charBuf);

// Note that auto-commit is true by default, thus an
// auto-commit mode of 'true' is assumed here!

System.out.println(" Binding data rows...");
pstmt1.setInt(1, 1);
pstmt1.setShort(2, (short)11);
pstmt1.setDate(3, Date.valueOf("2008-01-18"));
pstmt1.setString(4, strBuf);
pstmt1.addBatch();
batchCount++;

pstmt1.setInt(1, 2);
pstmt1.setShort(2, (short)22);
pstmt1.setDate(3, Date.valueOf("2008-01-17"));
pstmt1.setString(4, strBuf);
pstmt1.addBatch();
batchCount++;

System.out.println(" Inserting data rows...");
int updateCounts[] = pstmt1.executeBatch();
if (updateCounts == null) {
System.out.println(
"ERROR: A null update count was returned!");
} else {
if (updateCounts.length != batchCount) {
System.out.println(
"ERROR: The update count does not match the"
+ " number of rows batched: expected "
+ batchCount + ", got "
+ updateCounts.length);
}
for (int i = 0; i < updateCounts.length; i++) {
if (updateCounts[i] != 1) {
System.out.println(
"ERROR: The update count for row " + (i+1)
+ " failed: expected 1, got "
+ updateCounts[i]);
}
}
rowsInserted += updateCounts.length;
}

System.out.println(" Binding parameter marker...");
pstmt2.setShort(1, (short)11);

// Note that FastExport sessions can only be verified
// while FastExport is active, i.e. a setXXX method must
// have been called!
verifyFastExportSessions(con, pstmt2.hashCode(),
sessions);

System.out.println(" Selecting data rows...");
ResultSet rs = pstmt2.executeQuery();
try {
int rowsSelected = 0;
ResultSetMetaData rsmd = rs.getMetaData();
for (int i = 1; rs.next(); i++, rowsSelected++) {
for (int j = 1; j <= rsmd.getColumnCount(); j++) {
System.out.println(" " + tableName + " row "
+ i + ", col " + j + ": "
+ rsmd.getColumnName(j) + "="
+ rs.getObject(j));
}
}
if (rowsSelected != 1) {
System.out.println("ERROR: Didn't retrieve"
+ " expected rows: expected 1, got "
+ rowsSelected);
}
} finally {
rs.close();
}

w = pstmt2.getWarnings();
// Note that there could be multiple SQLWarning objects
// chained together!
while (w != null) {
System.out.println("*** SQLWarning caught ***");
StringWriter sw = new StringWriter();
w.printStackTrace(new PrintWriter(sw, true));
System.out.println("SQL State = " + w.getSQLState()
+ ", Error Code = " + w.getErrorCode()
+ "" + sw.toString());
w = w.getNextWarning();
}
} finally {
System.out.println(
" Closing PreparedStatement object for SELECT...");
pstmt2.close();
System.out.println(
" PreparedStatement object for SELECT closed.");
}
} finally {
System.out.println(
" Closing PreparedStatement object for INSERT...");
pstmt1.close();
System.out.println(
" PreparedStatement object for INSERT closed.");
}
} finally {
System.out.println(" Closing Statement object...");
stmt.close();
System.out.println(" Statement object closed.");
}
} finally {
System.out.println(" Closing Connection to Teradata...");
con.close();
System.out.println(" Connection to Teradata closed.");
}

System.out.println(
"Sample " + progName + " finished. " + new java.util.Date());
}
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.") ;
}
}

private static void verifyFastExportSessions(Connection con, int hashCode,
int sessions)
throws SQLException
{
System.out.println(" Verifying FastExport SESSIONS for PreparedStatement="
+ hashCode + "...");

// Note that the method for verifying FastExport SESSIONS may change at any
// time without prior notice!

String amps = con.nativeSQL("{fn teradata_amp_count()}");
System.out.println(" AMP count = " + amps);
int consExpected = sessions < Integer.parseInt(amps) ? sessions
: Integer.parseInt(amps);

String lsnList = con.nativeSQL("{fn teradata_logon_sequence_number()}");
System.out.println(" LSN(s) = " + lsnList);

String lsnArray[] = lsnList.split(",");
if ((lsnArray.length & 1) == 1) {
System.out.println("ERROR: Expected an even count of delimited strings,"
+ " indicating pairs of PreparedStatement hash codes and LSNs, but"
+ " received an odd count instead!");
throw new IllegalStateException (
"Received invalid list from teradata_logon_sequence_number") ;
}

for (int i = 0; i < lsnArray.length; i += 2) {
int hashCodeForLSN = Integer.parseInt(lsnArray[i]);
int lsnForHashCode = Integer.parseInt(lsnArray[i+1]);
if (hashCodeForLSN == hashCode)
{
System.out.println(" LogonSequenceNo=" + lsnForHashCode
+ " for PreparedStatement=" + hashCode);

String selectSessionCount = "SELECT COUNT(*) FROM DBC.SessionInfo"
+ " WHERE Partition='EXPORT' AND LogonSequenceNo=?";

PreparedStatement pstmt1 = con.prepareStatement(selectSessionCount);
try {
byte bytes[] = new byte[4];
bytes[1] = (byte) ((lsnForHashCode >> 24) & 0xFF);
bytes[0] = (byte) ((lsnForHashCode >> 16) & 0xFF);
bytes[3] = (byte) ((lsnForHashCode >> 8) & 0xFF);
bytes[2] = (byte) (lsnForHashCode & 0xFF);
pstmt1.setBytes(1, bytes);
ResultSet rs1 = pstmt1.executeQuery();
try {
int consFound = 0;
if (rs1.next()) {
ResultSetMetaData rsmd = rs1.getMetaData();
consFound = rs1.getInt(1);
System.out.println(" FastExportConnection session "
+ rsmd.getColumnName(1) + "=" + consFound
+ " for LogonSequenceNo=" + lsnForHashCode);
}
if (consFound != consExpected) {
System.out.println("ERROR: Did not detect a correct count"
+ " of FastExportConnection session(s): expected "
+ consExpected + ", got " + consFound);
}
} finally {
rs1.close();
}

String selectSessionNums =
"SELECT SessionNo,Partition FROM DBC.SessionInfo"
+" WHERE LogonSequenceNo=?"
+ " ORDER BY SessionNo,Partition";

PreparedStatement pstmt2 =
con.prepareStatement(selectSessionNums);
try {
pstmt2.setBytes(1, bytes);
ResultSet rs2 = pstmt2.executeQuery();
try {
ResultSetMetaData rsmd = rs2.getMetaData();
while (rs2.next()) {
System.out.println(" Found " + rsmd.getColumnName(2)
+ "=" + rs2.getObject(2) + " with "
+ rsmd.getColumnName(1) + "=" + rs2.getObject(1)
+ " for LogonSequenceNo=" + lsnForHashCode);
}
} finally {
rs2.close();
}
} finally {
pstmt2.close();
}
} finally {
pstmt1.close();
}
break;
}
}
}
}

T20400JD.java

tnolan — Thu, 04 Sep 2008 13:58:00 GMT

T20401JD.java

tnolan — Thu, 04 Sep 2008 13:57:47 GMT

T20402JD.java

tnolan — Thu, 04 Sep 2008 13:57:29 GMT

T20403JD.java

tnolan — Thu, 04 Sep 2008 13:57:14 GMT

T20404JD.java

tnolan — Thu, 04 Sep 2008 13:57:00 GMT

T20500JD.java

tnolan — Thu, 04 Sep 2008 13:56:45 GMT

T20501JD.java

tnolan — Thu, 04 Sep 2008 13:56:33 GMT

T20600JD.java

tnolan — Thu, 04 Sep 2008 13:53:46 GMT

T20601JD.java

tnolan — Thu, 04 Sep 2008 13:53:28 GMT

T20602JD.java

tnolan — Thu, 04 Sep 2008 13:53:14 GMT

T20603JD.java

tnolan — Thu, 04 Sep 2008 13:52:58 GMT

T20604JD.java

tnolan — Thu, 04 Sep 2008 13:52:44 GMT

T20605JD.java

tnolan — Thu, 04 Sep 2008 13:52:27 GMT

//************************************************************************************
//
// Copyright (c) 2006-2008 by Teradata Corporation
// All Rights Reserved
//
// TERADATA CORPORATION CONFIDENTIAL AND TRADE SECRET
//
//************************************************************************************
//
// File: T20605JD.java
// Header: none
// Purpose: Demonstrate basic Teradata SQL using the JDBC API by
// creating/executing a Java Stored Procedure.
// The program will:
// - Connect as user guest/please
// - Create a stored java procedure with LOB parameters
// - Execute the stored procedure
// - Obtain and display the results
// - Disconnect.
//
// JDBC API: java.sql.Connection, java.sql.Statement,
// java.sql.Statement.executeUpdate
//
// Version: Created for Teradata Database 12
//
//************************************************************************************

import java.sql.*;
import java.io.*;

public class T20605JD
{
// Name of the user able to create, drop, and manipulate 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";

// Stored procedure creation command and its respective parameters
String sProcedure =
"REPLACE PROCEDURE getEmpInfo " +
"(IN name VARCHAR(30), OUT id INTEGER, OUT dept VARCHAR(50), " +
"OUT job VARCHAR(300), OUT res CLOB) " +
"LANGUAGE JAVA MODIFIES SQL DATA " +
"PARAMETER STYLE JAVA " +
"EXTERNAL NAME 'SampleJXSP:com.teradata.sample.EmpInfo.getEmpInfo(" +
"java.lang.String,java.lang.Integer[],java.lang.String[]," +
"java.lang.String[],java.sql.Clob[])'";

String sCall = "{CALL getEmpInfo(?,?,?,?,?)}";
String sName = "Brian Lee";

try
{
System.out.println(" Sample T20605JD: ");
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 request to the database,
// replacing any old implementations, if present
stmt.executeUpdate(sProcedure);
System.out.println(" Procedure created successfully.");

// Creating a CallableStatement object, representing
// a precompiled SQL statement and preparing the callable
// statement for execution.
CallableStatement cStmt = con.prepareCall(sCall);
// Setting up input parameter value
cStmt.setString(1, sName);
// Setting up output parameters for data retrieval by
// declaring parameter types.
cStmt.registerOutParameter(2, Types.INTEGER);
cStmt.registerOutParameter(3, Types.VARCHAR);
cStmt.registerOutParameter(4, Types.VARCHAR);
cStmt.registerOutParameter(5, Types.CLOB);
System.out.println(" Calling the procedure with '"
+ sName + "' ...");
// Making a procedure call
cStmt.executeUpdate();
// Displaying procedure call result
System.out.println(" Call successful.");
System.out.println(" Displaying output of the call to "
+ "getEmpInfo(...) : ");
System.out.println(" " + sName);
System.out.println(" -----------------");
int id = cStmt.getInt(2);
System.out.println(" Employee ID : " + id);
System.out.println(" Department : " + cStmt.getString(3));
System.out.println(" Job Description : " + cStmt.getString(4));
System.out.print(" Resume : ");
// Writing CLOB data out to a file for review
createClobFile(cStmt.getClob(5), (id + "resumeT20605.txt"));
}
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 T20605JD 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 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(...)
} // End class T20605JD

T20606JD.java

tnolan — Thu, 04 Sep 2008 13:50:53 GMT

T20607JD.java

tnolan — Thu, 04 Sep 2008 13:50:37 GMT

//************************************************************************************
//
// Copyright (c) 2007-2008 by Teradata Corporation
// All Rights Reserved
//
// TERADATA CONFIDENTIAL AND TRADE SECRET
//
//************************************************************************************
//
// File: T20607JD.java
// Header: none
// Purpose: Demonstrate basic Teradata SQL using the JDBC API by
// creating/executing a Java Stored Procedure that returns
// a result set.
// The program will:
// - Connect as user guest/please
// - Create a stored procedure
// - Execute the stored procedure
// - Obtain and display the result set
// - 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 T20607JD
{
// 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 getBookInfoByAuthor " +
"(IN author VARCHAR(30)) " +
"LANGUAGE JAVA MODIFIES SQL DATA " +
"PARAMETER STYLE JAVA " +
"DYNAMIC RESULT SETS 1 " +
"EXTERNAL NAME" +
" 'SampleJXSP:com.teradata.sample.BookInfo.getBookInfoByAuthor'";

String sCall = "{CALL getBookInfoByAuthor(?)}";
String sAuthor = "White, Joseph";
try
{
System.out.println(" Sample T20607JD: ");
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, sAuthor);
System.out.println(" Calling the procedure with '"
+ sAuthor + "' ...");
// Making a procedure call
ResultSet rs = cStmt.executeQuery();
// Displaying procedure result set, please refer to the
// driver manual for a full list of data retrieval methods
System.out.println(" Call successful.");
while (rs.next())
{
System.out.println(
" Displaying the result set returned by procedure"+
" call to getBookInfoByAuthor(...) : ");
System.out.println(" Book Author: " + sAuthor);
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 T20607JD 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 T20607JD