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 Tue, 28 Jun 2005 07:37:34 GMT
I realized that too. Thanks.

BTW, the inline parameter format given by Clinton:
"{#value,mode=OUT,jdbcType=INTEGER# = call TestReturnValue}"

is not the same as that in the online document:

"insert into PRODUCT (PRD_ID, PRD_DESCRIPTION)
values (#id:NUMERIC#, #description:VARCHAR#);"

Where can I find the specification of the former one?

Many thanks!

Bing

On 6/27/05, Daniel Henrique Ferreira e Silva <dhsilva@gmail.com> wrote:
> 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