Support Questions
Find answers, ask questions, and share your expertise

Managing multiple insert queries

Solved Go to solution

Managing multiple insert queries

New Contributor

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

Accepted Solutions
Highlighted

Re: Managing multiple insert queries

Super 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
Highlighted

Re: Managing multiple insert queries

Super 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

Highlighted

Re: Managing multiple insert queries

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

Re: Managing multiple insert queries

New Contributor

Thanks @Matt Burgess! What a useful solution!

Highlighted

Re: Managing multiple insert queries

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. 

Don't have an account?