MS SQL Server stored proc from XML

[Bondi]

Hi,
Anyone know how to call a MS SQL Server stored proc with parameters from an XML mapping?

I have the Java set up correctly in the subclass of StoredProcedure constructor to pass in 2 parameters and return 1:

declareParameter(new SqlParameter(PARAM_ONE, Types.VARCHAR));
declareParameter(new SqlParameter(PARAM_TWO, Types.VARCHAR));
declareParameter(new SqlReturnResultSet("rs", new RowMapper()));

and then in the load method...

Map inParams = new HashMap();
inParams.put(PARAM_ONE, 'foo');
inParams.put(PARAM_TWO, '123123');
Map outParams;
try {
outParams = execute(inParams);
} catch (DataAccessException e) {
throw new myException(e);
}
return (List) outParams.get("rs");

and the XML is...

<bean id="mybean" class="myDAO">
<constructor-arg>
<ref bean="myDataSource"/>
</constructor-arg>
<constructor-arg>
<value>pMyStoredProc</value>
</constructor-arg>
</bean>

But I always get BadSqlGrammarException; bad SQL grammar [pMyStoredProc]; [Microsoft][SQLServer 2000 Driver for JDBC]Invalid parameter binding(s)

I can't find how to define the stored proc name in the XML to accept 2 parameters for SQL Server (the ? wildcards for Oracle don't work)

.... anyone done this before?

thanks heaps

[trisberg]

Can you post the code for your stored procedure?

[Bondi]

thanks for replying Thomas,
however we've just got it working... syntax as below for anyone who has the same problem
To call MS SQL Server stored proc with 2 params use

{call pMyStoredProc(?,?)} ... as follows

<bean id="mybean" class="myDAO">
<constructor-arg>
<ref bean="myDataSource"/>
</constructor-arg>
<constructor-arg>
<value>{call pMyStoredProc(?,?)}</value>
</constructor-arg>
</bean>