You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-cs@ibatis.apache.org by Jean Guillaume LALANNE <ho...@cma-cgm.com> on 2005/01/24 10:14:34 UTC
Oracle Stored Procedure and Ibatisnet
Hi,
I am trying to use the following Oracle Stored Procedure with IbatisNet:
CREATE OR REPLACE FUNCTION DT_WIMB_INSERTHTTPREQUEST
(
BROWSER IN TWIMB_HTTPREQUEST.HTTPREQUEST_BROWSER%TYPE,
IP IN TWIMB_HTTPREQUEST.HTTPREQUEST_IP%TYPE,
RTYPE IN TWIMB_HTTPREQUEST.HTTPREQUEST_TYPE%TYPE,
ENTITYNUM IN TWIMB_HTTPREQUEST.HTTPREQUEST_ENTITYNUM%TYPE,
STARTDATE IN TWIMB_HTTPREQUEST.HTTPREQUEST_STARTDATE%TYPE,
ENDDATE IN TWIMB_HTTPREQUEST.HTTPREQUEST_ENDDATE%TYPE,
RESULT IN TWIMB_HTTPREQUEST.HTTPREQUEST_RESULT%TYPE
) RETURN NUMBER
I have defined the following mapping xml file:
<parameterMap id="HTTPRequestParamMap" class="HTTPRequest">
<parameter property="Browser" column="BROWSER" dbType="VARCHAR"/>
<parameter property="Ip" column="IP" dbType="VARCHAR"/>
<parameter property="RType" column="RTYPE" dbType="VARCHAR"/>
<parameter property="EntityNum" column="ENTITYNUM" dbType="VARCHAR"/>
<parameter property="StartDate" column="STARTDATE" dbType="DATE"/>
<parameter property="EndDate" column="ENDDATE" dbType="DATE"/>
<parameter property="Result" column="RESULT" dbType="VARCHAR"/>
</parameterMap>
And statement:
<procedure id="setHTTPRequest" parameterMap="HTTPRequestParamMap"
resultClass="System.Int32">
DT_WIMB_INSERTHTTPREQUEST
</procedure>
And in my Dao object I am doing this:
<< Object obj = ExecuteInsert("setHTTPRequest", httpRequest); log.Debug("
result = " + obj ); >>
But it does not seem to work correctly. I get the following exception
message:
<< 2005-01-24 10:08:07,918 [2328] DEBUG
IBatisNet.DataMapper.Configuration.Statemen
ts.PreparedStatementFactory [] <> - SQL for statement 'setHTTPRequest'
:DT_WIMB_INSERTHTTPREQUEST
Exception non gérée : IBatisNet.Common.Exceptions.IBatisNetException: Error
executing query 'setHTTPRequest' for insert. Cause: Le format de la chaîne
d'entrée est incorrect. ---> System.FormatException: Le format de la chaîne
d'entrée est
incorrect.
at System.Number.ParseDecimal(String s, NumberStyles style,
NumberFormatInfoinfo)
at System.Decimal.Parse(String s, NumberStyles style, IFormatProvider
provider)
at System.Convert.ToDecimal(String value, IFormatProvider provider)
at System.String.System.IConvertible.ToDecimal(IFormatProvider provider)
at System.Convert.ChangeType(Object value, Type conversionType,
IFormatProvider provider) >>
Do anyone one of you know what I am doing wrong?
Thanks a lot for your help,
Regards
Jean-Guillaume LALANNE
Re: Problem of connection in the case of asynchronous call
Posted by Gilles Bayon <ib...@gmail.com>.
There is something strange the property DaoSessionHandler is null but
the variable _daoSessionHandler is set !
On Wed, 26 Jan 2005 17:19:07 +0100, Jean Guillaume LALANNE
<ho...@cma-cgm.com> wrote:
> I have debugged my application and it seems that the daoManager has lost
> some information because of the asynchronous call:
>
> Look at this picture:
> http://jeanguitou.jexiste.fr/tests/daomanager_problem.bmp
>
> It seems there is a problem with the session handler object.
>
> Regards
>
> Jean-Guillaume
>
> -----Message d'origine-----
> De : ho.jglalanne@cma-cgm.com [mailto:ho.jglalanne@cma-cgm.com]
> Envoyé : mercredi 26 janvier 2005 16:38
> À : ibatis-user-cs@incubator.apache.org
> Objet : Problem of connection in the case of asynchronous call
>
> Hi all,
>
> I have a class that I access either synchronously either asynchronously by
> using a delegate.
> In this class, there is a method that is using ibatis to store information
> in a database.
> When this method is accessed synchronously, everything is fined.
> But when I try asynchronously, I cannot open the connection:
>
> ((new ServiceConfig()).GetInstance("statisticsSqlMapDao")).openConnection()
>
> throws an exception :
>
> "the object reference is not defined as an object instance"
>
> What do I miss ?
>
> Thanks in advance for your help
>
> Regards
>
> Jean-Guillaume
>
>
RE: Problem of connection in the case of asynchronous call
Posted by Jean Guillaume LALANNE <ho...@cma-cgm.com>.
I have debugged my application and it seems that the daoManager has lost
some information because of the asynchronous call:
Look at this picture:
http://jeanguitou.jexiste.fr/tests/daomanager_problem.bmp
It seems there is a problem with the session handler object.
Regards
Jean-Guillaume
-----Message d'origine-----
De : ho.jglalanne@cma-cgm.com [mailto:ho.jglalanne@cma-cgm.com]
Envoyé : mercredi 26 janvier 2005 16:38
À : ibatis-user-cs@incubator.apache.org
Objet : Problem of connection in the case of asynchronous call
Hi all,
I have a class that I access either synchronously either asynchronously by
using a delegate.
In this class, there is a method that is using ibatis to store information
in a database.
When this method is accessed synchronously, everything is fined.
But when I try asynchronously, I cannot open the connection:
((new ServiceConfig()).GetInstance("statisticsSqlMapDao")).openConnection()
throws an exception :
"the object reference is not defined as an object instance"
What do I miss ?
Thanks in advance for your help
Regards
Jean-Guillaume
Problem of connection in the case of asynchronous call
Posted by Jean Guillaume LALANNE <ho...@cma-cgm.com>.
Hi all,
I have a class that I access either synchronously either asynchronously by
using a delegate.
In this class, there is a method that is using ibatis to store information
in a database.
When this method is accessed synchronously, everything is fined.
But when I try asynchronously, I cannot open the connection:
((new ServiceConfig()).GetInstance("statisticsSqlMapDao")).openConnection()
throws an exception :
"the object reference is not defined as an object instance"
What do I miss ?
Thanks in advance for your help
Regards
Jean-Guillaume
RE: Oracle Stored Procedure and Ibatisnet
Posted by roberto <ro...@theaegis.org>.
Oh, if you use a stored procedure instead of a stored function (the
DT_WIMB_INSERTHTTPREQUEST SQL that was given shows that it's a function),
that should work better since the function's implicit returnValue parameter
would just be another output parameter in the stored procedure.
The chosen provider would then be able to pick up the output parameter when
the provider's DeriveParameters method is called, and iBATIS would know it
was there when preparing the command object. This should work with the
System.Data.OracleClient provider or maybe with the ODP.NET 10g provider. I
tested the 10g provider briefly since the 9.2 provider does not have a
DeriveParameters method. And this is all with the latest DataMapper 1.1
alpha release.
Just an idea but a bit duplicative...you could keep
DT_WIMB_INSERTHTTPREQUEST as a stored function, create a wrapper stored
procedure that calls that function, and have your SqlMap call the new stored
procedure if you don't want to have a SQL SELECT func(?) statement in your
SqlMap file.
FYI, I believe there are plans to rework the iBATIS.NET's internal handling
of parameterMaps and resultMaps. Maybe it would then use a given
parameterMap for a <procedure> statement (or maybe we need a <function>
statement hehe) instead of defaulting to DeriveParameters. :-)
Roberto
> -----Original Message-----
> From: Jean Guillaume LALANNE [mailto:ho.jglalanne@cma-cgm.com]
> Sent: Wednesday, January 26, 2005 10:13 AM
> To: ibatis-user-cs@incubator.apache.org
> Subject: RE: Oracle Stored Procedure and Ibatisnet
>
> Thanks Roberto,
>
> Finally I have decided not to use stored procedure right now. I'll check
> your solution later on.
>
> Cheers
> Jean-Guillaume
>
> -----Message d'origine-----
> De : ibatis-user-cs-return-160-ho.jglalanne=cma-
> cgm.com@incubator.apache.org
> [mailto:ibatis-user-cs-return-160-ho.jglalanne=cma-
> cgm.com@incubator.apache.
> org] De la part de roberto
> Envoyé : lundi 24 janvier 2005 17:17
> À : ibatis-user-cs@incubator.apache.org
> Objet : RE: Oracle Stored Procedure and Ibatisnet
>
> Try:
>
> + wrapping the function call in a SELECT SQL statement such as:
> select fnc_Test(?) from dual
>
> + use a <statement> with Decimal resultClass (as Ron mentioned)
>
> + use object newObj = sqlMap.QueryForObject("statementname", paramObj)
> also
> as Ron mentioned to get the returned number
>
> I'm not sure which provider you're using, but Oracle functions aren't
> exactly the same as procedures since they have a returnValue parameter
> direction.
>
> For example using the .NET 1.1 OracleClient provider, I can't confirm that
> using a <procedure> statement is being handled currently when a
> parameterMap
> has a returnValue parameter for an Oracle function.
>
> I think it's skipping (maybe? Haven't spent much time looking) the
> parameterMap when prepping the Command for a CommandType.StoredProcedure
> since DeriveParameters is being used to initially generate the <procedure>
> statement...
>
> Meaning, it doesn't see or prep the returnValue parameter since the
> parameterMap isn't being used for prepping the <procedure> statement's
> parameters, and the returnValue parameter isn't being found as a parameter
> for the Oracle function with DeriveParameters().
>
> Hope this helps.
>
> Roberto
>
>
>
>
> > -----Original Message-----
> > From: Ron Grabowski [mailto:rongrabowski@yahoo.com]
> > Sent: Monday, January 24, 2005 10:27 AM
> > To: ibatis-user-cs@incubator.apache.org
> > Subject: RE: Oracle Stored Procedure and Ibatisnet
> >
> > I have not used IBatis with Oracle stored procedures. I've noticed
> > there are a lot of people on the Java list who use Oracle.
> >
> > Have you tried calling sqlMap.QueryForObject instead of sqlMap.Insert?
> > I only call Insert on <insert> statements. Those <insert> statements
> > always contain a <selectKey> statement.
> >
> > - Ron
> >
> > --- Jean Guillaume LALANNE <ho...@cma-cgm.com> wrote:
> >
> > > Thanks a lot Ron,
> > >
> > > But I have still the same error.
> > > I don't understand my problem.
> > > Have you already used Ibatisnet with oracle stored procedure ?
> > >
> > > Cheers
> > > Jean-Guillaume LALANNE
> > >
> > > -----Message d'origine-----
> > > De :
> > >
> > ibatis-user-cs-return-157-ho.jglalanne=cma-cgm.com@incubator.apache.org
> > >
> > [mailto:ibatis-user-cs-return-157-ho.jglalanne=cma-
> > cgm.com@incubator.apache.
> > > org] De la part de Ron Grabowski
> > > Envoyé : lundi 24 janvier 2005 14:00
> > > À : ibatis-user-cs@incubator.apache.org
> > > Objet : Re: Oracle Stored Procedure and Ibatisnet
> > >
> > > Maybe Oracle's NUMBER cannot be converted to a System.Int32. What
> > > happens if you change your resultClass to "System.Decimal"?
> > >
> > > --- Jean Guillaume LALANNE <ho...@cma-cgm.com> wrote:
> > >
> > > > Hi,
> > > >
> > > >
> > > >
> > > > I am trying to use the following Oracle Stored Procedure with
> > > > IbatisNet:
> > > >
> > > >
> > > >
> > > > CREATE OR REPLACE FUNCTION DT_WIMB_INSERTHTTPREQUEST
> > > >
> > > > (
> > > >
> > > > BROWSER IN TWIMB_HTTPREQUEST.HTTPREQUEST_BROWSER%TYPE,
> > > >
> > > > IP IN TWIMB_HTTPREQUEST.HTTPREQUEST_IP%TYPE,
> > > >
> > > > RTYPE IN TWIMB_HTTPREQUEST.HTTPREQUEST_TYPE%TYPE,
> > > >
> > > > ENTITYNUM IN TWIMB_HTTPREQUEST.HTTPREQUEST_ENTITYNUM%TYPE,
> > > >
> > > > STARTDATE IN TWIMB_HTTPREQUEST.HTTPREQUEST_STARTDATE%TYPE,
> > > >
> > > > ENDDATE IN TWIMB_HTTPREQUEST.HTTPREQUEST_ENDDATE%TYPE,
> > > >
> > > > RESULT IN TWIMB_HTTPREQUEST.HTTPREQUEST_RESULT%TYPE
> > > >
> > > > ) RETURN NUMBER
> > > >
> > > >
> > > >
> > > > I have defined the following mapping xml file:
> > > >
> > > >
> > > >
> > > > <parameterMap id="HTTPRequestParamMap" class="HTTPRequest">
> > > >
> > > > <parameter property="Browser" column="BROWSER"
> > > > dbType="VARCHAR"/>
> > > >
> > > > <parameter property="Ip" column="IP" dbType="VARCHAR"/>
> > > >
> > > > <parameter property="RType" column="RTYPE" dbType="VARCHAR"/>
> > > >
> > > > <parameter property="EntityNum" column="ENTITYNUM"
> > > > dbType="VARCHAR"/>
> > > >
> > > > <parameter property="StartDate" column="STARTDATE"
> > > > dbType="DATE"/>
> > > >
> > > > <parameter property="EndDate" column="ENDDATE"
> > > dbType="DATE"/>
> > > >
> > > > <parameter property="Result" column="RESULT"
> > > dbType="VARCHAR"/>
> > > >
> > > >
> > > > </parameterMap>
> > > >
> > > >
> > > >
> > > > And statement:
> > > >
> > > >
> > > >
> > > > <procedure id="setHTTPRequest" parameterMap="HTTPRequestParamMap"
> > > > resultClass="System.Int32">
> > > >
> > > > DT_WIMB_INSERTHTTPREQUEST
> > > >
> > > > </procedure>
> > > >
> > > >
> > > >
> > > > And in my Dao object I am doing this:
> > > >
> > > >
> > > >
> > > > << Object obj = ExecuteInsert("setHTTPRequest", httpRequest);
> > > > log.Debug("
> > > > result = " + obj ); >>
> > > >
> > > >
> > > >
> > > > But it does not seem to work correctly. I get the following
> > > exception
> > > > message:
> > > >
> > > >
> > > >
> > > > << 2005-01-24 10:08:07,918 [2328] DEBUG
> > > > IBatisNet.DataMapper.Configuration.Statemen
> > > >
> > > > ts.PreparedStatementFactory [] <> - SQL for statement
> > > > 'setHTTPRequest'
> > > > :DT_WIMB_INSERTHTTPREQUEST
> > > >
> > > >
> > > >
> > > > Exception non girie :
> > > IBatisNet.Common.Exceptions.IBatisNetException:
> > > > Error
> > > > executing query 'setHTTPRequest' for insert. Cause: Le format de
> > > la
> > > > channe
> > > > d'entrie est incorrect. ---> System.FormatException: Le format de
> > > la
> > > > channe
> > > > d'entrie est
> > > >
> > > > incorrect.
> > > >
> > > > at System.Number.ParseDecimal(String s, NumberStyles style,
> > > > NumberFormatInfoinfo)
> > > >
> > > > at System.Decimal.Parse(String s, NumberStyles style,
> > > > IFormatProvider
> > > > provider)
> > > >
> > > > at System.Convert.ToDecimal(String value, IFormatProvider
> > > > provider)
> > > >
> > > > at System.String.System.IConvertible.ToDecimal(IFormatProvider
> > > > provider)
> > > >
> > > > at System.Convert.ChangeType(Object value, Type conversionType,
> > > > IFormatProvider provider) >>
> > > >
> > > >
> > > >
> > > > Do anyone one of you know what I am doing wrong?
> > > >
> > > >
> > > >
> > > > Thanks a lot for your help,
> > > >
> > > >
> > > >
> > > > Regards
> > > >
> > > >
> > > >
> > > > Jean-Guillaume LALANNE
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
>
>
RE: Oracle Stored Procedure and Ibatisnet
Posted by Jean Guillaume LALANNE <ho...@cma-cgm.com>.
Thanks Roberto,
Finally I have decided not to use stored procedure right now. I'll check
your solution later on.
Cheers
Jean-Guillaume
-----Message d'origine-----
De : ibatis-user-cs-return-160-ho.jglalanne=cma-cgm.com@incubator.apache.org
[mailto:ibatis-user-cs-return-160-ho.jglalanne=cma-cgm.com@incubator.apache.
org] De la part de roberto
Envoyé : lundi 24 janvier 2005 17:17
À : ibatis-user-cs@incubator.apache.org
Objet : RE: Oracle Stored Procedure and Ibatisnet
Try:
+ wrapping the function call in a SELECT SQL statement such as:
select fnc_Test(?) from dual
+ use a <statement> with Decimal resultClass (as Ron mentioned)
+ use object newObj = sqlMap.QueryForObject("statementname", paramObj) also
as Ron mentioned to get the returned number
I'm not sure which provider you're using, but Oracle functions aren't
exactly the same as procedures since they have a returnValue parameter
direction.
For example using the .NET 1.1 OracleClient provider, I can't confirm that
using a <procedure> statement is being handled currently when a parameterMap
has a returnValue parameter for an Oracle function.
I think it's skipping (maybe? Haven't spent much time looking) the
parameterMap when prepping the Command for a CommandType.StoredProcedure
since DeriveParameters is being used to initially generate the <procedure>
statement...
Meaning, it doesn't see or prep the returnValue parameter since the
parameterMap isn't being used for prepping the <procedure> statement's
parameters, and the returnValue parameter isn't being found as a parameter
for the Oracle function with DeriveParameters().
Hope this helps.
Roberto
> -----Original Message-----
> From: Ron Grabowski [mailto:rongrabowski@yahoo.com]
> Sent: Monday, January 24, 2005 10:27 AM
> To: ibatis-user-cs@incubator.apache.org
> Subject: RE: Oracle Stored Procedure and Ibatisnet
>
> I have not used IBatis with Oracle stored procedures. I've noticed
> there are a lot of people on the Java list who use Oracle.
>
> Have you tried calling sqlMap.QueryForObject instead of sqlMap.Insert?
> I only call Insert on <insert> statements. Those <insert> statements
> always contain a <selectKey> statement.
>
> - Ron
>
> --- Jean Guillaume LALANNE <ho...@cma-cgm.com> wrote:
>
> > Thanks a lot Ron,
> >
> > But I have still the same error.
> > I don't understand my problem.
> > Have you already used Ibatisnet with oracle stored procedure ?
> >
> > Cheers
> > Jean-Guillaume LALANNE
> >
> > -----Message d'origine-----
> > De :
> >
> ibatis-user-cs-return-157-ho.jglalanne=cma-cgm.com@incubator.apache.org
> >
> [mailto:ibatis-user-cs-return-157-ho.jglalanne=cma-
> cgm.com@incubator.apache.
> > org] De la part de Ron Grabowski
> > Envoyé : lundi 24 janvier 2005 14:00
> > À : ibatis-user-cs@incubator.apache.org
> > Objet : Re: Oracle Stored Procedure and Ibatisnet
> >
> > Maybe Oracle's NUMBER cannot be converted to a System.Int32. What
> > happens if you change your resultClass to "System.Decimal"?
> >
> > --- Jean Guillaume LALANNE <ho...@cma-cgm.com> wrote:
> >
> > > Hi,
> > >
> > >
> > >
> > > I am trying to use the following Oracle Stored Procedure with
> > > IbatisNet:
> > >
> > >
> > >
> > > CREATE OR REPLACE FUNCTION DT_WIMB_INSERTHTTPREQUEST
> > >
> > > (
> > >
> > > BROWSER IN TWIMB_HTTPREQUEST.HTTPREQUEST_BROWSER%TYPE,
> > >
> > > IP IN TWIMB_HTTPREQUEST.HTTPREQUEST_IP%TYPE,
> > >
> > > RTYPE IN TWIMB_HTTPREQUEST.HTTPREQUEST_TYPE%TYPE,
> > >
> > > ENTITYNUM IN TWIMB_HTTPREQUEST.HTTPREQUEST_ENTITYNUM%TYPE,
> > >
> > > STARTDATE IN TWIMB_HTTPREQUEST.HTTPREQUEST_STARTDATE%TYPE,
> > >
> > > ENDDATE IN TWIMB_HTTPREQUEST.HTTPREQUEST_ENDDATE%TYPE,
> > >
> > > RESULT IN TWIMB_HTTPREQUEST.HTTPREQUEST_RESULT%TYPE
> > >
> > > ) RETURN NUMBER
> > >
> > >
> > >
> > > I have defined the following mapping xml file:
> > >
> > >
> > >
> > > <parameterMap id="HTTPRequestParamMap" class="HTTPRequest">
> > >
> > > <parameter property="Browser" column="BROWSER"
> > > dbType="VARCHAR"/>
> > >
> > > <parameter property="Ip" column="IP" dbType="VARCHAR"/>
> > >
> > > <parameter property="RType" column="RTYPE" dbType="VARCHAR"/>
> > >
> > > <parameter property="EntityNum" column="ENTITYNUM"
> > > dbType="VARCHAR"/>
> > >
> > > <parameter property="StartDate" column="STARTDATE"
> > > dbType="DATE"/>
> > >
> > > <parameter property="EndDate" column="ENDDATE"
> > dbType="DATE"/>
> > >
> > > <parameter property="Result" column="RESULT"
> > dbType="VARCHAR"/>
> > >
> > >
> > > </parameterMap>
> > >
> > >
> > >
> > > And statement:
> > >
> > >
> > >
> > > <procedure id="setHTTPRequest" parameterMap="HTTPRequestParamMap"
> > > resultClass="System.Int32">
> > >
> > > DT_WIMB_INSERTHTTPREQUEST
> > >
> > > </procedure>
> > >
> > >
> > >
> > > And in my Dao object I am doing this:
> > >
> > >
> > >
> > > << Object obj = ExecuteInsert("setHTTPRequest", httpRequest);
> > > log.Debug("
> > > result = " + obj ); >>
> > >
> > >
> > >
> > > But it does not seem to work correctly. I get the following
> > exception
> > > message:
> > >
> > >
> > >
> > > << 2005-01-24 10:08:07,918 [2328] DEBUG
> > > IBatisNet.DataMapper.Configuration.Statemen
> > >
> > > ts.PreparedStatementFactory [] <> - SQL for statement
> > > 'setHTTPRequest'
> > > :DT_WIMB_INSERTHTTPREQUEST
> > >
> > >
> > >
> > > Exception non girie :
> > IBatisNet.Common.Exceptions.IBatisNetException:
> > > Error
> > > executing query 'setHTTPRequest' for insert. Cause: Le format de
> > la
> > > channe
> > > d'entrie est incorrect. ---> System.FormatException: Le format de
> > la
> > > channe
> > > d'entrie est
> > >
> > > incorrect.
> > >
> > > at System.Number.ParseDecimal(String s, NumberStyles style,
> > > NumberFormatInfoinfo)
> > >
> > > at System.Decimal.Parse(String s, NumberStyles style,
> > > IFormatProvider
> > > provider)
> > >
> > > at System.Convert.ToDecimal(String value, IFormatProvider
> > > provider)
> > >
> > > at System.String.System.IConvertible.ToDecimal(IFormatProvider
> > > provider)
> > >
> > > at System.Convert.ChangeType(Object value, Type conversionType,
> > > IFormatProvider provider) >>
> > >
> > >
> > >
> > > Do anyone one of you know what I am doing wrong?
> > >
> > >
> > >
> > > Thanks a lot for your help,
> > >
> > >
> > >
> > > Regards
> > >
> > >
> > >
> > > Jean-Guillaume LALANNE
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
>
RE: Oracle Stored Procedure and Ibatisnet
Posted by roberto <ro...@theaegis.org>.
Try:
+ wrapping the function call in a SELECT SQL statement such as:
select fnc_Test(?) from dual
+ use a <statement> with Decimal resultClass (as Ron mentioned)
+ use object newObj = sqlMap.QueryForObject("statementname", paramObj) also
as Ron mentioned to get the returned number
I'm not sure which provider you're using, but Oracle functions aren't
exactly the same as procedures since they have a returnValue parameter
direction.
For example using the .NET 1.1 OracleClient provider, I can't confirm that
using a <procedure> statement is being handled currently when a parameterMap
has a returnValue parameter for an Oracle function.
I think it's skipping (maybe? Haven't spent much time looking) the
parameterMap when prepping the Command for a CommandType.StoredProcedure
since DeriveParameters is being used to initially generate the <procedure>
statement...
Meaning, it doesn't see or prep the returnValue parameter since the
parameterMap isn't being used for prepping the <procedure> statement's
parameters, and the returnValue parameter isn't being found as a parameter
for the Oracle function with DeriveParameters().
Hope this helps.
Roberto
> -----Original Message-----
> From: Ron Grabowski [mailto:rongrabowski@yahoo.com]
> Sent: Monday, January 24, 2005 10:27 AM
> To: ibatis-user-cs@incubator.apache.org
> Subject: RE: Oracle Stored Procedure and Ibatisnet
>
> I have not used IBatis with Oracle stored procedures. I've noticed
> there are a lot of people on the Java list who use Oracle.
>
> Have you tried calling sqlMap.QueryForObject instead of sqlMap.Insert?
> I only call Insert on <insert> statements. Those <insert> statements
> always contain a <selectKey> statement.
>
> - Ron
>
> --- Jean Guillaume LALANNE <ho...@cma-cgm.com> wrote:
>
> > Thanks a lot Ron,
> >
> > But I have still the same error.
> > I don't understand my problem.
> > Have you already used Ibatisnet with oracle stored procedure ?
> >
> > Cheers
> > Jean-Guillaume LALANNE
> >
> > -----Message d'origine-----
> > De :
> >
> ibatis-user-cs-return-157-ho.jglalanne=cma-cgm.com@incubator.apache.org
> >
> [mailto:ibatis-user-cs-return-157-ho.jglalanne=cma-
> cgm.com@incubator.apache.
> > org] De la part de Ron Grabowski
> > Envoyé : lundi 24 janvier 2005 14:00
> > À : ibatis-user-cs@incubator.apache.org
> > Objet : Re: Oracle Stored Procedure and Ibatisnet
> >
> > Maybe Oracle's NUMBER cannot be converted to a System.Int32. What
> > happens if you change your resultClass to "System.Decimal"?
> >
> > --- Jean Guillaume LALANNE <ho...@cma-cgm.com> wrote:
> >
> > > Hi,
> > >
> > >
> > >
> > > I am trying to use the following Oracle Stored Procedure with
> > > IbatisNet:
> > >
> > >
> > >
> > > CREATE OR REPLACE FUNCTION DT_WIMB_INSERTHTTPREQUEST
> > >
> > > (
> > >
> > > BROWSER IN TWIMB_HTTPREQUEST.HTTPREQUEST_BROWSER%TYPE,
> > >
> > > IP IN TWIMB_HTTPREQUEST.HTTPREQUEST_IP%TYPE,
> > >
> > > RTYPE IN TWIMB_HTTPREQUEST.HTTPREQUEST_TYPE%TYPE,
> > >
> > > ENTITYNUM IN TWIMB_HTTPREQUEST.HTTPREQUEST_ENTITYNUM%TYPE,
> > >
> > > STARTDATE IN TWIMB_HTTPREQUEST.HTTPREQUEST_STARTDATE%TYPE,
> > >
> > > ENDDATE IN TWIMB_HTTPREQUEST.HTTPREQUEST_ENDDATE%TYPE,
> > >
> > > RESULT IN TWIMB_HTTPREQUEST.HTTPREQUEST_RESULT%TYPE
> > >
> > > ) RETURN NUMBER
> > >
> > >
> > >
> > > I have defined the following mapping xml file:
> > >
> > >
> > >
> > > <parameterMap id="HTTPRequestParamMap" class="HTTPRequest">
> > >
> > > <parameter property="Browser" column="BROWSER"
> > > dbType="VARCHAR"/>
> > >
> > > <parameter property="Ip" column="IP" dbType="VARCHAR"/>
> > >
> > > <parameter property="RType" column="RTYPE" dbType="VARCHAR"/>
> > >
> > > <parameter property="EntityNum" column="ENTITYNUM"
> > > dbType="VARCHAR"/>
> > >
> > > <parameter property="StartDate" column="STARTDATE"
> > > dbType="DATE"/>
> > >
> > > <parameter property="EndDate" column="ENDDATE"
> > dbType="DATE"/>
> > >
> > > <parameter property="Result" column="RESULT"
> > dbType="VARCHAR"/>
> > >
> > >
> > > </parameterMap>
> > >
> > >
> > >
> > > And statement:
> > >
> > >
> > >
> > > <procedure id="setHTTPRequest" parameterMap="HTTPRequestParamMap"
> > > resultClass="System.Int32">
> > >
> > > DT_WIMB_INSERTHTTPREQUEST
> > >
> > > </procedure>
> > >
> > >
> > >
> > > And in my Dao object I am doing this:
> > >
> > >
> > >
> > > << Object obj = ExecuteInsert("setHTTPRequest", httpRequest);
> > > log.Debug("
> > > result = " + obj ); >>
> > >
> > >
> > >
> > > But it does not seem to work correctly. I get the following
> > exception
> > > message:
> > >
> > >
> > >
> > > << 2005-01-24 10:08:07,918 [2328] DEBUG
> > > IBatisNet.DataMapper.Configuration.Statemen
> > >
> > > ts.PreparedStatementFactory [] <> - SQL for statement
> > > 'setHTTPRequest'
> > > :DT_WIMB_INSERTHTTPREQUEST
> > >
> > >
> > >
> > > Exception non girie :
> > IBatisNet.Common.Exceptions.IBatisNetException:
> > > Error
> > > executing query 'setHTTPRequest' for insert. Cause: Le format de
> > la
> > > channe
> > > d'entrie est incorrect. ---> System.FormatException: Le format de
> > la
> > > channe
> > > d'entrie est
> > >
> > > incorrect.
> > >
> > > at System.Number.ParseDecimal(String s, NumberStyles style,
> > > NumberFormatInfoinfo)
> > >
> > > at System.Decimal.Parse(String s, NumberStyles style,
> > > IFormatProvider
> > > provider)
> > >
> > > at System.Convert.ToDecimal(String value, IFormatProvider
> > > provider)
> > >
> > > at System.String.System.IConvertible.ToDecimal(IFormatProvider
> > > provider)
> > >
> > > at System.Convert.ChangeType(Object value, Type conversionType,
> > > IFormatProvider provider) >>
> > >
> > >
> > >
> > > Do anyone one of you know what I am doing wrong?
> > >
> > >
> > >
> > > Thanks a lot for your help,
> > >
> > >
> > >
> > > Regards
> > >
> > >
> > >
> > > Jean-Guillaume LALANNE
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
>
RE: Oracle Stored Procedure and Ibatisnet
Posted by Ron Grabowski <ro...@yahoo.com>.
I have not used IBatis with Oracle stored procedures. I've noticed
there are a lot of people on the Java list who use Oracle.
Have you tried calling sqlMap.QueryForObject instead of sqlMap.Insert?
I only call Insert on <insert> statements. Those <insert> statements
always contain a <selectKey> statement.
- Ron
--- Jean Guillaume LALANNE <ho...@cma-cgm.com> wrote:
> Thanks a lot Ron,
>
> But I have still the same error.
> I don't understand my problem.
> Have you already used Ibatisnet with oracle stored procedure ?
>
> Cheers
> Jean-Guillaume LALANNE
>
> -----Message d'origine-----
> De :
>
ibatis-user-cs-return-157-ho.jglalanne=cma-cgm.com@incubator.apache.org
>
[mailto:ibatis-user-cs-return-157-ho.jglalanne=cma-cgm.com@incubator.apache.
> org] De la part de Ron Grabowski
> Envoy� : lundi 24 janvier 2005 14:00
> � : ibatis-user-cs@incubator.apache.org
> Objet : Re: Oracle Stored Procedure and Ibatisnet
>
> Maybe Oracle's NUMBER cannot be converted to a System.Int32. What
> happens if you change your resultClass to "System.Decimal"?
>
> --- Jean Guillaume LALANNE <ho...@cma-cgm.com> wrote:
>
> > Hi,
> >
> >
> >
> > I am trying to use the following Oracle Stored Procedure with
> > IbatisNet:
> >
> >
> >
> > CREATE OR REPLACE FUNCTION DT_WIMB_INSERTHTTPREQUEST
> >
> > (
> >
> > BROWSER IN TWIMB_HTTPREQUEST.HTTPREQUEST_BROWSER%TYPE,
> >
> > IP IN TWIMB_HTTPREQUEST.HTTPREQUEST_IP%TYPE,
> >
> > RTYPE IN TWIMB_HTTPREQUEST.HTTPREQUEST_TYPE%TYPE,
> >
> > ENTITYNUM IN TWIMB_HTTPREQUEST.HTTPREQUEST_ENTITYNUM%TYPE,
> >
> > STARTDATE IN TWIMB_HTTPREQUEST.HTTPREQUEST_STARTDATE%TYPE,
> >
> > ENDDATE IN TWIMB_HTTPREQUEST.HTTPREQUEST_ENDDATE%TYPE,
> >
> > RESULT IN TWIMB_HTTPREQUEST.HTTPREQUEST_RESULT%TYPE
> >
> > ) RETURN NUMBER
> >
> >
> >
> > I have defined the following mapping xml file:
> >
> >
> >
> > <parameterMap id="HTTPRequestParamMap" class="HTTPRequest">
> >
> > <parameter property="Browser" column="BROWSER"
> > dbType="VARCHAR"/>
> >
> > <parameter property="Ip" column="IP" dbType="VARCHAR"/>
> >
> > <parameter property="RType" column="RTYPE" dbType="VARCHAR"/>
> >
> > <parameter property="EntityNum" column="ENTITYNUM"
> > dbType="VARCHAR"/>
> >
> > <parameter property="StartDate" column="STARTDATE"
> > dbType="DATE"/>
> >
> > <parameter property="EndDate" column="ENDDATE"
> dbType="DATE"/>
> >
> > <parameter property="Result" column="RESULT"
> dbType="VARCHAR"/>
> >
> >
> > </parameterMap>
> >
> >
> >
> > And statement:
> >
> >
> >
> > <procedure id="setHTTPRequest" parameterMap="HTTPRequestParamMap"
> > resultClass="System.Int32">
> >
> > DT_WIMB_INSERTHTTPREQUEST
> >
> > </procedure>
> >
> >
> >
> > And in my Dao object I am doing this:
> >
> >
> >
> > << Object obj = ExecuteInsert("setHTTPRequest", httpRequest);
> > log.Debug("
> > result = " + obj ); >>
> >
> >
> >
> > But it does not seem to work correctly. I get the following
> exception
> > message:
> >
> >
> >
> > << 2005-01-24 10:08:07,918 [2328] DEBUG
> > IBatisNet.DataMapper.Configuration.Statemen
> >
> > ts.PreparedStatementFactory [] <> - SQL for statement
> > 'setHTTPRequest'
> > :DT_WIMB_INSERTHTTPREQUEST
> >
> >
> >
> > Exception non girie :
> IBatisNet.Common.Exceptions.IBatisNetException:
> > Error
> > executing query 'setHTTPRequest' for insert. Cause: Le format de
> la
> > channe
> > d'entrie est incorrect. ---> System.FormatException: Le format de
> la
> > channe
> > d'entrie est
> >
> > incorrect.
> >
> > at System.Number.ParseDecimal(String s, NumberStyles style,
> > NumberFormatInfoinfo)
> >
> > at System.Decimal.Parse(String s, NumberStyles style,
> > IFormatProvider
> > provider)
> >
> > at System.Convert.ToDecimal(String value, IFormatProvider
> > provider)
> >
> > at System.String.System.IConvertible.ToDecimal(IFormatProvider
> > provider)
> >
> > at System.Convert.ChangeType(Object value, Type conversionType,
> > IFormatProvider provider) >>
> >
> >
> >
> > Do anyone one of you know what I am doing wrong?
> >
> >
> >
> > Thanks a lot for your help,
> >
> >
> >
> > Regards
> >
> >
> >
> > Jean-Guillaume LALANNE
> >
> >
> >
> >
> >
> >
>
>
RE: Oracle Stored Procedure and Ibatisnet
Posted by Jean Guillaume LALANNE <ho...@cma-cgm.com>.
Thanks a lot Ron,
But I have still the same error.
I don't understand my problem.
Have you already used Ibatisnet with oracle stored procedure ?
Cheers
Jean-Guillaume LALANNE
-----Message d'origine-----
De : ibatis-user-cs-return-157-ho.jglalanne=cma-cgm.com@incubator.apache.org
[mailto:ibatis-user-cs-return-157-ho.jglalanne=cma-cgm.com@incubator.apache.
org] De la part de Ron Grabowski
Envoyé : lundi 24 janvier 2005 14:00
À : ibatis-user-cs@incubator.apache.org
Objet : Re: Oracle Stored Procedure and Ibatisnet
Maybe Oracle's NUMBER cannot be converted to a System.Int32. What
happens if you change your resultClass to "System.Decimal"?
--- Jean Guillaume LALANNE <ho...@cma-cgm.com> wrote:
> Hi,
>
>
>
> I am trying to use the following Oracle Stored Procedure with
> IbatisNet:
>
>
>
> CREATE OR REPLACE FUNCTION DT_WIMB_INSERTHTTPREQUEST
>
> (
>
> BROWSER IN TWIMB_HTTPREQUEST.HTTPREQUEST_BROWSER%TYPE,
>
> IP IN TWIMB_HTTPREQUEST.HTTPREQUEST_IP%TYPE,
>
> RTYPE IN TWIMB_HTTPREQUEST.HTTPREQUEST_TYPE%TYPE,
>
> ENTITYNUM IN TWIMB_HTTPREQUEST.HTTPREQUEST_ENTITYNUM%TYPE,
>
> STARTDATE IN TWIMB_HTTPREQUEST.HTTPREQUEST_STARTDATE%TYPE,
>
> ENDDATE IN TWIMB_HTTPREQUEST.HTTPREQUEST_ENDDATE%TYPE,
>
> RESULT IN TWIMB_HTTPREQUEST.HTTPREQUEST_RESULT%TYPE
>
> ) RETURN NUMBER
>
>
>
> I have defined the following mapping xml file:
>
>
>
> <parameterMap id="HTTPRequestParamMap" class="HTTPRequest">
>
> <parameter property="Browser" column="BROWSER"
> dbType="VARCHAR"/>
>
> <parameter property="Ip" column="IP" dbType="VARCHAR"/>
>
> <parameter property="RType" column="RTYPE" dbType="VARCHAR"/>
>
> <parameter property="EntityNum" column="ENTITYNUM"
> dbType="VARCHAR"/>
>
> <parameter property="StartDate" column="STARTDATE"
> dbType="DATE"/>
>
> <parameter property="EndDate" column="ENDDATE" dbType="DATE"/>
>
> <parameter property="Result" column="RESULT" dbType="VARCHAR"/>
>
>
> </parameterMap>
>
>
>
> And statement:
>
>
>
> <procedure id="setHTTPRequest" parameterMap="HTTPRequestParamMap"
> resultClass="System.Int32">
>
> DT_WIMB_INSERTHTTPREQUEST
>
> </procedure>
>
>
>
> And in my Dao object I am doing this:
>
>
>
> << Object obj = ExecuteInsert("setHTTPRequest", httpRequest);
> log.Debug("
> result = " + obj ); >>
>
>
>
> But it does not seem to work correctly. I get the following exception
> message:
>
>
>
> << 2005-01-24 10:08:07,918 [2328] DEBUG
> IBatisNet.DataMapper.Configuration.Statemen
>
> ts.PreparedStatementFactory [] <> - SQL for statement
> 'setHTTPRequest'
> :DT_WIMB_INSERTHTTPREQUEST
>
>
>
> Exception non girie : IBatisNet.Common.Exceptions.IBatisNetException:
> Error
> executing query 'setHTTPRequest' for insert. Cause: Le format de la
> channe
> d'entrie est incorrect. ---> System.FormatException: Le format de la
> channe
> d'entrie est
>
> incorrect.
>
> at System.Number.ParseDecimal(String s, NumberStyles style,
> NumberFormatInfoinfo)
>
> at System.Decimal.Parse(String s, NumberStyles style,
> IFormatProvider
> provider)
>
> at System.Convert.ToDecimal(String value, IFormatProvider
> provider)
>
> at System.String.System.IConvertible.ToDecimal(IFormatProvider
> provider)
>
> at System.Convert.ChangeType(Object value, Type conversionType,
> IFormatProvider provider) >>
>
>
>
> Do anyone one of you know what I am doing wrong?
>
>
>
> Thanks a lot for your help,
>
>
>
> Regards
>
>
>
> Jean-Guillaume LALANNE
>
>
>
>
>
>
Re: Oracle Stored Procedure and Ibatisnet
Posted by Ron Grabowski <ro...@yahoo.com>.
Maybe Oracle's NUMBER cannot be converted to a System.Int32. What
happens if you change your resultClass to "System.Decimal"?
--- Jean Guillaume LALANNE <ho...@cma-cgm.com> wrote:
> Hi,
>
>
>
> I am trying to use the following Oracle Stored Procedure with
> IbatisNet:
>
>
>
> CREATE OR REPLACE FUNCTION DT_WIMB_INSERTHTTPREQUEST
>
> (
>
> BROWSER IN TWIMB_HTTPREQUEST.HTTPREQUEST_BROWSER%TYPE,
>
> IP IN TWIMB_HTTPREQUEST.HTTPREQUEST_IP%TYPE,
>
> RTYPE IN TWIMB_HTTPREQUEST.HTTPREQUEST_TYPE%TYPE,
>
> ENTITYNUM IN TWIMB_HTTPREQUEST.HTTPREQUEST_ENTITYNUM%TYPE,
>
> STARTDATE IN TWIMB_HTTPREQUEST.HTTPREQUEST_STARTDATE%TYPE,
>
> ENDDATE IN TWIMB_HTTPREQUEST.HTTPREQUEST_ENDDATE%TYPE,
>
> RESULT IN TWIMB_HTTPREQUEST.HTTPREQUEST_RESULT%TYPE
>
> ) RETURN NUMBER
>
>
>
> I have defined the following mapping xml file:
>
>
>
> <parameterMap id="HTTPRequestParamMap" class="HTTPRequest">
>
> <parameter property="Browser" column="BROWSER"
> dbType="VARCHAR"/>
>
> <parameter property="Ip" column="IP" dbType="VARCHAR"/>
>
> <parameter property="RType" column="RTYPE" dbType="VARCHAR"/>
>
> <parameter property="EntityNum" column="ENTITYNUM"
> dbType="VARCHAR"/>
>
> <parameter property="StartDate" column="STARTDATE"
> dbType="DATE"/>
>
> <parameter property="EndDate" column="ENDDATE" dbType="DATE"/>
>
> <parameter property="Result" column="RESULT" dbType="VARCHAR"/>
>
>
> </parameterMap>
>
>
>
> And statement:
>
>
>
> <procedure id="setHTTPRequest" parameterMap="HTTPRequestParamMap"
> resultClass="System.Int32">
>
> DT_WIMB_INSERTHTTPREQUEST
>
> </procedure>
>
>
>
> And in my Dao object I am doing this:
>
>
>
> << Object obj = ExecuteInsert("setHTTPRequest", httpRequest);
> log.Debug("
> result = " + obj ); >>
>
>
>
> But it does not seem to work correctly. I get the following exception
> message:
>
>
>
> << 2005-01-24 10:08:07,918 [2328] DEBUG
> IBatisNet.DataMapper.Configuration.Statemen
>
> ts.PreparedStatementFactory [] <> - SQL for statement
> 'setHTTPRequest'
> :DT_WIMB_INSERTHTTPREQUEST
>
>
>
> Exception non g�r�e : IBatisNet.Common.Exceptions.IBatisNetException:
> Error
> executing query 'setHTTPRequest' for insert. Cause: Le format de la
> cha�ne
> d'entr�e est incorrect. ---> System.FormatException: Le format de la
> cha�ne
> d'entr�e est
>
> incorrect.
>
> at System.Number.ParseDecimal(String s, NumberStyles style,
> NumberFormatInfoinfo)
>
> at System.Decimal.Parse(String s, NumberStyles style,
> IFormatProvider
> provider)
>
> at System.Convert.ToDecimal(String value, IFormatProvider
> provider)
>
> at System.String.System.IConvertible.ToDecimal(IFormatProvider
> provider)
>
> at System.Convert.ChangeType(Object value, Type conversionType,
> IFormatProvider provider) >>
>
>
>
> Do anyone one of you know what I am doing wrong?
>
>
>
> Thanks a lot for your help,
>
>
>
> Regards
>
>
>
> Jean-Guillaume LALANNE
>
>
>
>
>
>