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