ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Larry Meadors" <larry.mead...@gmail.com>
Subject Re: table alias works in MySQL but not Oracle gives "Invalid column name"
Date Mon, 20 Oct 2008 12:13:16 GMT
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 <randy.baron@roche.com> 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/user-java@ibatis.apache.org/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
>
>
>

Mime
View raw message