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 Tue, 28 Jun 2005 13:22:28 GMT
HAHAHAHA!

Clinton, you made me laugh hard!

"Undocumented stuff" is documented in the wiki! He he! ;-)

Well, at least it is a better situation than Oracle's undocumented parameters.

Daniel Silva.

On 6/28/05, Clinton Begin <clinton.begin@gmail.com> wrote:
> 
>  The syntax I used is the newer one...and yes it's undocumented right
> now....
>  
>  You can find it in the "undocumented stuff" section of the wiki.  ;-)
>  
>  Clinton
> 
> 
> On 6/28/05, Bing Qiao <qiaobing@gmail.com> wrote:
> > 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