on 03-08-2011 12:07 PM
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..
FLAG( 0) - > Test Function(EQUALS) -> CREATEIF -> HEADER NODE OF DELETE STATEMENT STRUCTURE.
CONSTANT(0) ->
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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........
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
u r welcome ...
anytime
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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..
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
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) ?
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>
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.