How to Extract table values row by row, send the column value dynamically to ExecuteSQL processor and update the another table with query start time and end time?

[#1]

I have two table in mysql
1)query

mysql> desc query;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| query_id | int(11) | YES | | NULL | |
| query_execute | varchar(500) | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+

2)queryexecute

mysql> desc queryExecutionInfo;
+-----------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------+------+-----+---------+-------+
| query_id | int(11) | YES | | NULL | |
| query_startTime | time | YES | | NULL | |
| query_endTime | time | YES | | NULL | |
+-----------------+---------+------+-----+---------+-------+

1) I read query table rows 1 by 1
record like -(1,'select * from sample')
(2,'select * from demo')
2) then send every row query_execute column value dyanamicaly to ExecuteSql processor.
3) that time update queryExecutionInfo table column query_startTime.
4) run Executesql processor query.
5) after completion of query execution update the query_endTime column value of queryExecutionInfo table.

this process continue till all the rows completed in query table.

Can someone point me at an example to get me going

[#2]

1 - ExecuteSQL -> 2 - ConvertAvroToJSON -> 3 - splitJSon -> 4 - EvaluateJsonPath -> 5 - UpdateAttribute -> 6 ExecuteSQL -> 7 - replaceText -> 8- putSQL.

1 - 2 - 3 : query your table and split by row. You will have one flowfile by row. Use $ for the JsonPathExpression in the split processor.

4 - Retrieve the query_id and the query_execute in two attributes of each flowfile (values of your properties are $.query_id and $.query_execute)

5 - Create a new attribute query_startTime with the value ${now():format('HH:mm:ss')

6 - Execute the query ${query_execute}

7 - Write your insert query : INSERT INTO queryExecutionInfo VALUES (${query_id}, ${query_startTime},{${now():format('HH:mm:ss'));

8 - Execute your insert

I think this flow works, maybe someone will have something efficient.

[#3]

Thanks Quentin for reply,

I designed the flow as you said,after the replace text the query_id,query_startTime,query_endTime is not inserting into

queryExecutionInfo table .

I attached the screen shot of replaceText and ExecuteSql processor.

Please kindly guide me for the same.

[#4]

Do you have an error ?