PutSQL question

["Perko, Ralph J" <Ra...@pnnl.gov>]

Hi

I want to use the PutSQL processor to execute an insert statement. The use case is straightforward.  I need to pull an id attribute from a JSON message and write it to a database.  I am using EvaluateJSONPath to grab the id but I’m hung up on how to then pass the SQL statement as the content.  I could put the SQL statement in a file to be loaded as content but I am wondering if there is a way to do this inline with the flow? Ideally I would have the content of the FlowFile be the SQL and pass in the id as a SQL parameter as mentioned in the documentation.

Thanks for your help,
Ralph

Re: PutSQL question

[Joe Percivall <jo...@yahoo.com>]

Hello Ralph,
A common way to replace the contents of a FlowFile with specific text is to use the ReplaceText processor. The default search query will match the entire content and replace it with whatever your replacement value is. So if you set the replacement value (complete with expression language usage) to the query you want the FlowFile content will become the query.
In order to do that you need to have the ID from the JSON message as a FlowFile attribute. You will need to use ExtractText [1] to get the ID out of the content. If the JSON tag is unique (found via regex), you could forgo the EvaluateJSONPath processor and just use the ExtractText processor.  [1] https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi.processors.standard.ExtractText/index.html
Hope that helps,Joe- - - - - - Joseph Percivalllinkedin.com/in/Percivalle: joepercivall@yahoo.com
 

    On Wednesday, January 13, 2016 3:16 PM, "Perko, Ralph J" <Ra...@pnnl.gov> wrote:
 

  Hi 
I want to use the PutSQL processor to execute an insert statement. The use case is straightforward.  I need to pull an id attribute from a JSON message and write it to a database.  I am using EvaluateJSONPath to grab the id but I’m hung up on how to then pass the SQL statement as the content.  I could put the SQL statement in a file to be loaded as content but I am wondering if there is a way to do this inline with the flow? Ideally I would have the content of the FlowFile be the SQL and pass in the id as a SQL parameter as mentioned in the documentation.  
Thanks for your help,Ralph


  

Re: PutSQL question

["Perko, Ralph J" <Ra...@pnnl.gov>]

Mark and Joe – Thanks for the quick replay – that did the trick.  Worked great.

From: Mark Payne <ma...@hotmail.com>>
Reply-To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
Date: Wednesday, January 13, 2016 at 12:25 PM
To: "users@nifi.apache.org<ma...@nifi.apache.org>" <us...@nifi.apache.org>>
Subject: Re: PutSQL question

Ralph,

Hello. An easy way to do this is to use ReplaceText to set the content of the FlowFile.
You can set the Replacement Strategy to "Always Replace" and set the Replacement Value
to something like: INSERT INTO MY_TABLE (ID) VALUES ('${id}')

You could also use SQL parameters (though it's slightly more involved), and use ReplaceText, setting the Replacement
Value to: INSERT INTO MY_TABLE (ID) VALUES (?)

This would then require you to use UpdateAttribute to create the necessary parameters. You would
need to add the following attributes:

sql.args.1.value    => This is the value to insert, so ${id}
sql.args.1.type      => This will be the JDBC Type, which for a VARCHAR is 12.

So you would add two user-defined properties with UpdateAttribute:

Property Name         Property Value
--------------------         ----------------------
sql.args.1.value        ${id}
sql.args.1.type          12

Does this all make sense?

Thanks
-Mark


On Jan 13, 2016, at 3:16 PM, Perko, Ralph J <Ra...@pnnl.gov>> wrote:

Hi

I want to use the PutSQL processor to execute an insert statement. The use case is straightforward.  I need to pull an id attribute from a JSON message and write it to a database.  I am using EvaluateJSONPath to grab the id but I’m hung up on how to then pass the SQL statement as the content.  I could put the SQL statement in a file to be loaded as content but I am wondering if there is a way to do this inline with the flow? Ideally I would have the content of the FlowFile be the SQL and pass in the id as a SQL parameter as mentioned in the documentation.

Thanks for your help,
Ralph

Re: PutSQL question

[Mark Payne <ma...@hotmail.com>]

Ralph,

Hello. An easy way to do this is to use ReplaceText to set the content of the FlowFile.
You can set the Replacement Strategy to "Always Replace" and set the Replacement Value
to something like: INSERT INTO MY_TABLE (ID) VALUES ('${id}')

You could also use SQL parameters (though it's slightly more involved), and use ReplaceText, setting the Replacement
Value to: INSERT INTO MY_TABLE (ID) VALUES (?)

This would then require you to use UpdateAttribute to create the necessary parameters. You would
need to add the following attributes:

sql.args.1.value    => This is the value to insert, so ${id}
sql.args.1.type      => This will be the JDBC Type, which for a VARCHAR is 12.

So you would add two user-defined properties with UpdateAttribute:

Property Name         Property Value
--------------------         ----------------------
sql.args.1.value        ${id}
sql.args.1.type          12

Does this all make sense?

Thanks
-Mark


> On Jan 13, 2016, at 3:16 PM, Perko, Ralph J <Ra...@pnnl.gov> wrote:
> 
> Hi 
> 
> I want to use the PutSQL processor to execute an insert statement. The use case is straightforward.  I need to pull an id attribute from a JSON message and write it to a database.  I am using EvaluateJSONPath to grab the id but I’m hung up on how to then pass the SQL statement as the content.  I could put the SQL statement in a file to be loaded as content but I am wondering if there is a way to do this inline with the flow? Ideally I would have the content of the FlowFile be the SQL and pass in the id as a SQL parameter as mentioned in the documentation.  
> 
> Thanks for your help,
> Ralph
>