ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Baron, Randy" <randy.ba...@roche.com>
Subject table alias works in MySQL but not Oracle gives "Invalid column name"
Date Mon, 20 Oct 2008 10:50:41 GMT
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