HXTT ACCESS
ALTER TABLE ... MODIFY (error detection)
Ezio
2007-02-19 08:08:13.0
I want MODIFY the SQL_TYPE in table1. VARCHAR in INTEGER.
IF is not possibles I want erase the values in VARCHAR column1 and after
executeUpdate-> ALTER TABLE table1 MODIFY colulmn1 INTEGER.
BUT I can not detect the error because the driver go in crash.

Have You a solution for me?
Thank You.

This is the code for test the error.
private void testAlterTable(){
try {
Class.forName("com.hxtt.sql.access.AccessDriver");
String url = "jdbc:Access:/c:";
Connection con = DriverManager.getConnection(url, new Properties());

//init database
Statement astat = con.createStatement();
astat.execute("create database if not exists testAlter");

astat.executeUpdate(" drop table if exists testAlter.Table1");
astat.executeUpdate(" create table if not exists testAlter.Table1 (id int unique,"+
" A CHAR(10), B VARCHAR(10),C LONGVARCHAR, D NUMERIC(8,2), E DECIMAL(8,2), F BIT, G TINYINT, H SMALLINT, I INTEGER,"+
" L BIGINT, M REAL, N FLOAT, O DOUBLE, P BINARY(10), Q VARBINARY(10), R LONGVARBINARY);");

astat.executeUpdate(" delete from testAlter.Table1 where id>=0");
astat.close();

con.setCatalog("testAlter.mdb");

PreparedStatement ps = con.prepareStatement(
"insert into Table1 (id, A,B,C,D,E,F,G,H,I,L,M,N,O,P,Q,R) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);");

for (int i = 1; i <= 19; i++) {
ps.setInt(1, i); //id
ps.setString(2,"AAAAAAAA"+i);//A CHAR(10)
ps.setString(3,"BBBBBBBB"+i);//B VARCHAR(10)
ps.setString(4,"CCCCCCCC"+i);//C LONGVARCHAR
ps.setBigDecimal(5,new BigDecimal(i));//D NUMERIC(8,2)
ps.setBigDecimal(6,new BigDecimal(i));//E DECIMAL(8,2)
ps.setBoolean(7,false);//F BIT
ps.setByte(8,(byte)i);//G TINYINT
ps.setShort(9,(short)i);//H SMALLINT
ps.setInt(10,i);//I INTEGER
ps.setLong(11,i);//L BIGINT
ps.setFloat(12,i);//M REAL
ps.setFloat(13,i);//N FLOAT
ps.setDouble(14,i);//O DOUBLE
ps.setBytes(15,new byte[]{'A','B','C','D'});//P BINARY(10)
ps.setBytes(16,new byte[]{'A','B','C','D'});//Q VARBINARY(10)
ps.setBytes(17,new byte[]{'A','B','C','D'});//R LONGVARBINARY
ps.executeUpdate();
}

ps.close();

con.setCatalog("testAlter.mdb");

Statement stmt = con.createStatement();

int count = stmt.executeUpdate("ALTER TABLE table1 MODIFY A REAL");
//if(in error){
// stmt.executeUpdate("Update table1 set A=''");
// stmt.executeUpdate("ALTER TABLE table1 MODIFY A REAL");
//}

stmt.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();
}
}

Re:ALTER TABLE ... MODIFY (error detection)
HXTT Support
2007-02-19 08:32:39.0
It's normal since HXTT Access will fail to convert your varchar values into number values.
Change
>Statement stmt = con.createStatement();
>int count = stmt.executeUpdate("ALTER TABLE table1 MODIFY A REAL");
to
Statement stmt = con.createStatement();
stmt.executeUpdate("update table1 set A=null");
int count = stmt.executeUpdate("ALTER TABLE table1 MODIFY A REAL");

Then you will pass:)


Google