I would like to ask if it's possible to use excel as the persistence layer with iBATIS doing the O/R mapping job. I have been trying to implement but get stuck with two problems.
Firstly, I set up a ODBC datasource with my excel file in my machine. I tried to access the data in excel with simple JDBC command to make sure that it's working. However, when I tried to make a query with iBATIS, I got the following exception.
java.sql.SQLException: [Microsoft][ODBC Excel Driver]Optional feature not implemented
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6958)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
at sun.jdbc.odbc.JdbcOdbc.SQLSetConnectOption(JdbcOdbc.java:5115)
at sun.jdbc.odbc.JdbcOdbcConnection.setAutoCommit(JdbcOdbcConnection.java:651)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at com.ibatis.common.jdbc.SimpleDataSource$SimplePooledConnection.invoke(SimpleDataSource.java:946)
at $Proxy0.setAutoCommit(Unknown Source)
at com.ibatis.sqlmap.engine.transaction.jdbc.JdbcTransaction.init(JdbcTransaction.java:56)
at com.ibatis.sqlmap.engine.transaction.jdbc.JdbcTransaction.getConnection(JdbcTransaction.java:89)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList(GeneralStatement.java:119)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:610)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:584)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:105)
at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForList(SqlMapClientImpl.java:77)
at test.TestIBatis.testSelectExcel(TestIBatis.java:64)
at test.TestIBatis.main(TestIBatis.java:36)
It seems like the ODBC driver for excel doesn't support the setAutoCommint(). Is there anyway to get away with it? Could I just turn off the transaction manager for iBATIS?
Secondly, even though I get away with the first problem by changing the source code, my sql query cannot be parsed. This is my sql: Select * from [Sheet1$]
'$' is reserved word in SQL. I don't know if iBATIS can handle this kind of exceptional case.
Could somebody help to solve the problem? Thx a lot
Regards, Sancho Chiang
_________________________________________________________________
Don’t just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/
