selecting near 100 columns and some thousand rows,I've got an error: some columns of some rows were null in ResultSet,but they really had valid values in table.When reducing column count I've got valid values. Is it normal behaviour ? Ihaven't got any warnings.
Thank you!
|
I created a dbf table with 200 columns and 100000 rows. It seems normal.
/* A simple test to recur an issue reported by Tverdokhlebov Mikhail */
import java.sql.*;
import java.util.Properties;
public class testDBFLotColumn1{
public static void main(String argv[]){
try{
Class.forName("com.hxtt.sql.dbf.DBFDriver");
String url="jdbc:dbf:////d:/test";
Properties properties=new Properties();
Connection con = DriverManager.getConnection(url,properties);
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
String columnDefs="char0 char(20),int0 int";
final int columnCoupleCount=100;
for(int i=1;i columnDefs+=",char"+i+" char(20),int"+i+" int";
}
String sql="create table if not exists lotcolumns ("+columnDefs+");";
boolean initDataBaseFlag=stmt.executeUpdate(sql)>0;
stmt.close();
if(initDataBaseFlag){
String questions="?,?";
for(int i=1;i questions+=",?,?";
}
sql="insert into lotcolumns values("+questions+");";
PreparedStatement pstmt = con.prepareStatement(sql);
for(int i=0;i<100000;i++){
for(int j=0;j pstmt.setString(j*2+1,i+":"+j);
pstmt.setInt(j*2+2,j);
}
pstmt.executeUpdate();
}
pstmt.close();
}
sql="select recno(),* from lotcolumns";
PreparedStatement pstmt = con.prepareStatement(sql);
ResultSet rs=pstmt.executeQuery();
ResultSetMetaData resultSetMetaData = rs.getMetaData();
int iNumCols = resultSetMetaData.getColumnCount();
for (int j = 1; j <= iNumCols; j++) {
System.out.println(resultSetMetaData.getColumnLabel(j)
+ " " + resultSetMetaData.getColumnTypeName(j)
+ " " + resultSetMetaData.getColumnType(j)
+ " " + resultSetMetaData.getPrecision(j)
+ " " + resultSetMetaData.getScale(j)
);
}
Object colval;
while (rs.next()) {
for (int j = 1; j <= iNumCols; j++) {
colval = rs.getObject(j);
if(colval==null){
throw new Exception("Found invalid value at row "+rs.getObject(1));
}
}
}
rs.close();
rs = null;
pstmt.close();
con.close();
}
catch( SQLException sqle )
{
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);
}
catch( Exception e )
{
System.out.println(e.getMessage());
e.printStackTrace();
}
}
}
|
|
Please check whether you're using a very old package.
|
But problem really exists.Maybe,the reason is my SQL is complicated. I use such construction: select protocol.*,protinf.*,dcount.num_count,dcount.date_close from protocol join protinf join dcount where (protocol.contents=protinf.num) and (protocol.num_count=dcount.num_count) and (dcount.date_close= null )
The table protocol has 33 columns and near 100000 records,the table protinf has 2 columns and near 300000 rows, and table dcount has 68 columns and near 5000 rows.
When I use simply * in fields list, null values appear.
Excuse me for delay
|
It seems normal still. Please download the latest package on June 13, 2006. You can try the testDBFLotColumn2 sample on your data, and that sample won't corrupte your data.
/* A simple test to recur an issue reported by Tverdokhlebov Mikhail */
import java.sql.*;
import java.util.Properties;
public class testDBFLotColumn2{
public static void main(String argv[]){
try{
Class.forName("com.hxtt.sql.dbf.DBFDriver");
String url="jdbc:dbf:////d:/test";
Properties properties=new Properties();
Connection con = DriverManager.getConnection(url,properties);
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
String columnDefs="char0 char(10),int0 int";
int columnCoupleCount=15;
for(int i=1;i columnDefs+=",char"+i+" char(10),int"+i+" int";
}
String sql="create table if not exists protocol (contents int,num_count int, "+columnDefs+");";
boolean initDataBaseFlag=stmt.executeUpdate(sql)>0;
columnDefs="char0 char(10),int0 int";
columnCoupleCount=5;
for(int i=1;i columnDefs+=",char"+i+" char(10),int"+i+" int";
}
sql="create table if not exists protinf (num int, "+columnDefs+");";
stmt.executeUpdate(sql);
columnDefs="char0 char(10),int0 int";
columnCoupleCount=32;
for(int i=1;i columnDefs+=",char"+i+" char(10),int"+i+" int";
}
sql="create table if not exists dcount (num_count int,date_close boolean, "+columnDefs+");";
stmt.executeUpdate(sql);
stmt.close();
if(initDataBaseFlag){
String questions="?,?,?,?";
columnCoupleCount=15;
for(int i=1;i questions+=",?,?";
}
sql="insert into protocol values("+questions+");";
PreparedStatement pstmt = con.prepareStatement(sql);
for(int i=0;i<100000 ;i++){
pstmt.setInt(1,i);
pstmt.setInt(2,i);
for(int j=0;j pstmt.setString(j*2+3,i+":"+j);
pstmt.setInt(j*2+4,j);
}
pstmt.executeUpdate();
}
pstmt.close();
questions="?,?,?";
columnCoupleCount=5;
for(int i=1;i questions+=",?,?";
}
sql="insert into protinf values("+questions+");";
pstmt = con.prepareStatement(sql);
for(int i=0;i<300000;i++){
pstmt.setInt(1,i);
for(int j=0;j pstmt.setString(j*2+2,i+":"+j);
pstmt.setInt(j*2+3,j);
}
pstmt.executeUpdate();
}
pstmt.close();
questions="?,?,?,?";
columnCoupleCount=32;
for(int i=1;i questions+=",?,?";
}
sql="insert into dcount values("+questions+");";
pstmt = con.prepareStatement(sql);
for(int i=0;i<300000;i++){
pstmt.setInt(1,i);
pstmt.setObject(2,i%2==0?null:Boolean.TRUE);
for(int j=0;j pstmt.setString(j*2+3,i+":"+j);
pstmt.setInt(j*2+4,j);
}
pstmt.executeUpdate();
}
pstmt.close();
}
sql="select protocol.*,protinf.*,dcount.num_count,dcount.date_close from protocol join protinf join dcount where (protocol.contents=protinf.num) and (protocol.num_count=dcount.num_count) and (dcount.date_close= null ) ";
PreparedStatement pstmt = con.prepareStatement(sql);
ResultSet rs=pstmt.executeQuery();
ResultSetMetaData resultSetMetaData = rs.getMetaData();
int iNumCols = resultSetMetaData.getColumnCount();
for (int j = 1; j <= iNumCols; j++) {
System.out.println(resultSetMetaData.getColumnLabel(j)
+ " " + resultSetMetaData.getColumnTypeName(j)
+ " " + resultSetMetaData.getColumnType(j)
+ " " + resultSetMetaData.getPrecision(j)
+ " " + resultSetMetaData.getScale(j)
);
}
Object colval;
int count=0;
while (rs.next()) {
count++;
for (int j = 1; j <= iNumCols; j++) {
colval = rs.getObject(j);
if(colval==null && j throw new Exception("Found invalid value at row "+rs.getObject(1));
}
}
}
System.out.println("count:"+count);
rs.close();
rs = null;
pstmt.close();
con.close();
}
catch( SQLException sqle )
{
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);
}
catch( Exception e )
{
System.out.println(e.getMessage());
e.printStackTrace();
}
}
}
|
The
throw new Exception("Found invalid value at row "+rs.getObject(1));
should be:
throw new Exception("Found invalid value at row "+count);
|
Main
