cancel
Showing results for 
Search instead for 
Did you mean: 

Delete and Insert logic depends on Flag JDBC Receiver

Former Member
0 Kudos

Hi Experts,

My Scenario is Proxy to JDBC. Present I am uisng 2 ststements for the receiver structure one for Delete and another for Insert. When I get a message from the source system, Database will be deleted and records will be inserted. Its working great.

Now one more new FLAG filed is added in the source structure. My requirement is when i get '0' value in the flag filed, I want to delete the database and need to insert records. and If value '1' comes in the FLAG filed, records needs to be inserted in the database table with out deleting old records.

How can I achive this? thanks in Adv

Vankadoath..

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

FLAG( 0) - > Test Function(EQUALS) -> CREATEIF -> HEADER NODE OF DELETE STATEMENT STRUCTURE.

CONSTANT(0) ->

Former Member
0 Kudos

sorry I did the same as you said but its not working... just inserting data in the Database with out deleting.

Former Member
0 Kudos

in msg structure for delete part have access node and give primary key fields there so that only the rec which needs to be updated gets deleted and then inserted.............so flag 0 means updation and flag 1 means insertion........suppose u have struc -

msg struc - delete part and then insert part..................modify it like msg struc - header1 having delete and insert part and header 2 having only insert part........now based on flag value using equalS function and creatif create either header 1 and header 2.... the only thing u need to see is the delete in header 1 should have primary key fields so that all the records r not getting deleted.. then ur this scenario may work........

Former Member
0 Kudos

I am expecting on your receiver side. you have two have structure under one root node ..... One for Insert other for delete

like

root

<Structure_Insert

action >

<table></table>

<access></access>

<Structure_Insert>

<Structure_Delete

action>

<table></table>

<access></access>

<key></key>

acc to this .

FLAG(0,1) -> Structure_Insert

Summary : whatever will be on Source(Flag) Node - Insert Structure will be created.

FLAG( 0) - > Text Function(EQUALS) -> CREATEIF -> <Structure_Delete>

CONSTANT(0) ->

Summary : IF FLAG has 0 Then Only Delete Structure will create , else if 1 then 1Not equal to 0 return False Hence Stucture_Delete will not be created.

Hope i am understanding your target structure right ...

If Stil not working ... Paste Ur SOURCE AND TARGET XML STRUCTURE ... then i can help.

Regards

PS

Edited by: prabhatxi on Mar 8, 2011 8:06 PM

naveen_chichili
Active Contributor
0 Kudos

Hi Vankadoath,

Surely it will work please check the condetion properly...it will work

Cheers!!!

Naveen.

Former Member
0 Kudos

Hi Prabhat and Naveen,

Its working Great now. Thanks for ur help.

Regards

Vankadoath

Answers (3)

Answers (3)

Former Member
0 Kudos

Hi ,

Ok . What do you mean by this " Delete is not happening "????

Can you see the Delete Statement Node on your target generated Structure ???

Or is it Structure is getting generated and Database is not actually deleteing data ??????

See What is key structure ...

Key is basically for where clause of JDBC Statement .

Example : Delete from Tablename where ColumnName = "PRABHAT"

So there Where clause is Key like

<KEY>

<COLUMNNAME>PRABHAT</COLUMNNAME>

</KEY>

Now lets say if you dont have key element .. entire rows from table will be deleted

Regards

PS

Former Member
0 Kudos

Are you having your delete structure like this

<StatementName3>

<dbTableName action=u201DDELETEu201D>

<table> tableName</table>

<key1>

<col2>val2old</col2>

<col4>val4</col4>

</key1>

</dbTableName>

</StatementName3>

Regards

Prabhat Sharma

Former Member
0 Kudos

If you dont want to keep the key element in structure . Go to JDBC receiver adapter . There you find .one option Check Box . Mandatory Key element.

This can help in remoe your key element .

Former Member
0 Kudos

HI Prabhat thanks for the replies.

its working fine when proxy triggers from source system. what we did earlier is correct.

