1

am trying to insert a record in mysql database and fetch the last inserted record on success of insertion using nifi. below is flow structure am trying.

convertJsontomysql -> putSQL(insert record) -> executeSQL (query to fetch last inserted record)

but it throws an error, I don't know how to achieve this somebody please help.

thanks in advance

sakthivel
  • 71
  • 2
  • 10
  • What is the structure of the table? – Ben Yaakobi Feb 26 '19 at 10:10
  • Hi @BenYaakobi, it's a simple table with basic fields, I can successfully insert the record on success trying to fetch the last inserted record using executeSQL but it throws an error, if I run the same query individually in executeSQL It works perfectly. – sakthivel Feb 26 '19 at 11:15
  • What are basic fields? Is there an auto increment ID field? What is the query you're trying to perform? – Ben Yaakobi Feb 26 '19 at 11:18
  • @BenYaakobi Basic fields are id, name, created_dt,modified_dt yes 'ID' is auto increment field, `SELECT 'id','name' FROM table1 WHERE id = (SELECT MAX(id) FROM table1);` the above query I tried to run in executeSQL processor – sakthivel Feb 26 '19 at 11:33
  • I've added my answer :) – Ben Yaakobi Feb 26 '19 at 13:41
  • @BenYaakobi but I can run the query I've provided in executeSQL processor standalone, if I connect it for putSQL to executeSQL at that time only am facing the problem – sakthivel Feb 26 '19 at 13:51
  • Could you provide the ExecuteSQL configuration? – Ben Yaakobi Feb 26 '19 at 14:05

2 Answers2

0

Try running query SELECT max(id), name FROM table1 instead. If you still want to use wrapper for the name, use ` instead of '(backtick instead of an apostrophe).

Ben Yaakobi
  • 1,620
  • 8
  • 22
0

Are you sure this will work? in between new records might have already been inserted into the table. So you might not get the id of the record in question.

Maybe a better solution would be to end the flow with the PutSQL and Have a second one picking up the data? You can check here to get some ideas.

Christoph Bauer
  • 88
  • 5