0

I have created a simple Data Service using wso2 dss as follows.

Query : SELECT id, key, value, description FROM dbo.configs where key like '%:filter%'

Final XML : 

<query id="select_all_configs_like_query" useConfig="default">
<sql>SELECT id, key, value, description FROM dbo.configs where key like '%:filter%' </sql>
<result element="configsCollection" rowName="configs">
    <element column="id" name="id" xsdType="xs:integer"/>
    <element column="key" name="key" xsdType="xs:string"/>
    <element column="value" name="value" xsdType="xs:string"/>
    <element column="description" name="description" xsdType="xs:string"/>
</result>
<param name="filter" sqlType="QUERY_STRING"/>
</query>
<operation name="select_all_configs_like_operation">
    <call-query href="select_all_configs_like_query">
        <with-param name="filter" query-param="filter"/>
    </call-query>
</operation>

When I invoke the service it responds with an empty response. No Error on the console. Once I checked the database history, query is recorded as below

SELECT id, key, value, description FROM dbo.configs where key like '%:filter%'

Looks like parameter is not assigned to the actual query. Any Help?

Yasas Karunarathna
  • 361
  • 2
  • 9

1 Answers1

0

You can use the Concat function within the SQL statement so for example CONCAT('%',:filter,'%')

user3758298
  • 380
  • 5
  • 17
  • Thanks for the reply. SELECT id, key, value, description FROM dbo.configs where key like CONCAT('%',:filter,'%') doesn't work. – Yasas Karunarathna Aug 14 '14 at 11:56
  • Have you tried CONCAT(:filter,'%') or CONCAT('%',:filter)? I'm using DSS version 3.2.1 and CONCAT(:filter,'%') works for me what version are you on? – user3758298 Aug 14 '14 at 14:09