cancel
Showing results for 
Search instead for 
Did you mean: 

Issue with UPDATE_INSERT scenario

Former Member
0 Kudos

Dear experts,

I have a File to JDBC scenario. I am doing an UPDATE_INSERT statement on the DB. The target table has a 6 columns with three primary keys. Hence my target structure looks like this.

<STATEMENT_NAME>

<TABLE_NAME action="UPDATE_INSERT">

<table>TBL</table>

<access>

<col1></col1>

<col2></col2>

<col3></col3>

<col4></col4>

<col5></col5>

<col6></col6>

</access>

<key>

<col1></col1>

<col2></col2>

<col3></col3>

</key>

The scenario is working absolutely fine with regards to update and insert conditions depending on the primary keys.

The col6 needs to be a timestamp which I mapped to the currentDateTime so that it reflects when the record is updated or inserted into the DB. The issue is, when the first run is done, I get all the records as inserted in to the DB and the col6 reflects the timestamp. But when I change some records in the file (source) and run the scenario, I see that the value of col6 for all the records is updated instead of the changed records. I know that I need to do some changes in the mapping of col6 with regards to update or insert, just not sure how/what to do. Any help would be greatly appreciated.

Regards,

Accepted Solutions (1)

Accepted Solutions (1)

stefan_grube
Active Contributor
0 Kudos

> But when I change some records in the file (source) and run the scenario, I see that the value of col6 for all the records is updated instead of the changed records.

Do you mean: you change some records, but the other records remain in the file unchanged?

Former Member
0 Kudos

Yes Stefan...!!!

Former Member
0 Kudos

how are you setting the timestamp on the col6? is it through Mapping?

If it is thru mapping, you are probably changing the col6 for all the records and then updating the database... Remove those unchanged records from file and try again (to see if the update timestamp is getting updated)

Former Member
0 Kudos

Yes Kris..I am doing it with mapping..!!!

When I have the changed records only, then only those records are updated with the timestamp in the table.

But the thing is, the flat file comes from a different source which I have no control of and it might contain unchanged records too.

I am wondering how to do the mapping for this. Like, if there is a change in the record, do an update or insert else leave it as is.

Regards,

Former Member
0 Kudos

If you do not know the actual delta, One of the possible option is not to send the timestamp and let the database handle the last update timestamp (by writing a trigger function on create/change to update time)

What database areyou updating?

Former Member
0 Kudos

I am updating SQL server.

Regards,

stefan_grube
Active Contributor
0 Kudos

This is not a PI question. Discuss the whole scenario with your team lead.

In my opinion it is correct that the timestamp is changed for every record.

stefan_grube
Active Contributor
0 Kudos

> I am updating SQL server.

You could use a stored procedure.

Former Member
0 Kudos

I will try to use a trigger or a stored procedure and see how it effects and update the thread.

Regards,

Answers (2)

Answers (2)

Former Member
0 Kudos

Are you passing all the records or just the records that needs to be changed during your second run? And I presume you are filling the col6 with mapped timestamp value (and not the DB determined value)

Former Member
0 Kudos

Yes. You are right Kris. That is what I am doing.

Regards,

Edited by: Nagasatya Devarakonda on Feb 18, 2011 3:54 PM

baskar_gopalakrishnan2
Active Contributor
0 Kudos

>> just not sure how/what to do. Any help would be greatly appreciated.

Seems like this problem is at the database level. Timestamp updates when it gets updated every time. Talk to DBA and see how that column is created...

Former Member
0 Kudos

Hey Baskar,

Thanks for the reply. I have created the table myself. It is a simple varchar column.

Regards,