wood burning stoves 2.0*
The moose likes JDBC and the fly likes SQL Error: string data, right truncation Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL Error: string data, right truncation" Watch "SQL Error: string data, right truncation" New topic
Author

SQL Error: string data, right truncation

Mike Rosser
Greenhorn

Joined: Sep 15, 2008
Posts: 8
I'm stumped. This (newbie) routine reads data from one database into a RecordSet, and then takes three fields from each record in the set and inserts them into another SQL database. All the fields in the 2nd database are substantially larger than the fields in the source database. It reads 2,290 records into the RecordSet, and then writes the first 359 successfully into the 2nd database, but then throws the error:

SQLException: [Microsoft][ODBC SQL Server Driver]String data, right truncation 0 22001

There's nothing unusual about the 360th record - the field sizes are the same as the first 359. Even if I reduce the record set to just the six records that start with the same three letters, it still throws and error on the same record. But the routine shows me that the field sizes on that 360th record are the same as the others.

Any ideas? Can I improve on my string manipulation in a way that would help? And is it possible to force it to ignore this error and either write what it has or just skip the record?

Thanks

import java.sql.*;

/**
*
*/
public class WriteToGC {
public static void main(String[] args) {
String data = "jdbcdbc:gc";
String driverName = "sun.jdbc.odbc.JdbcOdbcDriver";
String data1 = "jdbcdbc:B_PASS";

try {
Class.forName(driverName);
Connection conn1 = DriverManager.getConnection(data1,"me","password");

Statement st1 = conn1.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet rec1 = st1.executeQuery(
"SELECT B_PASS.RLAST_NAME, B_PASS.RFIRST_NAME, " +
"B_PASS.REXPIRATION_DATE, MASTER_ACCOUNT.RADDRESS_1, " +
"MASTER_ACCOUNT.RADDRESS_2, MASTER_ACCOUNT.RCITY, " +
"MASTER_ACCOUNT.RSTATE, MASTER_ACCOUNT.RPOSTCODE, " +
"MASTER_ACCOUNT.RDAY_PHONE " +
"FROM B_PASS INNER JOIN MASTER_ACCOUNT " +
"ON B_PASS.RACCOUNT = MASTER_ACCOUNT.RACCOUNT " +
"WHERE B_PASS.RLAST_NAME LIKE 'A%' AND " +
"B_PASS.rexpiration_date>'2008-09-19' " +
"ORDER BY RLAST_NAME, RFIRST_NAME");

rec1.beforeFirst();
rec1.last();
int size = rec1.getRow();
System.out.println(size);

rec1.beforeFirst();
for (int i=1;i<=size;i++) {
System.out.print(rec1.getString("RLAST_NAME") + "\t"
+rec1.getString("RFIRST_NAME") + "\t"
+rec1.getString("RDAY_PHONE"));
System.out.println();
// let�s me view the data in the RecordSet
}
System.out.println("Done reading");

rec1.beforeFirst(); //put the cursor back at the beginning of rec1
rec1.next();
Connection conn = DriverManager.getConnection(data,"sa","mymicros");

PreparedStatement update = conn.prepareStatement(
"INSERT into gc.Guest (gst_last_name, gst_first_name, gst_phone, gst_vip_level) VALUES (?, ?, ?, '1')");

System.out.println(rec1.getString("RLAST_NAME")); //just to make sure I'm at the top
// and rec1 is still available
rec1.beforeFirst();
rec1.next();
for (int x = 1; x < size; x++) {
String lastname = rec1.getString("RLAST_NAME");
String firstname = rec1.getString("RFIRST_NAME");
String phone = rec1.getString("RDAY_PHONE");
lastname.trim();
int lastnamelength=lastname.length();
if (lastnamelength>24) lastname=lastname.substring(0,24);
firstname.trim();
int firstnamelength=firstname.length();
if (firstnamelength>24) firstname=firstname.substring(0,24);
phone.trim();
int phonelength=phone.length();
if (phonelength < 15) phone = phone + " ";
String phone1 = phone.substring(0,15);
int phone1length = phone1.length();
System.out.print(lastnamelength +" " + lastname + " " + firstnamelength + " " + firstname + phone + phonelength + " " + phone1 + " " + phone1length);

//all name fields are 24 chars, phone is 15 chars
//name fields are going into 100-character fields in db; phone goes into //50-character field in db

System.out.println();

update.setObject(1,lastname,java.sql.Types.VARCHAR);
update.setObject(2,firstname,java.sql.Types.VARCHAR);
update.setObject(3,phone1,java.sql.Types.VARCHAR);
int rowCount = update.executeUpdate();
System.out.println(rowCount + x);
rec1.next();
}
System.out.println("Done writing");
st1.close();
conn1.close();
conn.close();
//)
}catch (SQLException s) { //end try
System.out.println("SQL Error: " + s.getMessage() + " " + s.toString() + " "
+ s.getErrorCode() + " " + s.getSQLState());
} //end catch
catch (ClassNotFoundException classNotFound)
{
classNotFound.printStackTrace();
} //end catch

} //end main
} //end class
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

There's a lot of things I could say about that, but let's start with this:This line of code doesn't do anything useful. It returns the trimmed value of lastname, but you don't assign it to anything so the trimmed value is just lost.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


sun.jdbc.odbc.JdbcOdbcDriver

Have you tried using a proper type-4 driver? The JDBC-ODBC bridge is (by Sun's own admission) a litle less than perfect.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Mike Rosser
Greenhorn

Joined: Sep 15, 2008
Posts: 8
I'll check out the driver options. Thanks
Mike Rosser
Greenhorn

Joined: Sep 15, 2008
Posts: 8
lastname and firstname each cannot exceed 16 characters. Not sure why, but once I trimmed any names over 16 characters, the error went away.
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38001
    
  22
Originally posted by Mike Rosser:
lastname and firstname each cannot exceed 16 characters. Not sure why, but once I trimmed any names over 16 characters, the error went away.
See if you can get into the database and try something like "explain name;" or whatever the table with the name columns in. You may find they are declared as "varchar(16)" in which case you can't enter more than 16 characters.
Mike Rosser
Greenhorn

Joined: Sep 15, 2008
Posts: 8
I've been in there and they are both declared nvarchar(100).

Wasn't that big a deal for names - very few were over 16 characters, but addresses are the next thing I need to plug in, and that could pose some problems with a 16-character limitation.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL Error: string data, right truncation
 
Similar Threads
SQL Query using WHERE clause.
Newbie jdbc sql problem
Can't delete a row in an Access database
Transactions
help with a sql statement