post data to several tables using jdbc

[Former Member]

Hello,

Is there anybody knows how to post data to several tables from one xml file using jdbc?

For example, the data in sender side will like this,

<ns1:DATA_FILESENDER xmlns:ns1="http://XI/MYSAMPLE">

<EMPID>3</EMPID>

<EMPNAME>C</EMPNAME>

<EMPAGE>6</EMPAGE>

<FLAG>Y</FLAG>

</ns1:DATA_FILESENDER>

I want to post EMPID to table1, EMPNAME and EMPAGE to talbe2, FLAG to table3. How can I set up receiver side's data type? And how can I set up message mapping, because I have three tables? Thanks in advance!

Marea

table1

[udo_martens · Accepted solution]

Hi Marea,

you dont need a message split. You can create more than one statement for update in one message.

Regards,

Udo

[Former Member · Accepted solution]

Hi, Udo,

Would you please tell me how? Thank you very much!

Marea

[Former Member · Accepted solution]

Hi Marea,

in BPM make a fork as myny as JDBC-tables you got.

In every fork there is at least

a) one transformation step

b) one send step

In ID create as many receiver determinations, interface-datenminations and receiver agreements as tables you got.

That's it.

You may also have a look in you SWC SAPBASIS, Namespace:

http://sap.com/xi/XI/System/Patterns

Regards Mario

[udo_martens · Accepted solution]

Hi Marea,

just like described in <a href="http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/frameset.htm">SAP Library: Document Formats for the Receiver JDBC Adapter</a>. Map more than one element for the statement into the root element.

Regards,

Udo

[Former Member · Accepted solution]

Hi, Mario and Udo,

Thanks for the help. I'll look at the links provided and get some knowledge about BPM and JDBC Adapter. Then come back to this scenario. Maybe still your help later. Thanks again.

Marea

[Former Member]

Your datatype, sample for three db-tables

<datatype_three_tables>

<statement for table1>

__<dbTableName>

___<attribute action> --> map to constant "insert"

___<table>--> map to constant name of the database-table

___<access>

____<field_1> --> map to source-field

____<field_2> --> map to source-field

____<field_n> --> map to source-field

<statement for table2>

__<dbTableName>

___<attribute action> --> map to constant "insert"

___<table>--> map to constant name of the database-table

___<access>

____<field_1> --> map to source-field

____<field_2> --> map to source-field

____<field_n> --> map to source-field

<statement for table3>

__<dbTableName>

___<attribute action> --> map to constant "insert"

___<table>--> map to constant name of the database-table

___<access>

____<field_1> --> map to source-field

____<field_2> --> map to source-field

____<field_n> --> map to source-field

[Former Member]

Hi, Mario,

I just finished designing my three data types and mapping the same as you described below. Then do you think I need to do something in BPM. I know nothing about it. But I really want to learn it. I just opened the SAP Library about BMP. Thank for the input.

Marea

[udo_martens]

Hi Marea,

you dont need BPM. Mario as well agreed as you see in his last answer. It was only an example for message with serveral statements, what is enough.

You can use BPM for that, but you should have good arguments for a business process, coz it's decreasing performance.

Regards,

Udo

[Former Member]

Hi Marea.

Udo is right!

You do not need BPM!

Regards Mario

[Former Member]

I see. Then how can I make condition say 'If the records already exists in the current table, then update it with the data sent, otherwise insert records into the table?

Can I do that without BPM? Thanks a lot!

Marea

[udo_martens]

Hi Marea,

action="UPDATE_INSERT"

Regards,

Udo

[Former Member]

I see. Thank you, Udo!

I just tested my scenario. FTP sends file successfuly, but the jdbc adapter says 'no massage pocessed till now' and no error showing. Do you know why?

[Former Member]

Hi Marea,

The 'To Be Delivered' status in the AE means that the message has been transferred to the messaging system and is waiting to be processed. Please double check your Integration Directory configuration - mainly your receiver determination, corresponding receiver agreement, and the receiver agreement's corresponding receiver CC(s). Make sure the CC you're looking at in the adapter monitor corresponds to the one you have configured in your scenario.

And if the CC is green and says "no messages processed until now" it is very unlikely that it has anything to do with the settings of the Channel, but rather with the ReceiverAgreement that doesn´t use the correct channel.

Regards,

Abhy

[moorthy]

Hi,

Is it shows any error in the SXMB_MONI ? if not ,

Did you check Message Monitoring in RWB ? If not check this

RWB-Message Monitoring->Message Display Tool ->Audit log

Probably jdbc message structure may have problem ..

Also make sure that, Reciever Agreement is configured for the specific sender interface correctly

Regards,

moorthy

[Former Member]

Hi, all,

I checked everything in ID and I think there is no problem here. And I went to SXMB_MONI, it shows 'problem is in mapping'.

My sender structure likes this,

.DATA_FILESENDER

.empid

.empname

.empage

.flag

My receiver stucture likes this,

.DATA_FILERECEIVER

.statement1

.zjdbc_my1

.action(value insert)

.table(value zjdbc_my1)

.access

.empid(mapping with sender-empid)

.empname(mapping with sender-empname)

.statement2

.zjdbc_my2

.action(value insert)

.table(value zjdbc_my2)

.access

.empage(mapping with sender-empage)

.statement3

.zjdbc_my3

.action(value insert)

.table(value zjdbc_my3)

.access

.flag(mapping with sender-flag)

Do you see any problem here? Thank you!

Marea

[moorthy]

Hi,

WHat is the error you are getting?

Go to SXMB_MONI-> and pick the xml and test the mapping in the repository with test tab.

Then check the error- to test the mapping-

/people/michal.krawczyk2/blog/2005/09/16/xi-how-to-test-your-mapping-in-real-life-scenarios

Regards,

Moorthy

[Former Member]

I finally got it works. Thanks Moorthy provide me the link to tell me how to test the real mapping. I found the problems there. Two things I need to remember

1. the field name for table has to be capital

2. the element name in the XML has to be axactly the same as it is in the data type definition. They are <b>case sensitive</b>.

I really learned a lot from here. Thanks everyone. I hope I can give everyone a big point.

Marea

[Former Member]

Hi,

You will have to have three message strucutre for each Table.

Here you can use multi mapping with one source and three targets.

for JDBC adapter you need to have the specified strcutre. Please refer to this for the same

http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

Check out for multi mapping

http://help.sap.com/saphelp_nw04/helpdata/en/21/6faf35c2d74295a3cb97f6f3ccf43c/content.htm

Regards

Vijaya

[Former Member]

Hi, Vijaya,

Thanks for the advise and information. I will try it and let you know if I can do it.

Marea