Writing to an excel file from oracle table

[pjeypal]

Hi,
Importing data to oracle from Excel is working perfectly with the JDBC-ODBC brdige driver.But when i try to export data back from oracle to Excel using the same method its raising exception

Exception:[Microsoft][ODBC Excel Driver] Operation must use an updateable query.


I'm adding the code here for exporting from oracle to Excel

import java.sql.*;

public class OratoXLS {
public PreparedStatement xlsData;

public OratoXLS() {
export("");
}

public void export(String xlsFile){
Connection connection=null;
try{
//Class.forName("oracle.jdbc.driver.OracleDriver");
//Connection c = DriverManager.getConnection("jdbcracle:thinhostname:1521:SID", "user", "passwd");

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con=DriverManager.getConnection("jdbcdbc:htool1","scott","tiger");
Statement st=con.createStatement();
ResultSet rs=st.executeQuery("select * from buildhierarchy");
//ResultSet rs=st.executeQuery("select * from [WithParent$]");
ResultSetMetaData rsmd=rs.getMetaData();
int numberOfColumns=rsmd.getColumnCount();

Connection con1=DriverManager.getConnection("jdbcdbc:jarus");
xlsData=con1.prepareStatement("insert into [WithParent$]" +
"(location,description,parent,equipment,eqdescription,drawing,ex01,ex02,ex03,ex04,ex05) values(?,?,?,?,?,?,?,?,?,?,?)");
while(rs.next()){
for(int i=1;i<=numberOfColumns;i++){
if(i>1)System.out.print(",");
String columnValue=rs.getString(i);
System.out.print(columnValue);
xlsData.setString(i,columnValue);
}
System.out.println("");
xlsData.executeUpdate();
}

st.close();
con.close();
}
catch(Exception ex){
System.err.print("Exception:");
System.err.println(ex.getMessage());
}

}

}
I've read in many places that this method should work.Can anyone spot the error in my coding?

Priya

 

[stefanwagner]

Is this something different than your other Question?

seeking a job as java-programmer in Berlin:

http://home.arcor.de/hirnstrom/bewerbung

 

[pjeypal]

Hi,
Its the same thing but writing to excel(other one reading from excel).I managed to sort the error by unchecking read only in the dsn settings for excel.now program is running but records are getting inserted into excel only when the excel workbook is open..

any idea why this is happening?


Priya

 

[stefanwagner]

Well excel is not a database and wasn't designed with databases in mind.

Using it as Database is a 'dirty hack' and should be avoided if possible.

seeking a job as java-programmer in Berlin:

http://home.arcor.de/hirnstrom/bewerbung