Query from View using iBatis

[Sam Gehouse]

I am using iBatis (2.5) with DB2 version 8.x and Spring 2.5. Is there any issue in querying a view (as opposed to table)? It is a very simple select as:

    
<resultMap id="codeMap" class="org.impl.CodeBean">
        <result column="TBL_NM"      property="tableName"   typeHandler="org.our.StringTrimTypeHandler"/>
        <result column="COL_NM"     property="columnName" typeHandler="org.our.StringTrimTypeHandler"/>
        <result column="CD"            property="code"        typeHandler="org.our.StringTrimTypeHandler"/>  
        <result column="CD_DESC"   property="shortDesc"  typeHandler="org.our.StringTrimTypeHandler"/>  
        <result column="CD_LNG_DESC" property="longDesc"   typeHandler="org.our.StringTrimTypeHandler"/>
</resultMap>

<select id="findAll" resultMap="codeMap">
        select CD from ${jdbc.schema}.V_MY_VIEW
</select>

org.impl.CodeBean is a simple POJO with String attributes only with public getter and setter. StringTrimTypeHandler are custom code written.

My SQL are working when I am using table, as opposed to View.

When I call the above findAll from JUnit for View, I get exception as:

  
--    Exception:  org.springframework.jdbc.BadSqlGrammarException: SQL MAPPING ID: code.findAll; bad SQL grammar []; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:   

--- The error occurred in org/.../mapping/code.xml.  

--- The error occurred while applying a parameter map.  

--- Check the code.findAll-InlineParameterMap.  

--- Check the statement (query failed).  

--- Cause: com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -204, SQLSTATE: 42704, SQLERRMC: MY_SCHEMA.V_MY_VIEW

--    Exception Detail:  com.ibatis.common.jdbc.exception.NestedSQLException:   

--- The error occurred in org/.../mapping/code.xml.  

I must be missing something.

Query from View is supported in iBatis, correct?

[Sam Gehouse]

The view exists in DB2 for that schema. I can successfully run the generated SQL (that is generated by iBatuis by querying the View) in DB2 and get correct result.

While there is no table with the name V_MY_VIEW, there is view with the name V_MY_VIEW. The error code 42704 is thrown if table is not found. But the goal here is to run against the View and not the table.

I am not clear why 42704 error is thrown while I was primarily querying the view.