Randy, Oracle has a clever licencing options for developers; you can download their products and use for development for free. Things change a lot when you go to production but that's another story. you can download their products at http://www.oracle.com/technology/software/index.html Giovanni
On Mon, Oct 20, 2008 at 2:13 PM, Larry Meadors <[EMAIL PROTECTED]> wrote: > That's because Oracle's jdbc driver returns it as simply "id", not > "usr.id", and ibatis can't fix that. > > I believe that the MySQL driver's behavior in this case is > non-standard, but I could be wrong. > > Larry > > > PS: Sorry you're in this situation, I know how it feels - I built a > prototype system with MySQL and moved it to M$SQL one time (never > again). FYI, I think you can develop against Oracle for free (actually > most DBs have free versions), it's not terribly difficult to get > working, and in this case would have made life lots easier for you. > :-) > > > On Mon, Oct 20, 2008 at 4:50 AM, Baron, Randy <[EMAIL PROTECTED]> wrote: >> hi, >> We can't seem to get table aliases to work with iBatis when using >> Oracle. An application we developed worked fine with MySQL but when we >> finally switched to our Oracle database it failed with the error >> "Invalid column name", see output below. We've tried a bunch of things, >> went through ibatis source etc., over the past days but nothing has >> worked (except using column name aliases but we'd like to avoid that if >> possible since it's a bit messy). >> This mapping is the kind that does work with MySQL but not with >> Oracle: >> >> <resultMap id="UserResult" class="User"> >> <result property="id" column="usr.id"/> >> <result property="name" column="usr.name"/> >> </resultMap> >> >> <select id="retrieve-Users" resultMap="UserResult"> >> SELECT usr.id, usr.name FROM Users usr >> </select> >> >> This is a simple example where you could drop the "usr" alias but other >> queries really benefit from using an alias, like: >> >> "SELECT gen.name, gen.gene_comment, gen.theme, gen.user_id, >> gen.physical_location, gen.forward, usr.id, usr.name FROM genes gen >> INNER JOIN users usr ON usr.id = gen.user_id" >> >> ...particularly when columns from the different tables have the same >> (conflicting) name (e.g. gen.name & usr.name). >> >> Using the debugger, it seems the result set object doesn't contain the >> table alias, which looks similar to >> http://www.mail-archive.com/[email protected]/msg03755.html. >> However, the alias seems to be missing from the result set even when >> using MySQL and there the application runs without error. >> >> Any suggestions would be appreciated. >> >> Randy >> >> >> p.s. Attached (?) is a mini-application, minus the libraries, that >> demonstrates the problem; you need your own mysql & oracle to test it >> though. >> Libraries used are: >> ibatis-2.3.4.726.jar >> log4j-1.2.9.jar >> mysql-connector-java-5.1.6-bin.jar >> ojdbc14.jar (1,378,346 bytes, Oracle Database 10g 10.1.0.5 JDBC >> Driver) (I can send the libs on request if s.o. wants) >> >> Oracle db: Oracle9i Enterprise Edition Release 9.2.0.8.0 MySQL db: 5.1 >> >> Console output with error message: >> >> DEBUG - Created connection 19327071. >> DEBUG - {conn-100000} Connection >> DEBUG - {conn-100000} Preparing Statement: SELECT usr.id, >> usr.name FROM Users usr >> DEBUG - {pstm-100001} Executing Statement: SELECT usr.id, >> usr.name FROM Users usr >> DEBUG - {pstm-100001} Parameters: [] >> DEBUG - {pstm-100001} Types: [] >> DEBUG - {rset-100002} ResultSet >> DEBUG - Returned connection 19327071 to pool. >> com.ibatis.common.jdbc.exception.NestedSQLException: >> --- The error occurred in sqlmap/user.xml. >> --- The error occurred while applying a result map. >> --- Check the User.UserResult. >> --- Check the result mapping for the 'id' property. >> --- Cause: java.sql.SQLException: Invalid column name >> at >> com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryW >> ithCallback(MappedStatement.java:201) >> at >> com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryF >> orList(MappedStatement.java:139) >> at >> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMap >> ExecutorDelegate.java:567) >> at >> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMap >> ExecutorDelegate.java:541) >> at >> com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessi >> onImpl.java:118) >> at >> com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessi >> onImpl.java:122) >> at >> com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForList(SqlMapClient >> Impl.java:98) >> at com.roche.dev.testiBatis.main(testiBatis.java:58) >> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) >> at >> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.jav >> a:39) >> at >> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessor >> Impl.java:25) >> at java.lang.reflect.Method.invoke(Method.java:597) >> at >> com.intellij.rt.execution.application.AppMain.main(AppMain.java:90) >> Caused by: java.sql.SQLException: Invalid column name >> at >> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:12 >> 5) >> at >> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:16 >> 2) >> at >> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:22 >> 7) >> at >> oracle.jdbc.driver.OracleStatement.get_column_index(OracleStatement.java >> :3099) >> at >> oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.ja >> va:1854) >> at >> oracle.jdbc.driver.OracleResultSet.getInt(OracleResultSet.java:1605) >> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) >> at >> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.jav >> a:39) >> at >> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessor >> Impl.java:25) >> at java.lang.reflect.Method.invoke(Method.java:597) >> at >> com.ibatis.common.jdbc.logging.ResultSetLogProxy.invoke(ResultSetLogProx >> y.java:47) >> at $Proxy2.getInt(Unknown Source) >> at >> com.ibatis.sqlmap.engine.type.IntegerTypeHandler.getResult(IntegerTypeHa >> ndler.java:35) >> at >> com.ibatis.sqlmap.engine.mapping.result.ResultMap.getPrimitiveResultMapp >> ingValue(ResultMap.java:619) >> at >> com.ibatis.sqlmap.engine.mapping.result.ResultMap.getResults(ResultMap.j >> ava:345) >> at >> com.ibatis.sqlmap.engine.execution.SqlExecutor.handleResults(SqlExecutor >> .java:384) >> at >> com.ibatis.sqlmap.engine.execution.SqlExecutor.handleMultipleResults(Sql >> Executor.java:300) >> at >> com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor. >> java:189) >> at >> com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteQue >> ry(MappedStatement.java:221) >> at >> com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryW >> ithCallback(MappedStatement.java:189) >> ... 12 more >> Exception in thread "main" java.lang.RuntimeException: Error during >> retrieveing the user list. >> Cause:com.ibatis.common.jdbc.exception.NestedSQLException: >> --- The error occurred in sqlmap/user.xml. >> --- The error occurred while applying a result map. >> --- Check the User.UserResult. >> --- Check the result mapping for the 'id' property. >> --- Cause: java.sql.SQLException: Invalid column name >> at com.roche.dev.testiBatis.main(testiBatis.java:61) >> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) >> at >> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.jav >> a:39) >> at >> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessor >> Impl.java:25) >> at java.lang.reflect.Method.invoke(Method.java:597) >> at >> com.intellij.rt.execution.application.AppMain.main(AppMain.java:90) >> >> Process finished with exit code 1 >> >> >> > -- -------------------------------------------------------------------- "You don't know the power of dark side" - Darth Vader
