Managing multiple insert queries

[#1]

Hi everyone.

I have two ReplaceText Processors injecting two different insert queries into a single PutSQL Processor (my insert queries use values coming from elements of two xml files).

My goal is to make sure that if an insert fails then a rollback operation is performed on the other (assuming it doesn't fail), because I don't have to create a mismatch between the tables.

How can I obtain such a feature?

Any help will be appreciated.

[#2 · Accepted solution]

You can use PutDatabaseRecord instead of ReplaceText -> PutSQL, it will take the data itself and generate/execute the necessary SQL for your inserts. It also supports "Rollback on Failure" which should give you the error handling behavior you're looking for. You'll need to configure a RecordReader, and there isn't currently an XML RecordReader (but you can script one, see this template for an example), but however you're using ReplaceText to generate SQL, you could alternatively have it generate JSON or CSV and then configure a JsonTreeReader or CSVReader. The schema would look like the following (with your field names/types in place of the field1/field2 in the example:

{"name": "myRecord", "type": "record", "fields": [
  {"name": "field1", "type": ["null", "int"]},
  {"name": "field2", "type": ["null", "string"]} 
]}

For a comprehensive example, see Andrew Lim's CDC with Apache NiFi series.

[#3]

Hello mburgess,

I am able to extract attributes from using GetFile>SplitXml> EvaluateXQuery processor.

Now, can you please tell me how can I store these dynamic attributes to the PostgreSQL database using PutSQL processor ?

Is this the correct expression I am putting in PutSQL?

INSERT INTO alstom_radioscopy_amsterdam_blue."xml_log"(block_id,kp_begin,kp_end) VALUES (${block_id.text()},${kp_begin.text()},${kp_end.text()});

[#4]

Thanks @Matt Burgess! What a useful solution!

[#5]

Is there a way to make this work for inserts in multiple tables?  I am looking for a transactional insert to multiple tables, rollback all inserts if insert to one of  the tables fail.