Inserting null to a database through WSO2 DSS
The WSO2 DSS server [1] which is currently integrated with the WSO2 EI server [2] provides the capability to implement data services that facilitate integration operations involving data stores. Among the number of functionalities available in the DSS server, the possibility of inserting a default value to an SQL query as explained in [3] is one of the features. This allows a default value to be automatically added to the query if a user has not entered a specific parameter value in a request.
However, since in the data service point of view “” or “null” are valid values for a parameter, if the above values are set for a particular parameter and
{NULL}is set as the default value, it will not set Null in the query. This blog explains an approach to insert Null as a value to an SQL query.
Consider the following payload as an example.
{
"request": {
"value0": "Val1",
"value1": 10,
"value2": 100,
"value3": "10.00",
"value4": "",
"value5": "Val2",
"value6": "Val3"
}
}If the expectation is to set NULL as the value for the “value4” parameter above, setting the default value to NULL will not insert NULL value to the SQL query because the value is set as “” for the above parameter.
The approach that can be used to insert NULL will be to check the length of the value. In the above example payload, since the length of value4 is equal to 0, a condition can be applied as follows in the DSS query to set the value as NULL.
IF(LENGTH(:value4)=0,NULL,:value4)The complete DSS query will be as follows.
<query id="insert_request" useConfig="default">
<sql>INSERT INTO request(value0,value1,value2,value3,value4,value5,value6) VALUES(:value0,:value1,:value2,:value3,IF(LENGTH(:value4)=0,NULL,:value4),:value5,:value6)</sql>
<param name="value0" sqlType="DOUBLE"/>
<param name="value1" sqlType="DOUBLE"/>
<param name="value2" sqlType="STRING"/>
<param name="value3" sqlType="DOUBLE"/>
<param name="value4" sqlType="STRING"/>
<param name="value5" sqlType="STRING"/>
<param name="value6" sqlType="STRING"/>
</query>[1]. https://docs.wso2.com/display/DSS351/About+Data+Services+Server
