How to assign MySQL stored procedure return auto increment id value as flowfile attribute in Apache Nifi ?

[#1]

Hi All,

In Nifi flow, I have below stored procedure need to call in NiFi flow to get auto increment key as flow file attribute. Incoming flow file attribute file_id will have value and same is inserting into table with stored procedure call and return value as auto increment key. In MySQL dB i am able to call this procedure and getting auto increment key as return value. In NIFI, i need to call this stored procedure and get auto increment key as flow file attribute... this procedure call should not affect my existing flow file content. I need help on how to call stored procedure and how to assign return value into flow file attribute. please help me on this.

with this ExecuteSQL processor.. i am able to insert data into table.. but not able to get return value and store it into flow file attribute.

//table creation  

CREATE TABLE TRANS_ID_GENERATOR(
TRANSACTION_ID INT(11) PRIMARY KEY AUTO_INCREMENT,
FILE_ID INT(11)
);

//procedure to add file_id into table and get incremented value
BEGIN
INSERT INTO TRANS_ID_GENERATOR(FILE_ID) VALUES (100);
SELECT COUNT(TRANSACTION_ID) INTO OUT_SEQ_NUMBER FROM TRANS_ID_GENERATOR;
END

Thanks,

Rangareddy Y

[#2]

At moment you can not run stored procedure using ExecuteSQL, however, you can use ExecureScript to do the same with stored procedure wrapped in script with MySQL engine or any other client you can use please refer : https://community.cloudera.com/t5/Support-Questions/Does-ExecuteSQL-processor-allow-to-execute-store...

If you found this response assisted with your issue, please take a moment to login and click on "Accept as Solution" below this post.

Thank you