Update_Insert functionality using JDBC adapter

[Former Member]

Dear Experts,

I have to develop one scenario where I am using "JDBC" adapter in receiver side. I have to use "Update_Insert" operation. Now the requirement is: There are two fields in the receiver table "Update_Timestamp" & "Create_Timestamp". If any "UPDATE" operation happens for any value, then " PI System_Date" will go to "Update_Timestamp" field. If "Insert" operation hapens for any value, then "PI System_Date" will go to "Create_Timestamp" field.

Please suggest how to achieve this.

Thanks in advance.

Regards,

Moumita

[Former Member]

Hi Ridvan/Arun,

Thanks for your suggestion.

I started implementig the JDBC lookup. Now I am facing one problem: While creating the external definition of the DB table in PI ESR, I am not able to see tables under schema B. I can see only tha tables under schema A. Although the user which I am using in Receiver JDBC channel, has the same access (S-I-U-D) in both the schema  of the DB.

To test it further, I tried to do "INSERT/UPDATE" operation in the tables under schema B and it was successful.

So, is there any way to slect the schema here like we did in message mapping?

Please suggest how to implement this?

Thanks & Regards,

Moumita

[former_member257758]

Hi Moumita,

If you are able to see table in one schema and not in other for the same user, then it is for sure authorization issue for that user.  Ask your DBA folks they will able figure out and help you.

thx..

Arun

[former_member257758]

Hi Moumita,

Do the JDBC lookup on the DB keyfield in the mapping if self. If you get the record, set the date to Update_timeStamp else set it to Create_TimeStamp.

[ridvan_polat]

Hi Moumita,

I think you can overcome it using jdbc lookup.

First make lookup to decide which field will be mapped.

If there is data on table, map to 'Update_Timestamp' field.

If there is no data then map to 'Create_Timestamp' field.

Regards,

Ridvan Polat

[iaki_vila]

Hi Moumita,

AFAIK the update_insert PI message works in this way: first try to do the update if there is not a register do the insert but a prior PI doesn't know if the register exits or not, then PI can't change the data that will update or will insert depending this factor.

The only way that i think right know is to do two scenarios, the first one will do Select to know if the register exists and depending of this you can do a second scenario to do the insert or the update with the data you want to.

Regards.