How to Externalize Query in WSO2 ESB

Question

I have created ESB proxy using WSO2 ESB 4.8.1

Currently DBLookup mediator contains following sql

<sql>select A, B, C from cis-dev.table1 where ... </sql>

Since, cis-dev schema is specific to the dev environment, I need to store this query outside the proxy in order to migrate proxy service to QA and PROD environments.

Is there a way to accomplish this in WSO2 ESB?

Thanks,

Dharshana Dharshana · Accepted Answer · 2014-08-20T11:43:10

There is no direct way store query outside and invoke it as per the environment. But you can parameterize the query and pass parameters with the request to alter values in the query . See the sample below

<dblookup>
   <connection>
       <pool>
           <driver>org.apache.derby.jdbc.ClientDriver</driver>
           <url>jdbc:derby://localhost:1527/esbdb;user=esb;password=esb</url>
           <user>esb</user>
           <password>esb</password>
       </pool>
   </connection>
   <statement>
       <sql>select * from company where name =?</sql>
       <parameter expression="//m0:getQuote/m0:request/m0:symbol"
                  xmlns:m0="http://services.samples" type="VARCHAR"/>
       <result name="company_id" column="id"/>
       <result name="company_price_0" column="price" rowIndex="2"/>
       <result name="company_price_1" column="price" rowIndex="4"/>
   </statement>
</dblookup>

How to Externalize Query in WSO2 ESB

1 Answers