The problem is in the payload which is coming from the source system. for the flag value its getting 0 with space. its corrected now and its working great now.

Thanks

Former Member
0 Kudos

u r welcome ...

anytime

Former Member
0 Kudos

HI Prabhat..

When i test from PI(RWB) its working great.. but when proxy triggers from source system its given the same problem as before.

in the Proxy payload for the flag value is 0, by mapping it has to be delete the table, but its not deleting the table just inserting records.

My structure

root

_<StatementDel>

__<EMP_QUAL>

___<action>

___<table>

___<access>

____....

____....

____.....

_<StatementDel>

__<EMP_QUAL>

___<action>

___<table>

___<access>

____....

____....

____.....

XLM Inputt

<?xml version="1.0" encoding="utf-8" ?>

- <n0:RedMatchDB_EmployeesQualification_MT_OB xmlns:n0="ness.com:RedMatchDB_EmployeesQualifications" xmlns:prx="urn:sap.com:proxy:DV3:/1SAI/TAS8CFD98F20F1539B552C4:701:2009/02/10">

- <EMP_QUALIFICATION>

<Flag>0</Flag>

<PERNR>00000123</PERNR>

<QGROUP_ID>40001055</QGROUP_ID>

<RATING>0005</RATING>

</EMP_QUALIFICATION>

- <EMP_QUALIFICATION>

<Flag>1</Flag>

<PERNR>00000123</PERNR>

<QGROUP_ID>40001109</QGROUP_ID>

<RATING>0004</RATING>

</EMP_QUALIFICATION>

</n0:RedMatchDB_EmployeesQualification_MT_OB>

XLM output

<?xml version="1.0" encoding="UTF-8" ?>

- <ns0:RedMatchDB_EmployeesQualification_MT_IB xmlns:ns0="ness.com:RedMatchDB_EmployeesQualifications">

- <statementINSERT>

- <EMP_QUALIFICATION action="INSERT">

<table>EMP_QUALIFICATION</table>

- <access>

<EmployeeID>00000123</EmployeeID>

<QID>40001055</QID>

<ProficiencyID>0005</ProficiencyID>

</access>

</EMP_QUALIFICATION>

</statementINSERT>

- <statementINSERT>

- <EMP_QUALIFICATION action="INSERT">

<table>EMP_QUALIFICATION</table>

- <access>

<EmployeeID>00000123</EmployeeID>

<QID>40001109</QID>

<ProficiencyID>0004</ProficiencyID>

</access>

</EMP_QUALIFICATION>

</statementINSERT>

</ns0:RedMatchDB_EmployeesQualification_MT_IB>

dont know where its going wrong..

Thanks In Adv..

rajasekhar_reddy14
Active Contributor
0 Kudos

Hi,

The logic what you have done seems to be not correct.

First check you mapping with ABAP proxy data and see in test data is it working as you excpected or not.

Regards,

Raj

Former Member
0 Kudos

HI Raja,

while testing with the proxy data table is not deleting only records are inserting.

I used the logic in mapping as

FLAG( 0) - > Test Function(EQUALS) -> CREATEIF -> HEADER NODE OF DELETE STATEMENT STRUCTURE.

CONSTANT(0) ->

Is key element is mandatory under the statement delete? i have not added the key element.

how to do mapping with this key element from which source field to which target. how to implement the logic depending on the Flag value?

Thanks

rajasekhar_reddy14
Active Contributor
0 Kudos

key field is mandatory for Update and Delete operations.

Former Member
0 Kudos

Hi Raja

I added Key fields under the statement delete structure and added all the three fields under the key filed. can you tell me how to do mapping here.

Former Member
0 Kudos

The key field is the one which can uniquely identify a record in the target DB table (the key node specifies the where criteria of SQL statement) , in your case i guess it will be PERNR.

