You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ibatis.apache.org by Giovanni Cuccu <gi...@gmail.com> on 2007/11/14 15:12:14 UTC
Mapping oracle data types
Hi all,
I'm currently using ibatis 2.3 with Oracle and I'm trying to
mapping some oracle datatypes. For now I'm focusing on VARRAY and with
the current version of IBatis I'm able to map a String[] to a VARRAY
type (where the basic type is varchar2) as an input param to a stored
procedure.
What I'm not able to do is to create a mapping for an output parameter.
The case I'm currently investigating is a pl\SQL function that returns a varray.
the plain jdbc code is the following (take it a first experiment)
public class TestOracleArray {
/**
* @param args
*/
public static void main(String[] args) throws Exception {
OracleDataSource ods = new OracleDataSource();
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
ods.setURL(url);
ods.setUser("test");
ods.setPassword("test");
Connection conn = ods.getConnection();
ArrayDescriptor desc = ArrayDescriptor.createDescriptor("VARRAY_TEST", conn);
CallableStatement csOut= conn.prepareCall("{? = call
test_ibatis_varray_fuc}");
String[] elementsOUT=new String[20];
ARRAY arrayParamOut = new ARRAY (desc, conn, elementsOUT);
OracleCallableStatement ocs = (OracleCallableStatement)csOut;
ocs.registerOutParameter(1, java.sql.Types.ARRAY,"VARRAY_TEST");
csOut.execute();
arrayParamOut=(ARRAY)csOut.getArray(1);
elementsOUT=(String[])arrayParamOut.getArray();
for (int i=0;i<elementsOUT.length;i++) {
System.out.println(elementsOUT[i]);
}
conn.commit();
}
the previous code works, my guess is that iBatis does not support this
kind of mapping mainly because of this line:
ocs.registerOutParameter(1, java.sql.Types.ARRAY,"VARRAY_TEST");
there is not callback function in typehandler able to support the
registration of a parameter.
My proposal is to create a new interface tht extends TypeHandler and
allows for custom parameter registration; and modify the code of
SqlExecutor namley the method
private void registerOutputParameters(CallableStatement cs,
ParameterMapping[] mappings) throws SQLException {
in order to accomodate the new feature.
the new interface should preserve the current typeandler
implementations and it will allow a new feature which is not bounded
to a specific database (since custom parameter registration can be
viewed as a generic JDBC concept).
does anyone agree with my proposal?
If yes can i open a Jira issue and try to submit some code?
Thanks in advance,
Giovanni
--
--------------------------------------------------------------------
"You don't know the power of dark side" - Darth Vader
Re: Mapping oracle data types
Posted by Jeff Butler <je...@gmail.com>.
Giovanni - feel free to create a patch and attach it to a JIRA issue. Make
sure to include some tests!
Jeff Butler
On Nov 29, 2007 8:26 AM, Giovanni Cuccu <gi...@gmail.com> wrote:
> The type handler, in its current form, it's not enough to map out
> parameters since it does not allow to register out parameters before
> issuing the call.
> I've sucessufully create a typehandler that manages IN varray and, as
> far as I can understand, ibatis is not able to support such Oracle
> kind of types when they are OUT ones.
> That's why I proposed an enhancement.
> Giovanni
>
>
> On Nov 29, 2007 3:17 PM, Larry Meadors <lm...@apache.org> wrote:
> > I'd try a type handler.
> >
> > Larry
> >
> >
> >
> > On Nov 29, 2007 5:20 AM, Giovanni Cuccu <gi...@gmail.com>
> wrote:
> > > Any comments on this?
> > > Thanks,
> > > Giovanni
> > >
> > >
> > > On Nov 14, 2007 3:12 PM, Giovanni Cuccu <gi...@gmail.com>
> wrote:
> > > > Hi all,
> > > > I'm currently using ibatis 2.3 with Oracle and I'm trying to
> > > > mapping some oracle datatypes. For now I'm focusing on VARRAY and
> with
> > > > the current version of IBatis I'm able to map a String[] to a VARRAY
> > > > type (where the basic type is varchar2) as an input param to a
> stored
> > > > procedure.
> > > > What I'm not able to do is to create a mapping for an output
> parameter.
> > > > The case I'm currently investigating is a pl\SQL function that
> returns a varray.
> > > > the plain jdbc code is the following (take it a first experiment)
> > > > public class TestOracleArray {
> > > >
> > > > /**
> > > > * @param args
> > > > */
> > > > public static void main(String[] args) throws Exception {
> > > > OracleDataSource ods = new OracleDataSource();
> > > > String url = "jdbc:oracle:thin:@localhost
> :1521:orcl";
> > > > ods.setURL(url);
> > > > ods.setUser("test");
> > > > ods.setPassword("test");
> > > > Connection conn = ods.getConnection();
> > > > ArrayDescriptor desc =
> ArrayDescriptor.createDescriptor("VARRAY_TEST", conn);
> > > > CallableStatement csOut= conn.prepareCall("{? = call
> > > > test_ibatis_varray_fuc}");
> > > > String[] elementsOUT=new String[20];
> > > > ARRAY arrayParamOut = new ARRAY (desc, conn,
> elementsOUT);
> > > > OracleCallableStatement ocs =
> (OracleCallableStatement)csOut;
> > > > ocs.registerOutParameter(1, java.sql.Types.ARRAY
> ,"VARRAY_TEST");
> > > > csOut.execute();
> > > > arrayParamOut=(ARRAY)csOut.getArray(1);
> > > > elementsOUT=(String[])arrayParamOut.getArray();
> > > > for (int i=0;i<elementsOUT.length;i++) {
> > > > System.out.println(elementsOUT[i]);
> > > > }
> > > > conn.commit();
> > > >
> > > > }
> > > >
> > > > the previous code works, my guess is that iBatis does not support
> this
> > > > kind of mapping mainly because of this line:
> > > > ocs.registerOutParameter(1, java.sql.Types.ARRAY,"VARRAY_TEST");
> > > >
> > > > there is not callback function in typehandler able to support the
> > > > registration of a parameter.
> > > > My proposal is to create a new interface tht extends TypeHandler and
> > > > allows for custom parameter registration; and modify the code of
> > > > SqlExecutor namley the method
> > > > private void registerOutputParameters(CallableStatement cs,
> > > > ParameterMapping[] mappings) throws SQLException {
> > > > in order to accomodate the new feature.
> > > > the new interface should preserve the current typeandler
> > > > implementations and it will allow a new feature which is not bounded
> > > > to a specific database (since custom parameter registration can be
> > > > viewed as a generic JDBC concept).
> > > > does anyone agree with my proposal?
> > > > If yes can i open a Jira issue and try to submit some code?
> > > > Thanks in advance,
> > > > Giovanni
> > > >
> > > >
> > > > --
> > > > --------------------------------------------------------------------
> > > > "You don't know the power of dark side" - Darth Vader
> > > >
> > >
> > >
> > >
> > > --
> > > --------------------------------------------------------------------
> > > "You don't know the power of dark side" - Darth Vader
> > >
> >
>
>
>
> --
> --------------------------------------------------------------------
> "You don't know the power of dark side" - Darth Vader
>
Re: Mapping oracle data types
Posted by Giovanni Cuccu <gi...@gmail.com>.
The type handler, in its current form, it's not enough to map out
parameters since it does not allow to register out parameters before
issuing the call.
I've sucessufully create a typehandler that manages IN varray and, as
far as I can understand, ibatis is not able to support such Oracle
kind of types when they are OUT ones.
That's why I proposed an enhancement.
Giovanni
On Nov 29, 2007 3:17 PM, Larry Meadors <lm...@apache.org> wrote:
> I'd try a type handler.
>
> Larry
>
>
>
> On Nov 29, 2007 5:20 AM, Giovanni Cuccu <gi...@gmail.com> wrote:
> > Any comments on this?
> > Thanks,
> > Giovanni
> >
> >
> > On Nov 14, 2007 3:12 PM, Giovanni Cuccu <gi...@gmail.com> wrote:
> > > Hi all,
> > > I'm currently using ibatis 2.3 with Oracle and I'm trying to
> > > mapping some oracle datatypes. For now I'm focusing on VARRAY and with
> > > the current version of IBatis I'm able to map a String[] to a VARRAY
> > > type (where the basic type is varchar2) as an input param to a stored
> > > procedure.
> > > What I'm not able to do is to create a mapping for an output parameter.
> > > The case I'm currently investigating is a pl\SQL function that returns a varray.
> > > the plain jdbc code is the following (take it a first experiment)
> > > public class TestOracleArray {
> > >
> > > /**
> > > * @param args
> > > */
> > > public static void main(String[] args) throws Exception {
> > > OracleDataSource ods = new OracleDataSource();
> > > String url = "jdbc:oracle:thin:@localhost:1521:orcl";
> > > ods.setURL(url);
> > > ods.setUser("test");
> > > ods.setPassword("test");
> > > Connection conn = ods.getConnection();
> > > ArrayDescriptor desc = ArrayDescriptor.createDescriptor("VARRAY_TEST", conn);
> > > CallableStatement csOut= conn.prepareCall("{? = call
> > > test_ibatis_varray_fuc}");
> > > String[] elementsOUT=new String[20];
> > > ARRAY arrayParamOut = new ARRAY (desc, conn, elementsOUT);
> > > OracleCallableStatement ocs = (OracleCallableStatement)csOut;
> > > ocs.registerOutParameter(1, java.sql.Types.ARRAY,"VARRAY_TEST");
> > > csOut.execute();
> > > arrayParamOut=(ARRAY)csOut.getArray(1);
> > > elementsOUT=(String[])arrayParamOut.getArray();
> > > for (int i=0;i<elementsOUT.length;i++) {
> > > System.out.println(elementsOUT[i]);
> > > }
> > > conn.commit();
> > >
> > > }
> > >
> > > the previous code works, my guess is that iBatis does not support this
> > > kind of mapping mainly because of this line:
> > > ocs.registerOutParameter(1, java.sql.Types.ARRAY,"VARRAY_TEST");
> > >
> > > there is not callback function in typehandler able to support the
> > > registration of a parameter.
> > > My proposal is to create a new interface tht extends TypeHandler and
> > > allows for custom parameter registration; and modify the code of
> > > SqlExecutor namley the method
> > > private void registerOutputParameters(CallableStatement cs,
> > > ParameterMapping[] mappings) throws SQLException {
> > > in order to accomodate the new feature.
> > > the new interface should preserve the current typeandler
> > > implementations and it will allow a new feature which is not bounded
> > > to a specific database (since custom parameter registration can be
> > > viewed as a generic JDBC concept).
> > > does anyone agree with my proposal?
> > > If yes can i open a Jira issue and try to submit some code?
> > > Thanks in advance,
> > > Giovanni
> > >
> > >
> > > --
> > > --------------------------------------------------------------------
> > > "You don't know the power of dark side" - Darth Vader
> > >
> >
> >
> >
> > --
> > --------------------------------------------------------------------
> > "You don't know the power of dark side" - Darth Vader
> >
>
--
--------------------------------------------------------------------
"You don't know the power of dark side" - Darth Vader
Re: Mapping oracle data types
Posted by Larry Meadors <lm...@apache.org>.
I'd try a type handler.
Larry
On Nov 29, 2007 5:20 AM, Giovanni Cuccu <gi...@gmail.com> wrote:
> Any comments on this?
> Thanks,
> Giovanni
>
>
> On Nov 14, 2007 3:12 PM, Giovanni Cuccu <gi...@gmail.com> wrote:
> > Hi all,
> > I'm currently using ibatis 2.3 with Oracle and I'm trying to
> > mapping some oracle datatypes. For now I'm focusing on VARRAY and with
> > the current version of IBatis I'm able to map a String[] to a VARRAY
> > type (where the basic type is varchar2) as an input param to a stored
> > procedure.
> > What I'm not able to do is to create a mapping for an output parameter.
> > The case I'm currently investigating is a pl\SQL function that returns a varray.
> > the plain jdbc code is the following (take it a first experiment)
> > public class TestOracleArray {
> >
> > /**
> > * @param args
> > */
> > public static void main(String[] args) throws Exception {
> > OracleDataSource ods = new OracleDataSource();
> > String url = "jdbc:oracle:thin:@localhost:1521:orcl";
> > ods.setURL(url);
> > ods.setUser("test");
> > ods.setPassword("test");
> > Connection conn = ods.getConnection();
> > ArrayDescriptor desc = ArrayDescriptor.createDescriptor("VARRAY_TEST", conn);
> > CallableStatement csOut= conn.prepareCall("{? = call
> > test_ibatis_varray_fuc}");
> > String[] elementsOUT=new String[20];
> > ARRAY arrayParamOut = new ARRAY (desc, conn, elementsOUT);
> > OracleCallableStatement ocs = (OracleCallableStatement)csOut;
> > ocs.registerOutParameter(1, java.sql.Types.ARRAY,"VARRAY_TEST");
> > csOut.execute();
> > arrayParamOut=(ARRAY)csOut.getArray(1);
> > elementsOUT=(String[])arrayParamOut.getArray();
> > for (int i=0;i<elementsOUT.length;i++) {
> > System.out.println(elementsOUT[i]);
> > }
> > conn.commit();
> >
> > }
> >
> > the previous code works, my guess is that iBatis does not support this
> > kind of mapping mainly because of this line:
> > ocs.registerOutParameter(1, java.sql.Types.ARRAY,"VARRAY_TEST");
> >
> > there is not callback function in typehandler able to support the
> > registration of a parameter.
> > My proposal is to create a new interface tht extends TypeHandler and
> > allows for custom parameter registration; and modify the code of
> > SqlExecutor namley the method
> > private void registerOutputParameters(CallableStatement cs,
> > ParameterMapping[] mappings) throws SQLException {
> > in order to accomodate the new feature.
> > the new interface should preserve the current typeandler
> > implementations and it will allow a new feature which is not bounded
> > to a specific database (since custom parameter registration can be
> > viewed as a generic JDBC concept).
> > does anyone agree with my proposal?
> > If yes can i open a Jira issue and try to submit some code?
> > Thanks in advance,
> > Giovanni
> >
> >
> > --
> > --------------------------------------------------------------------
> > "You don't know the power of dark side" - Darth Vader
> >
>
>
>
> --
> --------------------------------------------------------------------
> "You don't know the power of dark side" - Darth Vader
>
Re: Mapping oracle data types
Posted by Giovanni Cuccu <gi...@gmail.com>.
Any comments on this?
Thanks,
Giovanni
On Nov 14, 2007 3:12 PM, Giovanni Cuccu <gi...@gmail.com> wrote:
> Hi all,
> I'm currently using ibatis 2.3 with Oracle and I'm trying to
> mapping some oracle datatypes. For now I'm focusing on VARRAY and with
> the current version of IBatis I'm able to map a String[] to a VARRAY
> type (where the basic type is varchar2) as an input param to a stored
> procedure.
> What I'm not able to do is to create a mapping for an output parameter.
> The case I'm currently investigating is a pl\SQL function that returns a varray.
> the plain jdbc code is the following (take it a first experiment)
> public class TestOracleArray {
>
> /**
> * @param args
> */
> public static void main(String[] args) throws Exception {
> OracleDataSource ods = new OracleDataSource();
> String url = "jdbc:oracle:thin:@localhost:1521:orcl";
> ods.setURL(url);
> ods.setUser("test");
> ods.setPassword("test");
> Connection conn = ods.getConnection();
> ArrayDescriptor desc = ArrayDescriptor.createDescriptor("VARRAY_TEST", conn);
> CallableStatement csOut= conn.prepareCall("{? = call
> test_ibatis_varray_fuc}");
> String[] elementsOUT=new String[20];
> ARRAY arrayParamOut = new ARRAY (desc, conn, elementsOUT);
> OracleCallableStatement ocs = (OracleCallableStatement)csOut;
> ocs.registerOutParameter(1, java.sql.Types.ARRAY,"VARRAY_TEST");
> csOut.execute();
> arrayParamOut=(ARRAY)csOut.getArray(1);
> elementsOUT=(String[])arrayParamOut.getArray();
> for (int i=0;i<elementsOUT.length;i++) {
> System.out.println(elementsOUT[i]);
> }
> conn.commit();
>
> }
>
> the previous code works, my guess is that iBatis does not support this
> kind of mapping mainly because of this line:
> ocs.registerOutParameter(1, java.sql.Types.ARRAY,"VARRAY_TEST");
>
> there is not callback function in typehandler able to support the
> registration of a parameter.
> My proposal is to create a new interface tht extends TypeHandler and
> allows for custom parameter registration; and modify the code of
> SqlExecutor namley the method
> private void registerOutputParameters(CallableStatement cs,
> ParameterMapping[] mappings) throws SQLException {
> in order to accomodate the new feature.
> the new interface should preserve the current typeandler
> implementations and it will allow a new feature which is not bounded
> to a specific database (since custom parameter registration can be
> viewed as a generic JDBC concept).
> does anyone agree with my proposal?
> If yes can i open a Jira issue and try to submit some code?
> Thanks in advance,
> Giovanni
>
>
> --
> --------------------------------------------------------------------
> "You don't know the power of dark side" - Darth Vader
>
--
--------------------------------------------------------------------
"You don't know the power of dark side" - Darth Vader