ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kris Schneider" <kschnei...@gmail.com>
Subject Re: Does IBatis support VARRAY output parameters for store procedure call?
Date Thu, 25 May 2006 18:03:41 GMT
On 5/25/06, Jeff Butler <jeffgbutler@gmail.com> wrote:
>
> I'll preface this by saying that I'm not an Oracle person...
>
> All the examples I find for VARRAY in JDBC show that you need to use
> Oracle's proprietary API calls to make this work.  Obviously iBATIS does
> not, and will not, support Oracle's proprietary API.

For *creating* SQL arrays (e.g. to use as an input param), I believe
that's true. However, I'm pretty sure I've used iBATIS to retrieve SQL
arrays as out params. Try using ARRAY for the JDBC type. You should
end up with an instance of java.sql.Array.

> I think your best option to make this work is to implement a custom type
> handler, as shown in the manual or the WIKI.  I'm pretty sure you can make
> it work that way - unless someone else has a better idea?

Even if you can get it to work with ARRAY, you'll probably still want
to use a type handler to actually  get the array contents. You'll need
to use one of the getArray or getResultSet methods on java.sql.Array.

> Jeff Butler
>
>
>
> On 5/25/06, Christina Xu <christxu@yahoo.com> wrote:
> > Hi, Jeff
> >
> > I tried your suggestion, but I got the same error as:
> >
> > "register output parameters failed" ,
> > "Invalid column type".
> >
> > What I do wrong ? Any other options? Any documents or
> > examples that I can follow?
> >
> > Thanks,
> > Chris
> >
> >
> > --- Jeff Butler < jeffgbutler@gmail.com> wrote:
> >
> > > VARRAY is not a JDBC type - it is Oracle specific.
> > > You might get this to
> > > work by specifying the JDBC type of OTHER.  Then you
> > > can cast the returned
> > > object yourself after iBATIS returns it.
> > >
> > > Jeff Butler
> > >
> > >
> > > On 5/24/06, Christina Xu <christxu@yahoo.com> wrote:
> > > >
> > > > Hello All,
> > > >
> > > > I wonder iBatis supports the store procedure call
> > > > which has a out parameter of VARRAY. Please help.
> > > >
> > > > On store procedure side I have:
> > > >
> > > > create or replace TYPE myType is VARRAY(100) of
> > > > VARCHAR(2).
> > > >
> > > > create or replace procedure MyProcedure(inParam IN
> > > > DATE, outParam OUT myType) is .....
> > > >
> > > > On the sqlMap I have:
> > > > <sqlMap>
> > > > <resultMap id="procResultMap" class="MyResult">
> > > >    <result property="dataName" column="MY_NAME" />
> > > > </resultMap>
> > > > <parameterMap id="procMap" class="Map" >
> > > >    <parameter property="inParam" jdbcType="DATE"
> > > > javaType="java.sql.Date" mode="IN" >
> > > >    <parameter property="outParam"
> > > jdbcType="VARRAY"
> > > > mode="OUT" >
> > > > </parameterMap>
> > > > <procedure id="doMyProc" parameterMap="procMap"
> > > > resultMap="procResultMap">
> > > >     { call MyProcedure(?,?)}
> > > > </procedure>
> > > > </sqlMap>
> > > >
> > > >
> > > > At java code I have:
> > > >
> > > > ...
> > > > map = new HashMap();
> > > > map.put("inParam", new
> > > > java.sql.Date(System.currentTimeMillis()));
> > > > map.put("outParam",null);
> > > > sqlClient.queryForList("doMyProc", map);
> > > >
> > > > I got the error message "register output
> > > parameters
> > > > failed" , "Invalid column type".
> > > >
> > > >
> > > > Please help. Thanks a lot !
> > > >
> > > > Chris

-- 
Kris Schneider <mailto:kschneider@gmail.com>

Mime
View raw message