Support Questions

Find answers, ask questions, and share your expertise

Managing multiple insert queries

avatar
Explorer

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.

1 ACCEPTED SOLUTION

avatar
Master Guru

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.

View solution in original post

4 REPLIES 4

avatar
Master Guru

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.

avatar
Explorer

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 ?

xml_perse_1.JPG
xml_perse.JPG
 
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()});

avatar
Explorer

Thanks @Matt Burgess! What a useful solution!

avatar
New Contributor

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.