cancel
Showing results for 
Search instead for 
Did you mean: 

UPDATE_INSERT is not working

Former Member
0 Kudos

Hi Guyz,

I am having an issue while posting data in DB(DB2). I am working on PI 7.4 AEX.

If data already exist in database I want to perform update function else insert. I am using UPDATE_INSERT action in ESR. In key node I ve added all primary key fields and mapped. But I am getting error in communication channel "SqlIntegrityConstraintViolation". This error occurs when we try to duplicate values in DB. I am using UPDATE_INSERT not to try to insert duplicate values.

Please suggest

Thanks

Shivi

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi All,

I have resolved this issue. We can't have multiple access node in a single StatementName if we are doing update_insert.

So I made StatementName as 0..Unbounded ... and in a StatementName only once "access" node. StatementName will repeat based on no of rows inserted.

Thanks

Shivi

Answers (4)

Answers (4)

xinjiang_li
Active Participant
0 Kudos

Are you using batch mode? Batch mode can't be used for UPADATE_INSERT or EXECUTE.

Former Member
0 Kudos

Hi Xinjiang,

I am aware of this fact .. and I am not using batch mode.

Regards

Shivi

iaki_vila
Active Contributor
0 Kudos

Hi Shvanli,

As Mikhail said you need to try to insert a register directly in DB, Why?, because this exception indicates that an integrity constraint, foreign key, primary key or unique key, has been violated, then you need to know the exact DB table structure because you can't insert your register if you dont respect all the DB constraints implicated.

Regards.

Former Member
0 Kudos

Hi Inaki,

I know what are the PK or FK ... and I am putting them in tag <key> . So I am taking care of database but still things are not working ..

Regards

Shivi

former_member192343
Active Contributor
0 Kudos

or try to test your sql query at db side. You can use DB perspective of NWDS to connect to DB and to test sql queries.

Former Member
0 Kudos

Hi Mikhail,

Thanks for your response.

Can we update multiple rows at a time ... I mean some ll be just update and some ll be insert... Say 100 rows then 20 update and 80 Insert?

Second: what condition I should give in key mapping ? right now I am just doing a one to one mapping? Do I need to put some equal or AND OR condition?

Please suggest.

Thanks

Shivi

former_member192343
Active Contributor
0 Kudos

Can we update multiple rows at a time ... I mean some ll be just update and some ll be insert... Say 100 rows then 20 update and 80 Insert?

Yes we can.

I'm not sure if "Key" tag is case sensitive, but try it in lowercase (try "key" instead "Key")

Former Member
0 Kudos

Yeah ... I made key all small case .. but still its not working...

former_member192343
Active Contributor
0 Kudos

I meant only <Key> tag, not COD,LAND and  REG_COD

and will be better to test query at db side first, without channel.

Former Member
0 Kudos

Yes.. 🙂 it was Key .. now I made it key ... but still not working

former_member192343
Active Contributor
0 Kudos

and what is the full error text?

former_member192343
Active Contributor
0 Kudos