ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel Henrique Ferreira e Silva <dhsi...@gmail.com>
Subject Re: Get return value of stored procedure
Date Mon, 27 Jun 2005 17:02:37 GMT
Bing,

You didn't have success using java.lang.Integer because it is
immutable as is String class.
Use, for that situation, mutable classes, like Clinton said, the Map
implementations.
Just read carefully Clinton's e-mails and you'll see that he had
stated that previously.

Hope that helped,
Daniel Silva.

On 6/26/05, Bing Qiao <qiaobing@gmail.com> wrote:
> Dear Clinton,
> 
> Really appreciate your help, especially after I finally got it right
> from your last email.
> 
> sqlmap (both inline and parameterMap worked):
> 
> <sqlMap namespace="TestSqlMap">
> 
>     <parameterMap id="paraMap" class="">
>         <parameter property="value" jdbcType="INTEGER"
> javaType="java.lang.Integer" mode="OUT"/>
>     </parameterMap>
> 
>     <procedure id="callStoredProcedureA" parameterMap="paraMap">
>         {? = call TestReturnValue}
>     </procedure>
> 
>     <procedure id="callStoredProcedureB" parameterClass="java.util.HashMap">
>         {#value,mode=OUT,jdbcType=INTEGER# = call TestReturnValue}
>     </procedure>
> 
> </sqlMap>
> 
> ///////////////////////////////
> call from java:
> 
>  public static Integer callStoredProcedure() {
>         HashMap hm = new HashMap();
>         hm.put("value", new Integer(-1));
>         try {
>             getInstance().update("callStoredProcedureA", hm);
>         } catch (SQLException e) {
>             e.printStackTrace();
>         }
> 
>         return (Integer)hm.get("value");
>     }
> ///////////////////////////
> stored procedure:
> 
> CREATE PROCEDURE [dbo].[TestReturnValue] AS
> 
> UPDATE dbo.TEST
> SET USERID = USERID + 1
> WHERE USERNAME = 'test'
> 
> DECLARE @rtn  INT
> SELECT @rtn = USERID FROM TEST WHERE USERNAME = 'test'
> 
> RETURN @rtn
> GO
> 
> /////////////////////////
> 
> Another question is: can I use java.lang.Integer directly as a
> parameterClass instead of a Map?
> 
> I tried this but failed:
> sqlmap:
>     <procedure id="callStoredProcedureC" parameterClass="java.lang.Integer">
>         {#value,mode=OUT# = call TestReturnValue}
>     </procedure>
> //////////////////////
> call from java:
> public static Integer callStoredProcedureC() {
>         Integer integer = new Integer(-1);
>         try {
>             getInstance().update("callStoredProcedureC", integer);
>         } catch (SQLException e) {
>             e.printStackTrace();
>         }
> 
>         return integer;
>     }
> ///////////////////////
> exception:
> "  \n--- The error occurred in SqlMap/TestSqlMap.xml.  \n--- The error
> occurred while executing update procedure.  \n--- Check the {? = call
> TestReturnValue}.  \n--- Check the output parameters (register output
> parameters failed).  \n--- Cause: java.sql.SQLException:
> [Microsoft][SQLServer 2000 Driver for JDBC]The specified SQL type is
> not supported by this driver."
> ///////////////////////
> 
> And what about the raiserror, is it possible to get the severity level
> and state besides the error message or error code?
> 
> Thanks again,
> 
> Bing
> 
> 
> On 6/27/05, Clinton Begin <clinton.begin@gmail.com> wrote:
> >
> > Don't give up Bing!
> >
> > We'll get it eventually.  That error actually might just be a result of me
> > suggesting using a HashMap.  Maps are not typed, so the driver is probably
> > freaking because JDBC type in this case is being detected as simply
> > "Object". You'll want to specify a JDBC type explicitly (like you would if
> > it were JDBC)
> >
> > {#value,mode=OUT,jdbcType=NUMERIC# = call TestReturnValue}
> >
> > If NUMERIC doesn't work, try DECIMAL or INTEGER.
> >
> > Cheers,
> >
> > Clinton
> >
> > On 6/26/05, Bing Qiao <qiaobing@gmail.com> wrote:
> > > Hi Clinton
> > >
> > > Thanks for you help.
> > >
> > > Here is waht I did:
> > >
> > > sqlmap:
> > >
> > >     <procedure id="callStoredProcedure"
> > parameterClass="java.util.HashMap">
> > >         {#value,mode=OUT# = call TestReturnValue}
> > >     </procedure>
> > >
> > > call from java:
> > >
> > >     public static Integer callStoredProcedure() {
> > >         HashMap hm = new HashMap();
> > >         hm.put("value", new Integer(-1));
> > >         try {
> > >             getInstance().update("callStoredProcedure",
> > hm);
> > >         } catch (SQLException e) {
> > >             e.printStackTrace();
> > >         }
> > >
> > > Stored procedure:
> > > CREATE PROCEDURE [dbo].[TestReturnValue] AS
> > >
> > > UPDATE dbo.TEST
> > > SET USERID = USERID + 1
> > > WHERE USERNAME = 'test'
> > >
> > > RETURN -17
> > > GO
> > >
> > >
> > > The exception I got:
> > >
> > > "  \n--- The error occurred in SqlMap/TestSqlMap.xml.  \n--- The error
> > > occurred while executing update procedure.  \n--- Check the {? = call
> > > TestReturnValue}.  \n--- Check the output parameters (register output
> > > parameters failed).  \n--- Cause: java.sql.SQLException:
> > > [Microsoft][SQLServer 2000 Driver for JDBC]The specified SQL type is
> > > not supported by this driver."
> > >
> > > Having spent too much time on this issue, I have to give up If this
> > > "not supported by this driver" is the real cause. It's impossible to
> > > purssuade our DBA to use any JDBC drivers other than MS's.
> > >
> > >
> > > Regards
> > >
> > > Bing
> > >
> > > On 6/26/05, Clinton Begin <clinton.begin@gmail.com> wrote:
> > > > Hi Bing,
> > > >
> > > > Yes, your syntax is correct.
> > > >
> > > > You should be able to do something like this:
> > > >
> > > > <procedure id="myProc" parameterClass="map" >
> > > >     {#value,mode=OUT# = call procedure_name}
> > > > </procedure>
> > > >
> > > > I'll asume this is an update procedure, so no result class is needed.
> > > >
> > > > Notice the parameter class is a 'map", which means any instance of Map
> > (like
> > > > HashMap for example).
> > > >
> > > > It is important to use a mutable type for your parameter when using OUT
> > > > params.  This is because OUT params are passed back in the parameter
> > object,
> > > > not as a result object.
> > > >
> > > > So at the end of this call:
> > > >
> > > > Map myParam = new HashMap();
> > > > myParam.put("value", 0); //default value
> > > > sqlMapClient.update("myProc", myParam);
> > > > myParam.get("value"); // value should be updated with OUT param.
> > > >
> > > > You can (and should) use a JavaBean as your parameter class instead of
a
> > Map
> > > > if you like.
> > > >
> > > > Hope that helps,
> > > >
> > > > Clinton
> > > >
> > > >
> > > > On 6/26/05, Bing Qiao <qiaobing@gmail.com> wrote:
> > > > > From sun JDBC documentation:
> > > > >
> > > > > The syntax for a procedure that returns a result parameter is:
> > > > >
> > > > > {? = call procedure_name[(?, ?, ...)]}
> > > > >
> > > > > Bing
> > > > >
> > > > > On 6/26/05, Larry Meadors < larry.meadors@gmail.com > wrote:
> > > > > > I am not sure I understand. Are you trying to call a procedure
with
> > a
> > > > > > single OUT parameter?
> > > > > >
> > > > > > If so, the syntax should be:
> > > > > >
> > > > > > <procedure id="BlahOut" parameterMap="BlahParm"
> > > > > > resultClass="java.util.HashMap" >
> > > > > >  { call blah(?) }
> > > > > > </procedure>
> > > > > >
> > > > > > and the parameter map should look like this:
> > > > > >
> > > > > > <parameterMap id="BlahParm" class=" java.util.Map">
> > > > > >  <parameter property="a" mode="OUT" />
> > > > > > </parameterMap>
> > > > > >
> > > > > > Larry
> > > > > >
> > > > > > On 6/26/05, Bing Qiao < qiaobing@gmail.com> wrote:
> > > > > > > Hi Clinton
> > > > > > >
> > > > > > > I tried something like this:
> > > > > > > {? = call procedure_name}
> > > > > > >
> > > > > > > The ? is defined as an out parameter mapped onto a member
(String,
> > > > > > > int) of a HashMap object (I tried a javabean later as well)
in the
> > > > > > > parameter map.
> > > > > > >
> > > > > > > I didn't succeed with that.
> > > > > > >
> > > > > > > I tried to use inline parameter map too:
> > > > > > > {#value:OUT# = call procedure_name}
> > > > > > > by passing an Integer as parameter to queryForObject or
other
> > sqlmap
> > > > methods.
> > > > > > >
> > > > > > > It didn't work either.
> > > > > > >
> > > > > > > Could you please advice what I need to do?
> > > > > > >
> > > > > > > I'm new to java programming. I don't have much JDBC experience
> > > > > > > and sqlmap is the only database access tool I ever used
in a
> > > > > > > production project which I inherited from other developers.
> > > > > > >
> > > > > > > I appreciate you help, thanks very much!
> > > > > > >
> > > > > > > On 6/25/05, Clinton Begin <clinton.begin@gmail.com>
wrote:
> > > > > > > > Hi Bing,
> > > > > > > >
> > > > > > > > How do you get the return code using JDBC?
> > > > > > > >
> > > > > > > > Cheers,
> > > > > > > > Clinton
> > > > > > > >
> > > > > > > >
> > > > > > > > On 6/23/05, Bing Qiao < qiaobing@gmail.com>
wrote:
> > > > > > > > > Help please!
> > > > > > > > >
> > > > > > > > > I have had several talks with our DBA who is
so skeptical of
> > > > iBATIS
> > > > > > > > > and vow to force us to go back to jdbc. One of
his arguments
> > is
> > > > that
> > > > > > > > > the return code from stored procedure can not
be retrieved by
> > > > sqlmap.
> > > > > > > > >
> > > > > > > > > I tried all I could but no luck till now. Any
help please!
> > > > > > > > >
> > > > > > > > > Thanks a lot!
> > > > > > > > >
> > > > > > > > > On 6/23/05, Bing Qiao <qiaobing@gmail.com>
wrote:
> > > > > > > > > > Hi,
> > > > > > > > > >
> > > > > > > > > > What do I need to add to the sqlmap to get
the return value
> > (for
> > > > > > > > > > example, return -9) of stored procedure?
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > Thanks very much!
> > > > > > > > > >
> > > > > > > > > > bqiao
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Mime
View raw message