How to call stored procedure from Mule 3.5 with new Database endpoint

Question

I was testing Mule's new Database connector of Mule 3.5 with Stored Procedure ... (Reference :- http://www.mulesoft.org/documentation/display/current/Database+Connector ).. I had a following Stored Procedure :-

ALTER PROCEDURE [dbo].[sp_retrieveData]
 @Id  int
AS
Select * from getData Where ID = @Id

Which is working fine in Mule's old JDBC connector ... I used to call the stored procedure from the JDBC endpoint using the following syntax :- <jdbc-ee:query key="RetriveQuery" value="CALL sp_retrieveData(${Id})"/> Which worked fine ... But now the issue is in the Mule 3.5 new Database end point I am unable to call the same Stored Procedure ... My Mule configuration is :-

<db:stored-procedure config-ref="Generic_Database_Configuration" doc:name="Database">
   <db:parameterized-query><![CDATA[CALL sp_retrieveData(58)]]></db:parameterized-query>
   <db:in-param name="Id" type="INTEGER" value="58"/>
  </db:stored-procedure>

So, My question is how can I call Stored Procedure with the new DB endpoint ... Had anyone tried it ???... Please help ...

Update:- The following exception I get :-

Exception stack is:
1. The index 1 is out of range. (com.microsoft.sqlserver.jdbc.SQLServerException)
  com.microsoft.sqlserver.jdbc.SQLServerException:170 (null)
2. The index 1 is out of range. (com.microsoft.sqlserver.jdbc.SQLServerException). Message payload is of type: String (org.mule.api.MessagingException)
  org.mule.module.db.internal.processor.AbstractDbMessageProcessor:81 (http://www.mulesoft.org/docs/site/current3/apidocs/org/mule/api/MessagingException.html)
--------------------------------------------------------------------------------
Root Exception stack trace:
com.microsoft.sqlserver.jdbc.SQLServerException: The index 1 is out of range.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:170)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setterGetParam(SQLServerPreparedStatement.java:698)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setObject(SQLServerPreparedStatement.java:928)
    + 3 more (set debug level logging or '-Dmule.verbose.exceptions=true' for everything)
********************************************************************************

Answer 1

IMO the best documentation for anything are its tests. The stored procedure tests for the DB module are here: https://github.com/mulesoft/mule/tree/mule-3.5.0/modules/db/src/test/resources/integration/storedprocedure

Based on these, the following should work:

<db:stored-procedure config-ref="Generic_Database_Configuration">
  <db:parameterized-query>{ call sp_retrieveData(:Id) }</db:parameterized-query>
  <db:in-param name="Id" value="58" />
</db:stored-procedure>

(I haven't tested it though)

Answer 2

Sample Stored procedure call:

<db:stored-procedure config-ref="Oracle_Configuration1" doc:name="Database">
    <db:parameterized-query><![CDATA[
    {
      call apps.create_sales_Order(:p_header_rec_oper, :P_order_number, :P_ordered_date, :P_line_id, :p_flow_Status_code, :P_return_status)
    }
    ]]>
    </db:parameterized-query>
    <db:in-param name="p_header_rec_oper" value="CREATE"/>
    <db:out-param name="P_order_number" type="INTEGER"/>
    <db:out-param name="P_ordered_date" type="DATE"/>
    <db:out-param name="P_line_id" type="VARCHAR"/>
    <db:out-param name="p_flow_Status_code" type="VARCHAR"/>
    <db:out-param name="P_return_status" type="VARCHAR"/>
</db:stored-procedure>