Can't run Merge query to update or insert SQL Server table in PUTSQL in NiFi

LKB — Wed, 04 Oct 2023 02:18:12 GMT

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 CUSDB.dbo.CUSTOMERS 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?

Re: Can't run Merge query to update or insert SQL Server table in PUTSQL in NiFi

MattWho — Thu, 05 Oct 2023 15:02:35 GMT

@LKB
Can you share screenshots of your UpdateAttribute processor configuration?
Are you using the advanced UI of the UpdateAttribute processor?

The UpdateAttribute processor is fairly simplistic in design. Without configuring the advanced UI, it simply can remove attributes or create/modify existing attributes. Each Attribute is defined by key:value pairs where the property name is the key and property value is the key. The Advanced UI allows for conditionally based attribute additions or modifications.

If you found any of the suggestions/solutions provided helped you with your issue, please take a moment to login and click "Accept as Solution" on one or more of them that helped.

Thank you,
Matt

question Re: Can't run Merge query to update or insert SQL Server table in PUTSQL in NiFi in Support Questions

Can't run Merge query to update or insert SQL Server table in PUTSQL in NiFi

Re: Can't run Merge query to update or insert SQL Server table in PUTSQL in NiFi