When trying to run a query on some data (= 3768 records), I get the following exception:
java.sql.SQLException: Failed to go Record 937 of table applications! For more information, please use SQLException.getNextException().
at com.hxtt.global.SQLState.SQLException(Unknown Source)
at com.hxtt.sql.text.l.a(Unknown Source)
at com.hxtt.sql.ay.a(Unknown Source)
at com.hxtt.sql.co.a(Unknown Source)
at com.hxtt.sql.co.a(Unknown Source)
at com.hxtt.sql.ef.s(Unknown Source)
at com.hxtt.sql.dc.a(Unknown Source)
at com.hxtt.sql.dc.a(Unknown Source)
at com.hxtt.sql.cv.new(Unknown Source)
at com.hxtt.sql.cv.next(Unknown Source)
at CredentialsMigrator.main(CredentialsMigrator.java:41)
java.sql.SQLException: Failed to get an int value from [B: [B@166a22b
at com.hxtt.global.SQLState.SQLException(Unknown Source)
at com.hxtt.global.SQLState.transferValueException(Unknown Source)
at com.hxtt.global.ac.a(Unknown Source)
at com.hxtt.global.ac.new(Unknown Source)
at com.hxtt.sql.text.a.a(Unknown Source)
at com.hxtt.sql.text.a.a(Unknown Source)
at com.hxtt.sql.text.a.a(Unknown Source)
at com.hxtt.sql.text.l.a(Unknown Source)
at com.hxtt.sql.ay.a(Unknown Source)
at com.hxtt.sql.co.a(Unknown Source)
at com.hxtt.sql.co.a(Unknown Source)
at com.hxtt.sql.ef.s(Unknown Source)
at com.hxtt.sql.dc.a(Unknown Source)
at com.hxtt.sql.dc.a(Unknown Source)
at com.hxtt.sql.cv.new(Unknown Source)
at com.hxtt.sql.cv.next(Unknown Source)
at CredentialsMigrator.main(CredentialsMigrator.java:41)
My code:
Class.forName("com.hxtt.sql.text.TextDriver");
Properties props = new Properties();
props.put("_CSV_Header","true");
Connection con1 = DriverManager.getConnection("jdbc:csv:/" + dataPath, props);
Statement stmt1 = con1.createStatement();
ResultSet rs1 = stmt1.executeQuery("SELECT first_name, last_name FROM applications");
try{
while (rs1.next()) {
String firstName = rs1.getString("first_name");
String lastName = rs1.getString("last_name");
System.out.println(firstName + " " + lastName);
}
}
catch(SQLException sqle) {
sqle.printStackTrace();
sqle.getNextException().printStackTrace();
}
Any ideas?
|
>catch(SQLException sqle) {
>sqle.printStackTrace();
>sqle.getNextException().printStackTrace();
>}
It should be:
do
{
System.out.println(sqle.getMessage());
// System.out.println("Error Code:"+sqle.getErrorCode());
// System.out.println("SQL State:"+sqle.getSQLState());
sqle.printStackTrace();
}while((sqle=sqle.getNextException())!=null);
>java.sql.SQLException: Failed to go Record 937 of table applications!
>java.sql.SQLException: Failed to get an int value from [B: [B@166a22b
maxScanRows: Indicates how many rows should be scanned when determining the column types. If you set maxScanRows to 0, the entire file is scanned. If you set maxScanRows to a negative value, the file won't be scanned. For those tables with predefined table structure, that option will be ignored. Default value: 10
ignoreDirtyData: Indicates whether ignores all dirty data and return null value when failed to parse number value or date value. Default value: false
Because HXTT Text (CSV) scanned the first 10 rows, and thought one column is int type, but failed to get an int value for that column at 937 rows.
1st solution: set maxScanRows to -1, if you need only return string values.
2nd solution: set maxScanRows to 0, if you think that column should be string type, but some of other columns is int, date, or numeric.
3rd solution(preferable): set ignoreDirtyData to true, if you think only the value at 937 row is wrong, and most are int value indeed. You can know all dirty values through the following code:
//while(rs.next())...
java.sql.SQLWarning warnings=rs.getWarnings();
if(warnings!=null){
do{
System.out.println(warnings.getMessage());
System.out.println("Error Code:"+warnings.getErrorCode());
System.out.println("SQL State:"+warnings.getSQLState());
}while((warnings=warnings.getNextWarning())!=null);
rs.clearWarnings();
}
|
All your suggestions worked above, when I tried to the 'ignoreDirtyData' solution, I discovered the following.
1) The problem was not record 937, but rather record 943.
2) The warning given was the following:
Value 1/13/2006(row 943, column PrintDate) is not INTEGER type
Error Code: 786694
SQL State: C0106
Yes, PrintDate is a column in the table and it is empty EXCEPT for record 943 :)
However, if the query only stated "SELECT first_name, last_name FROM applications", then why would it even look at the data in PrintDate? Very tricky bug to debug!
|
All your suggestions worked above, when I tried to the 'ignoreDirtyData' solution, I discovered the following.
1) The problem was not record 937, but rather record 943.
2) The warning given was the following:
Value 1/13/2006(row 943, column PrintDate) is not INTEGER type
Error Code: 786694
SQL State: C0106
Yes, PrintDate is a column in the table and it is empty EXCEPT for record 943 :)
However, if the query only stated "SELECT first_name, last_name FROM applications", then why would it even look at the data in PrintDate? Very tricky bug to debug!
|
>1) The problem was not record 937, but rather record 943.
I guess tahtHXTT Text (CSV) will fetch 10 rows(ResultSet.setFetchSize(n)) per time .
>Yes, PrintDate is a column in the table and it is empty EXCEPT for record
> 943 :)
>However, if the query only stated "SELECT first_name, last_name FROM
> applications", then why would it even look at the data in PrintDate? Very
> tricky bug to debug!
Because it will check that data integrity for the total row.
|
>Yes, PrintDate is a column in the table and it is empty EXCEPT for record
> 943 :)
Fixed an issue for date type detection on null values. Please download the latest package.
|
Main
webmaster@hxtt.com