Receiving the array throw Wso2Dss and Store into PostgreSQL Database

Question

My client sending the Data in array format like {1,2,3}

i am receiving in array format throw Wso2 DSS and insert into the PostgreSql DataBase.

My WSo2 DSS Insert query is:-

<data name="SalArray">
   <config id="default">

   <query id="Insert" useConfig="default">
      <sql>insert into sal_emp(name,pay_by_quarter,schedule) values(?,?,?)</sql>
      <param name="name" ordinal="1" sqlType="STRING"/>
      <param name="pay_by_quarter" ordinal="2" paramType="ARRAY" sqlType="INTEGER"/>
      <param name="schedule" ordinal="3" paramType="ARRAY" sqlType="STRING"/>
   </query>

   <operation name="Insert">
      <call-query href="Insert">
         <with-param name="name" query-param="name"/>
         <with-param name="pay_by_quarter" query-param="pay_by_quarter"/>
         <with-param name="schedule" query-param="schedule"/>
      </call-query>
   </operation>
</data>

While i am inserting the data i am getting error

Error:-
Current Request Name: Insert
Current Params: {schedule={'abc'}, name=anil, pay_by_quarter={1,2,3}}
Nested Exception:-
java.lang.NumberFormatException: For input string: "{1,2,3}"

Someone help me to solve this.

Answer 1

By Looking at your SQL query .. you have a simple insert. Therefore you do not need array type as an input type for pay_by_quarter,schedule. Since, your SQL query only expect one value for pay_by_quarter and schedule you don't need array support for your query. So this should work if you remove paramType="ARRAY" as SQL query it self is not needing an array.

If you want an example with ARRAY for SQL please refer this. You can only use this Array feature for SQL syntax like "in" where you can give multiple values

ie

SELECT * FROM Customers
WHERE City IN ('Paris','London');

SELECT * FROM Customers
WHERE City IN (:Cities);

Answer 2

Seems the array is sent incorrectly. Refer this documentation on how to properly send an array type in a soap message.