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
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> > >
> >
> >
>
>
