jpetstore-iBatis and db2 compatibility
--------------------------------------
Key: IBATIS-262
URL: http://issues.apache.org/jira/browse/IBATIS-262
Project: iBatis for Java
Type: Improvement
Components: SQL Maps
Versions: 2.1.7
Environment: DB2 Environment
Reporter: Harinder Nandyala
Priority: Minor
SQL Map which needs a fix:
<select id="getItem" resultClass="item" parameterClass="string"
cacheModel="quantityCache">
select
ITEMID,
LISTPRICE,
UNITCOST,
SUPPLIER AS supplierId,
I.PRODUCTID AS "product.productId",
NAME AS "product.name",
DESCN AS "product.description",
CATEGORY AS "product.categoryId",
STATUS,
ATTR1 AS attribute1,
ATTR2 AS attribute2,
ATTR3 AS attribute3,
ATTR4 AS attribute4,
ATTR5 AS attribute5,
QTY AS quantity
from ITEM I, INVENTORY V, PRODUCT P
where P.PRODUCTID = I.PRODUCTID
and I.ITEMID = V.ITEMID
and I.ITEMID = #value#
</select>
****************************************************************************
Error reported when tested from WSAD, iBatis and DB2:
Caused by: com.ibatis.dao.client.DaoException: Failed to execute queryForObject
- id [getItem], parameterObject [EST-6]. Cause:
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in
com/ibatis/jpetstore/persistence/sqlmapdao/sql/Item.xml.
--- The error occurred while applying a parameter map.
--- Check the getItem-InlineParameterMap.
--- Check the statement (query failed).
--- Cause: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0203N A
reference to column "ITEMID" is ambiguous. SQLSTATE=42702
************************************************Quote from Jeff's
mail***********************
This error means that a column in the select list is in two or more of the
joined tables. You must qualify the column name with the table (or alias) in
the select list.
It looks to me like the "getItem" query is probably the offending one. Simple
solution - put "i." in front of the itemid like this:
SELECT
I.ITEMID
...
There may be more of these, you'll have to work them through. I would consider
this a bug in JPetStore (hsqldb must be very forgiving - my guess is that this
would fail on most databases). It would be good of you to open a JIRA ticket
with the details so we don't forget to make a change.
Jeff Butler
************************************************
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
