0

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..

santhosh
  • 439
  • 8
  • 17
  • `?` are placeholders for [query parameters](https://stackoverflow.com/questions/3727688/what-does-a-question-mark-represent-in-sql-queries). They are used to avoid any overhead with special symbols in your values and to prevent SQL injection. – markalex Mar 22 '23 at 15:42
  • As for delete: try to replace `DELETE` with `SELECT *` and confirm that result exists for this exact parameters. – markalex Mar 22 '23 at 15:46
  • @markalex so, you mean we can never see the real SQL statement that is generated out of this processor ? (it always masks with question marks ) I need to replace the ? with the sql.args.N.values to frame the SQL statement to troubleshoot ? – santhosh Mar 22 '23 at 16:09
  • 1
    Yes, familiar to you sql with "inline" parameters never is generated. Alternatively, I believe some SQL clients could prompt you for values to be substituted instead of `?`. – markalex Mar 22 '23 at 16:15

1 Answers1

1

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

daggett
  • 26,404
  • 3
  • 40
  • 56
  • thanks for the reply. one tricky thing is these sql.args were present in the putsql data Provenance, and are not present in the actual convertJSONtoSQL processor. And for my delete issue, it appears that source has a column of type tinyint(1) and it is getting as always value 1 for sql.args.N.value na matter what value you provide.. and because of this, destination could not able to find a record matching and nothing getting deleted. (all other sql.args.values were getting picked correctly.. – santhosh Mar 24 '23 at 16:51
  • but only this tinyint column is giving different values than actual as i checked in the parameters) So I changed the type of the datatype of source to Integer and its picking correct value and getting identified in destination and delete is succesful. My source is mysql 5.7 and my destination is mysql 8.x. I accepted your answer. – santhosh Mar 24 '23 at 16:52