Null is converted to empty string when writing to database.

Question

While updating a table from the process model using WriteToDatastoreEntity node. Even though CDT fields are null, it is updating empty string value in the table. How to update NULL in the table when data is null? 
 
 Thanks, 
 Varsha

Philip Snyman · Answer

An easy workaround to query entities with empty ( null ) text fields is to use the following query filter: 
 a!queryFilter(
 field: "name",
 operator: "in",
 value: {"", null}
)

bradc0001 · Answer

Hi Varsha, 
 From my experience, Appian will always write a blank value instead of null of data type Text. The workarounds I have used in the past are: 
 
 Following the writeToDataStoreEntity node, use a Query Database node with a statement such as UPDATE table SET col1 = NULL WHERE col1 = '' 
 If this particular value is always blank/null, you can exclude it from your CDT so that Appian does not replace is with a blank value. 
 
 I realize these solutions are less than ideal, and I do wish Appian would treat text fields the same as other data types and retain a NULL value in the database. I am curious if anyone else has come up with a better solution. 
 Brad

mitchellg · Answer

In addition to the alternatives suggested, you could enforce the logic on the SQL side by always excluding empty string values from the column.

Chris · Answer

Another option would be to utilize a trigger on the DB table that converts empty values to null, which can run automatically after each INSERT or UPDATE.

Null is converted to empty string when writing to database.

Top Replies