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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 < [EMAIL PROTECTED] > 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 < [EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> wrote:
> > > > > > > > Hi Bing,
> > > > > > > >
> > > > > > > > How do you get the return code using JDBC?
> > > > > > > >
> > > > > > > > Cheers,
> > > > > > > > Clinton
> > > > > > > >
> > > > > > > >
> > > > > > > > On 6/23/05, Bing Qiao < [EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
