ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Giovanni Cuccu" <giovanni.cu...@gmail.com>
Subject Re: table alias works in MySQL but not Oracle gives "Invalid column name"
Date Mon, 20 Oct 2008 13:37:38 GMT
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 <larry.meadors@gmail.com> 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 <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
>>
>>
>>
>



-- 
--------------------------------------------------------------------
"You don't know the power of dark side" - Darth Vader

Mime
View raw message