My Nifi flow looks like this captureChangeMysql -> TransformToFlatJSON -> ConvertJSONToSQL then I use putsql to store in DB.
I wanted to check how the sql looks like, So I used putfile processor after convertJSONToSQL and I see the SQL like this.
DELETE FROM dummy WHERE id = ? AND feature_name = ? AND state = ? AND tenant_id = ?
I am trying to understand why the SQL is getting question marks instead of showing original values ?
When I print the transformToFlatJson, it looks good.
{"id":1,"feature_name":"fff","state":16,"tenant_id":"abc123"}]
my expectation is the above delete should be like
DELETE FROM dummy WHERE id = 1 AND feature_name = 'fff' AND state = 16 AND tenant_id ='abc123'
And the delete is not able to delete any record from the destination table.
Can someone please help me understand how why the ? (question marks) and why the delete is not hapenning even though there is a matching record to delete..
The ConvertJSONToSQL processor stores actual values in attributes and keeps generated sql with ? placeholders.
You can find all the parameters in sql.args.N.value attributes of the same flowfile.
Check the Writes Attributes section in ConvertJSONToSQL documentation
To run actual delete statement you have to execute PutSql processor right after ConvertJSONToSQL