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

score 1 · Accepted Answer · answered Nov 03 '22 at 11:08

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

answered Nov 03 '22 at 11:08

sanoJ

2,935
2
8
18

Thanks it works for me , but it's not aligned with the documentation using the '''dynamic SQL Query''' where the "WHERE" statement shouldn't exist in the path https://apim.docs.wso2.com/en/latest/reference/synapse-properties/data-services/sample-queries/#defining-a-dynamic-sql-query – Oussama Nairi Nov 03 '22 at 11:29
1

The doc says `you must specify the required SQL query statements (e.g., with WHERE clause) using the QUERY_STRING data type. These statements will be directed to the final SQL query during runtime.` ie whatever we pass as the `filterQuery` will directed to the final SQL query. Therefore according to your requirement, you will need to pass the complete query. – sanoJ Nov 03 '22 at 12:45

SQL Multi Where Conditions using QUERY_STRING in WSO2 Dataservice

1 Answers1