how to handle duplicate entries in LAC

[Legacy User]

Hi ,

 

Lac Version: 3.1

User Case: Currently we are fetching three row from db1 and inserting the same in db2.

The Primary key on both the DB is the same req_id.

When we fetched the Row from db1 it gives us 100 to 1000 of records, the same needs to be inserted in the DB2.

At first, the API is executed and records are fetched and inserted in the db2.

The second time when we fetch records, there are some new records and some old records, when we insert the same it gives use below error

{
"statusCode": 500,
"errorCode": 5004,
"errorMessage": "Internal server error: ORA-00001: unique constraint (RCAS_NEW.PK_REQ_ID) violated\n\nrow: main:APAC_DETAILS[main:APAC_DETAILS[{REQ_ID=220348856}]] = [CRN=5898333, FT_RESPONSE_TIME=2018-12-14T06:12:24, LOAN_ACID=12345, REQ_ID=220348856, ]"
}

 

It seems that the error is due to duplicate entries we doing on the same reqid. 

What we need is that the LAC should insert the new records and discard the old records sent again.

 

For this scenarios, we are using the POST method to insert the record to the DB.

 

How we can ignore the old records sent and insert the new records?

 

 

Thanks,

Irfan

[Max.Tardiveau]

If you need to insert, but not fail if the row in question already exists, I would suggest you look at the MERGE_INSERT tag:

 

https://docops.ca.com/ca-live-api-creator/3-1/en/invoking-apis/complex-transaction-processing/merge_insert-metadata-action-tag 

 

Please let us know if that doesn't work for you.

[Legacy User]

Hi,

 

Thank you for the help.

I guess this will solve the issue i am facing.

Only one question how can we add the metadata action tag automatically to each json row data.

 

 

Thanks,

Irfan 

[Max.Tardiveau]

I'm not sure what you mean by "add the metadata action tag automatically" -- usually you have some sort of client program that send requests to your Live API Creator server. That client program would normally manipulate the JSON payload to add the metadata tag, which is usually pretty easy, depending on the programming language. Is your client written in JavaScript, Java, C#, ... ?

[Legacy User]

Hi,

 

First of all Thank you for your prompt response and helping me out.

 

This Service will be run as a Cronjob.

 

So first we will create a resource and then using function we will fetch the data from resources.

So after this we will get a json data of rows, now we need to add the action tag on the metadata. So how can we add this action data on the json payload depending upon the n number for rows?

 

 

Thanks,

Irfan 

[Max.Tardiveau]

Hi Irfan,

 

it sounds like you are using a function in Live API Creator, so you'll be using JavaScript. Manipulating a JSON payload is very easy in JavaScript, it would look something like this (I have not tested this code):

 

// We assume that jsonPayload contains a typical Live API Creator response, namely

// an array of objects, each with a @metadata section

for (var i = 0; i < jsonPayload.length; i++) {

  jsonPayload[i]["@metadata"].action = "MERGE_INSERT";
  jsonPayload[i]["@metadata"].key = "my_key_column";
}

 

That would add the action and key attributes to each object's @metadata section. You can then PUT this payload to your API.

 

Let us know if this is not what you were looking for.

[Legacy User]

Hi,

 

Thank you for your help!! 

Now I am able to insert or update the data from one table to another.

Only one thing, while customizing the JSON Packet we also need to remove the checksum and href value from the payload to work. So i did it in javascript as shown below and its working fine.

 

var jsonPayload = SysUtility.getResource("tb1", null);

for (var i = 0; i < jsonPayload.length; i++) {

jsonPayload[i]["@metadata"].action = "MERGE_INSERT";
jsonPayload[i]["@metadata"].key = "id";
}
for(var i = 0; i < jsonPayload.length; i++) {
delete jsonPayload[i]["@metadata"].checksum;
delete jsonPayload[i]["@metadata"].href;
}
var jsonPayload1=JSON.stringify(jsonPayload);

 

Once again thank a lot for the help!!

 

Thanks,

Irfan

[Max.Tardiveau]

Hi Irfan,

 

glad we were able to help -- please don't hesitate to reach out again if you have any questions.