ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Brandon Goodin" <brandon.goo...@gmail.com>
Subject Re: Collection as argument to stored procedure
Date Sun, 26 Aug 2007 21:47:28 GMT
Since iBATIS generates plain ol SQL you have to get iBATIS to mimic the way
in which you would call your procedure without iBATIS. Your sqlmap appears
to have an error. You are not using the pound '#' delimiter correctly and
your call is not wrapped in squiggly brackets '{'. Additionally, you would
need to use inline parameter maps. I don't think it would be possible to use
a static parameter map with the iterate since you only have 2 paramters
configured and you are trying to pass in more than two parameters. If the
below (see SHOULD BE) inline mapping does not work, the other way to handle
this would be to iterate your list in your DAO and pass each item in the
list as a parameter to your sql map call. If you need transactional safety
then you can wrap your DAO method call in a transaction. If none of those
suggestions are reasonable then you may need to go to straight JDBC in order
to accomplish your ends. I would recommend that you read about using the SQL
Map Developer Guide (
http://ibatis.apache.org/docs/java/pdf/iBATIS-SqlMaps-2_en.pdf). You can
search the document for "procedure". You should find something that will
help you there.

In summary:
1) Write out how the SQL call should look and get iBATIS to produce that
SQL.
2) Fix the '#' delimiter usage
3) Add the '{' squiggly brackets around your procedure call
4) Use inline parameter maps
5) If all else fails iterate in your DAO within a transaction
6) If that is unacceptable use straight JDBC

CURRENTLY IS:
    <procedure id="iterate" parameterMap="input">
      call iterate(
            <iterate property="inputList" open="(" close=")"
               conjunction="OR">
                #inputList[].id, #inputList[].name
            </iterate>
            )
    </procedure>

SHOULD BE (maybe):

    <procedure id="iterate">
      {call iterate(
            <iterate property="inputList" open="(" close=")"
               conjunction="OR">
                #inputList[].id,jdbcType=INTEGER,mode=IN#,
#inputList[].name,jdbcType=VARCHAR,mode=IN#
            </iterate>
            )}
    </procedure>

If you need additional help then it would be good to post the SQL you are
wanting iBATIS to produce. We may be able to help you further.

Brandon

On 8/23/07, bharathi.mani@accenture.com <bharathi.mani@accenture.com> wrote:
>
>
>
> <parameterMap id="input" class="emp">
>
>         <parameter property="id" jdbcType="NUMBER"
> javaType="java.lang.Integer" mode="IN" />
>
>                 <parameter property="name" jdbcType="VARCHAR"
> javaType="java.lang.String" mode="IN" />
>
>   </parameterMap>
>
>     <procedure id="iterate" parameterMap="input">
>       call iterate(
>             <iterate property="inputList" open="(" close=")"
>                conjunction="OR">
>                 #inputList[].id, #inputList[].name
>             </iterate>
>             )
>     </procedure>



Can you please tell me the correct way to use iterate tag? Have tried
> this.... (My procedure accepts a collection(inputList) of emp BOs) But
> it doesn't work. Any help would be great.
>
> Thanks
> Bharathi
>
>
>
> -----Original Message-----
> From: larry.meadors@gmail.com [mailto:larry.meadors@gmail.com] On Behalf
> Of Larry Meadors
> Sent: Wednesday, August 22, 2007 7:53 PM
> To: user-java@ibatis.apache.org
> Subject: Re: Collection as argument to stored procedure
>
> Not really, no - Java collections are not JDBC types, so you have to
> do some of the leg work.
>
> Look at batching and the iterate tag, they'll help you get this going.
>
> Larry
>
>
> On 8/22/07, bharathi.mani@accenture.com <bharathi.mani@accenture.com>
> wrote:
> >
> > Hi,
> >
> > Is it possible to pass a Collection of business Objects to stored
> procedure
> > through iBatis? How do we do the mapping in iBatis xml file?
> >
> > Can anyone please help me in this?
>
>
> This message is for the designated recipient only and may contain
> privileged, proprietary, or otherwise private information.  If you have
> received it in error, please notify the sender immediately and delete the
> original.  Any other use of the email by you is prohibited.
>

Mime
View raw message