attributes to sql

[#1]

Hi All,

I have 3 attributes X, Y, Z.

I want to execute an insert statement "insert into Table(X, Y, Z) values(${X}, ${Y}, ${Z})

I am thinking of using PutSQL. but I have no idea how to build this insert statement which should go to PutSQL as flowfile. Can you please help me?.

Thanks in advance.

[#2]

@Subrahmanya Oruganti

To use PutSQL you need to pass it the built SQL string, as you've already figured. One processor you can use for that is the ReplaceText processor. You can use regex to parmeterize and create the output string using the "Search Value" and "Replacement Value" fields.

To find out more about the ReplaceText processor see the link below.

http://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi/nifi-standard-nar/1.3.0/org.apache....

As always, if you find this post helpful, don't forget to "accept" answer.

[#3]

If you want to use a PreparedStatement (which can improve performance if PutSQL operates on a number of records up to the specified Batch Size), then your SQL could be:

insert into Table(X, Y, Z) values(?,?,?)

To use this you'd have to set up your attributes as follows:

sql.args.1.value=<value of X>
sql.args.1.type=<type of X's value, see here for numerical values of datatypes>sql.args.2.value=<value of Y>sql.args.2.type=<type of Y's value>sql.args.3.value=<value of Z>sql.args.3.type=<type of Z's value>

If you know the types of the columns, you can do all this work with a single UpdateAttribute processor before the PutSQL processor. For example, if X and Y were integers and Z was a string/varchar:

sql.args.1.value=${X}sql.args.1.type=4sql.args.2.value=${Y}sql.args.2.type=4sql.args.3.value=${Z}sql.args.3.type=12

See the PutSQL documentation (especially the "Reads Attributes" section) for more information.

If you don't know the types or order of your columns, then I recommend @Eyad Garelnabi's solution instead.

[#4]

@mburgess 

I am trying to run a merge query in PUTSQL after using ExecuteSQL to pull from source database. I then pass the results through ConvertAvrotoJSON and then to convertJSONToSQL (set to INSERT since there is no UPSERT for SQL server database). I want to now run the merge query to update if the primary key is matched or insert if not, but I have been getting all sorts of errors

Input flowfile has the following attributes

sql.args.1.type
12
sql.args.1.value
A-001
sql.args.2.type
12
sql.args.2.value
DDD
sql.args.3.type
12
sql.args.3.value
2B
sql.args.4.type
12
sql.args.4.value
AAA
sql.args.5.type
12
sql.args.5.value
2B
sql.args.6.type
12
sql.args.6.value
BBB
sql.args.7.type
93
sql.args.7.value
1954-08-01 22:46:50.73
sql.catalog
CUSDB
sql.table
CUSTOMERS
uuid
50a3daf9-e5d8-422b-b6cb-9c92da723c55

I created the following attributes in UpdateAtributes

I directed this flowfile to UpdateAttribute where I created above properties like this 

 sql.args.1.type = 12;  sql.args.1.value = ${vkey}

sql.args.2.type = 12;  sql.args.2.value = ${AP}

sql.args.3.type = 12;  sql.args.3.value = ${BP}

........

sql.args.7.type = 93;  sql.args.7.value = ${LAST_MOD_DATE}

But when I check the results of the queue of the UpdateAtributes, I see that sql.args.1.type, sql.args.2.type, ... sql.args.7.type return blank values. I am doing something wrong in the way I have defined those properties?

Merge query in PutSQL looks like this

MERGE INTO RCDB.dbo.RCDB_WELLHEADER_HZ_NIFI_V2 as t
USING (select ? as vkey, ? as ap, ? as bp, ? as lm, ? as lnum, ? as wn, ? as LAST_MOD_DATE) s
ON t.EKEY = s.EKEY
WHEN MATCHED THEN
UPDATE SET
t.ap = s.ap,
t.bp = s.bp,
t.lm = s.lm,
t.lnum = s.lnum,
t.wn = s.wn,
t.LAST_MOD_DATE = s.LAST_MOD_DATE
WHEN NOT MATCHED THEN
INSERT (vkey, ap, bp, lm, lnum, wn, LAST_MOD_DATE) VALUES (?, ?, ?, ?, ?, ?, to_date(?, 'YYYY-MM-DD HH34:MI:SS'))

I get the error that all parameters are blank. Can someone please point me in the direction to resolve this issue. Why are the attributes going blank when I set them in UpdateAttributes?