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:[EMAIL PROTECTED] > 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- > [EMAIL PROTECTED] > [mailto:ibatis-user-cs-return-160-ho.jglalanne=cma- > [EMAIL PROTECTED] > 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:[EMAIL PROTECTED] > > 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 <[EMAIL PROTECTED]> 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 : > > > > > [EMAIL PROTECTED] > > > > > [mailto:ibatis-user-cs-return-157-ho.jglalanne=cma- > > [EMAIL PROTECTED] > > > 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 <[EMAIL PROTECTED]> 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 > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
