ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Clinton Begin <clinton.be...@gmail.com>
Subject Re: Get return value of stored procedure
Date Tue, 28 Jun 2005 13:12:13 GMT
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