How do I set an attribute to null that is currently set to an empty string ''

I'm reading in a file using EvaluateJsonPath.

Some attributes values are set as empty string.

This is very problematic when I'm dealing with DATES.

If I use the TO_DATE on my call to Insert or Update and the DATE is '' then NiFi fails because as example

sql.args.2.value is '', which cannot be converted to a timestamp.

The database is setup to allow for null values on the field.

How is one to handle DATEs when the value may be empty and null is valid for the entry when using NiFi to send the data?

============================

updates

I created a test table with just 3 columns. id, TEST_DATE, TEST_TIMESTAMP.

Using NiFi processor 'PutSQL' I am able to insert 1 or both of the columns when a valid value is present in the data read in.

The issue is when the data does not contain the value for a date and NiFi sees it as an empty ''. When the processor attempts to make the call with an empty value '' that is where the message comes from.

Is there any way to conditionally check within the SQL INSERT statement the value of the parameter similar to NVL(?,NULL) ?

The solution when dealing with creating sql.args.#.value and null values is as follow.

On the UpdateAttributes processor where one would define the sql.args.#.value, you create a RULE under the ADVANCED section.

1. Create a rule to check if the attribute that is used to set the value is not empty ${arg:isEmpty():not()}
2. Create the sql.args.#.value and set to ${arg}

This will only create the sql.args.#.value if the value is not empty. On the processor that reads in the ? (sql.args.#.values) it will use null in place of the '' empty string value. This was very problematic when dealing with the ORACLE database field types DATE and TIMESTAMP.

Yes I also verified that the SQL works correctly when you have many values and a null is being used in place of defining the sql.args.#.value.

So as an example if you have 6 fields/values to set and the #4 field (sql.args.4.value) we don't set because the value is empty '' the statement will still work correctly for the other fields.

sql.args.1.value = arg1
sql.args.2.value = arg2
sql.args.3.value = arg3
sql.args.5.value = arg5
sql.args.6.value = arg6

INSERT INTO TABLEXYZ (
 COL1,
 COL2,
 COL3,
 COL4,
 COL5,
 COL6)
VALUES (
 ?,
 ?,
 ?,
 ?,
 ?,
 ?
)

I don't know how helpful this might be for your case, had a similar problem and this worked like a charm!

NULLIF(${YourField:isEmpty():not():ifElse(${YourField}, -1)}, -1)

In my case I had an Integer value so I used -1 which was a value that my specific dataset would never contain. Be careful as you have to respect the data type of your field else SQL will throw an error. I hope that helps!