question Managing multiple insert queries in Support Questions

Managing multiple insert queries

basiler82 — Thu, 01 Mar 2018 23:44:46 GMT

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.

Re: Managing multiple insert queries

mburgess — Fri, 02 Mar 2018 01:09:38 GMT

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.

Re: Managing multiple insert queries

basiler82 — Fri, 02 Mar 2018 22:02:28 GMT

Thanks @Matt Burgess! What a useful solution!

Re: Managing multiple insert queries

NitinSonawane — Wed, 11 Mar 2020 11:14:34 GMT

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.

Re: Managing multiple insert queries

DarkStar — Tue, 24 Mar 2020 12:54:26 GMT

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()});