0

I am developing simple dss service in which i am retrieving customer details based on some input parameters.

In the output, i am displaying customer_id,first_name,last_name,mobile_number,email and status of customer.

Now if any of the above 6 output field are blank in the database entry(i.e.if the mobile number of customer is not entered in DB) and if i try retrive that customer detail via dss, i dont get the customer details in the output.

Only if there is a value in the above 6 output field in the database, then only that customer's detail is retrieved.

I tried putting the output field as optional, but that didnt help. Also tried giving default value to outpu field, but that also didn't help

Following is my data service.

<data name="CustomerStatusManagementDssdirectconsole">
   <config id="ildb">
      <property name="carbon_datasource_name">il_database</property>
   </config>
   <query id="select_customer_details_by_any_parameter" useConfig="ildb">
      <sql>select * from ildb_schema.customer_detail where identifier like :cust_id and first_name like :firstname and last_name like :lastname and mobile_number like :mobilenumber and email like :email</sql>
      <result element="Customers" rowName="customer">
         <element column="identifier" name="cid" xsdType="xs:string"/>
         <element column="first_name" name="first_name" xsdType="xs:string"/>
         <element column="last_name" name="last_name" xsdType="xs:string"/>
         <element column="mobile_number" name="mobile_number" xsdType="xs:string"/>
         <element column="user_status" name="user_status" xsdType="xs:string"/>
         <element column="email" name="email" xsdType="xs:string"/>
      </result>
      <param name="cust_id" sqlType="STRING"/>
      <param name="firstname" sqlType="STRING"/>
      <param name="lastname" sqlType="STRING"/>
      <param name="mobilenumber" sqlType="STRING"/>
      <param name="email" sqlType="STRING"/>
   </query>
    <operation name="select_customer_details_by_any_parameter_operation">
      <call-query href="select_customer_details_by_any_parameter">
         <with-param name="cust_id" query-param="identifier"/>
         <with-param name="firstname" query-param="first_name"/>
         <with-param name="lastname" query-param="last_name"/>
         <with-param name="mobilenumber" query-param="mobile_number"/>
         <with-param name="email" query-param="email"/>
      </call-query>
   </operation>
   </data>

Eg. If there is a customer with customer_id=110,first_name=abc,last_name=xyz,email=abc@some.com,mobile=<<blank>>,status=active

And if i retrieve the above customer via dss in try the service option, i get following output with no details of customer

<Customers xmlns="http://ws.wso2.org/dataservice"/>
mihir S
  • 617
  • 3
  • 8
  • 23

1 Answers1

0

The query you wrote requires that all parameters be present. Perhaps it could have been named "select_customer_details_by_every_parameter" instead of "select_customer_details_by_any_parameter". :)

In WSO2 DDS, You can define multiple data service queries for each datasource, each with different parameters. It appears that you only have one query, and one operation that takes every parameter. I recommend writing multiple data service queries for your datasource, including one with only the primary key fields in the where clause. That should allow you to retrieve the rows that have other empty columns.

Cheers, Colin

Colinr
  • 201
  • 1
  • 3