Name: rm29839 Date: 12/17/97


1.Compile a program that copies a tables contents from
one server(odbc source) to another(odbc source).
2. get a result set.
3. loop thru the result set using a prepared statement to insert values
  in the table (same exact table def) on the target
  server.
runs for awhile and then gets the following error:

*** SQLException caught ***

SQLState: 22001
Message: [Microsoft][ODBC SQL Server Driver][SQL Server]Column 'company_code' of table 'csmart.dbo.
sdicms_pref_addr_work' cannot accept 3 bytes (2 max).
Vendor: 8152

SQLState: 01000
Message: [Microsoft][ODBC SQL Server Driver][SQL Server]Command has been aborted.
Vendor: 3621
  
This error is impossible since both table defs are
exactly the same.

jdk1.1.3 does not get this error.
microsoft jview does not get this error.

Doesn't matter whether i compile it with jdk1.1.3 or
1.1.5. "java" command fails for JDK1.1.5 only.

Shows up faster if i batch inserts with commit.
if using default for autocommit, then it runs a little longer
but still gets the error.

This error is pretty serious to me. I could not get around it using
just JDK1.1.5.

I did not try JDK1.1.4 as being windows, i skipped
  that release.

Some source(it is unclear how much you need and this input box is rather small!)

**** source begin ****

//
//
// RepPreferredAdr
//
//

import java.sql.*;

class RepPreferredAdr
{
   private static final String MS_DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";
public static void main (String args[]) {
// connection strings
String SDICMS_url = "JDBC:ODBC:SDICMS_RS_SYSTEM";
String EIS_url = "JDBC:ODBC:TCSMART";

try {

// Load the jdbc-odbc bridge driver

Class.forName (MS_DRIVER);

//following logs a lot of data
// DriverManager.setLogStream(System.out);

// Attempt to connect to a driver.

SDICMS_con = DriverManager.getConnection (
SDICMS_url, "xxxxx", "xxxxx");
EIS_con = DriverManager.getConnection (
EIS_url, "xxxxx", "xxxxx");

// If we were unable to connect, an exception
// would have been thrown. So, if we get here,
// we are successfully connected to the URL's

// Check for, and display and warnings generated
// by the connect.

checkForWarning (SDICMS_con.getWarnings ());
checkForWarning (EIS_con.getWarnings ());

// Get the DatabaseMetaData object and display
// some information about the connection

DatabaseMetaData SDICMS_dma = SDICMS_con.getMetaData ();
DatabaseMetaData EIS_dma = EIS_con.getMetaData ();

System.out.println("\nConnected to " + SDICMS_dma.getURL());
System.out.println("Driver " +
SDICMS_dma.getDriverName());
System.out.println("Version " +
SDICMS_dma.getDriverVersion());
System.out.println("");

System.out.println("\nConnected to " + EIS_dma.getURL());
System.out.println("Driver " +
EIS_dma.getDriverName());
System.out.println("Version " +
EIS_dma.getDriverVersion());
System.out.println("");

/** This copies the sdicms_pref_addr_work table */

copyPreferredAddress();

// Close the connection

SDICMS_con.close();
EIS_con.close();
}

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 ("\n*** SQLException caught ***\n");

while (ex != null) {
System.out.println ("SQLState: " +
ex.getSQLState ());
System.out.println ("Message: " + ex.getMessage ());
System.out.println ("Vendor: " +
ex.getErrorCode ());
ex = ex.getNextException ();
System.out.println ("");
}
}
catch (java.lang.Exception ex) {

// Got some other type of exception. Dump it.

ex.printStackTrace ();
}
}

// test SDICMS database connection
private static Connection EIS_con; {
}

// test EIS database connection
private static Connection SDICMS_con; {
}


//-------------------------------------------------------------------
// checkForWarning
// Checks for and displays warnings. Returns true if a warning
// existed
//-------------------------------------------------------------------

private static boolean checkForWarning (SQLWarning warn)
throws SQLException {
boolean rc = false;

// If a SQLWarning object was given, display the
// warning messages. Note that there could be
// multiple warnings chained together

if (warn != null) {
System.out.println ("\n *** Warning ***\n");
rc = true;
while (warn != null) {
System.out.println ("SQLState: " +
warn.getSQLState ());
System.out.println ("Message: " +
warn.getMessage ());
System.out.println ("Vendor: " +
warn.getErrorCode ());
System.out.println ("");
warn = warn.getNextWarning ();
}
}
return rc;
}

