Announcement Announcement Module
Collapse
No announcement yet.
MS SQL Server stored proc from XML Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • MS SQL Server stored proc from XML

    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

  • #2
    Can you post the code for your stored procedure?

    Comment


    • #3
      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>

      Comment

      Working...
      X