//************************************************************************************
//
// Copyright (c) 2006-2008 by Teradata Corporation
// All Rights Reserved
//
// TERADATA CORPORATION CONFIDENTIAL AND TRADE SECRET
//
//************************************************************************************
//
// File: T21802JD.java
// Header: none
// Purpose: Demonstrate how to use JDBC updatable result set using
// a CallableStatement obtained from the method
// Connection.prepareCall(String sql, int type, int concurrency).
//
// The program will:
// - Connect as user guest/please
//
// - If sample tables for this program exist,
// drop the following tables:
// updatableRS_jointable1
// updatableRS_jointable2
//
// - Create the following sample tables:
// updatableRS_jointable1 with the following columns:
// colIndex1 INT not null primary key,
// colByteInt BYTEINT,
// colSmallInt SMALLINT,
// colInt INT,
// colReal REAL,
// colFloat FLOAT,
// colDoublePrecision DOUBLE PRECISION,
// colDecimal DECIMAL(18,2),
// colNumeric NUMERIC,
// colChar CHAR(100),
// colVarChar VARCHAR(100),
// colByte BYTE(30),
// colVarByte VARBYTE(13)
//
// updatableRS_jointable2 with the following columns:
// colIndex2 INT not null primary key,
// colDate DATE,
// colTime TIME,
// colTimestamp TIMESTAMP
//
// - Insert data into these two sample tables with
// java.sql.PreparedStatement.executeUpdate
//
// - Create a CallableStatement with the following parameters:
// java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,
// java.sql.ResultSet.CONCUR_UPDATABLE
//
// - CallableStatement.executeQuery with following joined query:
// select * from updatableRS_jointable1 t1 join
// updatableRS_jointable2 t2 on t1.colIndex1=t2.colIndex2
// where t1.colIndex1=? order by t1.colIndex1;
//
// - Call the updater methods to update column values in
// the current row:
// java.sql.ResultSet.updateXXX
//
// - Call ResultSet.refreshRow() to clear the new contents
// of the current row of this ResultSet object
// java.sql.ResultSet.refreshRow
//
// - Call the updater methods to update column values in
// the current row:
// java.sql.ResultSet.updateXXX
//
// - Call ResultSet.updateRow() to Update the underlying
// database with the new contents of the current row of
// this ResultSet object
// java.sql.ResultSet.updateRow
//
// - Call ResultSet.moveToInsertRow() to move the cursor to
// the insert row
// java.sql.ResultSet.moveToInsertRow
//
// - Call the updater methods to update column values in
// the insert row:
// java.sql.ResultSet.updateXXX
//
// - Call ResultSet.insertRow() to insert the contents of
// the insert row into the database
// java.sql.ResultSet.insertRow
//
// - Call ResultSet.moveToCurrentRow() to move the cursor back
// to the remembered cursor position, usually the current row
// java.sql.ResultSet.moveToCurrentRow
//
// - Call ResultSet.deleteRow() to delete the current row
// java.sql.ResultSet.deleteRow
//
// - Disconnect.
//
// JDBC API: java.sql.Connection, java.sql.Statement,
// java.sql.PreparedStatement, java.sql.CallableStatement,
// java.sql.CallableStatement.executeQuery,
// java.sql.ResultSet.updateRow, java.sql.ResultSet.insertRow,
// java.sql.ResultSet.deleteRow, java.sql.ResultSet.refreshRow
//
// Version: Updated for Teradata V2R6.2
//
//************************************************************************************
import java.sql.*;
public class T21802JD
{
// Name of the user able to create, drop, and manipulate tables
public static String sUser = "guest";
public static String sPassword = "please";
// object data arrays for populating sample tables
public static Object data[] = null;
public static Object updateData1[] = null;
public static Object updateData2[] = null;
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";
// Strings used in table dropping
String sDropTbl1 = "DROP TABLE updatableRS_jointable1";
String sDropTbl2 = "DROP TABLE updatableRS_jointable2";
// Strings used in table creation
String sCreateTbl1 = "CREATE TABLE updatableRS_jointable1(" +
"colIndex1 INT not null primary key, " +
"colByteInt BYTEINT, " +
"colSmallInt SMALLINT, " +
"colInt INT, " +
"colReal REAL, " +
"colFloat FLOAT, " +
"colDoublePrecision DOUBLE PRECISION, " +
"colDecimal DECIMAL(18,2), " +
"colNumeric NUMERIC, " +
"colChar CHAR(100), " +
"colVarChar VARCHAR(100), " +
"colByte BYTE(30), " +
"colVarByte VARBYTE(13))";
String sCreateTbl2 = "CREATE TABLE updatableRS_jointable2(" +
"colIndex2 INT not null primary key, " +
"colDate DATE, " +
"colTime TIME, " +
"colTimestamp TIMESTAMP)";
// Strings used in data insertion
String sInsertData1 = "INSERT INTO updatableRS_jointable1(" +
"colIndex1, " +
"colByteInt, " +
"colSmallInt, " +
"colInt, " +
"colReal, " +
"colFloat, " +
"colDoublePrecision, " +
"colDecimal, " +
"colNumeric, " +
"colChar, " +
"colVarChar, " +
"colByte, " +
"colVarByte) " +
"VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)";
String sInsertData2 = "INSERT INTO updatableRS_jointable2(" +
"colIndex2, " +
"colDate, " +
"colTime, " +
"colTimestamp) " +
"VALUES(?,?,?,?)";
// String used in data selection from joined tables
String sSelectJoinedData =
"SELECT * FROM updatableRS_jointable1 t1 " +
"JOIN updatableRS_jointable2 t2 ON t1.colIndex1 = t2.colIndex2 " +
"WHERE t1.colIndex1 = ? " +
"ORDER BY t1.colIndex1";
// Populating object data arrays
data = getData();
updateData1 = getUpdateData1();
updateData2 = getUpdateData2();
try
{
Statement stmt = null;
Connection con = null;
System.out.println(" Sample T21802JD: ");
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
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.
stmt = con.createStatement();
try
{
// Cleanup procedures:
// If the sample tables already exists, drop them.
try
{
System.out.println(" Dropping table if present: "
+ sDropTbl1);
stmt.executeUpdate(sDropTbl1);
System.out.println(" Table dropped.");
// Close the Statement
stmt.close();
}
catch (SQLException ex)
{
// If the table did not exist, no drop is required.
// Ignore the thrown "no table exists" exception
System.out.println(" Drop table exception ignored: " + ex);
System.out.println(" Table could not be dropped."
+ " Execution will continue...");
}
try
{
System.out.println(" Dropping table if present: "
+ sDropTbl2);
stmt.executeUpdate(sDropTbl2);
System.out.println(" Table dropped.");
// Close the Statement
stmt.close();
}
catch (SQLException ex)
{
// If the table did not exist, no drop is required.
// Ignore the thrown "no table exists" exception
System.out.println(" Drop table exception ignored: " + ex);
System.out.println(" Table could not be dropped."
+ " Execution will continue...");
}
// Create the sample tables
System.out.println(" Creating table: " + sCreateTbl1);
stmt.executeUpdate(sCreateTbl1);
System.out.println(" Creating table: " + sCreateTbl2);
stmt.executeUpdate(sCreateTbl2);
System.out.println(" Sample tables created. ");
// Close the Statement
stmt.close();
System.out.println(" Inserting data into sample tables...");
// insert data into table 1
PreparedStatement pStmt1 = null;
pStmt1 = con.prepareStatement(sInsertData1);
int j = 1;
pStmt1.setInt(j++, 1); //colIndex1
pStmt1.setByte(j++, ((Byte) data[0]).byteValue()); //colByteInt
pStmt1.setShort(j++,
((Short) data[1]).shortValue()); //colSmallInt
pStmt1.setInt(j++, ((Integer) data[2]).intValue()); //colInt
pStmt1.setLong(j++, ((Long) data[3]).longValue()); //colReal
pStmt1.setFloat(j++, ((Double) data[4]).floatValue()); //colFloat
pStmt1.setDouble(j++,
((Double) data[5]).doubleValue()); //colDoublePrecision
pStmt1.setBigDecimal(j++,
(java.math.BigDecimal) data[6]); //colDecimal
pStmt1.setInt(j++, ((Integer) data[7]).intValue()); //colNumeric
pStmt1.setString(j++, (String) data[8]); //colChar
pStmt1.setString(j++, (String) data[9]); //colVarChar
pStmt1.setBytes(j++, (byte[]) data[10]); //colByte
pStmt1.setBytes(j++, (byte[]) data[11]); //colVarByte
if (pStmt1.executeUpdate() == 0) {
System.out.println(
" No data inserted into sample table 1...");
throw new IllegalStateException(
"Data insertion into table 1 failed");
}
else
System.out.println(" Data inserted into sample table 1.");
pStmt1.close();
// insert data into table 2
PreparedStatement pStmt2 = null;
pStmt2 = con.prepareStatement(sInsertData2);
int k = 1;
pStmt2.setInt(k++, 1); //colIndex2
pStmt2.setDate(k++, (java.sql.Date) data[12]); //colDate
pStmt2.setTime(k++, (Time) data[13]); //colTime
pStmt2.setTimestamp(k++, (Timestamp) data[14]); //colTimestamp
if (pStmt2.executeUpdate() == 0) {
System.out.println(" No data inserted into sample table 2.");
throw new IllegalStateException(
"Data insertion into table 2 failed");
}
else
System.out.println(" Data inserted into sample table 2.");
pStmt2.close();
CallableStatement cStmt = null;
ResultSet rs = null;
System.out.println(" Selecting data from joined sample tables" +
" using CallableStatement...");
cStmt = con.prepareCall(sSelectJoinedData,
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
cStmt.setInt(1, 1);
rs = cStmt.executeQuery();
System.out.println(" Result set is fetched from database.");
// move the cursor to the first row
rs.next();
// Call the updater methods to update column values in the
// current row
System.out.println(
" Calling updater methods to update initial new" +
" contents on the current row...");
updateRowData1(rs);
System.out.println(
" Initial new contents updated for the current row.");
// Call refreshRow() to clear the new contents of the current row
System.out.println(
" Calling refreshRow() to clear the new contents on...");
rs.refreshRow();
System.out.println(
" Initial new contents on the current row cleared.");
// Call the updater methods again to update column values in the
// current row
System.out.println(
" Calling updater methods again to update new" +
" contents on the current row...");
updateRowData2(rs);
System.out.println(
" New contents updated again for the current row.");
// Call updateRow() to Update the underlying database with the
// new contents of the current row of this ResultSet object
System.out.println(
" Calling updateRow() to update the current row...");
rs.updateRow();
System.out.println(" The current row updated.");
// Call moveToInsertRow() to move the cursor to the insert row
System.out.println(
" Calling moveToInsertRow() to move the cursor" +
" to the insert row...");
rs.moveToInsertRow();
System.out.println(" The cursor moved to the insert row.");
// Call the updater methods to update column values in the
// insert row
System.out.println(" Calling updater methods to update new" +
" contents on the insert row...");
insertRowData(rs);
System.out.println(" New contents updated for the insert row.");
// Call insertRow() to insert the contents of the insert row
// into database
System.out.println(" Calling insertRow() to insert a new row...");
rs.insertRow();
System.out.println(" A second row inserted.");
// Call moveToCurrentRow() to move the cursor back to the
// remembered cursor position, usually the current row
System.out.println(
" Calling moveToCurrentRow() to move the cursor" +
" back to the current row...");
rs.moveToCurrentRow();
System.out.println(" The cursor moved back to the current row.");
// Call deleteRow() to delete the current row
System.out.println(
" Calling deleteRow() to delete the first row...");
rs.deleteRow();
System.out.println(" The first row deleted.");
// Close the result set
rs.close();
cStmt.close();
}
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 T21802JD 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
// Method for calling updater methods to update initial new contents on the
// current row
static void updateRowData1(ResultSet rs) throws SQLException
{
int j = 2; //skip the column colIndex1
rs.updateByte(j++, ((Byte) updateData1[0]).byteValue()); //colByteInt
rs.updateShort(j++, ((Short) updateData1[1]).shortValue()); //colSmallInt
rs.updateInt(j++, ((Integer) updateData1[2]).intValue()); //colInt
rs.updateLong(j++, ((Long) updateData1[3]).longValue()); //colReal
rs.updateFloat(j++, ((Double) updateData1[4]).floatValue()); //colFloat
rs.updateDouble(j++,
((Double) updateData1[5]).doubleValue()); //colDoublePrecision
rs.updateBigDecimal(j++, (java.math.BigDecimal) updateData1[6]); //colDecimal
rs.updateInt(j++, ((Integer) updateData1[7]).intValue()); //colNumeric
rs.updateString(j++, (String) updateData1[8]); //colChar
rs.updateString(j++, (String) updateData1[9]); //colVarChar
rs.updateBytes(j++, (byte[]) updateData1[10]); //colByte
rs.updateBytes(j++, (byte[]) updateData1[11]); //colVarByte
j++; //skip the column colIndex2
rs.updateDate(j++, (java.sql.Date) updateData1[12]); //colDate
rs.updateTime(j++, (Time) updateData1[13]); //colTime
rs.updateTimestamp(j++, (Timestamp) updateData1[14]); //colTimestamp
}
// Method for calling updater methods to update new contents on the current row
static void updateRowData2(ResultSet rs) throws SQLException
{
int j = 2; //skip the column colIndex1
rs.updateByte(j++, ((Byte) updateData2[0]).byteValue()); //colByteInt
rs.updateShort(j++, ((Short) updateData2[1]).shortValue()); //colSmallInt
rs.updateInt(j++, ((Integer) updateData2[2]).intValue()); //colInt
rs.updateLong(j++, ((Long) updateData2[3]).longValue()); //colReal
rs.updateFloat(j++, ((Double) updateData2[4]).floatValue()); //colFloat
rs.updateDouble(j++,
((Double) updateData2[5]).doubleValue()); //colDoublePrecision
rs.updateBigDecimal(j++, (java.math.BigDecimal) updateData2[6]); //colDecimal
rs.updateInt(j++, ((Integer) updateData2[7]).intValue()); //colNumeric
rs.updateString(j++, (String) updateData2[8]); //colChar
rs.updateString(j++, (String) updateData2[9]); //colVarChar
rs.updateBytes(j++, (byte[]) updateData2[10]); //colByte
rs.updateBytes(j++, (byte[]) updateData2[11]); //colVarByte
j++; //skip the column colIndex2
rs.updateDate(j++, (java.sql.Date) updateData2[12]); //colDate
rs.updateTime(j++, (Time) updateData2[13]); //colTime
rs.updateTimestamp(j++, (Timestamp) updateData2[14]); //colTimestamp
}
// Method for calling updater methods to update contents on the insert row
static void insertRowData(ResultSet rs) throws SQLException
{
int j = 1;
rs.updateInt(j++, 2); //colIndex1
rs.updateByte(j++, ((Byte) data[0]).byteValue()); //colByteInt
rs.updateShort(j++, ((Short) data[1]).shortValue()); //colSmallInt
rs.updateInt(j++, ((Integer) data[2]).intValue()); //colInt
rs.updateLong(j++, ((Long) data[3]).longValue()); //colReal
rs.updateFloat(j++, ((Double) data[4]).floatValue()); //colFloat
rs.updateDouble(j++, ((Double) data[5]).doubleValue()); //colDoublePrecision
rs.updateBigDecimal(j++, (java.math.BigDecimal) data[6]); //colDecimal
rs.updateInt(j++, ((Integer) data[7]).intValue()); //colNumeric
rs.updateString(j++, (String) data[8]); //colChar
rs.updateString(j++, (String) data[9]); //colVarChar
rs.updateBytes(j++, (byte[]) data[10]); //colByte
rs.updateBytes(j++, (byte[]) data[11]); //colVarByte
rs.updateInt(j++, 2); //colIndex2
rs.updateDate(j++, (java.sql.Date) data[12]); //colDate
rs.updateTime(j++, (Time) data[13]); //colTime
rs.updateTimestamp(j++, (Timestamp) data[14]); //colTimestamp
}
// Original data used to insert into sample tables
// Also it is the insertion data when calling insertRow()
static Object[] getData()
{
byte[] b1 = "testBinarytestBinarytestBinary".getBytes();
byte[] b2 = "binary Column".getBytes();
Object[] data = new Object[15];
long ms = new Long("1081361243974").longValue();
data[0] = new Byte("49");
data[1] = new Short( (short) 79);
data[2] = new Integer(199);
data[3] = new Long((long) 1999999);
data[4] = new Double((float) 2999999);
data[5] = new Double((double) 959);
data[6] = new java.math.BigDecimal("59.58");
data[7] = new Integer(29);
data[8] = new String("A");
data[9] = new String("ABCDEFG");
data[10] = b1;
data[11] = b2;
data[12] = new java.sql.Date(ms);
data[13] = new Time(ms);
data[14] = new Timestamp(ms);
return data;
}
// Initial update contents values before calling refreshRow()
static Object[] getUpdateData1()
{
byte[] b1 = "initUpdateinitUpdateinitUpdate".getBytes();
byte[] b2 = "initialupdate".getBytes();
Object[] data = new Object[15];
long ms = new Long("1081492779998").longValue();
data[0] = new Byte("50");
data[1] = new Short( (short) 80);
data[2] = new Integer(200);
data[3] = new Long((long) 2000000);
data[4] = new Double((float) 3000000);
data[5] = new Double((double) 960);
data[6] = new java.math.BigDecimal("69.58");
data[7] = new Integer(30);
data[8] = new String("B");
data[9] = new String("MNOPQRS");
data[10] = b1;
data[11] = b2;
data[12] = new java.sql.Date(ms);
data[13] = new Time(ms);
data[14] = new Timestamp(ms);
return data;
}
// New update contents values when calling updateRow()
static Object[] getUpdateData2() {
byte[] b1 = "testUpdatetestUpdatetestUpdate".getBytes();
byte[] b2 = "binary update".getBytes();
Object[] data = new Object[15];
long ms = new Long("1081682779998").longValue();
data[0] = new Byte("51");
data[1] = new Short( (short) 81);
data[2] = new Integer(201);
data[3] = new Long((long) 2000001);
data[4] = new Double((float) 3000001);
data[5] = new Double((double) 961);
data[6] = new java.math.BigDecimal("70.58");
data[7] = new Integer(31);
data[8] = new String("C");
data[9] = new String("TUVWXYZ");
data[10] = b1;
data[11] = b2;
data[12] = new java.sql.Date(ms);
data[13] = new Time(ms);
data[14] = new Timestamp(ms);
return data;
}
} // End class T21802JD
|
|
|
|