Select from View (as opposed to table) using iBatis

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Select from View (as opposed to table) using iBatis

mule_user
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?
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Select from View (as opposed to table) using iBatis

Jeff Butler-2
Please join us at the new project site: www.mybatis.org

Select from view is support.  The error message is "Undefined Name".
DB2 cannot find the view called MY_SCHEMA.V_MY_VIEW.

Jeff Butler


On Wed, Jun 9, 2010 at 4:11 PM, mule_user <[hidden email]> wrote:

>
> 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?
> --
> View this message in context: http://old.nabble.com/Select-from-View-%28as-opposed-to-table%29-using-iBatis-tp28835900p28835900.html
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Select from View (as opposed to table) using iBatis

mule_user
In reply to this post by mule_user
The View exists in DB2, but no table exists with that name. Yes, 42704 is throws if table is not found.

I can run the SQL generated SQL by iBatis (that is spit out in Console) in DB2 control center and get successful results.

Yes, while table does not exist, the view exist.  The error code of 42704 is thrown if table is not found. The goal is to run the query against the View (which exists for the schema), not the table.


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Select from View (as opposed to table) using iBatis

Larry Meadors
Are you connecting as the same use that your code is connecting as?

The ibatis project has moved and been renamed.

It is no longer being maintained as an Apache project, but has moved
(along with the development team) here:

http://www.mybatis.org/

Please join us at the new location by joining the mailing list here:

http://groups.google.com/group/mybatis-user

Larry

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Loading...