//-------------------------------------------------------------------
// copyPreferredAddress
// Copies all columns and rows in the given result set
//-------------------------------------------------------------------

private static void copyPreferredAddress() throws SQLException
{
// SQL Table variables
String said;
String company_code;
String address_line_1;
String address_line_2;
String address_line_3;
String address_line_4;
String address_line_5;
String address_city;
String address_state;
String address_zip;
double commitCount=0;

// program variables
String query;
String delete;
String DropCreateIndex;
ResultSet SDICMS_rs;

// Create a Statement object so we can submit
// SQL statements to the driver

Statement SDICMS_stmt = SDICMS_con.createStatement ();
Statement EIS_stmt = EIS_con.createStatement ();
boolean moreSDICMS;
java.sql.PreparedStatement EIS_stmt_insert;

// Submit an update, deleteing old table entries

delete = "truncate table sdicms_pref_addr_work ";
EIS_stmt.executeUpdate(delete);

// Submit a query, creating a ResultSet object

query = "SELECT said, company_code, "
+ "address_line_1, address_line_2, address_line_3, "
+ "address_line_4, address_line_5, address_city, "
+ "address_state, address_zip"
+ " FROM sdicms_pref_addr_work ";
SDICMS_rs = SDICMS_stmt.executeQuery (query);

// Using the retrieved result set, insert this into a table in a different database

EIS_stmt_insert = EIS_con.prepareStatement(
"INSERT sdicms_pref_addr_work "
+ "VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )");
// set autocommit to false so inserts can be batched
EIS_con.setAutoCommit( false );
// pass the retrieved columns as fields from each row and insert these

moreSDICMS = SDICMS_rs.next ();
while (moreSDICMS) {

if (commitCount > 50) {
EIS_con.commit();
commitCount=0;
} else {
commitCount = commitCount + 1;
}

said = SDICMS_rs.getString("said");
company_code = SDICMS_rs.getString("company_code");
address_line_1 = SDICMS_rs.getString("address_line_1");
address_line_2 = SDICMS_rs.getString("address_line_2");
address_line_3 = SDICMS_rs.getString("address_line_3");
address_line_4 = SDICMS_rs.getString("address_line_4");
address_line_5 = SDICMS_rs.getString("address_line_5");
address_city = SDICMS_rs.getString("address_city");
address_state = SDICMS_rs.getString("address_state");
address_zip = SDICMS_rs.getString("address_zip");


EIS_stmt_insert.setString(1,said);
EIS_stmt_insert.setString(2,company_code);
if (address_line_1 == null) {
EIS_stmt_insert.setNull(3,Types.VARCHAR);
} else {
EIS_stmt_insert.setString(3,address_line_1);
}
if (address_line_2 == null) {
EIS_stmt_insert.setNull(4,Types.VARCHAR);
} else {
EIS_stmt_insert.setString(4,address_line_2);
}
if (address_line_3 == null) {
EIS_stmt_insert.setNull(5,Types.VARCHAR);
} else {
EIS_stmt_insert.setString(5,address_line_3);
}
if (address_line_4 == null) {
EIS_stmt_insert.setNull(6,Types.VARCHAR);
} else {
EIS_stmt_insert.setString(6,address_line_4);
}
if (address_line_5 == null) {
EIS_stmt_insert.setNull(7,Types.VARCHAR);
} else {
EIS_stmt_insert.setString(7,address_line_5);
}
if (address_city == null) {
EIS_stmt_insert.setNull(8,Types.VARCHAR);
} else {
EIS_stmt_insert.setString(8,address_city);
}
if (address_state == null) {
EIS_stmt_insert.setNull(9,Types.VARCHAR);
} else {
EIS_stmt_insert.setString(9,address_state);
}
if (address_zip == null) {
EIS_stmt_insert.setNull(10,Types.VARCHAR);
} else {
EIS_stmt_insert.setString(10,address_zip);
}
EIS_stmt_insert.executeUpdate();
moreSDICMS = SDICMS_rs.next ();
}

EIS_con.commit();

// set autocommit to true so no need to control commits anymore, batching done.
EIS_con.setAutoCommit( true );

// Close the result set

SDICMS_rs.close();
// EIS_rs.close();

// Close the statements

SDICMS_stmt.close();
EIS_stmt.close();

}

}

***** source end *****
(Review ID: 22082)
======================================================================