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 Christina Xu <ch...@yahoo.com> on 2006/05/24 14:06:23 UTC
Does IBatis support VARRAY output parameters for store procedure call?
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
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Re: Does IBatis support VARRAY output parameters for store procedure call?
Posted by Kris Schneider <ks...@gmail.com>.
On 5/25/06, Jeff Butler <je...@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 <ch...@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 <ch...@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 <ma...@gmail.com>
Re: Does IBatis support VARRAY output parameters for store procedure call?
Posted by Jeff Butler <je...@gmail.com>.
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.
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?
Jeff Butler
On 5/25/06, Christina Xu <ch...@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 <je...@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 <ch...@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
> > >
> > > __________________________________________________
> > > Do You Yahoo!?
> > > Tired of spam? Yahoo! Mail has the best spam
> > protection around
> > > http://mail.yahoo.com
> > >
> >
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
Re: Does IBatis support VARRAY output parameters for store procedure call?
Posted by Christina Xu <ch...@yahoo.com>.
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 <je...@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 <ch...@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
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Tired of spam? Yahoo! Mail has the best spam
> protection around
> > http://mail.yahoo.com
> >
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Re: Does IBatis support VARRAY output parameters for store procedure call?
Posted by Jeff Butler <je...@gmail.com>.
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 <ch...@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
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>