ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bing Qiao <qiaob...@gmail.com>
Subject Re: Get return value of stored procedure
Date Sun, 26 Jun 2005 21:35:06 GMT
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