SQL Multi Where Conditions using QUERY_STRING in WSO2 Dataservice

Question

I have SQL Select statement with optional filter that i would like to exposed as an API . I used the dynamic SQL Query to get the parameters to filter with like this :

    <query id="selectprofile" useConfig="default">
    <sql>SELECT BADLOGINS,EMAIL,FRAMED_ROUTE,FULLNAME FROM USERS :filterQuery </sql>
<result outputType="json">{
     "accounts":  
              { "account" :
[{
"BADLOGINS":"$BADLOGINS",
"EMAIL":"$EMAIL",
"FRAMED_ROUTE":"$FRAMED_ROUTE",
"FULLNAME":"$FULLNAME"
}]
}

}</result>
 <param name="filterQuery" sqlType="QUERY_STRING"/>
  </query>

but it didn't work for me and it give me an error

DS Code: INCOMPATIBLE_PARAMETERS_ERROR\nNested Exception:-\njavax.xml.stream.XMLStreamException: DS Fault Message: Error in 'Query.extractParams', cannot find query param with name:filterQuery\nDS Code:

i invoked the API like this with get method :

localhost:8290/services/MYSQLDataService/selectprofile?FULLNAME="AHMED"

NB : I followed this documentation https://apim.docs.wso2.com/en/latest/reference/synapse-properties/data-services/sample-queries/#defining-a-dynamic-sql-query

Answer 1

The resource path should only contain the resource name if you need to use a query param. In your case, the resource element should be as follows,

<resource method="GET" path="selectprofile">
  <call-query href="selectprofile">
     <with-param name="filterQuery" query-param="filterQuery"/>
  </call-query>
</resource>

According to the above example, when invoking the data service you will need to pass a query parameter named filterQuery. This parameter should contain the complete query you need to use(It seems you have missed the WHERE clause).

localhost:8290/services/MYSQLDataService/selectprofile?filterQuery=WHERE FULLNAME="AHMED"

For more information check Exposing Data as a REST Resource