WSO2 DSS How to use SQL input parameters in multiple places in SQL statement

Question

I have a a service getting 3 parameters and passing it to the below SQL:

<query id="getPratica" useConfig="is03">
  <sql>select * from VIAGGIFMM.PFPA00 where PATIP = ? and PAAPR = ? and PANPR = ?</sql>

<param name="PATIP" ordinal="1" sqlType="STRING"/>
  <param name="PAAPR" ordinal="2" sqlType="NUMERIC"/>
  <param name="PANPR" ordinal="3" sqlType="NUMERIC"/>

<operation name="getPratica">
  <call-query href="getPratica">
     <with-param name="PATIP" query-param="PATIP"/>
     <with-param name="PAAPR" query-param="PAAPR"/>
     <with-param name="PANPR" query-param="PANPR"/>
  </call-query>

<resource method="GET" path="getPratica/{PATIP}/{PAAPR}/{PANPR}">
  <call-query href="getPratica">
     <with-param name="PATIP" query-param="PATIP"/>
     <with-param name="PAAPR" query-param="PAAPR"/>
     <with-param name="PANPR" query-param="PANPR"/>
  </call-query>

Now the SQL provider changed the SQL statement as below:

... WHERE APPTIP = ? AND APPANP = ? AND APPNUP = ?
... WHERE APPTIP = ? AND APPANP = ? AND APPNUP = ?  (yes, the same names as the above)
... WHERE A.TIPR60 = ? AND A.ANPR60 = ? AND A.NUPR60 = ?

Little explanation: The new SQL use 3 times the same 3 parameters as my original DSS service.

I wish to continue to pass 3 parameters to my DSS service but after two days still I'm not able to adapt my DSS service.

Also I discovered that my SQL backend doesn't can store values in variables.

Please anyone can help me ?

Ivano C.

Answer 1

You can use named parameters for the input params as in here instead of using ?.

Answer 2

Instead of using question mark ?, you can use named parameter starting with colon : like. :PATIP. For You example it will be something like below:

... WHERE APPTIP = :PATIP AND APPANP = :PANPR AND APPNUP = :PANPR  
... WHERE A.TIPR60 = :PATIP AND A.ANPR60 = :PANPR AND A.NUPR60 = :PANPR