You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user-java@ibatis.apache.org by harinder nandyala <ha...@hotmail.com> on 2006/02/20 22:04:50 UTC

Question.....

I am using WSAD to evaluate on a requirement with DB2 and Websphere 
application server. Started with 'petstore' sample downloaded from ibatis 
web site. Here are the issues noticed in using this sample, import war file, 
in this environment as is without making any changes:

When a new user id registered a error was reported -->SQLSTATE=S1003--> 
Program type out of range. On debugging I found that field 'Status' which 
can be nullable in db2 TABLE is the one creating problem. When ibatis tries 
to insert a record with 'status' value as 'null' as this is not a user entry 
field, ibatis tries to set jdbcType as 0 (zero0 which is not acceptable with 
prepared statement when used with DB2 driver. So I have updaed Account.xml 
file with a 'parameterMap' where I declared this field. Then there was no 
problem. So, my questions is how do I make it work without updating *.xml 
file and use simply java bean (ibatis may use reflection mechanism to get 
types and valeus from java bean)? Please note that there is no problem if I 
use hsqldb (in memory DB).

Any help is appreciated. Thank you,


"The more you strive for humanity, the more civilized you become" -ASHA

Harinder Nandyala
http://www.ashakiran.org

Re: Similar problem with the same set up....may be there is some solution already..

Posted by "Albert L. Sapp" <as...@uiuc.edu>.

Jeff Butler wrote:

> 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
>
>
>  
> On 2/20/06, *harinder nandyala* <harindern@hotmail.com 
> <ma...@hotmail.com>> wrote:
>
>     Hello,
>
>     Many thanks for prompt response. That was an easy solution and it
>     worked. I
>     am seeing one more problem with the same setup ( i.e. DB2 driver
>     type-2).
>     When I try to add one order from petstore sample:
>
>     *****************************
>     "A reference to column "ITEMID" is ambigous. SQLSTATE=42702"
>     The error occured in com/ibatis/jpetstore/sqlmapdao/sql/Item.xml
>     Check the getItem - InlineParameterMap
>     Check the statemnent (Query Failed"
>     **********************************
>
>     Probably you may know the answer. I am trying to debug, but if
>     there is
>     ready made answer it saves a lot.
>
>     "The more you strive for humanity, the more civilized you become"
>     -ASHA
>
>     Harinder Nandyala
>     http://www.ashakiran.org
>
>
>
This same thing happened when run against a Oracle database.  Doing what 
Jeff suggests fixed it.

Al

Re: Similar problem with the same set up....may be there is some solution already..

Posted by Jeff Butler <je...@gmail.com>.

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

On 2/20/06, harinder nandyala <ha...@hotmail.com> wrote:
>
> Hello,
>
> Many thanks for prompt response. That was an easy solution and it worked.
> I
> am seeing one more problem with the same setup (i.e. DB2 driver type-2).
> When I try to add one order from petstore sample:
>
> *****************************
> "A reference to column "ITEMID" is ambigous. SQLSTATE=42702"
> The error occured in com/ibatis/jpetstore/sqlmapdao/sql/Item.xml
> Check the getItem - InlineParameterMap
> Check the statemnent (Query Failed"
> **********************************
>
> Probably you may know the answer. I am trying to debug, but if there is
> ready made answer it saves a lot.
>
> "The more you strive for humanity, the more civilized you become" -ASHA
>
> Harinder Nandyala
> http://www.ashakiran.org
>
>
>

Re: Question.....

Posted by Jeff Butler <je...@gmail.com>.

You can also use an inline parameter map to specify the field type (I find
this more convenient than using a seperate parameter map). For example:

<insert id="insertAccount">
  insert into account (..., status, ...) values (..., #status:VARCHAR#, ...)
</insert>

This is needed in DB2 whenever a field can be nullable.  Note that this is
true with the type 2 driver for certain.  The type 4 driver may be
different, but I doubt it.

So the simple answer is that you'll need to modify the xml supplied with
JPetStore either way - either with a parameterMap or specifying the inline
parameter map.  You're still using a Java bean, just supplying a little more
information in the SQL map.

Jeff Butler


On 2/20/06, harinder nandyala <harindern@hotmail.com > wrote:
>
>
> I am using WSAD to evaluate on a requirement with DB2 and Websphere
> application server. Started with 'petstore' sample downloaded from ibatis
> web site. Here are the issues noticed in using this sample, import war
> file,
> in this environment as is without making any changes:
>
> When a new user id registered a error was reported -->SQLSTATE=S1003-->
> Program type out of range. On debugging I found that field 'Status' which
> can be nullable in db2 TABLE is the one creating problem. When ibatis
> tries
> to insert a record with 'status' value as 'null' as this is not a user
> entry
> field, ibatis tries to set jdbcType as 0 (zero0 which is not acceptable
> with
> prepared statement when used with DB2 driver. So I have updaed Account.xml
> file with a 'parameterMap' where I declared this field. Then there was no
> problem. So, my questions is how do I make it work without updating *.xml
> file and use simply java bean (ibatis may use reflection mechanism to get
> types and valeus from java bean)? Please note that there is no problem if
> I
> use hsqldb (in memory DB).
>
> Any help is appreciated. Thank you,
>
>
> "The more you strive for humanity, the more civilized you become" -ASHA
>
> Harinder Nandyala
> http://www.ashakiran.org
>
>
>