also, have you created 2 structure (DT's) for the insert and delete or using only one (with value of action chaging with the value of flag) ?

Former Member
0 Kudos

HI Puneeth,

I am using both the Delete and Insert statements in one DT.

Flag is a filed name which is in the source DT . if '0' comes in this Flag then tables needs to be deleted and records needs to be Insert. If value 1comes only records needs to be Inserted.

how to do mapping with the key filed?

This is my Input xml

<?xml version="1.0" encoding="utf-8" ?>

- <n0:RedMatchDB_EmployeesQualification_MT_OB xmlns:n0="ness.com:RedMatchDB_EmployeesQualifications" xmlns:prx="urn:sap.com:proxy:DV3:/1SAI/TAS8CFD98F20F1539B552C4:701:2009/02/10">

- <EMP_QUALIFICATION>

<Flag>0</Flag>

<PERNR>7106413</PERNR>

<QGROUP_ID>40001055</QGROUP_ID>

<RATING>0005</RATING>

</EMP_QUALIFICATION>

- <EMP_QUALIFICATION>

<Flag>1</Flag>

<PERNR>7106414</PERNR>

<QGROUP_ID>40001109</QGROUP_ID>

<RATING>0004</RATING>

</EMP_QUALIFICATION>

- <EMP_QUALIFICATION>

<Flag>1</Flag>

<PERNR>7106415</PERNR>

<QGROUP_ID>40001154</QGROUP_ID>

<RATING>0004</RATING>

</EMP_QUALIFICATION>

</n0:RedMatchDB_EmployeesQualification_MT_OB>

This is the output xml deleting is nat happening only records are Inserting.

<?xml version="1.0" encoding="UTF-8" ?>

- <ns0:RedMatchDB_EmployeesQualification_MT_IB xmlns:ns0="ness.com:RedMatchDB_EmployeesQualifications">

- <statementDELETE>

- <EMP_QUALIFICATION action="DELETE">

<table>EMP_QUALIFICATION</table>

<access />

<key />

</EMP_QUALIFICATION>

</statementDELETE>

- <statementINSERT>

- <EMP_QUALIFICATION action="INSERT">

<table>EMP_QUALIFICATION</table>

- <access>

<EmployeeID>7106413</EmployeeID>

<QID>40001055</QID>

<ProficiencyID>0005</ProficiencyID>

</access>

</EMP_QUALIFICATION>

</statementINSERT>

- <statementINSERT>

- <EMP_QUALIFICATION action="INSERT">

<table>EMP_QUALIFICATION</table>

- <access>

<EmployeeID>7106414</EmployeeID>

<QID>40001109</QID>

<ProficiencyID>0004</ProficiencyID>

</access>

</EMP_QUALIFICATION>

</statementINSERT>

- <statementINSERT>

- <EMP_QUALIFICATION action="INSERT">

<table>EMP_QUALIFICATION</table>

- <access>

<EmployeeID>7106415</EmployeeID>

<QID>40001154</QID>

<ProficiencyID>0004</ProficiencyID>

</access>

</EMP_QUALIFICATION>

</statementINSERT>

</ns0:RedMatchDB_EmployeesQualification_MT_IB>

Former Member
0 Kudos

CreateIf will be function for your requirement.

Createif - > if Source Feild is comig with TRUE , then target structure will be created else not .

Using TEXT - EQUALS FUNCTION( Return true if condition satisfied else false) -> if true comes - next create if function will work and create delete structure , else for false createif works otherway around and will not create delete structure /.

Hope it helps .

if want more clearification please ask

Regards

PS

Former Member
0 Kudos

Thanks Prabhat,

can you please explain how to map for which source filed to which target filed.

Flag is a filed under a node in source structure. it will get 0 or 1 in this filed. depending on 0 or 1 the logic should work. I am trying as

Flag filed = 0 (0 in constant) is mapped with input of IF (if else not) and then is mapped with empty constant. the out put of if else not is mapped to first 1st Statement delete. and the root node of the source is not mapped to any filed for the 1st statement delete.

and the root node of the source is mapped to the 2nd statement Insert.

with this I am able to Insert fields in the database with out deleting old records.