Excel Update/Insert problems
I have bulit a simple test using Excel as an ODBC data source. I have un-ticked the ODBC read-only option. It all appears to work correctly, no exceptions and the update count is 1, but the insert or update isn't applied.
If I insert a row then select all, it shows the new row, but when I open the spreadsheet the new row isn't there. Also, if I have the spreadsheet open while I run the simple test, I see the new row.
My code is:
import java.sql.*;
/**
*Test Excel & JDBC
*/
publicclass ExcelTest{
publicstaticvoid select(){
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con =null;
con = DriverManager.getConnection("jdbc:odbc:ExcelJDBCTest");
Statement stm = con.createStatement();
ResultSet rs = stm.executeQuery("select Town, Population from [Sheet1$]");
while (rs.next()){
String town = rs.getString(1);
int population = rs.getInt(2);
System.out.println(town +": " + population);
}
rs.close();
stm.close();
}catch (ClassNotFoundException e){
System.err.println("ClassNotFoundException Was Thrown");
e.printStackTrace();
}catch (SQLException e){
System.err.println("SQLException Was Thrown");
e.printStackTrace();
}
}
publicstaticvoid insert(){
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con =null;
con = DriverManager.getConnection("jdbc:odbc:ExcelJDBCTest");
Statement stm = con.createStatement();
int r = stm.executeUpdate("Insert into [Sheet1$] values('Epsom', 144)");
System.out.println("updated: " + r);
stm.close();
}catch (ClassNotFoundException e){
System.err.println("ClassNotFoundException Was Thrown");
e.printStackTrace();
}catch (SQLException e){
System.err.println("SQLException Was Thrown");
e.printStackTrace();
}
}
publicstaticvoid main(String[] args){
insert();
select();
}
}
Thanks
Andrew
