wso2 dss mysql error

Question

I am getting this error while I am trying to test the data service on the wso2 dss

DS Fault Message: Error in 'SQLQuery.processStoredProcQuery'DS Code: DATABASE_ERRORSource Data Service:-Name: CustomerDSLocation: \CustomerDS-1.0.0.dbsDescription: N/ADefault Namespace: http://ws.wso2.org/dataserviceCurrent Request Name: op1Current Params: {Name=?, NID=?}Nested Exception:-DS Fault Message: Error in 'createProcessedPreparedStatement'DS Code: UNKNOWN_ERRORNested Exception:-java.sql.SQLException: Parameter index of 3 is out of range (1, 0)

any suggestions?

edited: The bds file is as follows, this is a sample case mentioned in the wso2 documentation.

<data name="CustomerDS">                  
   <config id="default">                              
      <property name="org.wso2.ws.dataservice.driver">com.mysql.jdbc.Driver</property>                              
      <property name="org.wso2.ws.dataservice.protocol">jdbc:mysql://localhost:3306/CustomersDatabase</property>                              
      <property name="org.wso2.ws.dataservice.user">root</property>                              
      <property name="org.wso2.ws.dataservice.password">root</property>               
   </config>               
   <query id="q1" useConfig="default">                              
      <sql>call getCustomer(?,?,?,?)</sql>                              
      <result element="Entries" rowName="Entry">                                             
         <element name="Flag" column="Flag" xsdType="xs:integer" optional="true" />                                             
         <element name="Customer" column="Customer" xsdType="xs:string" optional="true" />                              
      </result>                              
      <param name="NID" sqlType="STRING" ordinal="1" />                              
      <param name="Name" sqlType="STRING" ordinal="2" />                              
      <param name="Flag" sqlType="INTEGER" type="OUT" ordinal="3" />                              
      <param name="Customer" sqlType="STRING" type="OUT" ordinal="4" />               
   </query>               
   <operation name="op1">                              
      <call-query href="q1">                                             
         <with-param name="NID" query-param="NID" />                                             
         <with-param name="Name" query-param="Name" />                              
      </call-query>               
   </operation>
</data>

WHAT WORKED FOR ME!!

I found the reference code at http://svn.wso2.org/repos/wso2/people/kasun/wso2con_2013/starbucks_2.0/dss/StarbucksDataService.dbs

and the procedure call that is finally working for me is (which changes in procedure itself)

    <data name="CustomerDS">                  
   <config id="default">                              
      <property name="org.wso2.ws.dataservice.driver">com.mysql.jdbc.Driver</property>                              
      <property name="org.wso2.ws.dataservice.protocol">jdbc:mysql://localhost:3306/CustomersDatabase</property>                              
      <property name="org.wso2.ws.dataservice.user">root</property>                              
      <property name="org.wso2.ws.dataservice.password">root</property>               
   </config>               
   <query id="q1" useConfig="default">                              
      <sql>call getCustomer(?,?)</sql>                              
      <result element="Entries" rowName="Entry">                                             
         <element name="Flag" column="Flag" xsdType="xs:integer" optional="true" />                                             
         <element name="Customer" column="Customer" xsdType="xs:string" optional="true" />                              
      </result>                              
      <param name="NID" sqlType="STRING" ordinal="1" />                              
      <param name="Name" sqlType="STRING" ordinal="2" />                              
      <param name="Flag" sqlType="INTEGER" type="OUT" ordinal="3" />                              
      <param name="Customer" sqlType="STRING" type="OUT" ordinal="4" />               
   </query>               
   <operation name="op1">                              
      <call-query href="q1">                                             
         <with-param name="NID" query-param="NID" />                                             
         <with-param name="Name" query-param="Name" />                              
      </call-query>               
   </operation>
</data>

And the tentative working procedure call for now is :

DELIMITER // 
CREATE procedure getCustomer(NID varchar(200),Name varchar(200))  
BEGIN 
DECLARE id varchar(200); 
DECLARE flag int;
SET Flag = 0;
SET id = CONCAT(NID, '_' ,  Name); 

INSERT INTO Customer(NID, Name, customerID) VALUES(NID, Name, id); 

select flag, customerid from customer where customerID = id;
END//

Answer 1

In the query q1 call getCustomer(?,?,?,?) takes 4 arguments and in the input mapping list there are only 2 input params (IN Only). This must be the reason for the this exception. Please refer this tutorial.